Using Excel OleDb to get sheet names IN SHEET ORDER

Question

I'm using OleDb to read from an excel workbook with many sheets.

I need to read the sheet names, but I need them in the order they are defined in the spreadsheet; so If I have a file that looks like this;

|_____|_____|____|____|____|____|____|____|____|
|_____|_____|____|____|____|____|____|____|____|
|_____|_____|____|____|____|____|____|____|____|
\__GERMANY__/\__UK__/\__IRELAND__/

Then I need to get the dictionary

1="GERMANY", 
2="UK", 
3="IRELAND"

I've tried using OleDbConnection.GetOleDbSchemaTable(), and that gives me the list of names, but it alphabetically sorts them. The alpha-sort means I don't know which sheet number a particular name corresponds to. So I get;

GERMANY, IRELAND, UK

which has changed the order of UK and IRELAND.

The reason I need it to be sorted is that I have to let the user choose a range of data by name or index; they can ask for 'all the data from GERMANY to IRELAND' or 'data from sheet 1 to sheet 3'.

Any ideas would be greatly appreciated.

if I could use the office interop classes, this would be straightforward. Unfortunately, I can't because the interop classes don't work reliably in non-interactive environments such as windows services and ASP.NET sites, so I needed to use OLEDB.

Answer 1

Can you not just loop through the sheets from 0 to Count of names -1? that way you should get them in the correct order.

Edit

I noticed through the comments that there are a lot of concerns about using the Interop classes to retrieve the sheet names. Therefore here is an example using OLEDB to retrieve them:

/// <summary>
/// This method retrieves the excel sheet names from 
/// an excel workbook.
/// </summary>
/// <param name="excelFile">The excel file.</param>
/// <returns>String[]</returns>
private String[] GetExcelSheetNames(string excelFile)
{
    OleDbConnection objConn = null;
    System.Data.DataTable dt = null;

    try
    {
        // Connection String. Change the excel file to the file you
        // will search.
        String connString = "Provider=Microsoft.Jet.OLEDB.4.0;" + 
          "Data Source=" + excelFile + ";Extended Properties=Excel 8.0;";
        // Create connection object by using the preceding connection string.
        objConn = new OleDbConnection(connString);
        // Open connection with the database.
        objConn.Open();
        // Get the data table containg the schema guid.
        dt = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

        if(dt == null)
        {
           return null;
        }

        String[] excelSheets = new String[dt.Rows.Count];
        int i = 0;

        // Add the sheet name to the string array.
        foreach(DataRow row in dt.Rows)
        {
           excelSheets[i] = row["TABLE_NAME"].ToString();
           i++;
        }

        // Loop through all of the sheets if you want too...
        for(int j=0; j < excelSheets.Length; j++)
        {
            // Query each excel sheet.
        }

        return excelSheets;
   }
   catch(Exception ex)
   {
       return null;
   }
   finally
   {
      // Clean up.
      if(objConn != null)
      {
          objConn.Close();
          objConn.Dispose();
      }
      if(dt != null)
      {
          dt.Dispose();
      }
   }
}

Extracted from Article on the CodeProject.

Answer 2

To James: I have the same problem as Steve Cooper. When I loop through the list of names until I hit the Nth element, the list is still sorted in alphabetical order. So, in Steve's example, he and I need get the second sheet name and need it to be "UK", but looping returns "Ireland".

Maybe I misunderstood what you mean by "looping through the sheets"?

Answer 3

This worked for me. Stolen from here: http://stackoverflow.com/questions/1427348/how-do-you-get-the-name-of-the-first-page-of-an-excel-workbook

        object opt = System.Reflection.Missing.Value;
        Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
        Excel.Workbook workbook = app.Workbooks.Open(WorkBookToOpen,
                                                 opt, opt, opt, opt, opt, opt, opt,
                                                 opt, opt, opt, opt, opt, opt, opt);
        Excel.Worksheet worksheet = workbook.Worksheets[1] as Microsoft.Office.Interop.Excel.Worksheet;
        string firstSheetName = worksheet.Name;

Answer 4

Hi Steve,

Were you able to get the sheet names in order as they exist in the file? I got same problem and cann't figure out how to get the sheet names in the original order(unsorted) using OLEDB. Anybody else got the solution?

Thanks, AQ78

Answer 5

Try this. Here is the code to get the sheet names in order.

    private Dictionary<int,string> GetExcelSheetNames(string fileName)
    {
     Excel.Application _excel = null;
     Excel.Workbook _workBook = null;
     Dictionary<int,string> excelSheets = new Dictionary<int,string>();
     try
     {
      object missing = Type.Missing;
      object readOnly = true;

  Excel.XlFileFormat.xlWorkbookNormal
  _excel = new Excel.ApplicationClass();
  _excel.Visible = false;
  _workBook = _excel.Workbooks.Open(fileName, 0, readOnly, 5, missing,
     missing, true, Excel.XlPlatform.xlWindows, "\\t", false, false, 0, true, true, missing);
  if (_workBook != null)
  {

                   int index = 0; 

   foreach (Excel.Worksheet sheet in _workBook.Sheets)
   {
    // Can get sheet names in order they are in workbook
    excelSheets.Add(++index, sheet.Name);
   }
  }
 }
 catch (Exception e)
 {
  return null;
 }
 finally
 {
  if (_excel != null)
  {

   if (_workBook != null)
   {
    _workBook.Close(false, Type.Missing, Type.Missing);
   }
   _excel.Application.Quit();
  }
  _excel = null;
  _workBook = null;
 }
    return excelSheets;
    }