使用jet.oledb.4.0导入excel表，将第一行设置为列名 [英] Import excel sheet using jet.oledb.4.0 , setting first row as column name

问题描述

我正在使用 Jet.Oledb 将excel文件导入到datatable。这是我的代码：

i am trying to import an excel file into a datatable in using Jet.Oledb. Here is my code:

            string sourceConstr = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + HttpContext.Current.Server.MapPath("~/" + fileName) + "';Extended Properties='Excel 8.0;HDR=No;IMEX=1'";
            OleDbConnection con = new OleDbConnection(sourceConstr);
            OleDbCommand oleDbCmd = new OleDbCommand();
            con.Open();
            oleDbCmd.Connection = con;
            DataTable dt = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

            string firstExcelSheetName=dt.Rows[0][2].ToString();
            string query = "select * from [" + firstExcelSheetName + "]";
            OleDbDataAdapter data = new OleDbDataAdapter(query, con);
            data.TableMappings.Add("Table", "dtExcel");
            data.Fill(dtExcel);

在excel文件中，第一行有列名。但是我的datatable作为列名称和第一行的excel文件的第一行得到（f1，f2，f3，...）。所以我可以猜到我必须告诉第一行包含columnName。但是如何才能实现？

In the excel file, first row has the columnname. but my datatable is getting (f1,f2,f3,...) as column name and first row of the excel file as first row. So i can guess I have to tell that the first row contains columnName. But how can i make it happen?

推荐答案

从 connectionstrings.com ：

HDR =是表示第一行包含列名，而不包含数据。 HDR =无;表示相反。

"HDR=Yes;" indicates that the first row contains columnnames, not data. "HDR=No;" indicates the opposite.

所以你可以尝试更改连接字符串来设置 HDR = Yes 。

So you could try changing your connection string to set HDR=Yes.

这篇关于使用jet.oledb.4.0导入excel表，将第一行设置为列名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！