Excel Formula Not Updating Cell

12
votes

I am using apache POI to change cells in an excel sheet. After I change the values, the cells with formulas corresponding to cells that have been changed are not updating.

When I go into excel and click on the cell with the formula, and then click in the function bar, the formula updates.

I have calculation options set to automatically update.

Quick example.

Row :

[A2 + A3] [1] [2]

A1 here would equal 3

When I change it using POI:

[A2+A3] [2] [5]

A1 still equals 3 until I click on that cell.

Refreshing the workbook or sheet also does not work. Is this a problem with excel or POI? Can anyone think of a workaround?

3
javaexcelapache-poi
Your question is not clear. Please describe the exact sequence of operations, including opening and closing the worksheet in Excel. Do you do the following steps? 1) Create the worksheet; 2) Close Excel; 3) Run your Java program; 4) Reopen the worksheet in Excel? If not, describe EXACTLY what you do. – Jim Garrison
The others answered without such description, and such description is a bit irrelevant in this situation. – PandaBearSoup

3 Answers

24
votes

If you're using XSSF workbooks, you can re-evaluate all formula cells as follows:

XSSFFormulaEvaluator.evaluateAllFormulaCells(workbook)

A similar API exists if you're using an HSSF workbook:

HSSFFormulaEvaluator.evaluateAllFormulaCells(workbook)

Or, depending on exactly how you're set up and what specifically you're trying to get accomplished, you should be able to find your solution here

11
votes

In case your sheet has some formula for say A3=sum(A1:A2)is calculated and it is working fine with A1=5, A2 =4. Now if change the value of A2 with 2 like

sh.getRow(0).getCell(0).setCellValue(2); // set A1=2 and perform write operation, check the value of A3 it is still showing 9. It seems wrong but actully not. The point is that Excel caches previously calculated results and you need to trigger recalculation to updated them.

wb.getCreationHelper().createFormulaEvaluator().evaluateAll(); or with

wb.setForceFormulaRecalculation(true); and then perform Write operation. This will give you the correct result. For Detail check here

2
votes

You have to explicitly trigger a recalculation. See the Recalculation of Formulas section here about why & how to do it.