Recalculate formulas in a spreadsheet using Apache POI

Question

I am trying to use the XSSF POI to evaluate some Excel formulas. The values ​​do not need to be stored, and I may have to calculate many formulas, so I am trying to do all this in the same cell.

The problem is that the cell value seems to be stuck in the first formula introduced even after I recounted

FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator(); XSSFCell formulaCell = row.createCell(6); formulaCell.setCellFormula("Date(2011,10,6)"); CellValue cellValue = evaluator.evaluate(formulaCell); System.out.println(cellValue.getNumberValue()); formulaCell.setCellFormula("Date(1911,3,4)"); cellValue = evaluator.evaluate(formulaCell); System.out.println(cellValue.getNumberValue()); 

Conclusion 40822.0 40822.0 (exceeds the equivalent of 10/6/2011) both times instead of reevaluating the new formula.

Answer 1

If you use formulaEvaluator more than once, you need this line between uses, or each time it uses the same result.

 formulaEvaluator.clearAllCachedResultValues() 

Answer 2

FormulaEvaluator caches calculated cell values ​​to speed up processing. If you are updating cells after creating an evaluator, you need to say so!

See the FormulaEvaluator documentation for more details . For you, try:

 formulaCell.setCellFormula("Date(1911,3,4)"); evaluator.notifySetFormula(formulaCell); cellValue = evaluator.evaluate(formulaCell); System.out.println(cellValue.getNumberValue()); 

Answer 3

You can use the following steps to do your job. These are two solutions from which you can use any one function. He is evaluating a complete workbook, so any formula you use will be evaluated. Hope this helps.

1) .evaluateAll () evaluator; 2) XSSFFormulaEvaluator.evaluateAllFormulaCells (wb);