EXCEL的报表设计及打印

基于VB和EXCEL的报表设计及打印
推荐文／朱诗兵高栓
在现代管理信息系统的开发中，经常涉及到数据信息的分析、加工，
最终还需把统计结果形成各种形式的报表提供给领导决策参考，或进行外
部交流。

在Visual Basic中制作报表，通常是用数据环境设计器(Data Environment Designer)与数据报表设计器(Data Report Designer),或者
使用第三方产品来完成。

但对于大多数习惯于Excel报表的用户而言,用以
上方法生成的报表在格式和功能等方面往往不能满足他们的要求。

由于Excel具有自己的对象库，在Visual Basic工程中可以加以引用，通过对Excel使用OLE自动化，可以创建一些外观整洁的报表，然后打印输出。

这样实现了Visual Basi应用程序对Excel的控制。

本文将针对一个具
体实例，阐述基于VB和EXCEL的报表设计及打印过程。

1）创建Excel对象
Excel对象模型包括了128个不同的对象，从矩形、文本框等简单的对象到透视表，图表等复杂的对象。

下面简单介绍一下其中最重要，也是用
得最多的五个对象。

（1）Application对象
Application对象处于Excel对象层次结构的顶层，表示Excel自身的
运行环境。

（2）Workbook对象
Workbook对象直接地处于Application对象的下层，表示一个Excel工作薄文件。

（3）Worksheet对象
Worksheet对象包含于Workbook对象，表示一个Excel工作表。

（4）Range对象
Range对象包含于Worksheet对象，表示Excel工作表中的一个或多个单元格。

（5）Cells对象
Cells对象包含于Worksheet对象，表示Excel工作表中的一个单元格。

如果要启动一个Excel，使用Workbook和Worksheet对象，下面的代码启动了Excel并创建了一个新的包含一个工作表的工作薄：
Dim zsbexcel As Excel.Application
Set zsbexcel = New Excel.Application
zsbexcel.Visible = True
如要Excel不可见，可使zsbexcel.Visible = False
zsbexcel.SheetsInNewWorkbook = 1
Set zsbworkbook = zsbexcel.Workbooks.Add
2）设置单元格和区域值
要设置一张工作表中每个单元格的值，可以使用Worksheet对象的Range属性或Cells属性。

With zsbexcel.ActiveSheet
.Cells(1, 2).Value = "100"
.Cells(2, 2).Value = "200"
.Cells(3, 2).Value = "=SUM(B1:B2)"
.Range("A3:A9") = "中国人民解放军"
End With
要设置单元格或区域的字体、边框，可以利用Range对象或Cells对象的Borders属性和Font属性：
With objexcel.ActiveSheet.Range("A2:K9").Borders'边框设置.LineStyle = xlBorderLineStyleContinuous
.Weight = xlThin
.ColorIndex = 1
End With
With objexcel.ActiveSheet.Range("A3:K9").Font'字体设置
.Size = 14
.Bold = True
.Italic = True
.ColorIndex = 3
End With
通过对Excel单元格和区域值的各种设置的深入了解,可以创建各种复杂、美观、满足需要的、具有自己特点的报表。

3）预览及打印
生成所需要的工作表后，就可以对EXCEL发出预览、打印指令了。

zsbexcel.ActiveSheet.PageSetup.Orientation = xlPortrait'
设置打印方向
zsbexcel.ActiveSheet.PageSetup.PaperSize = xlPaperA4'
设置打印纸的打下
zsbexcel.Caption = "打印预览"'设置预览窗口的
标题
zsbexcel.ActiveSheet.PrintPreview'打印预览
zsbexcel.ActiveSheet.PrintOut'打印输出
通过打印方向、打印纸张大小的设置，不断进行预览，直到满意为止，最终进行打印输出。

为了在退出应用程序后EXCEL不提示用户是否保存已修改的文件,需使用如下语句：
zsbexcel.DisplayAlerts = False
zsbexcel.Quit'退出EXCEL
zsbexcel.DisplayAlerts = True
如此设计的报表打印是通过EXCEL程序来后台实现的。

