I am trying to retrieve a list of the worksheets in an Excel workbook, but the collection I get back has both the sheet names and the data column id's, which seem to be called 'Defined Names' in the original xlsx xml. Can you tell me how to return only the worksheet names?
The code I'm using is along the lines of:
OleDbConnection connExcel = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;"
+ @"Data Source=" + FilePath + ";"
+ @"Extended Properties=""Excel 8.0;HDR=Yes;""");
OleDbCommand cmdExcel = new OleDbCommand();
cmdExcel.Connection = connExcel;
connExcel.Open();
DataTable testTable = connExcel.GetSchema("Tables");
The contents of the resulting testTable collection contain entries under TABLE_NAME of:
- DATA1
- DATA2
- DATA3
- DATA4
- DATA5
- Sheet1$
- TEST1 -TEST2
- TESTHKEY
- TESTKEYS
- TESTVKEY
They all have a TABLE_TYPE of TABLE.
The original workbook corresponding to the above would have 1 worksheet containing 5 columns, the first row would contain a header. I'm only interested in the Sheet1$ entry. The spreadsheet is created in Excel 2010, I'm trying to process it in an ASP.NET 4 app written in C#. Potentially, the worksheet name may have been changed so I can't guarrantee that it will always be Sheet1$.