【VB6|第22期】用SQL的方式读取Excel数据

日期：2023年8月7日
作者：Commas
签名：(ง •_•)ง 积跬步以致千里,积小流以成江海……
注释：如果您觉得有所帮助，帮忙点个赞，也可以关注我，我们一起成长；如果有不对的地方，还望各位大佬不吝赐教，谢谢^ - ^
1.01³⁶⁵ = 37.7834；0.99³⁶⁵ = 0.0255
1.02³⁶⁵ = 1377.4083；0.98³⁶⁵ = 0.0006

---

---

---

一、前言

Microsoft Excel 是广泛使用的电子表格软件, 在 VB6 中，读取 Excel 数据是常见的任务，可以用于数据处理、信息展示等。本文将介绍通过 第三方库（Jet.OLEDB） 读取 Excel 数据，话不多说，直接上教程。

二、使用第三方库（Jet.OLEDB）

Jet.OLEDB 是一种广泛使用的数据访问技术，我们可以通过它来读取 Excel 数据。这种方法通常被大家认定为只可以读取早期版本的 Excel 文件（Excel 97-2003，格式：.xls），难道真的不可以读取高版本的数据了吗？这里留个疑问，我们继续…

（2-1）添加引用

首先，我们在工程引用中添加 Microsoft ActiveX Data Objects xx.x Library的引用。

这个是采用了 前期绑定（静态绑定） 的方式，如果换乘 后期绑定（动态绑定） 的方式，就不需要添加引用，直接CreateObject获得对象实例就可以。

（2-2）创建对象实例

• 前期绑定（静态绑定） 创建方式

Dim CN As New ADODB.Connection
Dim RS As New ADODB.Recordset

• 后期绑定（动态绑定） 创建方式

Set CN = CreateObject("adodb.connection")
Set RS = CreateObject("adodb.recordset")

（2-3）数据连接字符串解析（重点）

数据连接字符串如下所示：

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=<path>;Extended Properties=""Excel 8.0;HDR=Yes;"";

• Provider: 指定要使用的数据提供程序。在这里，我们使用了 Microsoft Jet OLEDB 4.0 提供程序，该提供程序用于访问 Microsoft Excel 文件。
• Data Source: 指定 Excel 文件的路径。你需要将 <path> 替换为实际的Excel 文件路径，比如 D:\example.xls 。
• Extended Properties: 用于指定连接的扩展属性。在这里，我们设置为 Excel 8.0;HDR=Yes; ，其参数说明如下：

Extended Properties	参数说明
Excel 8.0	表示连接使用 Excel 97-2003 的文件格式。
Excel 12.0	表示连接使用 Excel 2007 及以上的文件格式，但是这个参数，不过不可用于Microsoft Jet OLEDB 4.0 提供程序
HDR=Yes	表示第一行的数据包含列标题
HDR=No	表示第一行的数据将被当作普通数据，而不是列标题。

（2-4）读取.xls格式的数据

示例代码：

Private Sub CmdJet_Click()
On Error Resume Next
'函数说明：使用第三方库（Jet.OLEDB）读取Excel数据(.xls格式)
'创建作者：Commas
'创建时间：2023-08-06
'修改时间：
'------传参说明------
'------传参说明------

    'Excel文件的路径
    Dim sXLPath As String
    
    'sXLPath = App.Path & "\example.xlsx"
    sXLPath = App.Path & "\example.xls"

    ' NO1：前期绑定（静态绑定）
    ' 需要添加对Microsoft ActiveX Data Objects xx.x Library的引用（版本号根据ADO版本变化）
    Dim CN As New ADODB.Connection
    Dim RS As New ADODB.Recordset
    ' NO2：后期绑定（动态绑定）
'''    Dim CN As Object 'New ADODB.Connection
'''    Dim RS As Object 'New ADODB.Recordset
'''    Set CN = CreateObject("adodb.connection")
'''    Set RS = CreateObject("adodb.recordset")
    
    Dim CNStr As String
    Dim sqlQuery As String
    
    ' 连接到Excel文件
    
    CNStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={path};Extended Properties=""Excel 8.0;HDR=Yes;"";"
    'CNStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={path};Extended Properties=""Excel 12.0;HDR=Yes;"";"
    
    CNStr = Replace(CNStr, "{path}", sXLPath)
    CN.Open CNStr
    
    ' 构造SQL查询语句
    sqlQuery = "SELECT * FROM [Sheet1$A1:C5];"
    
    ' 执行查询并将结果保存到记录集（Recordset）
    RS.Open sqlQuery, CN
    
    ' 将数据读取到数组中
    Dim aryData() As Variant
    aryData = RS.GetRows
    
    RS.Close
    CN.Close
    
    '输出,获取二维数组的行数和列数
    Dim rows As Long, cols As Long
    rows = UBound(aryData, 1) + 1  ' 行数
    cols = UBound(aryData, 2) + 1 ' 列数
    
    ' 遍历二维数组
    Dim i As Long, j As Long
    For i = 0 To rows - 1
        For j = 0 To cols - 1
            ' 访问二维数组中的元素
            Debug.Print "aryData(" & i & ", " & j & ") = " & aryData(i, j)
        Next j
    Next i
End Sub

返回结果：

aryData(0, 0) = 张三
aryData(0, 1) = 李小龙
aryData(0, 2) = 王五
aryData(0, 3) = 赵六
aryData(1, 0) = 技术部
aryData(1, 1) = 技术部
aryData(1, 2) = 市场部
aryData(1, 3) = 销售部
aryData(2, 0) = 18
aryData(2, 1) = 19
aryData(2, 2) = 20
aryData(2, 3) = 21

（2-5）读取.xlsx格式的数据

尝试一：

数据连接字符串：

sXLPath = App.Path & "\example.xlsx"
CNStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={path};Extended Properties=""Excel 8.0;HDR=Yes;"";"
CNStr = Replace(CNStr, "{path}", sXLPath)

测试结果：

尝试二：

数据连接字符串：

 sXLPath = App.Path & "\example.xlsx"
CNStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={path};Extended Properties=""Excel 12.0;HDR=Yes;"";"
CNStr = Replace(CNStr, "{path}", sXLPath)

测试结果：

三、结束语

经测试，我们会发现 Microsoft.Jet.OLEDB.4.0 确实只支持读取旧版本的 Excel 文件（即 .xls 格式的数据）。这是因为Microsoft Jet OLEDB 4.0 是一个较旧的数据提供程序，最初用于访问 Access数据库和旧版本的 Excel 文件，而新版本 Excel 文件使用了不同的数据存储格式，所以它并不支持直接读取的。

实际上，Microsoft.Jet.OLEDB.4.0 也可以读取新版 Excel 文件的。鉴于文章篇幅问题，下期我再出一期关于Microsoft.Jet.OLEDB.4.0 读取新版本 Excel 文件（即 .xlsx 格式的数据）的文章，出文速度看各位大佬的点赞速度了哈。

---

参考文章：

• 《Microsoft OLE DB Provider for Microsoft Jet》

---

版权声明：本文为博主原创文章，如需转载，请给出：
原文链接：https://blog.csdn.net/qq_35844043/article/details/132128714