使用 MS Excel 和 ADO.NET

引言

本文将指导开发人员如何使用 ADO.NET 连接到 Excel 文件并修改工作表。

背景

很多时候，开发人员需要将数据导出到其他应用程序，这可能是因为客户需要迁移其他系统中的一些数据。当数据重要且易于迁移时（例如，大约几千条记录），可能会出现此需求。有时您必须更改 Excel 文件中的这些数据，即必须合并两个数据表，或者从其他来源选择一些数据并将其添加到电子表格。

Using the Code

Microsoft Jet 数据库引擎可以通过可安装的索引顺序访问方法 (ISAM) 驱动程序访问其他数据库文件格式（例如 Excel 工作簿）中的数据。 要使用 ADO.NET 访问 Excel 工作簿，可以使用 Jet OLE DB 提供程序。 可以使用 OledbConnection 对象连接 Excel 文件。

要使用 Jet OLE DB 提供程序访问 Excel 工作簿，请使用具有以下语法的连接字符串

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\WorkBook1.xls;Extended Properties="Excel 8.0;HDR=YES;"

如果在连接字符串中指定 HDR=NO，则 Jet OLE DB 提供程序会自动为您命名这些字段（F1 表示第一个字段，F2 表示第二个字段，依此类推）。

在连接字符串中，在 Data Source 参数中指定工作簿的完整路径和文件名。 Extended Properties 参数可能包含两个属性：一个用于 ISAM 版本，另一个用于指示表是否包含标题。

与传统数据库不同，没有直接的方法可以指定 Excel 表中列的数据类型。 相反，OLE DB 提供程序会扫描列中的八行来猜测该字段的数据类型。 您可以通过在连接字符串的扩展属性中为 MAXSCANROWS 设置指定介于一 (1) 到十六 (16) 之间的值来更改要扫描的行数。

如何在 Excel 中引用数据：您可以通过多种方式引用表（范围），

• 使用工作表名称后跟 $。 例如：Sheet1$
• 使用具有定义名称的范围。 例如：[MyNamedRange]
• 使用具有特定地址的范围。 例如：Sheet1$A1:B10

注意：$ 表示表存在，因此当您创建新的 Excel 表时，无需包含 $ 符号。
创建表：要在 Excel 工作簿中创建表，

CREATE TABLE Sheet1 (F1 char(255), F2 char(255))

添加和更新：使用 ADO.NET，您可以通过以下三种方式之一在工作簿中插入和更新记录

• 使用 OLEDbCommand 并设置其 CommandText，然后调用 ExecuteNonQuery 方法。 INSERT INTO [Sheet1$] (F1, F2) values ('111', 'ABC')

UPDATE [Sheet1$] SET F2 = 'F2Value' WHERE F1 = 'F1Value'

• 对您已使用 Excel 工作簿中的表/查询填充的 DataSet 进行更改，然后调用 DataAdapter 的 Update 方法以将 DataSet 中的更改解析回工作簿。 但是，要使用 Update 方法进行更改解析，您必须为 DataAdapter 的 InsertCommand 设置参数化命令

INSERT INTO [Sheet1$] (F1, F2) values (?,?)

UPDATE [Sheet1$] SET F2 = ? WHERE F1 =?.

由于 OleDbDataAdapter 不提供 Excel 工作簿的键/索引信息，因此需要命令； 如果没有键/索引字段，CommandBuilder 无法自动为您生成命令。

• 来自其他文件的数据可以使用支持 Jet OLE DB 提供程序的单个插入命令从其他数据源导入到 Excel。

例如：文本文件、Microsoft Access 数据库，当然还有 Excel 工作簿。

INSERT INTO [Sheet1$] IN 'C:\Book1.xls' 'Excel 8.0;' SELECT * FROM MyTable"

删除记录：虽然 Jet OLE DB 提供程序允许您在 Excel 工作簿中插入和更新记录，但它不允许 DELETE 操作。

注意：如果您尝试在 64 位计算机上连接到 Excel，您可能会收到以下带有 Microsoft.Jet.OLEDB 提供程序的错误。 如下面的错误，

在本地计算机上未注册“Microsoft.Jet.OLEDB.12.0”提供程序。

没有 64 位驱动程序，因此您需要将其作为 32 位进程运行。因此您必须在项目属性中使用 x86 和 Microsoft.ACE.OLEDB.12.0。

// this code is for reference and consider changing the code for your need
OleDbConnection excelConnection=null;
OleDbDataAdapter adapter= null;
try
{
    excelConnection = new OleDbConnection();
    excelConnection.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\\DbBackup.xlsx;Extended Properties='Excel 8.0;HDR=YES;'";
    excelConnection.Open();
    DataTable dtTables = new DataTable();

    //to get the schema of the workbook.
    dtTables = excelConnection.GetSchema();

    //get the tables in the workbook
    dtTables = excelConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,null);
    String[] excelSheets = null;
    if((dtTables!=null))
    {
        excelSheets = new String[dtTables.Rows.Count];
        int i = 0;

        // Add the sheet name to the string array.
        foreach(DataRow row in dtTables.Rows)
        {
            excelSheets[i] = row["TABLE_NAME"].ToString();
            i++;
        }
    }  
    DataSet ds = new DataSet();
    
    //prepare dataset from the tables in the workbook
    foreach (string sheet in excelSheets)
    {
        OleDbCommand cmd = new OleDbCommand();
        cmd.Connection = excelConnection;
        cmd.CommandText = "Select * from ["+sheet+"]";
        DataTable dtItems = new DataTable();
        dtItems.TableName = sheet;
        
        adapter = new OleDbDataAdapter();
        adapter.SelectCommand = cmd;
        
        // adapter.FillSchema(ds
        adapter.Fill(dtItems);
        ds.Tables.Add(dtItems);
    }
    
    finally
    {
        adapter.Dispose();
        excelConnection.Dispose();
    }

历史

版本 1.0.0.0