基于VB和EXCEL的报表设计及打印

摘要：针对数据库应用系统中，报表处理的复杂性，提出了基于VB和EXCEL的报表设计及打印的解决方法，同时给出了实现这一功能的源代码。实验证明，该方法使报表打印更方便和灵活。

关键词：VB；EXCEL；报表

中图分类号：TP311文献标识码：A文章编号：1009-3044(2011)28-6807-04

Design and Printing of Data Report Based on VB and EXCEL

ZHANG Cui-ping

(School of Management, Fujian University of Traditional Chinese Medicine, Fuzhou 3501018, China)

Abstract: In the database application system, report processing complexity, proposed based on VB and EXCEL report design and printing solutions, and gives the realization of the source code of this function. Experiments show that, this method makes statements Print more convenient and flexible.

Key words: VB; EXCEL; report forms

在数据库应用系统的设计过程中，报表的设计是很重要的一个步骤。如果系统缺乏灵活适用的报表，那无疑会给用户带来不少的麻烦。Visual Basic6.0是一门可视化语言，它是集应用程序开发、测试和查错功能于一体的集成式开发环境[1]。它虽然不是专门的数据库语言，但它与其它的数据库语言有良好的兼容性和应用接口，可以方便地与Oracle，Access，SQL Server等多种数据库相连接，并存取其数据生成各种报表。

在VB中有DataReport设计器，可以直接在VB IDE中创建报表，没有单独的报表文件，报表定义保存在VB项目的一个ActiveX设计器窗体中。这意味着当编译一个项目时，报表定义被编译进可执行文件中。因而在VB中制作报表是很容易的，它只需一段很短的代码，但是对报表的排版却要花去大量的时间。因而，利用VB操作EXCEL来生成复杂的报表，即在VB中引用EXCEL对象来制作报表，这样能够大大提高工作的效率。

1 EXCEL对象

在VB中能处理的Excel对象有：Application、WorkBooks、WorkSheets、Range及Cells，利用这些对象所提供的属性、方法和文件，就可以很容易的在VB应用程序中生成EXCEL报表，但要注意使用它们之前要先声明[2]。

1) Application对象是对象模型的顶层，表示整个Excel应用程序；

2) Workbook对象表示Excel 应用程序中当前打开的一个工作簿；

3) Worksheet对象表示工作表对象（一个工作簿可以包含多个工作表）；

4) Range对象代表工作表的某一单元格或多个单元格、某一选定区域等；

5) Cell对象表示特定工作表的一个单元格对象。

2 注意事项

2.1 VB与EXCEL的连接

由于VB的报表功能有限，而且一旦报表格式发生变化，就得相应修改程序，给应用软件的维护工作带来极大的不便。因此该设计充分利用EXECL的强大报表功能来实现。但由于VB与EXCEL由于分别属于不同的应用系统，如何把它们有机地结合在一起，就是该设计所要解决的一个重要的问题。

2.2 报表中需要合计和总计的内容的填入

在报表中往往有很多的需要合计的一行或是某一列的值，那么如何控制这些行列的值的填入，也是该设计需要解决的问题。

3 从VB输出报表到EXCEL中的具体过程

3.1 用VB创建外部EXCEL对象

大多数大型ActiveX-enabled应用程序和其它ActiveX 部件，在它们的对象层次中都提供了一个顶层外部可创建对象。该对象提供了对该层次中其它对象的访问，并且还提供对整个应用程序起作用的方法和属性。每个Microsoft Office应用程序提供一个顶层Application对象。下面语句显示了对Microsoft Excel的 Application 对象的引用：

Dim vbExcel As New Excel.Application'引用Excel对象

Dim book As Excel.Workbook '引用工作薄

Dim mySheet As Excel.Worksheet'引用工作表

然后，可以用这些变量来访问在EXCEL应用程序中的从属对象、以及这些对象的属性和方法。

Set book = vbExcel.Workbooks.Open(TempFileName) '打开报表模版

'TempFileName为一个EXCEL报表文件

