[Bug 60302] New: Formulas not working correctly on LibreOffice with SXSSF

classic Classic list List threaded Threaded
8 messages Options
Reply | Threaded
Open this post in threaded view
|

[Bug 60302] New: Formulas not working correctly on LibreOffice with SXSSF

Bugzilla from bugzilla@apache.org
https://bz.apache.org/bugzilla/show_bug.cgi?id=60302

            Bug ID: 60302
           Summary: Formulas not working correctly on LibreOffice with
                    SXSSF
           Product: POI
           Version: 3.15-dev
          Hardware: PC
            Status: NEW
          Severity: normal
          Priority: P2
         Component: SXSSF
          Assignee: [hidden email]
          Reporter: [hidden email]

Created attachment 34402
  --> https://bz.apache.org/bugzilla/attachment.cgi?id=34402&action=edit
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

--
You are receiving this mail because:
You are the assignee for the bug.

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

[Bug 60302] Formulas not working correctly on LibreOffice with SXSSF

Bugzilla from bugzilla@apache.org
https://bz.apache.org/bugzilla/show_bug.cgi?id=60302

Javen O'Neal <[hidden email]> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
                 OS|                            |All
             Status|NEW                         |NEEDINFO

--- Comment #1 from Javen O'Neal <[hidden email]> ---
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

--
You are receiving this mail because:
You are the assignee for the bug.

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

[Bug 60302] Formulas not working correctly on LibreOffice with SXSSF

Bugzilla from bugzilla@apache.org
In reply to this post by Bugzilla from bugzilla@apache.org
https://bz.apache.org/bugzilla/show_bug.cgi?id=60302

[hidden email] changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
             Status|NEEDINFO                    |NEW

--- Comment #2 from [hidden email] ---
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!

--
You are receiving this mail because:
You are the assignee for the bug.

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

[Bug 60302] Formulas not working correctly on LibreOffice with SXSSF

Bugzilla from bugzilla@apache.org
In reply to this post by Bugzilla from bugzilla@apache.org
https://bz.apache.org/bugzilla/show_bug.cgi?id=60302

--- Comment #3 from Javen O'Neal <[hidden email]> ---
(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.

--
You are receiving this mail because:
You are the assignee for the bug.

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

[Bug 60302] Formulas not working correctly on LibreOffice with SXSSF

Bugzilla from bugzilla@apache.org
In reply to this post by Bugzilla from bugzilla@apache.org
https://bz.apache.org/bugzilla/show_bug.cgi?id=60302

--- Comment #4 from Greg Woolsey <[hidden email]> ---
(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.

--
You are receiving this mail because:
You are the assignee for the bug.

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

[Bug 60302] Formulas not working correctly on LibreOffice with SXSSF

Bugzilla from bugzilla@apache.org
In reply to this post by Bugzilla from bugzilla@apache.org
https://bz.apache.org/bugzilla/show_bug.cgi?id=60302

Dominik Stadler <[hidden email]> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
         Depends on|                            |61148


Referenced Bugs:

https://bz.apache.org/bugzilla/show_bug.cgi?id=61148
[Bug 61148] Formula evaluation on an SXSSFCell is not working
--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

[Bug 60302] Formulas not working correctly on LibreOffice with SXSSF

Bugzilla from bugzilla@apache.org
In reply to this post by Bugzilla from bugzilla@apache.org
https://bz.apache.org/bugzilla/show_bug.cgi?id=60302
Bug 60302 depends on bug 61148, which changed state.

Bug 61148 Summary: Formula evaluation on an SXSSFCell is not working
https://bz.apache.org/bugzilla/show_bug.cgi?id=61148

           What    |Removed                     |Added
----------------------------------------------------------------------------
             Status|NEW                         |RESOLVED
         Resolution|---                         |FIXED

--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

[Bug 60302] Formulas not working correctly on LibreOffice with SXSSF

Bugzilla from bugzilla@apache.org
In reply to this post by Bugzilla from bugzilla@apache.org
https://bz.apache.org/bugzilla/show_bug.cgi?id=60302

Dominik Stadler <[hidden email]> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
             Status|NEW                         |RESOLVED
         Resolution|---                         |WORKSFORME

--- Comment #5 from Dominik Stadler <[hidden email]> ---
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.

--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]