Excel 单元格显示零而不是计算/引用值 [英] Excel cell displaying zero instead of a calculated / referenced value

问题描述

我使用 Excel 文件作为源文件导出带有 Apache POI 的 Excel 文件，对于某些单元格(不是全部)，我只是引用其他单元格(例如 ='worksheet2'.B13)或计算一个值引用的单元格(如 ='worksheet2'.C13 + 'worksheet2'.D13)，它们只显示零 (0) 而不是正确的值.

I am exporting an Excel file with Apache POI using an Excel file as source and for some cells (not all) simply referencing others (like ='worksheet2'.B13) or calculating a value with referenced cells (like ='worksheet2'.C13 + 'worksheet2'.D13), they only display zero (0) instead of the right value.

奇怪的是，当我单击公式并更改其中的某些内容时，例如键入一个空格并删除它然后键入 enter 时，会显示正确的值！

Weird thing is that when I click on the formula and change something in it, as typing a space and deleting it and then I type enter, the right value is displayed !

有关信息，这些单元格的类型是数字/标准".

For info, the type of those cells is "Number / Standard".

我还在某处读到我应该在Excel 选项"中选中启用迭代计算"，但它对我不起作用.

I also read somewhere that I should check "Enable iterative calculation" in "Excel options", but it didn't work for me.

预先感谢您的帮助

推荐答案

在 @JohnBus​​tos 和 @Gagravarr 的帮助下，我使用以下方法解决了问题:wb.getCreationHelper().createFormulaEvaluator().evaluateAll();

With the help of @JohnBustos and @Gagravarr, I fixed the problem using : wb.getCreationHelper().createFormulaEvaluator().evaluateAll();

显然，Excel 缓存了先前计算的结果(在我的例子中它是源 Excel 文件中的零)，我们需要触发重新计算来更新它们.

Apparently, Excel caches previously calculated results (in my case it was the Zero in the source Excel file) and we need to trigger recalculation to update them.

请参阅此链接:http://poi.apache.org/spreadsheet/eval.html/部分:公式的重新计算"

See this link : http://poi.apache.org/spreadsheet/eval.html / Section : "Recalculation of Formulas"

这篇关于Excel 单元格显示零而不是计算/引用值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！