ExcelADO 演示如何使用 ADO 读写 Excel 工作簿中的数据

摘要

ExcelADO 示例演示了如何将 ActiveX 数据对象(ADO)与 Microsoft Jet OLE DB 4.0 提供程序结合使用,以便在 Microsoft Excel 工作簿中读取和写入数据。

更多信息

为什么使用 ADO?

在 excel 工作簿中使用 ADO 将数据传输到数据或从 Excel 工作簿检索数据为您提供了开发人员的多个优于 Excel 自动化的优势:

  • 性能。 Microsoft Excel 是进程外 ActiveX 服务器。 ADO 在进程内运行,并节省了昂贵的进程外调用的开销。

  • 可伸缩性。 对于 Web 应用程序,自动化 Microsoft Excel 并非总是需要。 ADO 为您提供了一个更具扩展性的解决方案来处理工作簿中的数据。

ADO 可以严格用于将原始数据传输到工作簿。 不能使用 ADO 对单元格应用格式或公式。 但是,你可以将数据传输到预格式的工作簿,并保留格式。 如果在插入数据后需要 "条件" 格式,则可以通过自动化或工作簿中的宏完成此格式设置。

适用于 Excel 工作簿的 Jet OLE DB 提供程序详细信息

Microsoft Jet 数据库引擎可用于通过可安装的索引顺序访问方法(ISAM)驱动程序访问其他数据库文件格式(如 Excel 工作簿)中的数据。 为了打开 Microsoft Jet 4.0 OLE DB 提供程序支持的外部格式,请在连接的扩展属性中指定数据库类型。 Jet OLE DB 提供程序支持 Microsoft Excel 工作簿的以下数据库类型:

  • Excel 3。0

  • Excel 4。0

  • Excel 5。0

  • Excel 8。0

注意:使用适用于 microsoft excel 5.0 和7.0 (95)工作簿的 excel 5.0 源数据库类型,并将 excel 8.0 源数据库类型用于 microsoft excel 8.0 (97)和9.0 (2000)工作簿。 ExcelADO 示例使用 Excel 97 和 Excel 2000 格式的 Excel 工作簿。以下示例演示了与 Excel 97 (或2000)工作簿的 ADO 连接:

Dim oConn As New ADODB.ConnectionWith oConn    .Provider = "Microsoft.Jet.OLEDB.4.0"    .Properties("Extended Properties").Value = "Excel 8.0"    .Open "C:\Book1.xls"    '....    .CloseEnd With

Dim oConn As New ADODB.ConnectionoConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _           "Data Source=C:\Book1.xls;" & _           "Extended Properties=""Excel 8.0;"""oConn.Close

表命名约定有多种方法可以引用 Excel 工作簿中的表格(或区域):

  • 使用工作表名称,后跟美元符号(例如,[Sheet1 $] 或 [我的工作表 $])。 以此方式引用的工作簿表由工作表的整个使用区域组成。

    oRS.Open "Select * from [Sheet1$]", oConn, adOpenStatic
  • 使用具有已定义名称的区域(例如,[Table1])。

    oRS.Open "Select * from Table1", oConn, adOpenStatic
  • 使用具有特定地址的范围(例如,[Sheet1 $ A1: B10])。

    oRS.Open "Select * from [Sheet1$A1:B10]", oConn, adOpenStatic

表格标题通过 Excel 工作簿,在默认情况下,区域中的第一行被视为标题行(或字段名称)。 如果第一个区域不包含标题,则可以在连接字符串的扩展属性中指定 HDR = NO 。 如果第一行不包含标题,则 OLE DB 提供程序会自动为您命名字段(其中 F1 表示第一个字段,F2 表示第二个字段等)。数据类型与传统数据库不同,没有指定 Excel 表中列的数据类型的直接方法。 相反,OLE DB 提供程序扫描列中的有限行数以 "猜测" 字段的数据类型。 要扫描的行数的默认值为八(8)行;你可以通过在连接字符串的扩展属性中为 MAXSCANROWS 设置指定一个介于1(1)和十六(16)之间的值来更改要扫描的行数。

示例中包含的文件

ExcelADO 文件包含 Visual Basic 标准 EXE 项目、Active Server Page (ASP)、Excel 97 和 Excel 2000 工作簿,这些工作簿充当模板以及 Microsoft Access 2000 数据库。 包括的文件如下所示:Visual Basic 标准 EXE 项目文件

  • ExcelADO.vbp

  • Form1.frm

  • Form1.frx

活动服务器页面

  • EmpData.asp

  • Orders.asp

Microsoft Excel 工作簿

  • OrdersTemplate.xls

  • EmpDataTemplate.xls

  • ProductsTemplate.xls

  • SourceData.xls

Microsoft Access 数据库

  • Data.mdb

如何使用示例

