在VB应用程序中实现对Excel的数据查询

王留洋

（淮阴工学院计算机工程系， 淮安223001）

自Microsoft 在Visual Basic（以下简称VB）中引入数据访问对象（DAO），VB编程语言就成为程序员基于Windows操作平台进行数据库编程的一种较好的编程工具。而Excel以其强大的公式和制表功能得到了广泛的应用。本文介绍在VB中利用结构化查询语言（SQL）实现对Excel中数据查询的2种方法。

1 后台Excel数据库的配置

Excel中的数据资源可称作平面文件数据库。平面文件数据库把结构化的但无关系的数据存储在一系列的磁盘文件中，在Excel外部使用SQL查询平面文件数据库中的数据可采取2种解决方案：（1）通过使用索引顺序存取方法（ISAM）驱动程序，利用Microsoft Jet数据引擎来实现对Excel数据源的查询。ISAM驱动程序含有关于特殊外部数据资源结构的信息，包括一系列Jet用来存取外部数据源的驱动程序，这些驱动程序注册在Windows注册表中。（2）通过开放式数据库连接（ODBC）方式，它包括整套的驱动程序，当系统安装了Excel的ODBC驱动程序，便可以编程使用SQL查询Excel中的数据。

使用SQL语句查询必然要涉及到数据库中的表和列（也称为域），因此在Excel中必须定义出表和域。在Excel中，表的定义有2种方法：

（1）把Excel工作簿（Workbook）中的每个工作表（Worksheet）作为一张表，表名是工作表的名称加上字符$。如工作表Worksheet1在用SQL查询时的数据表名就是Worksheet1$。

（2）把Excel工作表中的某一块区域（Range），即在工作表中用鼠标选取的矩形区域，作为一张虚拟的表，用户定义的区域名称就是数据表名。这种定义不影响工作表的布局，可在一个工作表中定义多表以供查询。

2 使用Jet/ISAM的DAO

通过数据访问对象（DAO）访问安装Excel ISAM驱动程序的Microsoft Jet数据引擎，可方便地实现Excel数据的查询及其它操作。

2.1 使用数据控件

最简单的方法是直接使用VB提供的数据控件，以及数据绑定控件查询显示数据。现假定在Excel文件“demo.xls”中用区域定义了表Product，第1行的域名和示例数据如表1。

表1 Product

在前台作以下设置：

（1）在窗体上添加一个Data控件，名称为dcProduct，将其Connect属性设为“Excel 8.0”，RecordsetType属性设为 “Dynaset”，作为与后台Excel数据库的接口；

（2）在窗体上添加一个Data Grid 控件， 命名为dbgProductInfo，用作显示表内数据，在它的属性设置中，将其DataSource属性设置为：“dcProduct”。

相应的代码，比如在Form_Load事件中：

dcProduct.DatabaseName=App.Path &"demo.xls"

dcProduct.RecordSource = "SELECT *FROM `Product` WHERE Price > 300"

