Click here to Skip to main content
15,860,972 members
Articles / Web Development / ASP.NET
Article

C# - Retrieve Excel Workbook Sheet Names.

Rate me:
Please Sign up or sign in to vote.
4.88/5 (84 votes)
25 Aug 2004 531.3K   108   49
This article provides a way to retrieve the worksheet names of an Excel Workbook. This can be used to query a workbook using ADO.NET if you do not know the names of the worksheets.

Introduction

There are many examples using ADO.NET to query an Excel Workbook but they all have a limitation, you must know the worksheet name. This might work if you know the name of the worksheet, but what if you don't? If your program is dynamic and your sheet names differ for each Excel workbook, you need a way to extract the names of the sheets. The code example provides you with a way to retrieve the work sheet names.

The following method returns a string array containing the names of the sheets. The method also shows how to loop through the array. The method has one input parameter excelFile which is the location of the Excel file.

Code Example

C#
/// <summary>
/// This mehtod 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();
    }
  }
}

Enjoy...

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here


Written By
United States United States
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
QuestionException: Could not find installable ISAM. Pin
Member 152742302-Jul-21 23:08
Member 152742302-Jul-21 23:08 
QuestionThe function return array of string in sorted order. my original sheetnames are not getting returned. Pin
Member 1326987726-Jul-17 9:49
Member 1326987726-Jul-17 9:49 
QuestionSee also this: Pin
dietmar paul schoder29-Jul-14 5:22
professionaldietmar paul schoder29-Jul-14 5:22 
Questionstop sorting Pin
Member 82406515-May-13 9:23
Member 82406515-May-13 9:23 
Generalmy vote de 100 !!! Pin
Member 87144215-Sep-12 9:18
Member 87144215-Sep-12 9:18 
Question_xlnm#_FilterDatabase Pin
com builder19-Jun-12 0:23
com builder19-Jun-12 0:23 
QuestionFaulty behavior Pin
cilker18-Jun-12 10:09
cilker18-Jun-12 10:09 
I have encountered a problem with this method of retrieving the sheet names. The code looks very fine and it also works but there seem to be pathological sheet names that lead to false returned name strings by this db-connection method, namely, if your sheet name contains a dot (e.g. Release 7.2) then the returned string has replaced the dot with a # (e.g. Release 7#2). When your sheet has names containing # then they are not replaced with another character so you cannot distinguish between replaced dots or with correct named sheets. I also encountered that my first sheet with the name Release 7.2 was returned with an invisible backslash behind the $ character whereas the second sheet only had the $ character, so when I stripped the strings of their last character then the second sheet was without the $ character but the first sheet was with $ character because deleting the last character deleted the backslash character. You should try it out to acknowledge my findings, i.e. give a name sheet 1.3 and the second sheet 1#3 and you should get indistiguishable strings with # in the name. Perhaps the string of the first returned string also contains an invisible backslash as with the name of my first sheet string.
Questionthanks Pin
r4p26-May-12 23:42
r4p26-May-12 23:42 
SuggestionJust for 2003 Pin
dyboo21-Mar-12 22:56
dyboo21-Mar-12 22:56 
QuestionRetrieve Excel Workbook Sheet Names. Pin
rozaq_jack11-Nov-11 0:29
rozaq_jack11-Nov-11 0:29 
GeneralMy vote of 5 Pin
Giannakakis Kostas3-Aug-11 21:14
professionalGiannakakis Kostas3-Aug-11 21:14 
GeneralMy vote of 5 Pin
Ankit Rajput21-Jul-11 2:17
Ankit Rajput21-Jul-11 2:17 
GeneralMy vote of 5 Pin
padmanabhan N10-Dec-10 0:30
padmanabhan N10-Dec-10 0:30 
GeneralC# to C++ Pin
maklein9918-Oct-10 6:31
maklein9918-Oct-10 6:31 
GeneralMy vote of 5 Pin
n52615-Jul-10 18:41
n52615-Jul-10 18:41 
GeneralOrder of Table Pin
Tom arnold25-Feb-10 15:53
Tom arnold25-Feb-10 15:53 
GeneralExcellent Pin
MAP Tiger14-Jan-10 18:26
MAP Tiger14-Jan-10 18:26 
Generalgetting error "no error message available, result code:E_UNEXPECTED (0x8000FFFF) Pin
Alamelu Suresh10-Nov-09 19:31
Alamelu Suresh10-Nov-09 19:31 
QuestionHow can i use this code on linux Pin
Alex_30208647-Sep-09 1:35
Alex_30208647-Sep-09 1:35 
GeneralGreat! Pin
P van Zyl30-Jun-09 2:32
P van Zyl30-Jun-09 2:32 
GeneralThanks Pin
Kenneth_Scott11-Jun-09 6:26
Kenneth_Scott11-Jun-09 6:26 
GeneralVery Helpful Pin
accusteve25-Mar-09 9:10
accusteve25-Mar-09 9:10 
GeneralVery useful Artical Pin
sorabh.jain17-Feb-09 19:43
sorabh.jain17-Feb-09 19:43 
GeneralOrder of Tabs Pin
sdb777-Oct-08 3:52
sdb777-Oct-08 3:52 
GeneralRe: Order of Tabs Pin
Khan_alpha15-Sep-09 6:47
Khan_alpha15-Sep-09 6:47 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.