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/14 16:49:45 UTC
DO NOT REPLY [Bug 44606] New: ClassCastException
https://issues.apache.org/bugzilla/show_bug.cgi?id=44606
Summary: ClassCastException
Product: POI
Version: 3.0
Platform: PC
OS/Version: Windows 2000
Status: NEW
Severity: blocker
Priority: P2
Component: HSSF
AssignedTo: dev@poi.apache.org
ReportedBy: antonio.chirizzi@gmail.com
Created an attachment (id=21666)
--> (https://issues.apache.org/bugzilla/attachment.cgi?id=21666)
The java file to compile, and the xls file. Run the java file using the xls
file as argument to get the error.
Hello,
POI Version: poi-3.0.2-FINAL, jdk1.6.0_05
Run by: java -classpath
poi-scratchpad-3.0.2-FINAL-20080204.jar;poi-3.0.2-FINAL-20080204.jar;.
MyExample
Error: Exception in thread "main" java.lang.ClassCastException:
org.apache.poi.hssf.record.aggregates.FormulaRecordAggregate cannot be cast to
org.apache.poi.hssf.record.LabelSSTRecord
at
org.apache.poi.hssf.usermodel.HSSFCell.setCellValue(HSSFCell.java:625)
at
org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator.evaluateFormulaCell(HSSFFormulaEvaluator.java:253)
at MyExample.main(MyExample.java:42)
The problem is that I am using in a cell a string and a formula like: '"Sold by
Company, TOT = "&SUM(D52:D55)'
This type of formula is stopping the program with the error.
Program:
for (int sheetNum = 0; sheetNum < wb.getNumberOfSheets(); sheetNum++) {
HSSFSheet sheet = wb.getSheetAt(sheetNum);
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) {
System.out.println(c);
evaluator.evaluateFormulaCell(c); // <--- LINE 42 ----
}
}
}
}
--
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 44606] [patch] Setting string value for formula
cell yields ClassCastException
Posted by bu...@apache.org.
https://issues.apache.org/bugzilla/show_bug.cgi?id=44606
Josh Micich <jo...@gildedtree.com> changed:
What |Removed |Added
----------------------------------------------------------------------------
Summary|ClassCastException |[patch] Setting string value
| |for formula cell yields
| |ClassCastException
--- Comment #2 from Josh Micich <jo...@gildedtree.com> 2008-03-14 11:09:04 PST ---
I took a look at the code+spreadsheet you attached. The latest POI fails well
before the ClassCastException. This is because since 3.0, support for parsing
percent formula token was added, but percent evaluation has not been done yet.
>From what I recall, in 3.0 the formula parser would just *stop* upon finding
any unknown char. That's why you don't *crash* on '%'. If you check the
evaluated values, you should find that they're out by a factor of 100.
After I hacked a quick fix for PercentPtg, I could see your ClassCastException
error. I have attached a patch to fix just this error. (The percent
evaluation stuff will come a bit later).
If you don't care about the actual results from HSSFFormulaEvaluator, you can
just apply this patch to your 3.0 version of the code to get up and running.
Otherwise you'll have to wait for PercentEval to be added to the svn trunk
before you'll have a version of POI that can handle your supplied example code.
--
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 44606] [patch] Setting string value for formula
cell yields ClassCastException
Posted by bu...@apache.org.
https://issues.apache.org/bugzilla/show_bug.cgi?id=44606
--- Comment #3 from Josh Micich <jo...@gildedtree.com> 2008-03-14 11:10:10 PST ---
Created an attachment (id=21668)
--> (https://issues.apache.org/bugzilla/attachment.cgi?id=21668)
svn diff of changes to HSSFCell and TestHSSFCell
--
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 44606] [patch] Setting string value for formula
cell yields ClassCastException
Posted by bu...@apache.org.
https://issues.apache.org/bugzilla/show_bug.cgi?id=44606
--- Comment #5 from Antonio Chirizzi <an...@gmail.com> 2008-03-17 03:16:45 PST ---
(In reply to comment #2)
> I took a look at the code+spreadsheet you attached. The latest POI fails well
> before the ClassCastException. This is because since 3.0, support for parsing
> percent formula token was added, but percent evaluation has not been done yet.
> From what I recall, in 3.0 the formula parser would just *stop* upon finding
> any unknown char. That's why you don't *crash* on '%'. If you check the
> evaluated values, you should find that they're out by a factor of 100.
> After I hacked a quick fix for PercentPtg, I could see your ClassCastException
> error. I have attached a patch to fix just this error. (The percent
> evaluation stuff will come a bit later).
> If you don't care about the actual results from HSSFFormulaEvaluator, you can
> just apply this patch to your 3.0 version of the code to get up and running.
> Otherwise you'll have to wait for PercentEval to be added to the svn trunk
> before you'll have a version of POI that can handle your supplied example code.
Thanks a lot Josh, I was able to patch the source and rebuild the jars.
The Exception does not show anymore, the sheet gets saved, the values are
recalculated correctly, but I still have a problem:
Even if the value are recalculated, if I modify a value in the rewritten sheet
or set in the first example program I attached:
sheet.setForceFormulaRecalculation(true);
when I open the workbook all the formulas like "=$D52/$D$47*$H$47*E11/100" show
the "#VALUE" value.
I deleted the % sign as you suggested, and replaced it with the operation
"*cell/100", but I still have problems.
Is the formula too complex?
-Antonio
--
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 44606] [patch] Setting string value for formula
cell yields ClassCastException
Posted by bu...@apache.org.
https://issues.apache.org/bugzilla/show_bug.cgi?id=44606
Nick Burch <ni...@torchbox.com> changed:
What |Removed |Added
----------------------------------------------------------------------------
Status|NEW |RESOLVED
Resolution| |FIXED
--- Comment #4 from Nick Burch <ni...@torchbox.com> 2008-03-16 08:28:37 PST ---
Thanks for the patch Josh, applied to svn trunk
--
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 44606] [patch] Setting string value for formula
cell yields ClassCastException
Posted by bu...@apache.org.
https://issues.apache.org/bugzilla/show_bug.cgi?id=44606
Josh Micich <jo...@gildedtree.com> changed:
What |Removed |Added
----------------------------------------------------------------------------
CC| |e.fougeras@gmail.com
--- Comment #6 from Josh Micich <jo...@gildedtree.com> 2008-04-28 12:00:17 PST ---
*** Bug 44861 has been marked as a duplicate of this bug. ***
--
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 44606] ClassCastException
Posted by bu...@apache.org.
https://issues.apache.org/bugzilla/show_bug.cgi?id=44606
Antonio Chirizzi <an...@gmail.com> changed:
What |Removed |Added
----------------------------------------------------------------------------
CC| |antonio.chirizzi@gmail.com
--- Comment #1 from Antonio Chirizzi <an...@gmail.com> 2008-03-14 09:07:24 PST ---
Please note also that if I modify one cell, the other cells are not
recalculated. So when I open the saved workbook, all the cells shows the
"#VALUE" value.
This looks like another bug.
So I tried to recalculate all the formulas in the sheet, added the for
statement and got the ClassCastException error, I think because of a cell
containg a string and a formula in the same cell.
-Antonio
--
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