60302
2016-10-24 20:17:06 +0000
Formulas not working correctly on LibreOffice with SXSSF
2017-09-19 19:11:57 +0000
1
1
1
Unclassified
POI
SXSSF
3.15-dev
PC
All
RESOLVED
WORKSFORME
P2
normal
---
61148
1
nicolaspohren
dev
0
oldest_to_newest
194659
0
34402
nicolaspohren
2016-10-24 20:17:06 +0000
Created attachment 34402
Sample source code
When creating a .xlsx file using SXSSF and including a formula (SUM for example), Microsoft Office can open the generated file perfectly.
However, when trying to open it with LibreOffice, the generated formula contains 0 as the result, even though it should have a value.
Sample source code in attachment
Poi version: 3.15
LibreOffice version: 5.2.2.2
194660
1
onealj
2016-10-24 21:09:22 +0000
Formulas are not automatically evaluated in POI.
You can either force Excel/LibreOffice to recalculate formulas when the workbook is opened [1] or use POI to update the formula result before saving the file. [2,3]
That said, I believe the described behavior is expected and not a bug in POI, but rather an implementation difference in formula evaluation on workbook open in Excel versus LibreOffice.
[1] https://poi.apache.org/apidocs/org/apache/poi/ss/usermodel/Workbook.html#setForceFormulaRecalculation(boolean)
[2] https://poi.apache.org/spreadsheet/formula.html
[3] https://poi.apache.org/spreadsheet/eval.html
194666
2
nicolaspohren
2016-10-25 10:13:00 +0000
Makes sense being a behavior difference between Microsoft Excel and LibreOffice when updating the formulas.
I tested setting "setForceFormulaRecalculation(true)" in my example source code, but it did not change the outcome (Still works in Microsoft Office, not in LibreOffice)
However, I find it strange that it works with XSSF or HSSF but not with SXSSF (I forgot to mention this in the original post). So this shows that there's a way to save the .xlsx file so that LibreOffice correctly updates the formulas. I don't know how SXSSF works internally, but we are restricted to using it because of memory usage.
I cannot evaluate the formulas before saving the file because our users may modify the original values and want the formulas to be updated, so it can't be a fixed value.
That said, if this is an expected behavior of SXSSF, even though is different than XSSF, I will change the bug status to resolved.
Thanks for the quick response!
194670
3
onealj
2016-10-25 15:53:23 +0000
(In reply to nicolaspohren from comment #2)
> I cannot evaluate the formulas before saving the file because our users may
> modify the original values and want the formulas to be updated, so it can't
> be a fixed value.
The OOXML format can save both a formula and a cached result (that can be re-evaluated).
Off the top of my head, it looks like this:
<c address="G6" type="f">
<f>SUM(A1:D3)</f>
<v>42</v>
</c>
Cell#setCellFormula("SUM(A1:D3)") will set f to "SUM(A1:D3)" and v to 0. POI can evaluate the formula result and set v to 42.
That said, SXSSF formula evaluation is contingent on all formulas being accessible in the row window.
194671
4
greg.woolsey
2016-10-25 16:16:17 +0000
(In reply to nicolaspohren from comment #2)
> However, I find it strange that it works with XSSF or HSSF but not with
> SXSSF (I forgot to mention this in the original post). So this shows that
> there's a way to save the .xlsx file so that LibreOffice correctly updates
> the formulas. I don't know how SXSSF works internally, but we are restricted
> to using it because of memory usage.
>
Since you can see it working one way but not the other, you can compare the two outputs (XSSF/SXSSF) to see what changed for the same workbook definition. The files are just zipped XML file trees - unzip them and compare the trees. It could well be that somehow the "recalculate on open" flag is not written properly in one case, or something like that. Having example files that are generated by the two pathways from the same Java objects would be a good start for a test case.
200975
5
dominik.stadler
2017-09-19 19:11:57 +0000
The problem is that the cached values for the formula is not populated by default in POI to save the CPU cycles if not needed. You can force single cells or all formulas to be evaluated with the FormulaEvaluator which is available via "wb.getCreationHelper().createFormulaEvaluator()", so in your case the following makes the result of the formula also show up in LibreOffice correctly:
wb.getCreationHelper().createFormulaEvaluator().evaluateAll();
Therefore I am closing this WORKSFORME now as this is currently by design and only Excel seems to somehow detect that the formulas need recalculation whereas LibreOffice seems to rely on them being populated always.
34402
2016-10-24 20:17:06 +0000
2016-10-24 20:17:06 +0000
Sample source code
POI.java
text/plain
1069
nicolaspohren
cGFja2FnZSBwb2k7DQoNCmltcG9ydCBqYXZhLmlvLkZpbGU7DQppbXBvcnQgamF2YS5pby5GaWxl
T3V0cHV0U3RyZWFtOw0KaW1wb3J0IG9yZy5hcGFjaGUucG9pLmhzc2YudXNlcm1vZGVsLkhTU0ZX
b3JrYm9vazsNCmltcG9ydCBvcmcuYXBhY2hlLnBvaS5zcy51c2VybW9kZWwuU2hlZXQ7DQppbXBv
cnQgb3JnLmFwYWNoZS5wb2kuc3MudXNlcm1vZGVsLldvcmtib29rOw0KaW1wb3J0IG9yZy5hcGFj
aGUucG9pLnhzc2Yuc3RyZWFtaW5nLlNYU1NGV29ya2Jvb2s7DQoNCnB1YmxpYyBjbGFzcyBQT0kg
ew0KDQogICAgcHVibGljIHN0YXRpYyB2b2lkIG1haW4oU3RyaW5nW10gYXJncykgdGhyb3dzIEV4
Y2VwdGlvbnsNCiAgICAgICAgLy8gU3dpdGNoIGJldHdlZW4gSEhTRiAoeGxzKSAvIFhTU0YgKHhs
c3gpDQovLyAgICAgICAgV29ya2Jvb2sgd2IgPSBuZXcgSFNTRldvcmtib29rKCk7DQogICAgICAg
IFdvcmtib29rIHdiID0gbmV3IFNYU1NGV29ya2Jvb2soKTsNCg0KICAgICAgICBTaGVldCBzaGVl
dCA9IHdiLmNyZWF0ZVNoZWV0KCk7DQoNCiAgICAgICAgc2hlZXQuY3JlYXRlUm93KDApLmNyZWF0
ZUNlbGwoMCkuc2V0Q2VsbFZhbHVlKDUpOw0KICAgICAgICBzaGVldC5jcmVhdGVSb3coMSkuY3Jl
YXRlQ2VsbCgwKS5zZXRDZWxsVmFsdWUoNik7DQogICAgICAgIHNoZWV0LmNyZWF0ZVJvdygyKS5j
cmVhdGVDZWxsKDApLnNldENlbGxGb3JtdWxhKCJzdW0oJEEkMTokQSQyKSIpOw0KDQoNCiAgICAg
ICAgU3RyaW5nIGZpbGUgPSAicmVzdWx0LnhscyI7DQogICAgICAgIGlmICghKHdiIGluc3RhbmNl
b2YgSFNTRldvcmtib29rKSkgew0KICAgICAgICAgICAgZmlsZSArPSAieCI7DQogICAgICAgIH0N
CiAgICAgICAgRmlsZU91dHB1dFN0cmVhbSBmaWxlT3V0ID0gbmV3IEZpbGVPdXRwdXRTdHJlYW0o
ZmlsZSk7DQogICAgICAgIHdiLndyaXRlKGZpbGVPdXQpOw0KICAgICAgICBmaWxlT3V0LmNsb3Nl
KCk7DQogICAgICAgIFN5c3RlbS5vdXQucHJpbnRsbigiR2VuZXJhdGVkIGZpbGUgIiArIG5ldyBG
aWxlKGZpbGUpLmdldEFic29sdXRlUGF0aCgpKTsNCiAgICB9DQoNCn0NCg==