Bug 44417

Summary: [patch] CellReference.getSheetName() returns a String which HSSFSheet.getSheet() does not find
Product: POI Reporter: Doug <breaux>
Component: HSSFAssignee: POI Developers List <dev>
Status: RESOLVED FIXED    
Severity: major    
Priority: P2    
Version: 3.0-FINAL   
Target Milestone: ---   
Hardware: PC   
OS: Windows XP   
Attachments: svn diffs for 16 changed files
tar bz2 of one added test case

Description Doug 2008-02-13 09:33:51 UTC
Following the example from

http://poi.apache.org/hssf/quick-guide.html#NamedRanges

I have the following code:

    int cellIndex = workbook.getNameIndex(cellName);
    HSSFName name = workbook.getNameAt(cellIndex);
    AreaReference aref = new AreaReference(name.getReference());
    // assume a single cell in named area
    CellReference cref = aref.getAllReferencedCells()[0];
    log.debug(cellName + ": " +
              cref.getSheetName() + ", " + cref.getRow() +
              ", " + cref.getCol());
    HSSFSheet s = workbook.getSheet(cref.getSheetName());

Unfortunately, the getSheet() call returns null.  In the logging line above,
getSheetName() shows single quotes around the name.  If I remove those single
quotes, the getSheet() call works correctly.

i.e.:

    HSSFSheet s = workbook.getSheet(cref.getSheetName().replaceAll("'", ""));
Comment 1 Josh Micich 2008-02-13 22:32:15 UTC
Created attachment 21525 [details]
svn diffs for 16 changed files
Comment 2 Josh Micich 2008-02-13 22:32:48 UTC
Created attachment 21526 [details]
tar bz2 of one added test case
Comment 3 Josh Micich 2008-02-13 22:35:30 UTC
Added patch to fix this bug.

The example provided was not explicit, but it is assumed that the sheet name 
had special characters in it (probably a space).  There has been a recent 
change (44336) that causes "name.getReference()" in the example to return 
quoted sheet names if any special characters are present.  The required fix is 
to make AreaReference and CellReference handle sheet names with special 
characters.


Fixed AreaReference.separateAreaRefs() to only consider cell delimiters (:) 
outside quoted sheet names.
Fixed CellReference.separateRefParts() to handle sheet names with special 
characters (assuming properly delimited).
Made sure CellReference.getSheetName() returns a raw sheet name.


Changed AreaReference to model its 2 CellReference components as first class 
fields 'firstCell' and 'lastCell' instead of an array.

Created new methods "formatAsString()" on AreaReference and CellReference and 
moved important logic out of toString().  Added javadoc.
Replaced all references to AreaReference.toString() and CellReference.toString
() with calls to formatAsString().

Added test cases to existing junits TestAreaReference and TestCellReference.  
Added test suite for org.apache.poi.hssf.util package.

Comment 4 Doug 2008-02-14 06:36:32 UTC
Actually, that's correct, which I meant to include in the initial description. 
That is, in this case, the Sheet name contains spaces in it.  Thanks for the
quick response.
Comment 5 Nick Burch 2008-02-15 04:05:38 UTC
Thanks for the patch Josh. I'd been planning to do something similar, but it
looks from your work that it was a bigger job than I'd expected, so it's great
you did it instead of me :)

Patch applied to svn