Reading from excel using oledbcommand

Question

In the below code instead of specifying the tab name.. Is there anyway we can just say "select * from [tab1]"? what ever the tab name might be..

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

Answer 1

This might 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);
        }
    }