Bug 41971 - Excel doesn't reevaluate formula cells, worked with 2.5.1
Summary: Excel doesn't reevaluate formula cells, worked with 2.5.1
Status: RESOLVED FIXED
Alias: None
Product: POI
Classification: Unclassified
Component: HSSF (show other bugs)
Version: 3.0-FINAL
Hardware: All All
: P2 critical with 17 votes (vote)
Target Milestone: ---
Assignee: POI Developers List
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2007-03-28 08:59 UTC by Julien Oster
Modified: 2008-01-24 07:44 UTC (History)
0 users



Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description Julien Oster 2007-03-28 08:59:33 UTC
With POI 2.5.1, whenever you changed a cell on which a formula depended, that
formula got reevaluated once you reopened the modified document in Excel.

In 3.0 (both from the repository and the latest dev "release" from the download
section), that doesn't seem to happy anymore.

Simple Example:

suppose you have the following Excel document:

A1:  somefoo
A2:  =A1

after modifying A1 with POI to contain 22 instead of "somefoo", A2 still shows
"somefoo". This didn't happen with 2.5.1.

Pressing F9 doesn't help, but one workaround I found: replace all occurences of
'=' in the sheet with '='. Yes, it's the same character, but since it "touches"
the formula cells, it forces Excel to reevaluate them (probably because Excel
thinks you just changed them).

So I'm actually contemplating to add a macro which does exactly that on load,
for every sheet in the correct order, but that's a rather crude workaround.

Maybe it's worth noting that I'm not using POI directly, but JXLS, which uses
POI as backend.
Comment 1 Julien Oster 2007-03-30 03:58:21 UTC
[copying and pasting from a mail which I just sent to the mailing list]

Hello,

what exactly is an IterationRecord for?
http://jakarta.apache.org/poi/apidocs/org/apache/poi/hssf/record/IterationRecord.html

I especially wonder why it's set to false upon document creation, as the
description somehow seems like it would control whether depending formulas are
recalculated upon modification of cells/other formulas.

The reason why I'm interested is because I have a document where formulas aren't
recalculated. After modifying the cells on which the formulas depend and saving
the document (JXLS does that using POI), the formula cells still show their old
value before modification.

I can trigger recalculation by pressing F2 or somehow modifying the formulas, or
at least pretending to do so. For example, searching and replacing '=' with '='
(yes, same character) in a sheet will update the formulas to their correct value.

However, it's an existing document, so maybe createIteration() isn't even
called. Maybe it's still connected to the IterationRecord?

That issue wasn't present in POI 2.5.1.

I also added a bug: http://issues.apache.org/bugzilla/show_bug.cgi?id=41971

Regards,
Julien
Comment 2 Dimitris.C. 2007-11-20 07:06:23 UTC
we have the same problem with POI 3.0.1

after opening/editing an excel spreadsheet and then saving the file, the
formulas are not re-calculating automatically. in POI 2.5.1 it is working fine.
Comment 3 Dimitris.C. 2007-11-20 07:10:02 UTC
(In reply to comment #1)
> [copying and pasting from a mail which I just sent to the mailing list]
> 
> Hello,
> 
> what exactly is an IterationRecord for?
>
http://jakarta.apache.org/poi/apidocs/org/apache/poi/hssf/record/IterationRecord.html
> 
> I especially wonder why it's set to false upon document creation, as the
> description somehow seems like it would control whether depending formulas are
> recalculated upon modification of cells/other formulas.
> 
> The reason why I'm interested is because I have a document where formulas aren't
> recalculated. After modifying the cells on which the formulas depend and saving
> the document (JXLS does that using POI), the formula cells still show their old
> value before modification.
> 
> I can trigger recalculation by pressing F2 or somehow modifying the formulas, or
> at least pretending to do so. For example, searching and replacing '=' with '='
> (yes, same character) in a sheet will update the formulas to their correct value.
> 
> However, it's an existing document, so maybe createIteration() isn't even
> called. Maybe it's still connected to the IterationRecord?
> 
> That issue wasn't present in POI 2.5.1.
> 
> I also added a bug: http://issues.apache.org/bugzilla/show_bug.cgi?id=41971
> 
> Regards,
> Julien

yes, I have the same problem here, with 2.5.1 everything is fine.
another workaround is to pres CTRL+SHIFT+F9 or to record a macro with VBA to
recalculate all fields on document start.
Comment 4 Nick Burch 2007-11-21 02:23:18 UTC
With POI 3.x, you can do formulas properly now. If you're editing cells used in
formulas, then either add a vb macro to trigger a full re-calculate, or use the
formula code in POI to recalculate the values.
Comment 5 Peter Breitling 2007-11-21 08:53:52 UTC
in our use-case we import a complete sheet as a template (from the user), modify some cells of it, and 
output the result (to the user). 

Thus we don't know which formulas exist and are dependent of the modified cells. So we need to do a full 
recalculation of all sheets before we output the result.

How would you recalculate all existing cells in all sheets using POI effectively?

A VB Macro ist not what we want, as this will depend on the client excel version and the permissions to 
execute.
Comment 6 Nick Burch 2008-01-09 15:26:30 UTC
See <http://poi.apache.org/hssf/eval.html#EvaluateAll> - it's about 10 lines of code

(There are also a few recalculation related booleans on the underling formula
record, you could always play with those if you don't fancy just using the
formula parser)
Comment 7 Peter Breitling 2008-01-24 05:01:20 UTC
The evaluateInCell Method removes the original formulas and replaces it with the result value. The 
resulting excel is unusable for our client.

We can not use poi 3x as we used to with poi 2.5: Load Template, inject some new cell values, save the 
result in a reliable manner.
Comment 8 Nick Burch 2008-01-24 07:44:05 UTC
(In reply to comment #7)
> The evaluateInCell Method removes the original formulas and replaces it with
the result value. The 
> resulting excel is unusable for our client.

Ah, so it does, my bad :( Sorry about that

I've updated the docs at <http://poi.apache.org/hssf/eval.html#EvaluateAll>

You'll want a svn checkout for evaluator.evaluateFormulaCell(c), but that should
do what you want (I even wrote a test to check that!). Also, with a svn
checkout, there's a new flag on a sheet to allow you to request a complete
formula re-evaluation from excel on the next load. That might also work for you