javaexcelapache-poixssfhssf

Recalculating formulas in spreadsheet using Apache POI

I'm trying to use POI XSSF to evaluate some Excel formulas. The values do not have to be saved, and I may have to calculate many formulas, so I'm trying to do it all in the same cell.

The problem is that the cell value seems to get stuck on the first formula entered even after I recalculate

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());

This outputs 40822.0 40822.0 (excel equivalent of 10/6/2011) both times instead of reevaluating to the new formula.

Solution

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

    formulaEvaluator.clearAllCachedResultValues()