/**
 * Copyright (c) 2003-2006 System Integrator Corporation.
 *                 All Rights Reserved.
 */
package jp.co.sint.tools;

import java.io.*;

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.poifs.filesystem.*;

import javax.servlet.http.HttpServletResponse;


/**
 * @version $Id : SIPoi.java,v 1.0 Exp $
 * @author : Naotaka Ohsugi
 * @description :Jakarta POIを用いたxls形式のファイルを扱うクラス<br>
 * @history 
 * Author&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Date&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Reason<br>
 * ============= ========= ===========================<br>
 * Naotaka Ohsugi 2006/02/09 Original
 */

public class SIPoi{

  public static void outputPOISample(String filePath, String fileName, HttpServletResponse response){
    try {
    //ファイルの読み込み
      POIFSFileSystem filein = new POIFSFileSystem(new FileInputStream(filePath));

      HSSFWorkbook workBook = SIPoiUtil.getWorkBook(filein);
      HSSFSheet newSheet = SIPoiUtil.getSheet("sample");
      //HSSFSheet newSheet = SIPoiUtil.createSheet(1,"サンプル");
      String[] cellValues = new String[] { "文字列１", "文字列２", "文字列３"};
      String[] cellValues2 = new String[] { "HAIR", "DOUBLE", "MEDIUM", "THIN", "DASH_DOT", "文字化け対応　１−２−３　�T�U�V　�@�A�B　�括窮戟@�i�a�g"};
      String[] cellValues3 = new String[] { "DIAMONDS/AQUA", "SQUARES/GOLD", "SOLID_FOLEGROUND/CORAL", "THICK_VERT_BANDS/LIGHT_ORANGE", "THICK_FORWARD_DIAG/LIGHT_BLUE","BRICKS/LIME"};
      for (int i = 0; i < 3; i++) {
        HSSFRow newRow = newSheet.createRow(i);                        //新規「行」を作成
        newRow.setHeightInPoints(11);                                  //行の高さを設定（ポイント）
        HSSFCell newCell1 = newRow.createCell((short) 0);             //新規「セル」を作成
        HSSFCell newCell2 = newRow.createCell((short) 1);
        newCell1.setCellValue(i + 1);                                  //各セルに「値」を追加
        newCell2.setEncoding(HSSFCell.ENCODING_UTF_16);
        newCell2.setCellValue(cellValues[i]);
      }
      for (int j = 5; j < 11; j++){
        HSSFRow newRow2 = newSheet.createRow(j);
        newRow2.setHeightInPoints(40);
        HSSFCell newCell1 = newRow2.createCell((short) 0);
        HSSFCell newCell2 = newRow2.createCell((short) 1);
        newCell1.setEncoding(HSSFCell.ENCODING_UTF_16);
        newCell1.setCellValue(cellValues2[j-5]);
        newCell2.setCellValue(cellValues3[j-5]);
      }
      HSSFCellStyle cellStyle1 = workBook.createCellStyle();           //新規スタイルを作成
      HSSFCellStyle cellStyle2 = workBook.createCellStyle();
      HSSFCellStyle cellStyle3 = workBook.createCellStyle();
      HSSFCellStyle cellStyle4 = workBook.createCellStyle();
      HSSFCellStyle cellStyle5 = workBook.createCellStyle();
      HSSFCellStyle cellStyle11 = workBook.createCellStyle();
      HSSFCellStyle cellStyle12 = workBook.createCellStyle();
      HSSFCellStyle cellStyle13 = workBook.createCellStyle();
      HSSFCellStyle cellStyle14 = workBook.createCellStyle();
      HSSFCellStyle cellStyle15 = workBook.createCellStyle();
      HSSFCellStyle cellStyle16 = workBook.createCellStyle();
      SIPoiUtil.setBorderB(cellStyle1,HSSFCellStyle.BORDER_HAIR,HSSFColor.AQUA.index);      //スタイルに「罫線」を追加
      SIPoiUtil.setBorderB(cellStyle2,HSSFCellStyle.BORDER_DOUBLE,HSSFColor.GOLD.index);
      SIPoiUtil.setBorderB(cellStyle3,HSSFCellStyle.BORDER_MEDIUM,HSSFColor.CORAL.index);
      SIPoiUtil.setBorderB(cellStyle4,HSSFCellStyle.BORDER_THIN,HSSFColor.LIGHT_ORANGE.index);
      SIPoiUtil.setBorderB(cellStyle5,HSSFCellStyle.BORDER_DASH_DOT,HSSFColor.LIGHT_BLUE.index);
      SIPoiUtil.setFill(cellStyle11,HSSFCellStyle.DIAMONDS,HSSFColor.AQUA.index);
      SIPoiUtil.setFill(cellStyle12,HSSFCellStyle.SQUARES,HSSFColor.GOLD.index);
      SIPoiUtil.setFill(cellStyle13,HSSFCellStyle.SOLID_FOREGROUND,HSSFColor.CORAL.index);
      SIPoiUtil.setFill(cellStyle14,HSSFCellStyle.THICK_VERT_BANDS,HSSFColor.LIGHT_ORANGE.index);
      SIPoiUtil.setFill(cellStyle15,HSSFCellStyle.THICK_FORWARD_DIAG,HSSFColor.LIGHT_BLUE.index);
      SIPoiUtil.setFill(cellStyle16,HSSFCellStyle.BRICKS,HSSFColor.LIME.index);
      SIPoiUtil.setStyle(5,0,cellStyle1);                              //セルにスタイルを追加
      SIPoiUtil.setStyle(6,0,cellStyle2);
      SIPoiUtil.setStyle(7,0,cellStyle3);
      SIPoiUtil.setStyle(8,0,cellStyle4);
      SIPoiUtil.setStyle(9,0,cellStyle5);
      SIPoiUtil.setStyle(5,1,cellStyle11);
      SIPoiUtil.setStyle(6,1,cellStyle12);
      SIPoiUtil.setStyle(7,1,cellStyle13);
      SIPoiUtil.setStyle(8,1,cellStyle14);
      SIPoiUtil.setStyle(9,1,cellStyle15);
      SIPoiUtil.setStyle(10,1,cellStyle16);

      HSSFCellStyle cellStyle6 = workBook.createCellStyle();
      HSSFFont font = workBook.createFont();                           //フォント作成
      SIPoiUtil.setFontPMin(font,10,"bold");
      font.setColor(HSSFColor.INDIGO.index);                           //色
      font.setStrikeout(false);                                       //取消線
      font.setTypeOffset(HSSFFont.SS_NONE);                            //上下付き文字
      font.setUnderline(HSSFFont.U_NONE);                              //下線
      font.setItalic(false);                                          //斜体
      cellStyle6.setFont(font);
      cellStyle6.setWrapText(true);
      SIPoiUtil.setStyle(10,0,cellStyle6);

      newSheet.setColumnWidth((short)0,(short)(256*20));              //列の幅の指定（列の位置,幅[文字サイズ/256]）
      newSheet.setColumnWidth((short)1,(short)(256*40));
      newSheet.setProtect(true);                                       //シート保護

      //作成したワークシートの書き出し
      response.setHeader("Content-Disposition","attachment;filename="+fileName+".xls");
      response.setContentType("application/vnd.ms-excel");
      workBook.write(response.getOutputStream());
    }catch(Exception e){
      e.printStackTrace();
    }finally{
    }
  }
}