Couldn't retrieve cell value in Excell by apache POI

Look at the below code,

-------------------------------------------------------------------------------------------------------

import java.io.FileOutputStream;
import java.io.IOException;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;

public class test {

    public static void main(String[] args) throws IOException  {
        // Create a workbook
        Workbook workbook = new HSSFWorkbook();
        // Create new sheet for workbook
        Sheet sheet = workbook.createSheet("Sheet one");
        // Create row at 3
        Row row = sheet.createRow(3);
        Row row2 = sheet.createRow(4);
        Row row3 = sheet.createRow(5);


        row.createCell(7).setCellType(HSSFCell.CELL_TYPE_NUMERIC);
        row.getCell(7).setCellValue(276.89);

        row2.createCell(7).setCellType(HSSFCell.CELL_TYPE_NUMERIC);
        row2.getCell(7).setCellValue(500);
        row3.createCell(7);
        row3.getCell(7).setCellFormula("SUM(H4:H5)");

        row.createCell(8).setCellType(HSSFCell.CELL_TYPE_NUMERIC);
        row.getCell(8).setCellValue(row3.getCell(7).getNumericCellValue());

        // Write the output to a file
        FileOutputStream fileOut = new FileOutputStream(
                "test.xls");
        workbook.write(fileOut);
        fileOut.close();

    }

}

-------------------------------------------------------------------------------------------------------

out come of above code

In the above created test.xls you will notice that the sum value 77.89 is displayed as 0 in cell I4 though I called row3.getCell(7).getNumericCellValue() method.

Later I realised that its because that cell H6 is i used here as a cell with a SUM formula. So I need to evluate it's value first before I get its value.

Finally the amended code is as below. 

-------------------------------------------------------------------------------------------------------

import java.io.FileOutputStream;

import java.io.IOException;

import org.apache.poi.hssf.usermodel.HSSFCell;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import org.apache.poi.ss.usermodel.Cell;

import org.apache.poi.ss.usermodel.CellStyle;

import org.apache.poi.ss.usermodel.FormulaEvaluator;

import org.apache.poi.ss.usermodel.IndexedColors;

import org.apache.poi.ss.usermodel.Row;

import org.apache.poi.ss.usermodel.Sheet;

import org.apache.poi.ss.usermodel.Workbook;

public class test {

    public static void main(String[] args) throws IOException  {

        // Create a workbook

        Workbook workbook = new HSSFWorkbook();

        // Create new sheet for workbook

        Sheet sheet = workbook.createSheet("Sheet one");

        // Create row at 3

        Row row = sheet.createRow(3);

        Row row2 = sheet.createRow(4);

        Row row3 = sheet.createRow(5);

        row.createCell(7).setCellType(HSSFCell.CELL_TYPE_NUMERIC);

        row.getCell(7).setCellValue(276.89);

        row2.createCell(7).setCellType(HSSFCell.CELL_TYPE_NUMERIC);

        row2.getCell(7).setCellValue(500);

        row3.createCell(7);

        row3.getCell(7).setCellFormula("SUM(H4:H5)");

        FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();

        Cell cell2 = row3.getCell(7);

        evaluator.evaluateFormulaCell(cell2);

        row.createCell(8).setCellType(HSSFCell.CELL_TYPE_NUMERIC);

        row.getCell(8).setCellValue(cell2.getNumericCellValue());

        // Write the output to a file

        FileOutputStream fileOut = new FileOutputStream(

                "test.xls");

        workbook.write(fileOut);

        fileOut.close();

    }

}

-------------------------------------------------------------------------------------------------------

out come of above code

Resources :

http://poi.apache.org/spreadsheet/eval.html

http://www.java-connect.com/category/apache-poi-tutorials/