Menu
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?
441
In the Excel for the web spreadsheet, click the Formulas tab. Next to Calculation Options, select one of the following options in the dropdown: To recalculate all dependent formulas every time you make a change to a value, formula, or name, click Automatic. This is the default setting.
To display the calculated value rather than the formula, you must change the format of the cell containing the formula and re-enter the formula. To do this, follow these steps: Select the cell with the formula, and then click Cells on the Format menu. Click the Number tab.
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
129
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
