Reading from excel with the oledbcommand command

Question

In the code below, instead of specifying the name of the tab .. Should I even say "select * from [tab1]"? whatever the tab name is ..

OleDbCommand excelOledbCommand = new OleDbCommand("Select * From [Sheet1$]", excelOledbCon); 

Answer 1

This can help

Tips for reading Excel spreadsheets using ADO.NET

OleDbConnection.GetOleDbSchemaTable Method

Something like

 OleDbConnection dbConnection = new OleDbConnection (@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\BAR.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 firstSheetName = dbSchema.Rows [0] ["TABLE_NAME"].ToString (); // Now we have the table name; proceed as before: OleDbCommand dbCommand = new OleDbCommand ("SELECT * FROM [" + firstSheetName + "]", dbConnection); OleDbDataReader dbReader = dbCommand.ExecuteReader (); // And so on... } finally { dbConnection.Close (); } 

Answer 2

 public DataSet GetDataSetFromFile() { string strFileName = _FilePath; string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;"; strConn += "Data Source= " + strFileName + "; Extended Properties='Excel 8.0;HDR=No;IMEX=1'"; OleDbConnection ObjConn = new OleDbConnection(strConn); ObjConn.Open(); string strSheetName = getSheetName(ObjConn); OleDbCommand ObjCmd = new OleDbCommand("SELECT * FROM [" + strSheetName + "]", ObjConn); OleDbDataAdapter objDA = new OleDbDataAdapter(); objDA.SelectCommand = ObjCmd; DataSet ObjDataSet = new DataSet(); objDA.Fill(ObjDataSet); ObjConn.Close(); return ObjDataSet; } private string getSheetName(OleDbConnection ObjConn) { string strSheetName = String.Empty; try { System.Data.DataTable dtSheetNames = ObjConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); if (dtSheetNames.Rows.Count > 0) { strSheetName = dtSheetNames.Rows[0]["TABLE_NAME"].ToString(); } return strSheetName; } catch (Exception ex) { throw new Exception("Failed to get the sheet name", ex); } }