Set mySheet = book.Worksheets(1) '设定工作表

3.2 用EXCEL设计报表的模版文件

根据用户提供的报表，可以很快在EXCEL里生成模板文件。所谓生成模板文件只是为了满足用户多方面的需求而设计的。也是为了适合报表以后的更改而做的一点预备工作。在程序里当然不要对模板文件进行操作了，只需要对模板文件的一个拷贝进行操作就行，这也是设计模板文件的一个目的和好处。

Dim TempFileName As String

TempFileName = App.Path + "\temp.xls"'临时文件所在位置

If Dir(TempFileName) "" Then Kill (TempFileName) '清空临时文件

FileCopy ExcelFileName, TempFileName'拷贝报表模板

‘ExcelFileName 为传入的参数报表模板所在的位置

3.3 生成工作表内容

有了上述两步工作的铺垫，下面接着就可以给各单元格赋值了。由于报表中有一些值是需要合计或是总计的，还需要判断那些单元格是需要计算，哪些是不需要计算的。那么实现这一功能的实现采用了循环语句，而且用变量的值记下了哪些是该计算的，哪些是报表的内容。具体如下：

For i = myrow_start To myRow + myrow_start - 1 '处理行

For n = mycolumn_start To myColumn + mycolumn_start - 1

If Not rs.EOF Then

book.ActiveSheet.Cells(i, n) = rs(n - 1)'将记录写入临时表中

If HjSign Or SumHjSign Then'判断是否要合计

For m = 1 To count

If n = HjsSign(m) Then

Hjs(m) = Hjs(m) + rs(n - 1) '计算合计数

SumHjs(m) = SumHjs(m) + rs(n - 1)

End If

Next

End If

Else

book.ActiveSheet.Cells(i, n) = "" '如果到表尾则写入空行

End If

Next

3.4 打印报表

生成了工作表后，就可以对EXCEL发出打印指令了。注意在执行打印操作之前应该对EXCEL临时文件执行一次保存操作，以免在退出应用程序后EXCEL还提示用户是否保存已修改的文件。而打印报表就可以交给EXCEL去处理。当然打印的过程会根据程序给出的页数进行处理。

4 具体实现过程

为所有的报表的制作了一个通用的模块，只要调用这一公用模块，就能生成所需的报表。调用时，应该输入报表所对应的记录集、报表的行数和列数、报表第一行所显示的位置、一个表示要合计数据列数的字符串、合计标志、总计标志、报表模板所在路径、表示报表所要显示的页头的字符串、表示报表所要显示的制表人和制表日期的字符串。

事先做好了所要报表的模板，用一个临时文件拷贝报表的模板，而后，将引入的记录集一行一行地添入EXCEL临时表中，添入记录的同时页检测是否进行合计运算，再将合计的值添入到应在的位置（一般在表尾）。运行之前必须判断临时表是否为空，如果不为空，则要清空临时表。

具体的代码以及代码注释（’后为代码注释）如下：

Public Sub CreateReport

(rs As ADODB.Recordset, myRow As Integer, myColumn As Integer, myrow_start As Integer, mycolumn_start As Integer, strHjSign As String, HjSign As Boolean, SumHjSign As Boolean, ExcelFileName As String, DwandRq As String, ZbRq As String, ZbR As String) '传入的各个参数

Dim vbExcel As New Excel.Application '引用Excel对象

Dim book As Excel.Workbook'工作薄

Dim mySheet As Excel.Worksheet '工作表

Dim i As Integer, m As Integer, n As Integer

Dim Page As Integer, SumPage As Integer

Dim Hjs() As Double

Dim SumHjs() As Double'记录总计的值

Dim HjsSign() As Integer'记录合计的值

Dim count As Integer

Dim strTemp As String

Dim TempFileName As String

TempFileName = App.Path + "\temp.xls"'临时文件所在位置

count = 0

On Error GoTo ProError

If Dir(TempFileName) "" Then Kill (TempFileName) '清空临时文件

FileCopy ExcelFileName, TempFileName '拷贝报表模板

