You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@poi.apache.org by bu...@apache.org on 2008/03/19 18:22:09 UTC
DO NOT REPLY [Bug 44636] New: HSSF formula cells not calculating
https://issues.apache.org/bugzilla/show_bug.cgi?id=44636
Summary: HSSF formula cells not calculating
Product: POI
Version: 3.0
Platform: PC
OS/Version: Windows XP
Status: NEW
Severity: normal
Priority: P2
Component: HSSF
AssignedTo: dev@poi.apache.org
ReportedBy: jkeller@oberonassociates.com
Created an attachment (id=21689)
--> (https://issues.apache.org/bugzilla/attachment.cgi?id=21689)
Input spreadsheet (created in Excel)
Using the sample "recalculate all" code from this page:
http://poi.apache.org/hssf/eval.html
does recalculate the cells, but does not seem to correctly handle the formulas
in all cases.
If you call cell.setCellForumla after evaluating the cell, it seems to work
fine.
Here's a simple test case.
1) Run the code below. It will open the attached simple.xls, change one cell,
and save it as changed.xls.
2) Open changed.xls in Excel.
3) Change the same cell (C1, which should now contain 25).
4) Note how the calculated cell (D1) does not recalc.
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.util.Iterator;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
public class Recalc
{
public static void main (String[] args)
{
try
{
File ssFile = new File ("simple.xls");
FileInputStream ssIn = new FileInputStream (ssFile);
HSSFWorkbook wb = new HSSFWorkbook (ssIn);
HSSFSheet sheet = wb.getSheetAt (0);
HSSFRow row = sheet.getRow (0);
HSSFCell cell = row.getCell ((short) 2);
cell.setCellValue (25);
// recalc
HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(sheet, wb);
for (Iterator rit = sheet.rowIterator(); rit.hasNext();)
{
HSSFRow r = (HSSFRow)rit.next();
evaluator.setCurrentRow(r);
for (Iterator cit = r.cellIterator(); cit.hasNext();)
{
HSSFCell c = (HSSFCell)cit.next();
if (c.getCellType() == HSSFCell.CELL_TYPE_FORMULA)
evaluator.evaluateFormulaCell (c);
}
}
FileOutputStream ssOut = new FileOutputStream ("changed.xls");
wb.write (ssOut);
ssOut.close();
}
catch (Exception x)
{
System.err.println (x);
}
}
}
--
Configure bugmail: https://issues.apache.org/bugzilla/userprefs.cgi?tab=email
------- You are receiving this mail because: -------
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
DO NOT REPLY [Bug 44636] [patch] HSSF formula cells not calculating
Posted by bu...@apache.org.
https://issues.apache.org/bugzilla/show_bug.cgi?id=44636
--- Comment #4 from Josh Micich <jo...@gildedtree.com> 2008-03-19 14:05:35 PST ---
(In reply to comment #2)
I just tried the patch in 3.0.2-FINAL. Both the before and after behaviour was
as noted above. I.E. it should work for you.
Perhaps the observation of '#VALUE!' in the formula cells is due to my version
of Excel. Which version are you using? Just for reference, can you describe
more clearly what your Excel does with 'changed.xls'? My observation of Excel
2007 is:
- The formulas all appear as '#VALUE!'.
- The correct formula text is still visible in the formulas.
- Pressing the <enter> key after selecting the formula causes it to evaluate
properly. This action seems to translate the tRef PTGs into tRefV PTGs
(observable after re-saving).
(In reply to comment #3)
> I should also point out that the problem is a bit tricky...
> If you simplify that spreadsheet at all - remove a row or column for example -
> the code works fine.
That makes sense. The specific bug that this one-line-fix addresses is the loss
of the ptg-class when translating a 'shared formula'. From what I remember on
previous bugs (bug 44449), excel has a minimum number of rows before it will
use a shared. I noticed a threshold of 6 but I'm not sure if that's universal.
POI does not erroneously disturb the ptg-class when reading/writing non-shared
formulas.
--
Configure bugmail: https://issues.apache.org/bugzilla/userprefs.cgi?tab=email
------- You are receiving this mail because: -------
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
DO NOT REPLY [Bug 44636] [patch] HSSF formula cells not calculating
Posted by bu...@apache.org.
https://issues.apache.org/bugzilla/show_bug.cgi?id=44636
Josh Micich <jo...@gildedtree.com> changed:
What |Removed |Added
----------------------------------------------------------------------------
Summary|HSSF formula cells not |[patch] HSSF formula cells
|calculating |not calculating
--- Comment #1 from Josh Micich <jo...@gildedtree.com> 2008-03-19 11:59:22 PST ---
I tried your test code on the latest svn trunk, and got a different error. All
formulas (in D1:D6) displayed as '#VALUE!' (my excel is 2007). I found one
difference which was that POI currently writes tRef PTGs involved in a shared
formula back as tRefV PTGs. There is a one line change to RefVPtg that will
fix that problem, after which all D1:D6 display ok:
$ svn diff src/java/org/apache/poi/hssf/record/formula/RefVPtg.java
Index: src/java/org/apache/poi/hssf/record/formula/RefVPtg.java
===================================================================
--- src/java/org/apache/poi/hssf/record/formula/RefVPtg.java (revision
638958)
+++ src/java/org/apache/poi/hssf/record/formula/RefVPtg.java (working copy)
@@ -32,6 +32,7 @@
public RefVPtg(int row, int column, boolean isRowRelative, boolean
isColumnRelative) {
super(row, column, isRowRelative, isColumnRelative);
+ setClass(CLASS_VALUE);
}
There have been a lot of changes in POI formula evaluation since v3.0, so I'm
not sure if the same patch will fully solve the problem in that version.
Furthermore, I know I haven't fully isolated this '#VALUE!' problem that I
observed in the latest POI trunk, because a simple test case would not
reproduce it (i.e. writing a spreadsheet formula just with tRefV PTGs instead
of tRef PTGs did not upset excel). There must be some other detail (in
combination with the wrong PTGs) that causes '#VALUE!' to appear.
If you attempt to apply this patch directly to v3.0, please post back if if
doesn't fix your problem.
The patch so far is tiny, but there are a few issues that need much more
investigation:
1 - Up until now, I had not seen any evidence of why POI bothers with PTG token
classes at all. All junit tests continue to run when that code
(setClass/getPtgClass) is disabled. The attached spreadsheet and test code
seems to be the first concrete example of why it might be necessary.
2 - It does not make sense to have ptg-class based java sub-classes of Ptg in
the presence of a method "setClass(byte)" which can change the ptg-lass.
3 - POI unpacks shared formula records, but doesn't seem to re-pack them
together when the spreadsheet is re-written.
--
Configure bugmail: https://issues.apache.org/bugzilla/userprefs.cgi?tab=email
------- You are receiving this mail because: -------
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
DO NOT REPLY [Bug 44636] [patch] HSSF formula cells not calculating
Posted by bu...@apache.org.
https://issues.apache.org/bugzilla/show_bug.cgi?id=44636
--- Comment #3 from J Keller <jk...@oberonassociates.com> 2008-03-19 13:32:21 PST ---
I should also point out that the problem is a bit tricky. I started with a
very large, complex spreadsheet - and kept removing data until the problem went
away. The attached xls is the simplest version in which I could reproduce it.
If you simplify that spreadsheet at all - remove a row or column for example -
the code works fine.
--
Configure bugmail: https://issues.apache.org/bugzilla/userprefs.cgi?tab=email
------- You are receiving this mail because: -------
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
DO NOT REPLY [Bug 44636] [patch] HSSF formula cells not calculating
Posted by bu...@apache.org.
https://issues.apache.org/bugzilla/show_bug.cgi?id=44636
--- Comment #2 from J Keller <jk...@oberonassociates.com> 2008-03-19 13:25:35 PST ---
I'm actually using version 3.0.2-FINAL. (3.0 was the closest in the
drop-down.)
Do I still need the patch?
--
Configure bugmail: https://issues.apache.org/bugzilla/userprefs.cgi?tab=email
------- You are receiving this mail because: -------
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
DO NOT REPLY [Bug 44636] [patch] HSSF formula cells not calculating
Posted by bu...@apache.org.
https://issues.apache.org/bugzilla/show_bug.cgi?id=44636
Nick Burch <ni...@torchbox.com> changed:
What |Removed |Added
----------------------------------------------------------------------------
Status|NEW |RESOLVED
Resolution| |FIXED
--- Comment #5 from Nick Burch <ni...@torchbox.com> 2008-03-20 03:14:39 PST ---
I started looking at this last night, but didn't finish before Josh also took a
look...
I've added a unit test to svn -
src/scratchpad/testcases/org/apache/poi/hssf/usermodel/TestFormulaEvaluatorBugs.java
Like Josh, if I start with your file, excel gives #VALUE if I change things. If
I start with an empty file, it seems fine.
Interestingly, gnumeric and openoffice have no such problems with the files.
With Josh's one line fix applied, the file from my unit test works fine in
excel. So, I've committed Josh's fix to svn
--
Configure bugmail: https://issues.apache.org/bugzilla/userprefs.cgi?tab=email
------- You are receiving this mail because: -------
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
DO NOT REPLY [Bug 44636] [patch] HSSF formula cells not calculating
Posted by bu...@apache.org.
https://issues.apache.org/bugzilla/show_bug.cgi?id=44636
--- Comment #6 from J Keller <jk...@oberonassociates.com> 2008-03-20 06:24:22 PST ---
My Excel behaves exactly the same as yours. I have version 2003
(11.8206.8202).
I'll try the patch (I don't have the POI source yet; I had just downloaded the
jars. Thanks.
--
Configure bugmail: https://issues.apache.org/bugzilla/userprefs.cgi?tab=email
------- You are receiving this mail because: -------
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org