C# ADO.NET get all tab names in Excel spreadsheet

I am trying to get all the tab names that have data from an Excel spreadsheet.

Somewhere I found this code and want to adapt it to loop through all tabs (worksheets) and store it in a drop down combobox:

OleDbConnection dbConnection = new OleDbConnection (@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\SAMPLE.XLS;Extended Properties=""Excel 8.0;HDR=Yes;""");
dbConnection.Open ();
try
{
    // Get the name of the first worksheet:
    DataTable dbSchema = dbConnection.GetOleDbSchemaTable (OleDbSchemaGuid.Tables, null);
    if (dbSchema == null || dbSchema.Rows.Count < 1)
    {
        throw new Exception ("Error: Could not determine the name of the first worksheet.");
    }
    string SheetNameOne = dbSchema.Rows [0] ["TABLE_NAME"].ToString ();
}
finally
{
    dbConnection.Close ();
}
conrad2010Asked:
Who is Participating?
 
wdosanjosConnect With a Mentor Commented:
Please try the following:

using (var dbConnection = new OleDbConnection (@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\SAMPLE.xls;Extended Properties=""Excel 8.0;HDR=Yes;"""))
{
	dbConnection.Open ();

	foreach (DataRow row in dbConnection.GetOleDbSchemaTable (OleDbSchemaGuid.Tables, null).Rows)
	{
		Console.WriteLine(row["TABLE_NAME"]);
	}
}

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.