Click here to Skip to main content
15,860,861 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 
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.