Monday, July 12, 2010

Retrieving Sheet Names in Excel - C#

The following code returns an array of string; each element in the array is the name of a sheet in the excelFile.

The one parameter is a string to the path of the excel file.
public String[] GetExcelSheetNames(string excelFile)
{
     OleDbConnection objConn = null;
     System.Data.DataTable dt = null;

     try
     {
        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] = cleanSheetName(row["TABLE_NAME"].ToString());
           i++;
        }

        return excelSheets;
    }
    catch (Exception ex){return null;}

    finally
    {
      // Clean up.
      if (objConn != null)
      {
         objConn.Close();
         objConn.Dispose();
      }
      if (dt != null)
      {
         dt.Dispose();
      }
   }
}

Note: Credits cannot go to me for this one. I found most of this code on Google and only edited it slightly.

0 comments:

Post a Comment