A Method to return Sheets Names in an EXCEL file using C# ADO.NET
public static List<string> GetSheetsNames(string path)
{
List<string> sheets = new List<string>();
string connectionString = @”Provider=Microsoft.Jet.OLEDB.4.0;” +
“Data Source=” + path + “;” +
@”Extended Properties=””Excel 8.0;HDR=YES;”””;
DbProviderFactory factory = DbProviderFactories.GetFactory(“System.Data.OleDb”);
DbConnection connection = factory.CreateConnection();
connection.ConnectionString = connectionString;
connection.Open();
DataTable tbl = connection.GetSchema(“Tables”);
connection.Close();
foreach (DataRow row in tbl.Rows)
{
string sheetName = (string)row[“TABLE_NAME”];
if (sheetName.EndsWith(“$”))
{
sheetName = sheetName.Substring(0, sheetName.Length – 1);
}
sheets.Add(sheetName);
}
return sheets;
}
Note that the schema has each sheet name ending with $. you must have the $ in the sheet name in order to read from or write to the excel using ADO.NET
Thanks mate! this is all I looked for 🙂
thanks 4 help ,, but the sheet names in the array r in assending order not in the order the sheets are in workbook ,,
but he sheet name in array r in asending order ,, not in the order sheet name are in excel sheet
Hi Stev,
with the code above you don’t (not always) get the sheetnames in the order the sheets appear while opening Excel.
I’m tried this in SSIS 2005 ScriptTask (only VB is allowed ;-(((( ) but didn’t work.
I’m using now something like the following:
You have to use Microsoft.Office.Interop.Excel
here the code snippets in VB (sorry …)
Imports Microsoft.Office.Interop.Excel
Private _app As Microsoft.Office.Interop.Excel.Application
Private _books As Microsoft.Office.Interop.Excel.Workbooks
Private _book As Microsoft.Office.Interop.Excel.Workbook
Protected _sheets As Microsoft.Office.Interop.Excel.Sheets
Protected _sheet As Microsoft.Office.Interop.Excel.Worksheet
‘opening
_app = New Microsoft.Office.Interop.Excel.Application()
If _book Is Nothing Then
_books = _app.Workbooks
_book = _books.Open(YourFileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing)
_sheets = _book.Worksheets
End If
Sheetname of the first Sheet will be:
CType(_sheets(1), Microsoft.Office.Interop.Excel.Worksheet).Name
‘closing
_book.Close(False, Type.Missing, Type.Missing)
Hope this helps you
Best regards
Helmut
Thank you so much. Very useful.