Bug 60302 - Formulas not working correctly on LibreOffice with SXSSF
Summary: Formulas not working correctly on LibreOffice with SXSSF
Status: RESOLVED WORKSFORME
Alias: None
Product: POI
Classification: Unclassified
Component: SXSSF (show other bugs)
Version: 3.15-dev
Hardware: PC All
: P2 normal (vote)
Target Milestone: ---
Assignee: POI Developers List
URL:
Keywords:
Depends on: 61148
Blocks:
  Show dependency tree
 
Reported: 2016-10-24 20:17 UTC by nicolaspohren
Modified: 2017-09-19 19:11 UTC (History)
0 users



Attachments
Sample source code (1.04 KB, text/plain)
2016-10-24 20:17 UTC, nicolaspohren
Details

Note You need to log in before you can comment on or make changes to this bug.
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: 5.2.2.2
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 [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
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.