Menu
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.
561
The Evaluate Formula feature walks you through each argument in a formula to help identify and fix any mistakes. You can also use it to understand complex formulas, seeing how each part of a nested function is calculated to reach the final result.
POI stands For “Poor Obfuscation Implementation”. Apache POI is an API provided by Apache foundation which is a collection of different java libraries. These libraries gives the facility to read, write and manipulate different Microsoft files such as excel sheet, power-point, and word files.
public interface FormulaEvaluator. Evaluates formula cells. For performance reasons, this class keeps a cache of all previously calculated intermediate cell values. Be sure to call clearAllCachedResultValues() if any workbook cells are changed between calls to evaluate~ methods on this class.
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.
60
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
