在C＃中如何使用OLEDB（不使用自动化）访问Excel头文件？

This is my code where I am trying to access first row, first column

这是我的代码，我试图访问第一行，第一列

     string connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;
                                      Data Source=" + fileName + @";Extended Properties=""Excel 8.0;HDR=NO;""";
            string CreateCommand = "SELECT * FROM [Sheet1$]";
            OleDbConnection conn = new OleDbConnection(connectionString);

              conn.Open();
              OleDbCommand cmd = new OleDbCommand(CreateCommand, conn);
             //   cmd.ExecuteNonQuery();
               DbDataReader dr= cmd.ExecuteReader();

              int i = 0;

               while (dr.Read())
               {

                   string ab = dr.GetValue(i).ToString();
                   MessageBox.Show(ab);
                   i++;
               }

6 个解决方案

#1

Did you try HDR=YES ? That's what tells the OLEDB provider that you do have a header row.

你尝试过HDR = YES吗？这就是告诉OLEDB提供商你有标题行的原因。

http://connectionstrings.com/excel

#2

I've always used the built in functions of GetSchema to enumerate Sheets and Headers. It's really quite slick and no non-sense. Good Luck!

我总是使用GetSchema的内置函数来枚举表格和标题。它真的非常光滑，没有任何无意义。祝你好运！

OleDbConnection xl = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=filename.xlsx;Extended Properties=\"Excel 12.0 Xml;HDR=YES;IMEX=1\"");
xl.Open();

//Get columns
DataTable dtColumns = xl.GetSchema("Columns", new string[] { null, null, sheetName, null });
List<string> columns = new List<string>();
foreach (DataRow dr in dtColumns.Rows)
   columns.Add(dr[3].ToString());

xl.Close();

#3

Wouldn't you want to set the HDR=No?

你不想设置HDR =否？

Telling the OLEDB provider that the first row contains headers will cause the provider to use the headers as the names for the fields. (I'm thinking about dumping the info into a datatable, after which you get the information @ DataTable.Columns["[HEADER]"].Row....)

告诉OLEDB提供程序第一行包含标题将导致提供程序使用标头作为字段的名称。（我正在考虑将信息转储到数据表中，之后你会得到@ DataTable.Columns [“[HEADER]”]的信息。行....）

Since you're using a simple data reader, and you want the "header" fields to be read as data, specify that these are not headers.

由于您使用的是简单的数据读取器，并且希望将“标题”字段作为数据读取，因此请指定这些字段不是标题。

#4

I had a different problem, but was able to access the first row of Excel data and remove it via this OleDBAdapter Excel QA I posted via stack overflow.

我有一个不同的问题，但能够访问第一行Excel数据，并通过堆栈溢出发布的OleDBAdapter Excel QA删除它。

If you are trying to access first row, first column, just populate a dataset per the post I mentioned and add to the bottom:

如果您尝试访问第一行，第一列，只需按照我提到的帖子填充数据集并添加到底部：

    // DataSet:          
    Object o = ds.Tables["xlsImport"].Rows[0]["LocationID"];
    Object oa = ds.Tables["xlsImport"].Rows[0]["PartID"];            
    Object row0Col3 = ds.Tables["xlsImport"].Rows[0][3];

    string valLocationID = o.ToString();
    string valPartID = oa.ToString();
    string rowZeroColumn3 = row0Col3.ToString();

#5

// CODE TO SET UP THE CONNECTION BETWEEN EXCEL AND VS2005 
// IN EXTENDED PROPERTIES SET HDR = YES FOR READING FIRST ROW AND HEADERS.
// IN EXTENDED PROPERTIES SET IMEX = 1 TO READ INTERMIXED DATA.

excelCon = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\ExcelDBtrial.xls;Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'");
excelCon.Open();
exDA = new OleDbDataAdapter("Select * from [Sheet1$]", excelCon);
exDA.Fill(exDT);

//CODE TO ADD TABLE HEADERS INTO THE HEADERS COMBOBOX
foreach (DataColumn dc in exDT.Columns)
    headerCB.Items.Add(dc.ToString());

#6

string xlPath = @"D:\Temparary.xlsx";    //location of xlsx file

string constr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + xlPath + ";Extended Properties=\"Excel 12.0 Xml; HDR=YES; IMEX=1;\"";

OleDbConnection con = new OleDbConnection(constr);

OleDbCommand cmd = new OleDbCommand("select * from [Sheet1$]",con);

con.Open();

OleDbDataReader dreader = cmd.ExecuteReader();

if (dreader.HasRows)
{
    dreader.Read();
    Label2.Text = dreader.GetValue(0).ToString();

}

dreader.Close();

con.Close();

#1