Bug 48923 - Method Name.getSheetIndex() fails to return valid sheet index
Summary: Method Name.getSheetIndex() fails to return valid sheet index
Alias: None
Product: POI
Classification: Unclassified
Component: XSSF (show other bugs)
Version: 3.6-FINAL
Hardware: PC Windows 2000
: P2 normal (vote)
Target Milestone: ---
Assignee: POI Developers List
Depends on:
Reported: 2010-03-16 16:38 UTC by Wayne Holder
Modified: 2010-05-26 10:52 UTC (History)
0 users

Test file for example code (9.89 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2010-03-16 16:38 UTC, Wayne Holder

Note You need to log in before you can comment on or make changes to this bug.
Description Wayne Holder 2010-03-16 16:38:40 UTC
Created attachment 25136 [details]
Test file for example code

In this example:

    InputStream inp = new FileInputStream("Book.xlsx");  // or .xls
    Workbook wb = WorkbookFactory.create(inp);
    int names = wb.getNumberOfNames() ;
    if (names > 0) {
      for (int ii = 0; ii < names; ii++) {
         Name name = wb.getNameAt(ii);
         String sName = name.getSheetName();
         //int nameIdx = name.getSheetIndex();        // Hmm... doesn't work
         int nameIdx = wb.getSheetIndex(sName);       // Alternate method

the commented out line always returns -1, even when the named cell, or range is
on a specific sheet.  The following line shows a workaround.  Note: this problem
also happens for .xls files.

Comment 1 Nick Burch 2010-05-26 10:52:07 UTC
You're using the wrong method there.

getSheetIndex will tell you which Sheet a named range is DEFINED FOR, or -1 if it applies to the whole of the workbook (see the javadocs for details). Your named ranges apply to the whole of the workbook, which is why you get -1

You probably want name.getSheetName(), which gives you the sheet it references, not the sheet it's defined on