This example shows that how to extract all spread sheet names of any excel file in vb.net.
You can find all spreadsheet or worksheet names in a string array. Before using this code sample add reference of ‘Microsoft ADO Ext 6.0 for DLL and Security’ (you can find in the com references) and ‘adodb’.
public string[] GetExcelSheetfromExcelFile(string strFileName) { string[] strTables = null; ADOX.Catalog oCatlog = new ADOX.Catalog(); ADOX.Table oTable = new ADOX.Table(); ADODB.Connection oConn = new ADODB.Connection(); if (strFileName.Contains(".xlsx")) { oConn.Open("Provider=Microsoft.ACE.OLEDB.12.0; Data Source = " + strFileName + "; Extended Properties = \"Excel 12.0;HDR=Yes;IMEX=1\";", "", "", 0); } else { oConn.Open("Provider=Microsoft.Jet.OleDb.4.0; Data Source = " + strFileName + "; Extended Properties = \"Excel 8.0;HDR=Yes;IMEX=1\";", "", "", 0); } oCatlog.ActiveConnection = oConn; if (oCatlog.Tables.Count > 0) { int item = 0; foreach (ADOX.Table tab in oCatlog.Tables) { if (tab.Type == "TABLE") { if (!tab.Name.Contains("#")) { Array.Resize(ref strTables, item + 1); strTables[item] = tab.Name; item = item + 1; } } } } if (oConn.State == 1) { oConn.Close(); } oCatlog.ActiveConnection = null; oCatlog = null; oTable = null; return strTables; }
you can use this function like :
string[] strSpreadsheets = GetExcelSheetfromExcelFile("E:\\AuthorCode\\CodeAuthor Articels.xlsx");