Bug 44606 - [patch] Setting string value for formula cell yields ClassCastException
Summary: [patch] Setting string value for formula cell yields ClassCastException
Status: RESOLVED FIXED
Alias: None
Product: POI
Classification: Unclassified
Component: HSSF (show other bugs)
Version: 3.0-FINAL
Hardware: PC Windows 2000
: P2 blocker (vote)
Target Milestone: ---
Assignee: POI Developers List
URL:
Keywords:
: 44861 (view as bug list)
Depends on:
Blocks:
 
Reported: 2008-03-14 08:49 UTC by Antonio Chirizzi
Modified: 2008-04-28 12:00 UTC (History)
2 users (show)



Attachments
The java file to compile, and the xls file. Run the java file using the xls file as argument to get the error. (5.93 KB, text/plain)
2008-03-14 08:49 UTC, Antonio Chirizzi
Details
svn diff of changes to HSSFCell and TestHSSFCell (5.07 KB, patch)
2008-03-14 11:10 UTC, Josh Micich
Details | Diff

Note You need to log in before you can comment on or make changes to this bug.
Description Antonio Chirizzi 2008-03-14 08:49:45 UTC
Created attachment 21666 [details]
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 ----
                    }
                }
            }
        }
Comment 1 Antonio Chirizzi 2008-03-14 09:07:24 UTC
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
Comment 2 Josh Micich 2008-03-14 11:09:04 UTC
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.
Comment 3 Josh Micich 2008-03-14 11:10:10 UTC
Created attachment 21668 [details]
svn diff of changes to HSSFCell and TestHSSFCell
Comment 4 Nick Burch 2008-03-16 08:28:37 UTC
Thanks for the patch Josh, applied to svn trunk
Comment 5 Antonio Chirizzi 2008-03-17 03:16:45 UTC
(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 
Comment 6 Josh Micich 2008-04-28 12:00:17 UTC
*** Bug 44861 has been marked as a duplicate of this bug. ***