CODEWITHSUNDEEP
javaexcelapache-poixssfhssf
qwerty
qwerty

Reputation: 525

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.

Upvotes: 5

Views: 7697

Answers (3)

VRJ
VRJ

Reputation: 1

You can use following steps to get your work done. These are two solutions out of which you can make use of any one function. It evaluates the complete workbook so whatever formula you use would get evaluated. Hope this helps.

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

Upvotes: 0

qwerty
qwerty

Reputation: 525

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

Upvotes: 6

Gagravarr
Gagravarr

Reputation: 48326

The FormulaEvaluator caches cell calculated values to speed up processing. If you perform cell updates after creating the evaluator, then you need to tell it!

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

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

Upvotes: 2

Related Questions