在 C# 中如何使用 OLEDB（不带自动化）访问 excel 标头？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/832001/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
In C# how to access excel headers using OLEDB (without Automation)?
提问by
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++;
}
回答by Alan McBee - MSFT
Did you try HDR=YES ? That's what tells the OLEDB provider that you do have a header row.
你试过 HDR=YES 吗？这就是告诉 OLEDB 提供者您确实有标题行的原因。
回答by corey broderick
Wouldn't you want to set the HDR=No?
您不想设置 HDR=No 吗？
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]"].Row....）
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.
由于您使用的是简单的数据读取器，并且您希望将“标题”字段作为数据读取，请指定这些不是标题。
回答by NISHEED
// 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());
回答by Brian Wells
I had a different problem, but was able to access the first row of Excel data and remove it via this OleDBAdapter Excel QAI 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();
回答by Snives
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 的内置函数来枚举 Sheets 和 Headers。它真的很光滑，没有废话。祝你好运！
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();
回答by Jayasankar
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();