对于使用者来说，根本看不到具体过程，只看到一张张漂亮的报表轻易地被打印出来了。

4）具体实例
下面给出一个具体实例，它在window98、Visual Basic 6.0、
Microsoft Office97的环境下调试通过。

在VB中启动一个新的Standard EXE工程，在“工程”菜单的“引用”选项下引用Excel Object Library；然后在Form中添加一个命令按钮cmdExcel；最后在窗体中输入如下代码：
Dim zsbexcel As Excel.Application
Private Sub cmdExcel_Click()
Set zsbexcel = New Excel.Application
zsbexcel.Visible = True
zsbexcel.SheetsInNewWorkbook = 1
Set zsbworkbook = zsbexcel.Workbooks.Add
With zsbexcel.ActiveSheet.Range("A2:C9").Borders'边框设置
.LineStyle = xlBorderLineStyleContinuous
.Weight = xlThin
.ColorIndex = 1
End With
With zsbexcel.ActiveSheet.Range("A3:C9").Font'字体设置
.Size = 14
.Bold = True
.Italic = True
.ColorIndex = 3
End With
zsbexcel.ActiveSheet.Rows.HorizontalAlignment =
xlV AlignCenter'水平居中
zsbexcel.ActiveSheet.Rows.VerticalAlignment =
xlV AlignCenter'垂直居中
With zsbexcel.ActiveSheet
.Cells(1, 2).Value = "100"
.Cells(2, 2).Value = "200"
.Cells(3, 2).Value = "=SUM(B1:B2)"
.Cells(1, 3).Value = "中国人民解放军"
.Range("A3:A9") = "50"
End With
zsbexcel.ActiveSheet.PageSetup.Orientation = xlPortrait'
xlLandscape
zsbexcel.ActiveSheet.PageSetup.PaperSize = xlPaperA4
zsbexcel.ActiveSheet.PrintOut
zsbexcel.DisplayAlerts = False
zsbexcel.Quit
zsbexcel.DisplayAlerts = True
Set zsbexcel = Nothing
将数据从Visual Basic 传输到Excel
推荐将数据从Visual Basic 传输到Excel 的方法适用于
概要
本文介绍将数据从Microsoft Visual Basic 应用程序传输到Microsoft Excel 的多种方法。

本文
还介绍了每种
方法的优缺点，这样您可以选择最适合您的解决方案。

更多信息
将数据传输到Excel 工作簿最常用的方法是“自动化”功能。

“自动化”功能为您提供了指定数据在工作簿中所
处位置的最大的灵活性，以及对工作簿进行格式设置和在运行时进行各种设置的功能。

利用“自动化”功能，您可
以使用多种方法传输数据：
a.. 逐单元格传输数据
b.. 将数组中的数据传输到单元格区域
c.. 使用CopyFromRecordset 方法向单元格区域传输ADO 记录集中的数据
d.. 在Excel 工作表上创建一个QueryTable，它包含对ODBC 或OLEDB 数据源进行查询的结果。

e.. 将数据传输到剪贴板，然后将剪贴板内容粘贴到Excel 工作表中。

您还可以使用一些其他方法将数据传输到Excel，而不必使用“自动化”功能。

如果您正在运行服务器端应用程序
，这是一种将批量数据处理从客户端移走的好方法。

在没有“自动化”功能的情况下，可以使用下列方法来传输数
据：
a.. 将数据传输到制表符分隔或逗号分隔的文本文件，然后Excel 可以将该文本文件分析为工作表上的单元格
b.. 使用ADO 将数据传输到工作表
c.. 使用动态数据交换(DDE) 将数据传输到Excel
下面的部分提供了每种解决方案的详细信息。

