在VB應(yīng)用程序中實現(xiàn)對Excel的數(shù)據(jù)查詢

王留洋

（淮陰工學(xué)院計算機(jī)工程系， 淮安223001）

自Microsoft 在Visual Basic（以下簡稱VB）中引入數(shù)據(jù)訪問對象（DAO），VB編程語言就成為程序員基于Windows操作平臺進(jìn)行數(shù)據(jù)庫編程的一種較好的編程工具。而Excel以其強(qiáng)大的公式和制表功能得到了廣泛的應(yīng)用。本文介紹在VB中利用結(jié)構(gòu)化查詢語言（SQL）實現(xiàn)對Excel中數(shù)據(jù)查詢的2種方法。

1 后臺Excel數(shù)據(jù)庫的配置

Excel中的數(shù)據(jù)資源可稱作平面文件數(shù)據(jù)庫。平面文件數(shù)據(jù)庫把結(jié)構(gòu)化的但無關(guān)系的數(shù)據(jù)存儲在一系列的磁盤文件中，在Excel外部使用SQL查詢平面文件數(shù)據(jù)庫中的數(shù)據(jù)可采取2種解決方案：（1）通過使用索引順序存取方法（ISAM）驅(qū)動程序，利用Microsoft Jet數(shù)據(jù)引擎來實現(xiàn)對Excel數(shù)據(jù)源的查詢。ISAM驅(qū)動程序含有關(guān)于特殊外部數(shù)據(jù)資源結(jié)構(gòu)的信息，包括一系列Jet用來存取外部數(shù)據(jù)源的驅(qū)動程序，這些驅(qū)動程序注冊在Windows注冊表中。（2）通過開放式數(shù)據(jù)庫連接（ODBC）方式，它包括整套的驅(qū)動程序，當(dāng)系統(tǒng)安裝了Excel的ODBC驅(qū)動程序，便可以編程使用SQL查詢Excel中的數(shù)據(jù)。

使用SQL語句查詢必然要涉及到數(shù)據(jù)庫中的表和列（也稱為域），因此在Excel中必須定義出表和域。在Excel中，表的定義有2種方法：

（1）把Excel工作簿（Workbook）中的每個工作表（Worksheet）作為一張表，表名是工作表的名稱加上字符$。如工作表Worksheet1在用SQL查詢時的數(shù)據(jù)表名就是Worksheet1$。

（2）把Excel工作表中的某一塊區(qū)域（Range），即在工作表中用鼠標(biāo)選取的矩形區(qū)域，作為一張?zhí)摂M的表，用戶定義的區(qū)域名稱就是數(shù)據(jù)表名。這種定義不影響工作表的布局，可在一個工作表中定義多表以供查詢。

2 使用Jet/ISAM的DAO

通過數(shù)據(jù)訪問對象（DAO）訪問安裝Excel ISAM驅(qū)動程序的Microsoft Jet數(shù)據(jù)引擎，可方便地實現(xiàn)Excel數(shù)據(jù)的查詢及其它操作。

2.1 使用數(shù)據(jù)控件

最簡單的方法是直接使用VB提供的數(shù)據(jù)控件，以及數(shù)據(jù)綁定控件查詢顯示數(shù)據(jù)。現(xiàn)假定在Excel文件“demo.xls”中用區(qū)域定義了表Product，第1行的域名和示例數(shù)據(jù)如表1。

表1 Product

在前臺作以下設(shè)置：

（1）在窗體上添加一個Data控件，名稱為dcProduct，將其Connect屬性設(shè)為“Excel 8.0”，RecordsetType屬性設(shè)為 “Dynaset”，作為與后臺Excel數(shù)據(jù)庫的接口；

（2）在窗體上添加一個Data Grid 控件， 命名為dbgProductInfo，用作顯示表內(nèi)數(shù)據(jù)，在它的屬性設(shè)置中，將其DataSource屬性設(shè)置為：“dcProduct”。

相應(yīng)的代碼，比如在Form_Load事件中：

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

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

