Bug 36646 - FormulaEvaluator fails to handle properly quoted spaces in cell reference sheet name for Excel 2002 SP1
Summary: FormulaEvaluator fails to handle properly quoted spaces in cell reference she...
Status: RESOLVED FIXED
Alias: None
Product: POI
Classification: Unclassified
Component: HSSF (show other bugs)
Version: 3.0-dev
Hardware: Other Linux
: P2 major (vote)
Target Milestone: ---
Assignee: POI Developers List
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2005-09-14 00:32 UTC by John Broglio
Modified: 2006-08-27 20:36 UTC (History)
0 users


Attachments
minimal xls test file containg ref. formula with space; Excell 2002 SP1 (13.50 KB, application/vnd.ms-excel)
2005-09-14 00:36 UTC, John Broglio 		Details
Code that reproduces problem. In default package. (1.62 KB, text/plain)
2005-09-14 00:41 UTC, John Broglio 		Details
Workaround code to get value of a formula cell without FormulaEvaluator (2.47 KB, text/x-java)
2005-09-14 19:19 UTC, John Broglio 		Details
View All Add an attachment (proposed patch, testcase, etc.)

Note You need to log in before you can comment on or make changes to this bug.
Description John Broglio 2005-09-14 00:32:53 UTC 
(Will attach test code and xls file.) 
When running FormulaEvaluator on a cell containing the reference formula
  ='Sheet 1'!A1
the Exception below is seen.  A minimal test file also shows that
FormulaParser.toFormulaString(...) is also missing the single quotes that exist
in the formula.  I was not able to identify where the single quotes were being
dropped (or try to fix it) after many tries with the debugger -- I don't know
the internal POI code yet.
================================
Exception in thread "main" java.lang.StringIndexOutOfBoundsException: String
index out of range: 0
	at java.lang.String.charAt(String.java:444)
	at org.apache.poi.hssf.util.CellReference.<init>(CellReference.java:44)
	at org.apache.poi.hssf.record.formula.ReferencePtg.<init>(ReferencePtg.java:50)
	at org.apache.poi.hssf.model.FormulaParser.Ident(FormulaParser.java:292)
	at org.apache.poi.hssf.model.FormulaParser.Factor(FormulaParser.java:480)
	at org.apache.poi.hssf.model.FormulaParser.Term(FormulaParser.java:569)
	at org.apache.poi.hssf.model.FormulaParser.Expression(FormulaParser.java:617)
	at org.apache.poi.hssf.model.FormulaParser.parse(FormulaParser.java:721)
	at
org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator.internalEvaluate(HSSFFormulaEvaluator.java:284)
	at
org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator.evaluate(HSSFFormulaEvaluator.java:181)
	at com.gametable.poi.Sandbox$Test.main(Sandbox.java:186)
=======
Workaround: I can't think of a reasonable one, unless one has control of the
spreadsheet generation.
Comment 1 John Broglio 2005-09-14 00:36:31 UTC 
Created attachment 16398 [details]
minimal xls test file containg ref. formula with space; Excell 2002 SP1
Comment 2 John Broglio 2005-09-14 00:41:23 UTC 
Created attachment 16399 [details]
Code that reproduces problem.  In default package.
Comment 3 John Broglio 2005-09-14 00:55:52 UTC 
Workaround: if you just want to get the cell value, this works for many cases in
3.0-alpha1:
//============= code ===================
    private static String tryAnything(HSSFCell cell)
    {
        try{
            return cell.getNumericCellValue()+"";
        } catch (NumberFormatException e){
            try{
                return cell.getStringCellValue();
            } catch (NumberFormatException f){
                try{
                    return cell.getStringCellValue();
                } catch (NumberFormatException g){
                    try {
                    return cell.getDateCellValue()+"";
                    } catch (NumberFormatException h){
                        // getCellBooleanValue doesn't allow fallthrough to
pre-computed formula
                        // so presumably you got a 1/0 on getNumericCellValue 
                        // earlier.
                        return "Value not available in field.";
                    }
                }
            }

        }
    }
//=============== end of code snippet ============
Comment 4 John Broglio 2005-09-14 19:19:45 UTC 
Created attachment 16407 [details]
Workaround code to get value of a formula cell without FormulaEvaluator

Workaround in bug text was incorrect.
Comment 5 Jason Height 2006-08-28 03:36:24 UTC 
Problem was in Ref3DPtg which did not quote the sheet name if it contained a
string/Sheetname with a space

Fixed in SVN.

Jason