使用“自动化”功能逐单元格传输数据
利用“自动化”功能，您可以逐单元格地向工作表传输数据：
Dim oExcel As Object
Dim oBook As Object
Dim oSheet As Object
'Start a new workbook in Excel
Set oExcel = CreateObject("Excel.Application")
Set oBook = oExcel.Workbooks.Add
'Add data to cells of the first worksheet in the new workbook
Set oSheet = oBook.Worksheets(1)
oSheet.Range("A1").Value = "Last Name"
oSheet.Range("B1").Value = "First Name"
oSheet.Range("A1:B1").Font.Bold = True
oSheet.Range("A2").Value = "Doe"
oSheet.Range("B2").Value = "John"
'Save the Workbook and Quit Excel
oBook.SaveAs "C:\Book1.xls"
oExcel.Quit如果数据量较少，逐单元格传输数据是一种完全可以接受的方法。

您可以灵活地将数据放到工作簿
中的任何位置，并可以在运行时根据条件对单元格进行格式设置。

不过，如果需要向Excel 工作簿传输大量数据
，则不建议您使用此方法。

您在运行时获取的每一个Range 对象都会产生一个接口请求；因此，以这种方式传输
数据速度较慢。

另外，Microsoft Windows 95 和Windows 98 在接口请求上有64K 限制。

如果在接口请求上达到
或超过这一64K 限制，自动化服务器(Excel) 可能停止响应，或者您可能收到表明内存不足的错误。

Windows 95
和Windows 98 中的这一限制在下面的知识库文章中进行了讨论：
216400 PRB:Cross-Process COM Automation Can Hang Client Application on Win95/98
需要再次强调的是，逐单元格传输数据仅适用于传输少量数据。

如果需要将大量的数据集传输到Excel，应考虑下
文提供的解决方案之一。

有关自动化Excel 的更多示例代码，请参见以下Microsoft 知识库文章：
219151 HOWTO：在Visual Basic 中自动运行Microsoft Excel
使用“自动化”功能将数据数组传输到工作表上的区域
一次可以将一个数据数组传输到多个单元格区域：
Dim oExcel As Object
Dim oBook As Object
Dim oSheet As Object
'Start a new workbook in Excel
Set oExcel = CreateObject("Excel.Application")
Set oBook = oExcel.Workbooks.Add
'Create an array with 3 columns and 100 rows
Dim DataArray(1 To 100, 1 To 3) As Variant
Dim r As Integer
For r = 1 To 100
DataArray(r, 1) = "ORD" & Format(r, "0000")
DataArray(r, 2) = Rnd() * 1000
DataArray(r, 3) = DataArray(r, 2) * 0.7
Next
'Add headers to the worksheet on row 1
Set oSheet = oBook.Worksheets(1)
oSheet.Range("A1:C1").Value = Array("Order ID", "Amount", "Tax")
'Transfer the array to the worksheet starting at cell A2
oSheet.Range("A2").Resize(100, 3).Value = DataArray
'Save the Workbook and Quit Excel
oBook.SaveAs "C:\Book1.xls"
oExcel.Quit
如果您使用数组传输数据而不是逐单元格传输数据，则在传输大量数据时，传输性能会大大增强。

请注意上述代码
中的以下行，该行将数据传输到工作表中的300 个单元格：
oSheet.Range("A2").Resize(100, 3).Value = DataArray此行表示两个接口请求（一个用于Range 方法返回
的Range 对象，另一个用于Resize 方法返回的Range 对象）。

另一方面，逐单元格传输数据需要请求指向
Range 对象的300 个接口。

只要有可能，您就可以从批量传输数据以及减少所发出的接口请求的数量中受益。

使用“自动化”功能将ADO 记录集传输到工作表区域
Excel 2000 引入了CopyFromRecordset 方法，使您能够将ADO（或DAO）记录集传输到工作表上的某个区域。

下
面的代码说明了如何自动化Excel 2000、Excel 2002 或Office Excel 2003，以及使用CopyFromRecordset 方
法传输罗斯文示例数据库中订单表的内容。

