基于VB的Excel与Access数据交换的实现

2013-08-06韦立梅

电脑与电信 2013年4期

韦立梅

(广东白云学院电气与信息工程学院，广东广州 510450)

1.引言

Visual Basic(VB)是比较常用的软件开发工具之一，但VB的报表功能有限，而Excel是一款使用广泛的、优秀的电子表格处理软件，其报表功能十分强大。日常工作中，用户也希望程序设计者能把报表结果输出到他们更熟悉的Excel文件中。那么，如何能把二者有机地结合在一起，实现在VB编程中有效地控制Excel生成报表，或者把Excel表格内容导入到数据库中的某个表，是程序设计员经常面临的问题，也是本文将要讨论的问题。

2.EXCEL对象模型

若想在VB中灵活地使用Excel文件，就要用到VB的OLE自动化技术获取Excel控制句柄，再利用其提供的Excel各种对象所具有的方法与属性，实现在VB中控制Excel的一系列操作。Excel是以层次结构组织对象的，最顶层是Application对象，代表Excel应用程序本身，其下一层是workbooks对象集，代表Excel工作簿文件；worksheets对象集，是Workbooks对象集的下层，它表示一个Excel工作表；Cells和Range对象，是worksheets对象的下层，代表Excel工作表中的一个或多个单元格。以上四个对象是Excel中最常用的对象。

3.ADO对象模型

ADO(ActiveX Data Objects，ActiveX数据对象)是Microsoft提出的应用程序接口(API)，用以实现访问关系或非关系数据库中的数据。ADO可以以ActiveX控件的形式出现，技术编程接口丰富，支持 Visual C++、Visual Basic、JS等。ADO常用的对象有：Connection，用于表示和数据源的连接，以及处理一些命令和事务；Recordset用于处理数据源的记录集，它是在表中修改、检索数据的最主要的方法。Command用于执行某些命令来进行诸如查询、修改数据库结构的操作。Field描述数据集中的列信息。Parameter用于对传递给数据源的命令赋参数值。Error用于承载所产生错误的详细信息。

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数据库(图书管理.mdb)，再建立新购图书.xls和myexl.xls两个工作簿，其中新购图书.xls的结构与“图书信息”表的结构一样，用于导入，myexl.xls用于导出报表。程序设计界面如下图1所示，导入前“图书信息”表的内容如图2所示，导入后“图书信息”表的内容如图3所示。

4.2 代码的编写及说明

(1)在窗体的通用区声明对象变量：

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事件中建立与数据库的连接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数据库Set rst=New Recordset‘创建一个新的记录集

(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")‘创建 Excel应用程序对象实例

exlapp.Visible=True‘使Excel应用程序可见

exlapp.Workbooks.Open FileName：="d：新购图书.xls"Setexlsheet=exlapp.ActiveSheet‘激活 Excel工作表

row=1

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

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

If Len(value1)=0 Then ExitDo‘如果书号单元格内容不为空时，就插入一条记录

sql="insert into图书信息 (书号，书名)values('"&value1&"'，"&"'"&value2&"')"

cnn.Execute sql‘在图书信息表末尾插入一条新记录

row=row+1

Loop

exlapp.ActiveWorkbook.Close False‘关闭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‘记录集中创建连接

rst.Open"图书信息 "，cnn，adOpenStatic，

adLockBatchOptim istic‘打开图书信息表

Set exlapp=CreateObject("excel.application")‘创建 Excel应用程序对象

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‘合并第一行单元格区域

exlsheet.Cells(2，col)=rst.Fields.Item(col-1).Name‘第二行显示列标题

If Not rst.EOFThen rst.MoveNext

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‘使用记录集，循环实现将图书信息表中的记录逐条显示

For col=1 To rst.Fields.Count

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

rst.MoveNext

row=row+1

Loop

exlapp.ActiveSheet.PrintPreview‘在Excel工作簿中预览图书信息表记录内容

End Sub

5.结束语

把后台数据库中的数据记录输出到Excel或者把Excel的内容输入到后台数据表，是程序开发人员经常要处理的问题，本文通过一个具体的实例，说明在VB中利用ADO对象建立VB与后台数据库的连接，同时使用VB控制Excel，把数据导入Excel应用程序中，很方便快捷地完成打印功能。并给出了具体的代码，所有代码已编译成功。用户在使用时，只要用鼠标单击一下相应的命令按钮，就可以轻松地实现在Access与Excel之间进行数据导入导出。

[1]刘东.VB控制EXCEL生成报表. 宜宾学院学报，2005，12：73-75.

[2]吴声松.VisualBasic调用Excel之技巧.工程地质计算机应用，2000，2：18-21.

[3]倾明.通过ADO实现VB与EXCEL的无缝连接.内蒙古石油化工，2007，10：60-62.