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
See http://poi.apache.org/hssf/eval.html
(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.
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.
Created attachment 23938 [details] Test spreadsheet
Created attachment 23939 [details] Java test case
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.
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.