'Create a Recordset from all the records in the Orders table
Dim sNWind As String
Dim conn As New ADODB.Connection
Dim rs As ADODB.Recordset
sNWind = _
"C:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb"
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
sNWind & ";"
conn.CursorLocation = adUseClient
Set rs = conn.Execute("Orders", , adCmdTable)
'Create a new workbook in Excel
Dim oExcel As Object
Dim oBook As Object
Dim oSheet As Object
Set oExcel = CreateObject("Excel.Application")
Set oBook = oExcel.Workbooks.Add
Set oSheet = oBook.Worksheets(1)
'Transfer the data to Excel
oSheet.Range("A1").CopyFromRecordset rs
'Save the Workbook and Quit Excel
oBook.SaveAs "C:\Book1.xls"
oExcel.Quit
'Close the connection
rs.Close
conn.CloseExcel
97 还提供了一种CopyFromRecordset 方法，但它只能用于DAO 记录集。

Excel 97 中的CopyFromRecordset 不
支持ADO。

有关使用ADO 和CopyFromRecordset 方法的更多信息，请参见以下Microsoft 知识库文章：246335 HOWTO：使用“自动化”功能将数据从ADO 记录集传输到Excel
使用“自动化”功能在工作表上创建QueryTable
QueryTable 对象代表由外部数据源返回的数据构建的表。

当您自动运行Microsoft Excel 时，只须提供指向
OLEDB 或ODBC 数据源的连接字符串和SQL 字符串就可以创建QueryTable。

Excel 假定能够生成记录集，并负责
将其插入工作表中您指定的位置。

使用QueryTables 可提供优于CopyFromRecordset 方法的多种优点：
a.. Excel 处理记录集的创建并将其放置到工作表中。

b.. 查询可以保存在QueryTable 中，以便在以后能够刷新，以获取更新的记录集。

c.. 当向工作表中添加新的QueryTable 时，可以指定将工作表上的单元格中已经存在的数据移位，以便放置新
数据（有关详细信息，请查看RefreshStyle 属性）。

下面的代码演示了如何自动运行Excel 2000、Excel 2002 或Office Excel 2003，以便使用罗斯文示例数据库中
的数据在Excel 工作表中创建新的QueryTable：
'Create a new workbook in Excel
Dim oExcel As Object
Dim oBook As Object
Dim oSheet As Object
Set oExcel = CreateObject("Excel.Application")
Set oBook = oExcel.Workbooks.Add
Set oSheet = oBook.Worksheets(1)
'Create the QueryTable
Dim sNWind As String
sNWind = _
"C:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb"
Dim oQryTable As Object
Set oQryTable = oSheet.QueryTables.Add( _
"OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
sNWind & ";", oSheet.Range("A1"), "Select * from Orders")
oQryTable.RefreshStyle = xlInsertEntireRows
oQryTable.Refresh False
'Save the Workbook and Quit Excel
oBook.SaveAs "C:\Book1.xls"
oExcel.Quit
使用剪贴板
Windows 剪贴板还可以用作将数据传输到工作表的一种机制。

要将数据粘贴到工作表上的多个单元格中，可以复制
列由制表符分隔、行由回车符分隔的字符串。

下面的代码说明了Visual Basic 如何使用其剪贴板对象将数据传输
到Excel：
'Copy a string to the clipboard
Dim sData As String
sData = "FirstName" & vbTab & "LastName" & vbTab & "Birthdate" & vbCr _
& "Bill" & vbTab & "Brown" & vbTab & "2/5/85" & vbCr _
& "Joe" & vbTab & "Thomas" & vbTab & "1/1/91"
Clipboard.Clear
Clipboard.SetText sData
'Create a new workbook in Excel
Dim oExcel As Object
Dim oBook As Object
Set oExcel = CreateObject("Excel.Application")
Set oBook = oExcel.Workbooks.Add
'Paste the data
oBook.Worksheets(1).Range("A1").Select
oBook.Worksheets(1).Paste
'Save the Workbook and Quit Excel
oBook.SaveAs "C:\Book1.xls"
oExcel.Quit
创建可由Excel 分析为行和列的带分隔符的文本文件
Excel 可以打开由制表符或逗号分隔的文件并正确地将数据分析为单元格。

当您希望向工作表传输大量数据而只使
用少量“自动化”功能（如果有）时，可以使用此功能。

这对于客户端-服务器应用程序而言可能是一种好方法，
因为文本文件可以在服务器端生成。

然后，可以在客户端根据需要使用“自动化”功能打开文本文件。

