use C# to get excel sheet names

Hi,

I have lots of excel files (*.xls) I need open and read it for further anslysis.

I use the following code to read excel sheet name. However, some excel files work some are not. for those has error. it  is because it will return extra strange sheet names which do not actually appear in the excel file.

for example an error in a excel file return me extra strange sheet names like my attached pictures. (It should be only 1 sheet3$)

I think the file does not have problem because I use Matlab to read excel sheet names. and it return me correct without any extra strange sheet names. like below

 [typ, Stru_sheet_names] = xlsfinfo('D:\VM_SSIS\xls_In\sample3.xls')

typ =

Microsoft Excel Spreadsheet

Stru_sheet_names =

    'Sheet1'    'Sheet2'    'Sheet3'    'Sheet4'    'Sheet5'    'Sheet6'    'Sheet7'    'Sheet8'

Could someone help me about this. thanks in advance

string str_conn_xls = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\\VM_SSIS\\xls_In\\sample3.xls;Extended Properties=\"Excel 8.0;HDR=NO;IMEX=1\"";

OleDbConnection objConn = null;
DataTable DT_sheet = null;

// Create and Open OleDbConnection
objConn = new OleDbConnection(str_conn_xls);
objConn.Open();

// Get the data table   
DT_sheet = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
objConn.Close();


 // get all sheet names
 for (int i = 0; i < DT_sheet.Rows.Count; i++) 
    {
      MessageBox.Show(DT_sheet.Rows[i][2].ToString());
    }

Select allOpen in new window

Snap1.jpg
Snap2.jpg
Snap3.jpg