Bug 41971

[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

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.

(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.

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.

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.

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)

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.

(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