CODEWITHSUNDEEP
c#excel
Kinyanjui Kamau
Kinyanjui Kamau

Reputation: 1926

Reading the 1st Excel sheet of a workbook by default

I have the following code that I am using to read an excel sheet. The workbooks that will be using this code will only be having a single sheet. The sheet names may change but I just want to be able to read the first sheet by default whatever the name. If possible how does one achieve this?

if (fileName != string.Empty)
                    {
                        string connString = "";
                        System.Data.DataTable dt = new System.Data.DataTable();

                        // Initialize connection string
                        connString = String.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 8.0;HDR=YES\"", fileName);

                        // Connect
                        OleDbConnection myConnection = new OleDbConnection(connString);

                        // Open connection if closed
                        if (myConnection.State != ConnectionState.Open)
                            myConnection.Open();

                        string sql = "SELECT * from [INTKEAP_INV_DST_48$]";

                        OleDbCommand cmd = new OleDbCommand(sql, myConnection);
                        cmd.CommandType = CommandType.Text;

                        OleDbDataAdapter adapter = new OleDbDataAdapter(cmd);

                        adapter.Fill(dt);

......... Any help appreciated.

Upvotes: 1

Views: 3302

Answers (1)

Yulia V
Yulia V

Reputation: 3559

My snippet, including more careful resouces handling:

const string CONNECTION_STRING = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=<FILENAME>;Extended Properties=\"Excel 8.0;HDR=no;\";";
OleDbConnection objConnection = new OleDbConnection(CONNECTION_STRING.Replace("<FILENAME>", fullFileName));
DataSet dsImport = new DataSet();       
try
{
    objConnection.Open();
     DataTable dtSchema = objConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
     if( (null != dtSchema) && ( dtSchema.Rows.Count > 0 ) )
    {
        string firstSheetName == dtSchema.Rows[0]["TABLE_NAME"].ToString();
         new OleDbDataAdapter("SELECT * FROM [" + firstSheetName + "]", objConnection).Fill(dsImport);
    }
    catch
    {
        throw;
    }
    finally
    {
        // Clean up.
        if(objConnection != null)
        {
        objConnection.Close();
             objConnection.Dispose();
        }
    }
return (dsImport.Tables.Count > 0) ? dsImport.Tables[0] : null;

Upvotes: 2

Related Questions