Retrieving Excel Sheet Names in Sheet Order Using OLEDB

The task of retrieving sheet names from an Excel workbook is commonly encountered in programming. However, obtaining these names in the order they are defined in the spreadsheet can be challenging when using OleDb.

Problem Definition

By rearranging the sheet names alphabetically, OleDbConnection.GetOleDbSchemaTable() fails to provide sheet names in the desired order. This hinders the user's ability to specify data retrieval based on sheet name or index, leading to confusion.

Solution Using Nested Looping

One approach involves iterating through the sheets sequentially from sheet 0 to the number of sheets minus 1. This ensures the preservation of sheet order.

Implementation Using OLEDB

If using the Office Interop classes is not feasible, a solution using OLEDB is available:

/// 

/// Retrieves excel sheet names from an excel workbook.
/// 
/// The excel file.
/// String[]
private String[] GetExcelSheetNames(string excelFile)
{
    OleDbConnection objConn = null;
    System.Data.DataTable dt = null;

    try
    {
        // Connection String
        String connString = "Provider=Microsoft.Jet.OLEDB.4.0;"   
          "Data Source="   excelFile   ";Extended Properties=Excel 8.0;";

        // Create connection and open connection to database
        objConn = new OleDbConnection(connString);
        objConn.Open();

        // Get data table containing schema guid
        dt = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

        if(dt == null)
           return null;

        // Initialize String[] to store sheet names.
        String[] excelSheets = new String[dt.Rows.Count];
        int i = 0;

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

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

This code connects to the Excel file, retrieves the data table containing sheet names, and populates a String[] with these names in the order they appear in the spreadsheet.