/*
 * クラス名: Formula.java
 * 作成者  : Utpal Shahane
 * 作成日  : 2005/10/27
 * 概要    : TODO
 * 履歴    : 
 */

package XL;

import java.io.FileOutputStream;
import java.io.IOException;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.CellReference;

  /**
   * Simple test class.
   *  <at  version 1.0
   */
  public class Formula {
      
      private HSSFWorkbook wb;
      private HSSFSheet sheet;
      
      public Formula(){
          this.wb = new HSSFWorkbook();
          this.createSheet();
      }
  
      private void createSheet(){
          this.sheet = this.wb.createSheet();
          this.createCells();
      }
      
      private void createCells(){
          HSSFRow r = null;
          HSSFCell c = null;
          
          HSSFCell formula = null;
          
          this.sheet.createRow(0);
          this.sheet.createRow(1);
          this.sheet.createRow(2);
         
          c = this.sheet.getRow(0).createCell((short)1);
          c.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
         
 
          this.sheet.getRow(0).getCell((short)1).setCellValue(10);
          c = this.sheet.getRow(1).createCell((short)1);
          c.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
         
 
          this.sheet.getRow(1).getCell((short)1).setCellValue(10);
          c = this.sheet.getRow(2).createCell((short)1);
          c.setCellType(HSSFCell.CELL_TYPE_FORMULA);
          c.setCellFormula("COUNTIF(B1:B2, 10)");
          
          HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(this.sheet,this.wb);
          
          CellReference cellReference = new CellReference(2,1);
          HSSFRow row = sheet.getRow(cellReference.getRow());
          HSSFCell cell = row.getCell(cellReference.getCol());
       
          if (cell == null){
              System.out.println("its null");
          } else if (cell.getCellType() == HSSFCell.CELL_TYPE_FORMULA) {
              System.out.println("Formula : "+ cell.getCellFormula());
          }
          evaluator.setCurrentRow(row);

          HSSFFormulaEvaluator.CellValue cellValue = evaluator.evaluate(cell);
  
         
          System.out.println(cellValue.getNumberValue());
          writeExcel();
      }
      private void writeExcel(){
      	 // Write the output 
		try{
			FileOutputStream fOut = new FileOutputStream("C:/eclipse/workspace/wms/WEB-INF/src/XL/FormulaTest.xls");
			// Write the XL sheet
			wb.write(fOut);
			fOut.flush();
			
			fOut.close();
		}catch(Exception e) {
			System.out.println("!!BANG!! CreateXL() : " + e );
			e.printStackTrace();
		}
      }
      public static void main(String args[]){
          Formula f = new Formula();
  		try {
			Process p = Runtime.getRuntime().exec("cmd.exe /C \"C:/eclipse/workspace/wms/WEB-INF/src/XL/FormulaTest.xls\"");
			int e = p.waitFor();
		} catch (IOException e) {
			e.printStackTrace();
		} catch (InterruptedException e) {
			e.printStackTrace();
		}
      }
  }