如何使用C#将数据从Excel的一列导入列表框

为了成功使用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):

1. 根据文件扩展名确定Excel文件类型.
2. 根据excel文件类型选择正确的OLEDB提供程序以建立连接字符串.
3. 确定Excel文件中包含的图纸名称.
4. 选择第一张表，选择所有行并将这些行存储在名为mytable的数据表中.
5. 在名为listbox1的列表框中显示第一列的所有值.

1. Determines the Excel file type based on the file extension.
2. Selects the correct OLEDB provider based on the excel file type to build the connection string.
3. Determines the sheet names included in the Excel file.
4. Selects the first sheet, selects all rows and stores the rows in a DataTable called mytable.
5. Displays all values of the first column in a listbox called 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"];             
}