CODEWITHSUNDEEP
javaexcelapache-poi
Tom Chiverton
Tom Chiverton

Reputation: 678

How to read the result of an Excel formula using POI after changing some cells

I have a simple Excel file with A1 being B1+C1. B1 is 1 and C1 is 4

I can read this in with POI, and display the result of the formula from A1:

workbook=XSSFWorkbook( fis );
sheet =  workbook.getSheetAt(0);
evaluator = workbook.getCreationHelper().createFormulaEvaluator();
row = sheet.getRow(0);
cell = row.getCell(0);
cellValue.getNumberValue();

Now suppose I update B1:

sheet.getRow(0).getCell(1).setCellValue(5)

How do I read the updated value (9) from A1. The docs suggest XSSFFormulaEvaluator's evaluate(cell) but this still returns 5, as does calling XSSFFormulaEvaluator.evaluateAllFormulaCells(workbook) e.g.

XSSFFormulaEvaluator.evaluateAllFormulaCells(workbook);
sheet =  workbook.getSheetAt(0);    
row = sheet.getRow(0);
cell = row.getCell(0);    
cellValue = evaluator.evaluate(cell);

But if I save and re-reload the .xlsx file (through POI or Excel itself) then A1 is 9 as expected, but I want to avoid the save step

Upvotes: 1

Views: 1186

Answers (1)

Tom Chiverton
Tom Chiverton

Reputation: 678

Call

evaluator.notifyUpdateCell( sheet.getRow(0).getCell(1) );

before evaluator.evaluate(cell) and it'll flush it's internal cache and update the value. Or get a new evaluator. See performance notes @ http://poi.apache.org/spreadsheet/eval.html

Upvotes: 4

Related Questions