更新时间:2023-01-21 17:56:41
为了成功使用OLEDB提供程序,我们必须考虑几点.
In order to use the OLEDB provider successfully we have to consider a few points.
用于Excel 2003文件的OLEDB提供程序不同于用于 Excel 2007/2010文件.所以,我们要做的第一件事 正在确定Excel文件格式,以便选择正确的提供程序. 在下面的代码示例中,我只是检查文件的扩展名以确定 Excel文件格式.请注意,还有更多详细的方法可以 确定Excel文件格式(例如通过魔术字节).
The OLEDB provider for Excel 2003 files is different from the one used for Excel 2007/2010 files. So, the first thing we have to do is determining the Excel file format in order to select the correct provider. In the code example below I simply check the extension of the file to determine the Excel file format. Please note, that there are more elaborated methods to determine the Excel file format (e.g. via the magic bytes).
要选择Excel工作表的所有行,我们需要知道其名称
Excel工作表.标准工作表名称取决于语言,并且
可以由用户重命名.
因此,我们需要一种方法来确定所含床单的名称
在Excel文件中是独立于语言的(当然也与重命名工作表无关).
幸运的是,OleDbConnection
类提供了
一种称为GetOleDbSchemaTable
的方法,它使我们能够获得所有
Excel文件中的工作表名称.
To select all rows of a Excel sheet we need to know the name of
the Excel sheet. The standard sheet names are language dependent and
could be renamed by the user.
So, we need a way to determine the name of the sheets included
in a Excel file to be language independent (and of course independent of renamed sheets).
Fortunately, the OleDbConnection
class provides
a method called GetOleDbSchemaTable
which allows us to get all
the sheet names in an Excel file.
用于Excel的OLEDB提供程序
支持称为HDR的扩展属性.将HDR
设置为Yes
表示工作表的第一行包含列标题.
因此,如果您使用列标题,则应设置
HDR=Yes
.
The OLEDB provider for Excel
supports an extended property called HDR. Setting HDR
to Yes
means that the first row of a sheet contains the column titles.
So, if you use column titles you should set
HDR=Yes
.
因此,要总结以下代码示例,请执行以下操作(单击按钮):
So, to summarize the code sample below does the following (on button click):
mytable
的数据表中.listbox1
的列表框中显示第一列的所有值.mytable
.listbox1
.代码示例:
private static bool IsExcelXmlFileFormat(string fileName)
{
return fileName.EndsWith("xlsx", StringComparison.OrdinalIgnoreCase);
}
private void button1_Click(object sender, EventArgs e)
{
// Open your FileOpenDialog and let the user select a file...
string fileName = "c:\\temp\\myexcelfile.xlsx";
OleDbConnectionStringBuilder connStringBuilder =
new OleDbConnectionStringBuilder();
connStringBuilder.DataSource = fileName;
if (IsExcelXmlFileFormat(fileName))
{
// Set HDR=Yes if first row contains column titles.
connStringBuilder.Provider = "Microsoft.ACE.OLEDB.12.0";
connStringBuilder.Add("Extended Properties", "Excel 8.0;HDR=NO;");
}
else
{
connStringBuilder.Provider = "Microsoft.Jet.OLEDB.4.0";
connStringBuilder.Add("Extended Properties", "Excel 8.0;");
}
DataSet data = new DataSet();
using (OleDbConnection dbConn = new OleDbConnection(connStringBuilder.ConnectionString))
{
dbConn.Open();
DataTable sheets = dbConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
using (OleDbCommand selectCmd = new OleDbCommand(
String.Format("SELECT * FROM [{0}]", sheets.Rows[0]["TABLE_NAME"]), dbConn))
{
using (OleDbDataAdapter dbAdapter = new OleDbDataAdapter())
{
dbAdapter.SelectCommand = selectCmd;
dbAdapter.Fill(data, "mytable");
}
}
}
// To enumerate all rows use the following code.
// foreach (DataRow row in data.Tables["mytable"].Rows)
// {
// Console.Out.WriteLine(row[0]);
// }
// Display the values of column 0 in a listbox called listBox1.
listBox1.ValueMember = data.Tables["mytable"].Columns[0].ColumnName;
listBox1.DisplayMember = data.Tables["mytable"].Columns[0].ColumnName;
listBox1.DataSource = data.Tables["mytable"];
}