Poi Formula evaluation

Question

I am facing difficulties while reading formula cell values with POI

I have created formula cell as follows:

cell.setCellType(HSSFCell.CELL_TYPE_FORMULA); 
cell.setCellFormula("SUM(D8..F8)");

And I am reading it as follow:

double formulaCellValue = row.getCell((short) 7).getNumericCellValue();

When read this way(using getNumericCellValue()), I am always getting the value of 0.0.

Any quick help in this regard would be highly appriciated.

Answer 1

After you're done setting all your formulas, you need to trigger a recalculation. See the why evaluate docs for the background on why you need to do it, and the Evaluation docs for details. Quick answer is you'd likely want to do something like:

HSSFFormulaEvaluator.evaluateAllFormulaCells(workbook);

Don't call it until you're done adding / editing all your cells though! You can also trigger the evaluation of just one cell, see the docs for details of the various options if you need full control.