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?

Answer 1

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