Bug 45873

Summary: Formulas are not being recalculated
Product: POI Reporter: Merav E. <merav.eshet>
Component: POI OverallAssignee: POI Developers List <dev>
Status: RESOLVED WORKSFORME    
Severity: normal CC: duc.nguyen, jeighfritz07, merav.eshet
Priority: P2    
Version: 3.1-FINAL   
Target Milestone: ---   
Hardware: PC   
OS: Windows Server 2003   
Attachments: Test spreadsheet
Java test case

Description Merav E. 2008-09-24 05:41:07 UTC
Hello,

Using poi v.3.0 (poi-3.0-rc4-20070503.jar) I encountered a problem which looks like bug #44233.
Formulas which are referenced to another sheet are not being recalculated when the source sheet data is modified.

I viewed the change list and saw that it was fixed in Version 3.0.2-FINAL (2008-02-04).

I tried using the latest version of POI 3.1-FINAL Released (2008-06-29) and still encountered the same problem, so I think that the fix for this bug might not be included in the new version.

Will you able to check it please? 
A fix above v.3.1 will be appreciated.

Thanks,
Merav
Comment 1 Nick Burch 2008-09-24 06:52:02 UTC
See http://poi.apache.org/hssf/eval.html
Comment 2 Merav E. 2008-10-02 05:36:31 UTC
(In reply to comment #0)
> Hello,
> Using poi v.3.0 (poi-3.0-rc4-20070503.jar) I encountered a problem which looks
> like bug #44233.
> Formulas which are referenced to another sheet are not being recalculated when
> the source sheet data is modified.
> I viewed the change list and saw that it was fixed in Version 3.0.2-FINAL
> (2008-02-04).
> I tried using the latest version of POI 3.1-FINAL Released (2008-06-29) and
> still encountered the same problem, so I think that the fix for this bug might
> not be included in the new version.
> Will you able to check it please? 
> A fix above v.3.1 will be appreciated.
> Thanks,
> Merav

(In reply to comment #1)
> See http://poi.apache.org/hssf/eval.html

Thanks for your quick response.
When I use old version of poi (I don’t know the exact version but it’s older than 3.0.0 and its size is 605 kb), the problem doesn’t occur and all formulas are recalculated properly, without using any of the HSSFFormulaEvaluator methods (which were not exist then, anyway).
When I use v.3.0 and v.3.1 this problem occurs: Formulas are not recalculated in a table list, although charts (I tried pie chart) are recalculated properly. When I try implementing the HSSFFormulaEvaluator in my code it doesn’t solve the problem.
Please let me know if you need our code examples.

Comment 3 Josh Micich 2008-11-05 20:03:00 UTC
When you write "modified" and "recalculated", it is not clear whether POI or Excel is the agent for either operation.

It will help a lot if you attach a sample spreadsheet and/or some sample code, to show the exact steps that result in your observed problem.
Comment 4 Andrew Gillett 2009-07-07 18:55:03 UTC
Created attachment 23938 [details]
Test spreadsheet
Comment 5 Andrew Gillett 2009-07-07 18:55:36 UTC
Created attachment 23939 [details]
Java test case
Comment 6 Andrew Gillett 2009-07-07 19:01:12 UTC
I've also encountered this problem recently. I have attached a test spreadsheet that contains data and a column with formula cells. The attached java program reads the spreadsheet and puts random numbers into the data cells.

If the java program is compiled and run with poi-2.5.1-final-20040804, the formula cells and chart are updated. With later versions of poi (I've tried 3.0-rc4-20070503, 3.2-FINAL-20081019, and 3.5-beta6-20090622) the formula cells retain their values from the original input file.
Comment 7 Josh Micich 2010-01-05 19:21:09 UTC
Your example seems to work OK with 
poi-2.5.1-final-20040804, poi-3.1-FINAL-20080629, poi-3.2-FINAL, poi-3.5-beta5-20090115, poi-3.5-FINAL-20090928, poi-3.6-20091214

But with poi-3.0-FINAL and poi-3.0.2-FINAL-20080204, POI fails to encode the formula correctly (perhaps an old bug token operand class transformation).  I'm a little surprised that you reported 3.0-rc4-20070503 to work for you.

Perhaps your observations are specific to a particular Excel version.  I use Win Excel 2007. It might be the case that prior versions of Excel don't auto-calculate  a spreadsheet without being forced to.  I don't recall if POI has changed recently (with regard to the default value of the auto-calculate flag).  Try HSSFSheet.setForceFormulaRecalculation() to force Excel to re-calculate all formulas on the current sheet upon opening.  On the other hand, you can use HSSFFormulaEvaluator and evaluateFormulaCell() to update the cached formula results so that re-calculation by Excel is not relevant.