|Summary:||Formulas not working correctly on LibreOffice with SXSSF|
|Component:||SXSSF||Assignee:||POI Developers List <dev>|
|Bug Depends on:||61148|
|Attachments:||Sample source code|
Description nicolaspohren 2016-10-24 20:17:06 UTC
Created attachment 34402 [details] 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: 220.127.116.11
Comment 1 Javen O'Neal 2016-10-24 21:09:22 UTC
Formulas are not automatically evaluated in POI. You can either force Excel/LibreOffice to recalculate formulas when the workbook is opened  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.  https://poi.apache.org/apidocs/org/apache/poi/ss/usermodel/Workbook.html#setForceFormulaRecalculation(boolean)  https://poi.apache.org/spreadsheet/formula.html  https://poi.apache.org/spreadsheet/eval.html
Comment 2 nicolaspohren 2016-10-25 10:13:00 UTC
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!
Comment 3 Javen O'Neal 2016-10-25 15:53:23 UTC
(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.
Comment 4 Greg Woolsey 2016-10-25 16:16:17 UTC
(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.
Comment 5 Dominik Stadler 2017-09-19 19:11:57 UTC
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.