Work at SourceForge, help us to make it a better place! We have an immediate need for a Support Technician in our San Francisco or Denver office.

Close

Excel sheet by name

2004-11-25
2013-04-17
  • Is it possible to access an Excel worksheet by name rather than index item?

    For example:

    ExcelWorkbooks xlBooks = excel.Workbooks();
    ExcelWorkbook xlBook = xlBooks.Open(xls);
    ExcelWorksheets xlSheets = xlBook.Worksheets();
    ExcelWorksheet xlSheet = xlSheets.Item("Costing");

    Thanks

    Rob

     
    • Yes, but it takes some work

      ExcelWorkbooks xlBooks=excel.Workbooks();
      ExcelWorkbook xlBook = xlBooks.Open(xls);
      ExcelWorksheets xlSheets = xlBook.Worksheets();

      //get number of worksheets
      int iSheets = xlSheets.Count();

      //put worksheet names into array
      String sSheetNames[] = new String[iSheets];
      for( int i = 0; i < iSheets; i++ ){
              sSheetNames[i] = xlSheets.item[i+1].Name();
      }

      // Call SheetNameLookup by name of sheet to get Sheet index number
      int shnCosting = SheetNameLookup("Costing", iSheets, sSheetNames);

      // Assign worksheet

      ExcelWorksheet shtCosting = xlSheets.Item(shnCosting);

      //  ...  rest of code

      //SheetNameLookup method

      private static int SheetNameLookup(String SheetName, int NumOfSheets, String[] SheetList){
           int iReturn = 0;
           for ( int i = 0; i < NumOfSheets; i++ ){
                  if( SheetName.equals(SheetList[i]) ){
                          iReturn = i + 1;
                  }
            }
            return iReturn;
      }

      Hope this helps

      I did this as part of my first real Java application
      so excuse any violation of Java "best practices"

      Randy