java Excel公式不更新单元格
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17929327/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel Formula Not Updating Cell
提问by PandaBearSoup
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.
我正在使用 apache POI 更改 Excel 工作表中的单元格。更改值后，具有与已更改单元格对应的公式的单元格不会更新。
When I go into excel and click on the cell with the formula, and then click in the function bar, the formula updates.
当我进入excel并单击带有公式的单元格，然后在功能栏中单击时，公式会更新。
I have calculation options set to automatically update.
我将计算选项设置为自动更新。
Quick example.
快速示例。
Row :
排 ：
[A2 + A3] [1] [2]
[A2 + A3] [1] [2]
A1 here would equal 3
这里的 A1 等于 3
When I change it using POI:
当我使用 POI 更改它时：
[A2+A3] [2] [5]
[A2+A3] [2] [5]
A1 still equals 3 until I click on that cell.
A1 仍然等于 3，直到我点击那个单元格。
Refreshing the workbook or sheet also does not work. Is this a problem with excel or POI? Can anyone think of a workaround?
刷新工作簿或工作表也不起作用。这是excel还是POI的问题？任何人都可以想出解决方法吗？
回答by akokskis
If you're using XSSF workbooks, you can re-evaluate all formula cells as follows:
如果您使用的是 XSSF 工作簿，您可以按如下方式重新评估所有公式单元格：
XSSFFormulaEvaluator.evaluateAllFormulaCells(workbook)
A similar API exists if you're using an HSSF workbook:
如果您使用的是 HSSF 工作簿，则存在类似的 API：
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
或者，具体取决于您的设置方式以及您想要完成的具体工作，您应该能够在此处找到您的解决方案
回答by nilamber
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
如果您的工作表有一些公式A3=sum(A1:A2)可以计算并且现在可以正常工作，A1=5, A2 =4.如果将 A2 的值更改为 2，例如
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.
sh.getRow(0).getCell(0).setCellValue(2);//设置A1=2，执行写操作，检查A3的值还是显示9，看起来不对，其实不对。关键是 Excel 缓存了以前计算的结果，您需要触发重新计算来更新它们。
wb.getCreationHelper().createFormulaEvaluator().evaluateAll();or with
wb.getCreationHelper().createFormulaEvaluator().evaluateAll();或与
wb.setForceFormulaRecalculation(true);and then perform Write operation. This will give you the correct result. For Detail check here
wb.setForceFormulaRecalculation(true);然后执行写操作。这会给你正确的结果。详情请看这里