下面的代码说明了如何从ADO 记录集创建逗号分隔的文本文件：
'Create a Recordset from all the records in the Orders table
Dim sNWind As String
Dim conn As New ADODB.Connection
Dim rs As ADODB.Recordset
Dim sData As String
sNWind = _
"C:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb"
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
sNWind & ";"
conn.CursorLocation = adUseClient
Set rs = conn.Execute("Orders", , adCmdTable)
'Save the recordset as a tab-delimited file
sData = rs.GetString(adClipString, , vbTab, vbCr, vbNullString)
Open "C:\Test.txt" For Output As #1
Print #1, sData
Close #1
'Close the connection
rs.Close
conn.Close
'Open the new text file in Excel
Shell "C:\Program Files\Microsoft Office\Office\Excel.exe " & _
Chr(34) & "C:\Test.txt" & Chr(34), vbMaximizedFocus
如果文本文件具有.CSV 扩展名，则Excel 将打开该文件，而不显示“文本导入向导”，并自动假定该文件是逗
号分隔文件。

类似地，如果文件具有.TXT 扩展名，Excel 将自动使用制表符分析此文件。

在前面的代码示例中，Excel 使用Shell 语句启动，文件名用作命令行参数。

前面的示例中没有使用“自动化”
功能。

不过，如果希望，您可以使用最少量的“自动化”功能打开文本文件，并以Excel 工作簿格式保存它：
'Create a new instance of Excel
Dim oExcel As Object
Dim oBook As Object
Dim oSheet As Object
Set oExcel = CreateObject("Excel.Application")
'Open the text file
Set oBook = oExcel.Workbooks.Open("C:\Test.txt")
'Save as Excel workbook and Quit Excel
oBook.SaveAs "C:\Book1.xls", xlWorkbookNormal
oExcel.Quit
有关使用Visual Basic 应用程序中“文件I/O”的更多信息，请参见以下Microsoft 知识库中文章：
172267 RECEDIT.VBP Demonstrates File I/O in Visual Basic
下面的文章还讨论并提供了控制Visual Basic for Applications 中的“文件I/O”的示例代码：File Access with Visual Basic for Applications（Visual Basic for Applications 中的文件访问）
使用ADO 将数据传输到工作表
使用Microsoft Jet OLE DB 提供程序，您可以将记录添加到现有Excel 工作簿的一个表中。

Excel 中的“表”
仅是带有定义名称的一个区域。

区域中的第一行必须包含标题（或字段名），而且所有后续行都包含记录。

下列步
骤说明了如何使用名为MyTable 的空表创建工作簿：
1.. 在Excel 中启动一个新工作簿。

2.. 将下面的标题添加到Sheet1 中的A1:B1 单元格：
A1:FirstName B1:LastName
3.. 将单元格B1 的格式设置为右对齐。

4.. 选择A1:B1。

5.. 在插入菜单上，选择名称，然后选择定义。

输入名称MyTable，并单击确定。

6.. 将新工作簿另存为C:\Book1.xls 并退出Excel。

要使用ADO 将记录添加到MyTable 中，您可以使用与以下内容类似的代码：'Create a new connection object for Book1.xls
Dim conn As New ADODB.Connection
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Book1.xls;Extended Properties=Excel 8.0;"
conn.Execute "Insert into MyTable (FirstName, LastName)" & _
" values ('Bill', 'Brown')"
conn.Execute "Insert into MyTable (FirstName, LastName)" & _
" values ('Joe', 'Thomas')"
conn.Close
在以此方式将记录添加到该表中后，工作簿中的格式将会保留。

在前面的示例中，添加到B 列中的新字段的格式
设置为右对齐。

添加到行中的每个记录都将继承它前面的行的格式。

请注意，在将一个记录添加到工作表中的一个或多个单元格时，该记录将会覆盖这些单元格中以前存在的任何数据
；也就是说，在添加新记录时，工作表中的行不会“向下推移”。

在工作表中设计数据的布局时应考虑到这一点。

