Bug 44417 - [patch] CellReference.getSheetName() returns a String which HSSFSheet.getSheet() does not find
Summary: [patch] CellReference.getSheetName() returns a String which HSSFSheet.getShee...
Alias: None
Product: POI
Classification: Unclassified
Component: HSSF (show other bugs)
Version: 3.0-FINAL
Hardware: PC Windows XP
: P2 major (vote)
Target Milestone: ---
Assignee: POI Developers List
Depends on:
Reported: 2008-02-13 09:33 UTC by Doug
Modified: 2008-02-15 04:05 UTC (History)
0 users

svn diffs for 16 changed files (53.83 KB, patch)
2008-02-13 22:32 UTC, Josh Micich
Details | Diff
tar bz2 of one added test case (1.01 KB, patch)
2008-02-13 22:32 UTC, Josh Micich
Details | Diff

Note You need to log in before you can comment on or make changes to this bug.
Description Doug 2008-02-13 09:33:51 UTC
Following the example from


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.


    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 

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