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