Apache POI does not update formulas

Question

I am using Apache POI library to use some formulas from the excel sheet but it seems that the formulas are not being updated at a runtime. If I save a workbook and reopen it, they are recalculated. Is there a way to calculate them at a runtime?

My code is:

FileInputStream excelFile = new FileInputStream(new File(FILE_NAME));
Workbook workbook = new XSSFWorkbook(excelFile);
Sheet datatypeSheet = workbook.getSheetAt(1);

datatypeSheet.getRow(19).getCell(2).setCellValue(0);  // set C20=0
workbook.setForceFormulaRecalculation(true); 

System.out.println(workbook.getSheetAt(1).getRow(19).getCell(8).getNumericCellValue()); // prints out the result

FileOutputStream out = new FileOutputStream(new File(FILE_NAME));
workbook.write(out);
out.close();

I also tried:

FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
evaluator.clearAllCachedResultValues();
evaluator.notifySetFormula(workbook.getSheetAt(1).getRow(19).getCell(8));
evaluator.evaluate(workbook.getSheetAt(1).getRow(19).getCell(8));

but they always return old value.

Axel Richter · Accepted Answer · 2021-04-09T16:36:35.803

Workbook.setForceFormulaRecalculation explicitly only forces the recalculation process to Excel while next time opening the workbook. So works as designed.

But using the FormulaEvaluator is the correct way. But you should use FormulaEvaluator.evaluateFormulaCell instead of FormulaEvaluator.evaluate.

From FormulaEvaluator.evaluate:

If cell contains a formula, the formula is evaluated and returned, else the CellValue simply copies the appropriate cell value from the cell and also its cell type.

There is nothing updating the result of the formula in the cell.

In opposite FormulaEvaluator.evaluateFormulaCell:

If cell contains formula, it evaluates the formula, and saves the result of the formula. The cell remains as a formula cell.

There the result of the formula in the cell is also saved.

Following works for me when in second sheet of ExcelExample.xlsx cell C20 contains a number and cell I20 contains a formula having C20 as a parameter.

import org.apache.poi.ss.usermodel.*;

import java.io.FileInputStream;
import java.io.FileOutputStream;

class ExcelEvaluateCell {

 public static void main(String[] args) throws Exception {
     
  Workbook workbook = WorkbookFactory.create(new FileInputStream("./ExcelExample.xlsx"));
  FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();  
 
  System.out.println(workbook.getSheetAt(1).getRow(19).getCell(2).getNumericCellValue()); // prints value of C20
  
  System.out.println(workbook.getSheetAt(1).getRow(19).getCell(8).getCellFormula()); // prints formula in I20
  System.out.println(workbook.getSheetAt(1).getRow(19).getCell(8).getNumericCellValue()); // prints result of formula in in I20
  
  workbook.getSheetAt(1).getRow(19).getCell(2).setCellValue(0); // set C20 = 0
  
  evaluator.evaluateFormulaCell(workbook.getSheetAt(1).getRow(19).getCell(8)); // evaluates formula in cell I20 and updates the result
  System.out.println(workbook.getSheetAt(1).getRow(19).getCell(8).getNumericCellValue()); // prints new result of formula in in I20
 
  FileOutputStream out = new FileOutputStream("./ExcelExampleNew.xlsx");
  workbook.write(out);
  out.close();
  workbook.close();
 }
}

Apache POI does not update formulas

1 Answers1