使用OLEDB从Excel读取数据 [英] Read Data from Excel using OLEDB

问题描述

我使用OLEDB读取了一个Excel文件.下面是代码:

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

excel文件如下:

The excel file is as follows:

textBox1中打印的数据是:

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 μμ

所以，我的问题是excel的第一行将转到Datatable的标题.如何避免这种情况(不向Excel添加任何额外的第一行)?

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)?

推荐答案

只需在连接字符串的末尾添加"HDR = No"，这意味着没有标题行指示列但包含数据"，那么您将还能获取第一行数据.

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"));

这篇关于使用OLEDB从Excel读取数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！