If HjSign Or SumHjSign Then' 如要进行合计则分离合计字段字标志字符串

count = Len(Replace(strHjSign, ",", "," & "*")) - Len(strHjSign)

'查出有几个字段要进行合计

ReDim Hjs(count) As Double '根据count的值重新定义数组

ReDim HjsSign(count) As Integer

ReDim SumHjs(count) As Double

strTemp = ""

n = 1

For i = 1 To Len(strHjSign)

If Mid(strHjSign, i, 1) "," Then

strTemp = strTemp + Mid(strHjSign, i, 1)

Else

HjsSign(n) = Int(Val(strTemp)) '记下了要合计的列

n = n + 1

strTemp = ""

End If

Next

End If

Set book = vbExcel.Workbooks.Open(TempFileName) '打开报表模版

Set mySheet = book.Worksheets(1)

SumPage = Int(rs.RecordCount / myRow) + IIf((rs.RecordCount Mod myRow) > 0, 1, 0)

'得到总页数

frmRepPrb.prb.Max = SumPage * myRow

frmRepPrb.Show

For Page = 1 To SumPage '处理页

book.Sheets("sheet1").Copy before:=book.Sheets("sheet1")'拷贝工作表

book.ActiveSheet.Name = str(Page) '工作表改名

book.ActiveSheet.Cells(2, 1) = DwandRq

For i = myrow_start To myRow + myrow_start - 1 '处理行

For n = mycolumn_start To myColumn + mycolumn_start - 1

If Not rs.EOF Then

book.ActiveSheet.Cells(i, n) = rs(n - 1)'将记录写入临时表中

If HjSign Or SumHjSign Then'判断是否要合计

For m = 1 To count

If n = HjsSign(m) Then

Hjs(m) = Hjs(m) + rs(n - 1) '计算合计数

SumHjs(m) = SumHjs(m) + rs(n - 1)

End If

Next

End If

Else

book.ActiveSheet.Cells(i, n) = "" '如果到表尾则写入空行

End If

Next

If Not rs.EOF Then rs.MoveNext '处理下一记录

frmRepPrb.prb.Value = frmRepPrb.prb.Value + 1

Next

If HjSign Then '处理合计

book.ActiveSheet.Cells(i, 1) = "合计"

For n = 1 To count

book.ActiveSheet.Cells(i, HjsSign(n)) = str(Hjs(n))

Hjs(n) = 0

Next

i = i + 1

End If

If SumHjSign And (Page = SumPage) Then'处理共计

book.ActiveSheet.Cells(i, 1) = "共计"

For n = 1 To count

book.ActiveSheet.Cells(i, HjsSign(n)) = str(SumHjs(n))

SumHjs(n) = 0

Next

i = i + 1

End If

book.ActiveSheet.Cells(i, 1) = "制表日期:" + ZbRq + Space(10) + "制表人:" + ZbR + _

Space(10) + "共" + str(SumPage) + "页" + "第" + str(Page) + "页"

Next

book.Sheets("sheet1").Visible = False

book.Save

Unload frmRepPrb

vbExcel.Visible = True

GoTo ProExit

ProError:'错误控制

Select Case Err.Number

Case 75:

MsgBox "报表文件正在使用!", vbCritical, "系统提示"

Case 94:

Resume Next

Case Else

MsgBox Err.Number & Err.Description

End Select

ProExit:

Set vbExcel = Nothing'释放EXCEL对象

Set book = Nothing

Set mySheet = Nothing

End Sub

5 结束语

针对数据库应用系统中，报表处理的复杂性，提出的基于VB和EXCEL的报表设计及打印的解决方法，该方法不仅可以减轻报表设计的工作量，也提高报表设计的灵活性，是生成报表的一种非常有效的方法。

参考文献：

[1] Eric A.Visual Basic 6.0宝典[M].蒋洪军,译.北京:电子工业出版社,1999.

[2] 盛晓蕾,刘晓梅,张玉军.VB调用EXCEL实现报表打印功能[J].内江科技,2007(3):102-110.