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("'", ""));
Created attachment 21525 [details] svn diffs for 16 changed files
Created attachment 21526 [details] tar bz2 of one added test case
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.
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.
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