将 .exe 文件的内容解压缩到文件夹。若要使用 Visual Basic 项目,请执行以下操作:

  1. 在 Visual Basic 中,打开 ExcelADO 文件。

  2. 在 " 项目 " 菜单上,选择 " 引用",然后设置对 Microsoft ADO EXT 的引用。对于 DDL 和安全 和 Microsoft ActiveX 数据对象库。 此示例代码同时适用于 ADO 2.5 和 ADO 2.6,因此请选择适合您的计算机的版本。

  3. 按 F5 键运行程序。 将显示一个用于演示的表单。

  4. 单击 " 示例 1"。 此示例创建 OrdersTemplate 的副本。 然后,它使用 ADO 连接到工作簿,并在工作簿中的已定义区域的表上打开 记录集 。 区域名称为 Orders_Table。 它使用 ADO AddNew/Update 方法将记录(或行)添加到工作簿中的已定义范围。 完成行添加操作后,ADO 连接 将关闭,工作簿将显示在 Microsoft Excel 中。 请按照以下步骤操作:

    1. 在 Excel 中的 " 插入 " 菜单上,选择 " 名称",然后选择 " 定义"。

    2. 在已定义名称的列表中,选择 " Orders_Table"。 请注意,已定义的名称已增长为包含新添加的记录。 定义的名称与 Excel 的 OFFSET 函数一起使用,用于计算添加到工作表的数据的总计。

    3. 退出 Microsoft Excel 并返回到 Visual Basic 应用程序。

  5. 单击 " 示例 2"。 此示例创建 EmpDataTemplate 的副本。 它使用 ADO 连接到工作簿,并使用 ADO 连接的 Execute 方法将数据(插入到 SQL 中)插入到工作簿中。 数据将添加到工作簿中定义的范围(或表)中。 传输数据时,将关闭连接,并在 Excel 中显示结果的工作簿。 检查工作簿后,退出 Microsoft Excel,然后返回到 Visual Basic 应用程序。

  6. 单击 " 示例 3"。 此示例创建 ProductsTemplate 的副本。 它使用 Microsoft ADO 扩展2.1 进行 DDL 和安全对象库(ADOX)将新表(或新工作表)添加到工作簿。 然后,将为新表获取 ADO 记录集 ,并使用 AddNew/Update 方法添加数据。 完成行添加操作后,ADO 连接 将关闭,工作簿将显示在 Excel 中。 工作簿包含工作簿的 Open 事件中的 Visual Basic for APPLICATIONS (VBA)宏代码。 宏在打开工作簿时运行;如果工作簿中存在新的 "产品" 工作表,则宏代码将设置工作表的格式,然后删除宏代码。 此技术为 Web 开发人员提供了一种将格式代码从 Web 服务器移动到客户端的方法。 Web 应用程序可以将包含数据的已设置格式的工作簿流式传输到客户端,并允许宏代码执行可能无法在客户端单独运行的模板中可能无法使用的任何 "条件" 格式。注意:若要检查宏代码,请在 VBAProject for ProductsTemplate 中查看 工作簿 模块。

  7. 单击 " 示例 4"。 此示例产生的结果与示例1相同,但用于传输数据的技术稍有不同。 在示例1中,一次向工作表中添加一条记录(或行)。 示例4通过将 Excel 表附加到 Access 数据库并运行追加查询(或插入到 ...)批量添加记录。选择 "自"),将 Access 表中表的记录追加到 Excel 表中。 传送完成后,Excel 表格将从 Access 数据库分离,而工作簿将在 Excel 中显示。 退出 Excel,并返回到 Visual Basic 应用程序。

  8. 最后一个示例演示了如何从 Excel 工作簿中读取数据。 在下拉列表中选择一个 表 ,然后单击 " 示例 5"。 "立即" 窗口显示所选表的内容。 如果为表选择整个工作表("Sheet1 $" 或 "Sheet2 $"),"立即" 窗口将显示该工作表的已用区域的内容。 请注意,所使用的范围不一定从工作表的第1行、第1列开始。 所用区域从工作表中包含数据的左上角单元格开始。如果选择特定范围地址或定义的区域,则 "立即" 窗口仅显示该区域在工作表上的内容。

若要使用 Active Server page (ASP):

  1. 在 Web 服务器的主目录中创建一个名为 ExcelADO 的新文件夹。 请注意,主目录的默认路径是 C:\InetPut\WWWRoot。

  2. 将以下文件复制到您在上一步中创建的文件夹中:

    • EmpData.asp

    • Orders.asp

    • Data.mdb

    • EmpDataTemplate.xls

    • OrdersTemplate.xls

  3. 此示例中的 ASP 脚本通过FileSystemObject的Copy方法创建工作簿模板的副本。 若要使 Copy 方法成功,正在访问脚本的客户端必须具有对包含 ASP 的文件夹的写访问权限。

  4. 导航到 "订单 .asp" (即 http://YourServer/ExcelADO/Orders.ASP),并注意浏览器显示的 Excel 工作簿与 Visual Basic 应用程序的 示例 1 中的工作簿类似。

  5. 导航到 EmpData (即 http://YourServer/ExcelADO/EmpData.ASP),并注意浏览器将显示一个 Excel 工作簿,该工作簿与 Visual Basic 应用程序的 示例 2 中的工作簿类似。

(c) Microsoft Corporation 2000,保留所有权利。 由洛利 Turner、Microsoft Corporation 发布的内容。

参考

有关其他信息,请单击下面的文章编号,以查看 Microsoft 知识库中的文章:

195951 方法:使用 ADO 从 ASP 查询和更新 Excel 数据

194124 PRB:使用 DAO OpenRecordset 返回值为 NULL 的 Excel 值

193998 方法:在 ASP 中读取和显示二进制数据

247412 信息:从 Visual Basic 将数据传输到 Excel 的方法

257819 方法:将 ADO 与 Visual Basic 或 VBA 中的 Excel 数据配合使用

需要更多帮助?

扩展你的技能
了解培训
抢先获得新功能
加入 Microsoft 内部人员

此信息是否有帮助?

你对翻译质量的满意程度如何?

哪些因素影响了你的体验?

是否还有其他反馈?(可选)

谢谢您的反馈意见!

×