此处SQL语句中的表名用“ ` ”符号括起来，查询的域则由Excel表中第1行中各单元格的字符表示。运行程序，Excel中的数据将显示在Data Grid 控件中，若不想显示所有列，可进一步设置Data Grid属性，使其显示特定列的数据。

2.2 使用代码

使用控件完成数据库连接和显示相当方便，但因功能有限，因而仍需用DAO的代码完成相应功能。仍以demo.xls的Product表为例：

（1）创建和打开数据库

Dim dbProduct As Database

Set dbProduct = OpenDatabase(App.Path& "demo.xls",False,False, "Excel 8.0;")

（2）使用SQL进行查询

Dim rsProduct As Recordset

Dim strSQL As String

strSQL = "SELECT * FROM `Product`"

Set rsProduct = dbProduct.OpenRecordset(strSQL)

用字符串变量保存SQL 语句， 并作为参数传入Recordset对象的OpenRecordset方法进行查询。语句可加上WHERE，ORDER BY，GROUP BY等子句，但由于Excel不能建关系表，所以在多表查询上会有些困难。

（3）操作表中的数据

显示数据：继续上述例子，用TextBox显示Name，Price和Quantity域。

With rsProduct

Text1.Text = !Name

Text2.Text = !Price

Text3.Text = !Quantity

End With

记录的添加和修改，使用DAO中Recordset对象的MoveFirst、MoveLast、MoveNext和MovePrevious 等记录定位方法移动游标，用AddNew、Edit和Update等方法更新。

（4）释放对象

保持良好的编程习惯，在程序用完各种对象后用下列语句将其释放：

Set rsProduct = Nothing

Set dbProduct = Nothing

3 ODBC

连接Excel数据文件查询还可以通过开放式数据连接ODBC来完成，有以下几种不同的方式：

（1）通过Jet引擎访问ODBC的Excel数据源，即不用数据文件名而用连接字符串调用Database对象的OpenDatabase方法；

（2）使用ODBC API直接访问ODBC数据库，不需Jet引擎的内务操作，访问速度加快许多，但程序设计和测试工作比较繁琐；

（3）使用远程数据对象（RDO）或远程数据控件，通过类似于DAO的对象接口绕过Jet引擎，直接到达ODBC数据源；

（4）使用ADO（ActiveX Data Objects），功能与RDO相似，2种模型间有相似的映射关系。ADO“扩展”了DAO 和 RDO 所使用的对象模型，意味着包含较少的对象，更多的属性、方法（和参数）以及事件。

ADO是一套面向对象的数据访问接口，作为OLE DB的Active包容器，最终将取代RDO，DAO，以下简要介绍ADO的连接方法：

（1）建立Excel文件的ODBC数据源，打开“控制面板”中的“ODBC数据源”，新建一个用户DSN，选择工作簿为“demo.xls”，命名为Demo Excel；

（2）连接数据库并用SQL查询数据

Dim adoConn As ADODB.Connection

Dim adoRecordset As ADODB.Recordset

Set adoConn = CreateObject("ADODB.Connection")

adoConn.Open "Provider=MSDASQL.1;Data Source=Demo.Excel;"

Set adoRecordset = CreateObject("ADODB.Recordset")

adoRecordset.Open "SELECT * FROM`Product`",adoConn, adOpenKeyset

（3）显示和操作数据，可用代码连接，也可将控件捆绑到记录集上显示，如将数据库中的Name字段捆绑到txtName控件中，做法如下：

Set txtName.DataSource = adoRecordsettxt-Name.DataField = "Name"

其它操作，如添加和修改，游标的移动等均类似于DAO的操作。

4 用Excel的OLE对象完成高级功能

Excel的ISAM驱动程序不支持删除操作，要完成删除及打印、格式设定和公式计算等功能，必须使用Excel的OLE对象。为程序可存取Excel对象，在主程序的引用属性中必须引用Microsoft Excel 8.0/9.0 Object Library，具体做法如下：

（1）创建和初始化Excel对象

'定义Excel对象

Dim appExcel As Excel.Application

'定义Excel对象中的Workbook对象

Dim wbkProduct As Excel.Workbook

'定义Excel对象中的Worksheet对象Dim CurrentWorksheet As Excel.Worksheet

'创建Excel对象的实例

Set appExcel = CreateObject("Excel.Application")

'从已有的文件demo.xls打开Workbook对象

Set wbkProduct = appExcel.Workbooks.Open(App.Path & "demo.xls")

'打开Product工作表

Set CurrentWorksheet = wbkProduct.

Worksheets("Product")

（2）操作Excel中的数据

主要针对工作表的各个数据单元（Cell）或区域（Range）进行操作，如要删除某行， 可以按二维数组Cell（Row，Col）查找到要删除的行号iRow，然后作如下调用：

CurrentWorksheet.Row(iRow).Delete 'iRow为行号变量

wbkProduct.Save '保存修改，若要打印报表，则可调用

wbkProduct.PrintOut

（3）关闭Excel

Set CurrentWorksheet = Nothing

wbkProduct.Close True '关闭并保存工作簿

Set wbkProduct = Nothing

appExcel.Quit

Set appExcel = Nothing

5 结束语

如何在VB中以SQL访问Excel平面文件数据库，关键是Excel中表的创建以及在程序中如何引用表名。文中提出通过Excel的ISAM和ODBC驱动程序来访问的2种方法，用DAO、RDO和ADO都可以通过ODBC访问Excel数据源，但使用DAO/Jet引擎时，不仅要用ODBC层，还要用到Jet引擎，使回应速度变慢，所以RDO和ADO是访问ODBC的首选。Excel文件数据库往往存在于本地，DAO/Jet是ISAM类型数据源首选的访问接口，但如果访问网络上的Excel数据源，ODBC也不失为一种好方法。该方法结合Excel的制表和统计功能，在产品查询报价系统中取得了良好的效果。

[1] 俞扬信，张一洲. 利用VFP实现对Excel中数据的查询[J] .铁路计算机应用，2007，16（3）.