有关使用ADO 访问Excel 工作簿的其他信息，请参见以下Microsoft 知识库中文章：
195951 HOWTO:Query and Update Excel Data Using ADO From ASP
使用DDE 将数据传输到Excel
在与Excel 通信和传输数据方面，DDE 是“自动化”的替代方法；不过，由于“自动化”和COM 的出现，DDE 不
再是与其他应用程序通信的首选方法，而且应仅在没有其他可用的解决方案时才使用该方法。

要使用DDE 将数据传输到Excel，您可以：
a.. 使用LinkPoke 方法将数据发送到特定的单元格区域，
- 或-
b.. 使用LinkExecute 方法发送Excel 将执行的命令。

下面的代码示例说明了如何建立DDE 与Excel 的会话，以便能够将数据发送到工作表中的单元格，并执行命令。

要使用此示例成功建立DDE 与LinkTopic Excel|MyBook.xls 的会话，名为MyBook.xls 的工作簿必须在正运行
的Excel 实例中已打开。

注意：在此示例中，Text1 代表Visual Basic 窗体上的文本框控件：
'Initiate a DDE communication with Excel
Text1.LinkMode = 0
Text1.LinkTopic = "Excel|MyBook.xls"
Text1.LinkItem = "R1C1:R2C3"
Text1.LinkMode = 1
'Poke the text in Text1 to the R1C1:R2C3 in MyBook.xls
Text1.Text = "one" & vbTab & "two" & vbTab & "three" & vbCr & _
"four" & vbTab & "five" & vbTab & "six"
Text1.LinkPoke
'Execute commands to select cell A1 (same as R1C1) and change the font
'format
Text1.LinkExecute "[SELECT(""R1C1"")]"
Text1.LinkExecute "[FONT.PROPERTIES(""Times New Roman"",""Bold"",10)]"
'Terminate the DDE communication
Text1.LinkMode = 0在Excel 中使用LinkPoke 时，您需要在行-列(R1C1) 批注中为LinkItem 指定区域。

如果您要将数据发送到多个单元格，则可以使用列由制表符分隔、行由回车符分隔的字符串。

在使用LinkExecute 请求Excel 执行命令时，必须为Excel 提供Excel 宏语言(XLM) 语法的命令。

XLM 文档
未包括在Excel 97 版和更高版本中。

有关如何获取XLM 文档的更多信息，请参见以下Microsoft 知识库文章：
143466 Download the Excel 97 Macro Functions Help File for XLM Documentation
不建议使用DDE 解决方案与Excel 通信。

“自动化”功能提供了最大的灵活性，让您能够访问Excel 提供的更
多新功能。

参考
有关其他信息，请单击下面的文章编号，以查看Microsoft 知识库中相应的文章：
306022 HOW TO：使用Visual Basic .NET 向Excel 工作簿传输数据
这篇文章中的信息适用于:
a.. Microsoft Office Excel 2003
b.. Microsoft Excel 2002
c.. Microsoft Excel 2000
d.. Microsoft Excel 97 for Windows
e.. Microsoft Visual Basic for Applications 5.0
f.. Microsoft Visual Basic for Applications 6.0
最近更新: 2004-4-26 (5.0)
关键字: kbAutomation kbDDE kbinfo KB247412 kbAudDeveloper
Microsoft和/或其各供应商对于为任何目的而在本服务器上发布的文件及有关图形所含信息的适用性，不作任何声
明。

所有该等文件及有关图形均"依样"提供，而不带任何性质的保证。

Microsoft和/或其各供应商特此声明，对
所有与该等信息有关的保证和条件不负任何责任，该等保证和条件包括关于适销性、符合特定用途、所有权和非侵
权的所有默示保证和条件。

在任何情况下，在由于使用或运行本服务器上的信息所引起的或
与该等使用或运行有关
的诉讼中，Microsoft和/或其各供应商就因丧失使用、数据或利润所导致的任何特别的、间接的或衍生性的损失或
任何种类的损失，均不负任何责任，无论该等诉讼是合同之诉、疏忽或其它侵权行为之诉。

---------------------
Best Regards
HI1/Apple Jia
Tel:68251233 ext:6536
Zero Defected。