基于VB的Excel與Access數(shù)據(jù)交換的實現(xiàn)

韋立梅

(廣東白云學院電氣與信息工程學院，廣東 廣州 510450)

1.引言

Visual Basic(VB)是比較常用的軟件開發(fā)工具之一，但VB的報表功能有限，而Excel是一款使用廣泛的、優(yōu)秀的電子表格處理軟件，其報表功能十分強大。日常工作中，用戶也希望程序設計者能把報表結(jié)果輸出到他們更熟悉的Excel文件中。那么，如何能把二者有機地結(jié)合在一起，實現(xiàn)在VB編程中有效地控制Excel生成報表，或者把Excel表格內(nèi)容導入到數(shù)據(jù)庫中的某個表，是程序設計員經(jīng)常面臨的問題，也是本文將要討論的問題。

2.EXCEL對象模型

若想在VB中靈活地使用Excel文件，就要用到VB的OLE自動化技術(shù)獲取Excel控制句柄，再利用其提供的Excel各種對象所具有的方法與屬性，實現(xiàn)在VB中控制Excel的一系列操作。Excel是以層次結(jié)構(gòu)組織對象的，最頂層是Application對象，代表Excel應用程序本身，其下一層是workbooks對象集，代表Excel工作簿文件；worksheets對象集，是Workbooks對象集的下層，它表示一個Excel工作表；Cells和Range對象，是worksheets對象的下層，代表Excel工作表中的一個或多個單元格。以上四個對象是Excel中最常用的對象。

3.ADO對象模型

ADO(ActiveX Data Objects，ActiveX數(shù)據(jù)對象)是Microsoft提出的應用程序接口(API)，用以實現(xiàn)訪問關(guān)系或非關(guān)系數(shù)據(jù)庫中的數(shù)據(jù)。ADO可以以ActiveX控件的形式出現(xiàn)，技術(shù)編程接口豐富，支持 Visual C++、Visual Basic、JS等。ADO常用的對象有：Connection，用于表示和數(shù)據(jù)源的連接，以及處理一些命令和事務；Recordset用于處理數(shù)據(jù)源的記錄集，它是在表中修改、檢索數(shù)據(jù)的最主要的方法。Command用于執(zhí)行某些命令來進行諸如查詢、修改數(shù)據(jù)庫結(jié)構(gòu)的操作。Field描述數(shù)據(jù)集中的列信息。Parameter用于對傳遞給數(shù)據(jù)源的命令賦參數(shù)值。Error用于承載所產(chǎn)生錯誤的詳細信息。

4.實例

4.1 建立工程文件

圖1 窗體設計界面

圖2 導入前“圖書信息”表記錄

圖3 導入后“圖書信息”表記錄

新建一個VB的“標準EXE”工程，在工程菜單上選擇“引用”，選中“Microsoft ActiveX Data Objects 2.8 Library”和“Microsoft Excel 11.0 Object Library”兩個選項后，單擊“確定”按鈕，將ADO對象和Excel對象引用到工程中，然后在窗體 Form1中添加兩個命令按鈕“access導出 excel”(OutExcel)和“excel導入 Access”按鈕(inputAcc)。完成此程序要在D盤根目錄下建立一個包含“圖書信息”表的Access數(shù)據(jù)庫(圖書管理.mdb)，再建立新購圖書.xls和myexl.xls兩個工作簿，其中新購圖書.xls的結(jié)構(gòu)與“圖書信息”表的結(jié)構(gòu)一樣，用于導入，myexl.xls用于導出報表。程序設計界面如下圖1所示，導入前“圖書信息”表的內(nèi)容如圖2所示，導入后“圖書信息”表的內(nèi)容如圖3所示。

4.2 代碼的編寫及說明

(1)在窗體的通用區(qū)聲明對象變量：

Option Explicit

Dim exlapp Asexcel.Application

Dim exlbook Asexcel.Workbook

Dim exlsheet Asexcel.Worksheet

說明：聲明Excel編程模型對象

Dim cnn AsNew ADODB.Connection

Dim rst As New ADODB.Recordset

說明：聲明ADO編程模型對象

(2)在窗體的Load事件中建立與數(shù)據(jù)庫的連接Dim s As String

s="Provider=Microsoft.Jet.OLEDB.4.0；Data

Source=D：圖書管理.mdb；Persist Security Info=False"cnn.CursorLocation=adUseClient

