Excel Formula Not Updating Cell

Question

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?

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, Jul 29 '13 at 16:36
The others answered without such description, and such description is a bit irrelevant in this situation. — PandaBearSoup, Jul 29 '13 at 16:57

score 25 · Accepted Answer · answered Jul 29 '13 at 16:41

25

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

answered Jul 29 '13 at 16:41

akokskis

1,486
15
32

Yes, this is what used as a workaround when I noticed the issue. I am more wondering why excel does not update the cells automatically, not just when I click on them. Refreshing the workbook should also work. Seems like a bug. I will accept this answer as it technically resolves the root problem. – PandaBearSoup Jul 29 '13 at 16:56
2

@PandaBearSoup The [Why Evaluate Formulas](http://poi.apache.org/spreadsheet/eval.html#WhyEvaluate) section of the Apache POI documentation explains why you need to do it, I'd suggest you read through that – Gagravarr Jul 30 '13 at 11:04

score 11 · Answer 2 · answered Jul 29 '13 at 16:58

11

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

answered Jul 29 '13 at 16:58

nilamber

371
2
10

I don't have sufficient reputation to post the comment for the above solution. So I have pasted the solution. – nilamber Jul 30 '13 at 05:09
2

thanks much!!! wb.setForceFormulaRecalculation(true); => worked like a charm for me! – eugene.polschikov Mar 22 '16 at 16:35

score 2 · Answer 3 · answered Jul 29 '13 at 16:39

2

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

answered Jul 29 '13 at 16:39

Eugen Constantin Dinca

8,994
2
34
51

Excel Formula Not Updating Cell

3 Answers3

Linked