Read Data from Excel using OLEDB

问题:

I read an excel file using OLEDB. Below is the code:

            string conn;             
            conn = ("Provider=Microsoft.ACE.OLEDB.12.0;" +
            ("Data Source=" + _filename + ";" +
            "Extended Properties=\"Excel 12.0;\""));
            OleDbConnection oleDBCon = new OleDbConnection(conn);
            oleDBCon.Open();
            DataTable dt = oleDBCon.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);                    
            string SSQL = "SELECT * from [ Sheet1$ ]";
            OleDbDataAdapter oleDA = new OleDbDataAdapter(SSQL, conn);
            DataSet ds = new DataSet();
            oleDA.Fill(ds);
            DataTable _DtTable = ds.Tables[0];
            oleDBCon.Close();
            dataGridView1.DataSource = _DtTable;
            foreach (DataRow rows in _DtTable.Rows)
            {
                string Description = rows[0].ToString();
                string Code= rows[1].ToString();              
                textBox1.AppendText("Printing Description: " + Description + " and Code: " + Code + ",Date:" + DateTime.Now.ToString() + Environment.NewLine);                             
            }

The excel file is as follows:

The data printed in textBox1 are:

Printing Description:Desc 2 and Code: Code 2,Date:20/12/2014 12:36:54 μμ
Printing Description: Desc 3 and Code: Code 3,Date:20/12/2014 12:36:54 μμ

So, my problem is that the 1st row of excel is going to the header of the Datatable. How can I avoid that (without adding any extra 1st row to excel)?

回答1:

Just add "HDR=No" at the end of your connection string which means "No header row that indicates column but it contains data", then you will be able to fetch 1st row data also.

So your complete connection string would be

 conn = ("Provider=Microsoft.ACE.OLEDB.12.0;" +
        ("Data Source=" + _filename + ";" +
        "Extended Properties=\"Excel 12.0;\";HDR=No"));