cnn.Open s‘打開D：圖書管理.mdb數(shù)據(jù)庫Set rst=New Recordset‘創(chuàng)建一個新的記錄集

(3)“excel導入Access”按鈕(inputAcc)單擊事件代碼

Private Sub Command1_Click()

Dim sql As String

Dim value1 As String

Dim value2 As String

Dim row As Integer

Screen.MousePointer=vbHourglass

DoEvents

Set exlapp=CreateObject("excel.Application")‘創(chuàng)建 Excel應用程序?qū)ο髮嵗?/p>

exlapp.Visible=True‘使Excel應用程序可見

exlapp.Workbooks.Open FileName：="d：新購圖書.xls"Setexlsheet=exlapp.ActiveSheet‘激活 Excel工作表

row=1

Do

value1=Trim$(exlsheet.Cells(row，1))‘得到書號值

value2=Trim$(exlsheet.Cells(row，2))‘得到書名值

If Len(value1)=0 Then ExitDo‘如果書號單元格內(nèi)容不為空時，就插入一條記錄

sql="insert into圖書信息 (書號，書名)values('"&value1&"'，"&"'"&value2&"')"

cnn.Execute sql‘在圖書信息表末尾插入一條新記錄

row=row+1

Loop

exlapp.ActiveWorkbook.Close False‘關(guān)閉Excel文件但不保存

exlapp.Quit‘退出Excel應用程序

Setexlsheet=Nothing‘釋放Excel工作表對象

Setexlapp=Nothing‘釋放Excel工作簿對象

Screen.MousePointer=vbDefault

End Sub

(4)“access導出excel”按鈕(OutExcel)單擊事件代碼

Private Sub Command2_Click()

Dim row As Integer，col As Integer

Set rst.ActiveConnection=cnn‘記錄集中創(chuàng)建連接

rst.Open"圖書信息 "，cnn，adOpenStatic，

adLockBatchOptim istic‘打開圖書信息表

Set exlapp=CreateObject("excel.application")‘創(chuàng)建 Excel應用程序?qū)ο?/p>

Set exlbook=exlapp.Workbooks.Open("d：myexl.xls")‘打開Excel工作簿

Setexlsheet=exlapp.Worksheets(1)‘選擇第一張工作表

exlapp.Visible=True‘Excel應用程序可見

row=1

For col=1 To rst.Fields.Count-1

exlsheet.Range(Cells(row，col)，Cells(row，col+1)).MergeCells=True‘合并第一行單元格區(qū)域

exlsheet.Cells(2，col)=rst.Fields.Item(col-1).Name‘第二行顯示列標題

If Not rst.EOFThen rst.MoveNext

Next

exlsheet.Cells(2，col)=rst.Fields.Item(col-1).Name rst.MoveFirst

exlsheet.Rows(1).HorizontalAlignment=xlCenter

exlsheet.Cells(1)="圖書信息"‘工作表第一行顯示圖書信息標題

row=3

col=1

DoWhile Not rst.EOF‘使用記錄集，循環(huán)實現(xiàn)將圖書信息表中的記錄逐條顯示

For col=1 To rst.Fields.Count

exlsheet.Cells(row，col)=rst(col-1)

Next

rst.MoveNext

row=row+1

Loop

exlapp.ActiveSheet.PrintPreview‘在Excel工作簿中預覽圖書信息表記錄內(nèi)容

End Sub

5.結(jié)束語

把后臺數(shù)據(jù)庫中的數(shù)據(jù)記錄輸出到Excel或者把Excel的內(nèi)容輸入到后臺數(shù)據(jù)表，是程序開發(fā)人員經(jīng)常要處理的問題，本文通過一個具體的實例，說明在VB中利用ADO對象建立VB與后臺數(shù)據(jù)庫的連接，同時使用VB控制Excel，把數(shù)據(jù)導入Excel應用程序中，很方便快捷地完成打印功能。并給出了具體的代碼，所有代碼已編譯成功。用戶在使用時，只要用鼠標單擊一下相應的命令按鈕，就可以輕松地實現(xiàn)在Access與Excel之間進行數(shù)據(jù)導入導出。

[1]劉東.VB控制EXCEL生成報表. 宜賓學院學報，2005，12：73-75.

[2]吳聲松.VisualBasic調(diào)用Excel之技巧.工程地質(zhì)計算機應用，2000，2：18-21.

[3]傾明.通過ADO實現(xiàn)VB與EXCEL的無縫連接.內(nèi)蒙古石油化工，2007，10：60-62.