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?

Accepted Answer

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

Excel Formula Not Updating Cell

Solution

Problem