此處SQL語句中的表名用“ ` ”符號括起來，查詢的域則由Excel表中第1行中各單元格的字符表示。運行程序，Excel中的數(shù)據(jù)將顯示在Data Grid 控件中，若不想顯示所有列，可進(jìn)一步設(shè)置Data Grid屬性，使其顯示特定列的數(shù)據(jù)。

2.2 使用代碼

使用控件完成數(shù)據(jù)庫連接和顯示相當(dāng)方便，但因功能有限，因而仍需用DAO的代碼完成相應(yīng)功能。仍以demo.xls的Product表為例：

（1）創(chuàng)建和打開數(shù)據(jù)庫

Dim dbProduct As Database

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

（2）使用SQL進(jìn)行查詢

Dim rsProduct As Recordset

Dim strSQL As String

strSQL = "SELECT * FROM `Product`"

Set rsProduct = dbProduct.OpenRecordset(strSQL)

用字符串變量保存SQL 語句， 并作為參數(shù)傳入Recordset對象的OpenRecordset方法進(jìn)行查詢。語句可加上WHERE，ORDER BY，GROUP BY等子句，但由于Excel不能建關(guān)系表，所以在多表查詢上會有些困難。

（3）操作表中的數(shù)據(jù)

顯示數(shù)據(jù)：繼續(xù)上述例子，用TextBox顯示Name，Price和Quantity域。

With rsProduct

Text1.Text = !Name

Text2.Text = !Price

Text3.Text = !Quantity

End With

記錄的添加和修改，使用DAO中Recordset對象的MoveFirst、MoveLast、MoveNext和MovePrevious 等記錄定位方法移動游標(biāo)，用AddNew、Edit和Update等方法更新。

（4）釋放對象

保持良好的編程習(xí)慣，在程序用完各種對象后用下列語句將其釋放：

Set rsProduct = Nothing

Set dbProduct = Nothing

3 ODBC

連接Excel數(shù)據(jù)文件查詢還可以通過開放式數(shù)據(jù)連接ODBC來完成，有以下幾種不同的方式：

（1）通過Jet引擎訪問ODBC的Excel數(shù)據(jù)源，即不用數(shù)據(jù)文件名而用連接字符串調(diào)用Database對象的OpenDatabase方法；

（2）使用ODBC API直接訪問ODBC數(shù)據(jù)庫，不需Jet引擎的內(nèi)務(wù)操作，訪問速度加快許多，但程序設(shè)計和測試工作比較繁瑣；

（3）使用遠(yuǎn)程數(shù)據(jù)對象（RDO）或遠(yuǎn)程數(shù)據(jù)控件，通過類似于DAO的對象接口繞過Jet引擎，直接到達(dá)ODBC數(shù)據(jù)源；

（4）使用ADO（ActiveX Data Objects），功能與RDO相似，2種模型間有相似的映射關(guān)系。ADO“擴(kuò)展”了DAO 和 RDO 所使用的對象模型，意味著包含較少的對象，更多的屬性、方法（和參數(shù)）以及事件。

ADO是一套面向?qū)ο蟮臄?shù)據(jù)訪問接口，作為OLE DB的Active包容器，最終將取代RDO，DAO，以下簡要介紹ADO的連接方法：

（1）建立Excel文件的ODBC數(shù)據(jù)源，打開“控制面板”中的“ODBC數(shù)據(jù)源”，新建一個用戶DSN，選擇工作簿為“demo.xls”，命名為Demo Excel；

（2）連接數(shù)據(jù)庫并用SQL查詢數(shù)據(jù)

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）顯示和操作數(shù)據(jù)，可用代碼連接，也可將控件捆綁到記錄集上顯示，如將數(shù)據(jù)庫中的Name字段捆綁到txtName控件中，做法如下：

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

其它操作，如添加和修改，游標(biāo)的移動等均類似于DAO的操作。

4 用Excel的OLE對象完成高級功能

Excel的ISAM驅(qū)動程序不支持刪除操作，要完成刪除及打印、格式設(shè)定和公式計算等功能，必須使用Excel的OLE對象。為程序可存取Excel對象，在主程序的引用屬性中必須引用Microsoft Excel 8.0/9.0 Object Library，具體做法如下：

（1）創(chuàng)建和初始化Excel對象

'定義Excel對象

Dim appExcel As Excel.Application

'定義Excel對象中的Workbook對象

Dim wbkProduct As Excel.Workbook

'定義Excel對象中的Worksheet對象Dim CurrentWorksheet As Excel.Worksheet

'創(chuàng)建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中的數(shù)據(jù)

主要針對工作表的各個數(shù)據(jù)單元（Cell）或區(qū)域（Range）進(jìn)行操作，如要刪除某行， 可以按二維數(shù)組Cell（Row，Col）查找到要刪除的行號iRow，然后作如下調(diào)用：

CurrentWorksheet.Row(iRow).Delete 'iRow為行號變量

wbkProduct.Save '保存修改，若要打印報表，則可調(diào)用

wbkProduct.PrintOut

（3）關(guān)閉Excel

Set CurrentWorksheet = Nothing

wbkProduct.Close True '關(guān)閉并保存工作簿

Set wbkProduct = Nothing

appExcel.Quit

Set appExcel = Nothing

5 結(jié)束語

如何在VB中以SQL訪問Excel平面文件數(shù)據(jù)庫，關(guān)鍵是Excel中表的創(chuàng)建以及在程序中如何引用表名。文中提出通過Excel的ISAM和ODBC驅(qū)動程序來訪問的2種方法，用DAO、RDO和ADO都可以通過ODBC訪問Excel數(shù)據(jù)源，但使用DAO/Jet引擎時，不僅要用ODBC層，還要用到Jet引擎，使回應(yīng)速度變慢，所以RDO和ADO是訪問ODBC的首選。Excel文件數(shù)據(jù)庫往往存在于本地，DAO/Jet是ISAM類型數(shù)據(jù)源首選的訪問接口，但如果訪問網(wǎng)絡(luò)上的Excel數(shù)據(jù)源，ODBC也不失為一種好方法。該方法結(jié)合Excel的制表和統(tǒng)計功能，在產(chǎn)品查詢報價系統(tǒng)中取得了良好的效果。

[1] 俞揚信，張一洲. 利用VFP實現(xiàn)對Excel中數(shù)據(jù)的查詢[J] .鐵路計算機(jī)應(yīng)用，2007，16（3）.