VB导出Excel报表

VB 是常用的应用软件开发工具之一，由于VB的报表功能有限，而且一但报表格式发生变化，就得相应修改程序，给应用软件的维护工作带来极大的不便。因此有很多程序员现在已经充分利用EXECL的强大报表功来实现报表功能。但由于VB与EXCEL由于分别属于不同的应用系统，如何把它们有机地结合在一起，是一个值得我们研究的课题。

　　一、 VB读写EXCEL表：

能获取到表的行数的代码：xlSheet2.UsedRange.Rows.Count

　　VB本身提自动化功能可以读写EXCEL表，其方法如下：

　　1、在工程中引用Microsoft Excel类型库：

　　从"工程"菜单中选择"引用"栏；选择Microsoft Excel 9.0 Object Library（EXCEL2000），然后选择"确定"。表示在工程中要引用EXCEL类型库。

　　2、在通用对象的声明过程中定义EXCEL对象：

　　Dim xlApp As Excel.Application
　　Dim xlBook As Excel.WorkBook
　　Dim xlSheet As Excel.Worksheet

　　3、在程序中操作EXCEL表常用命令：

　　Set xlApp = CreateObject("Excel.Application") '创建EXCEL对象
　　Set xlBook = xlApp.Workbooks.Open("文件名") '打开已经存在的EXCEL工件簿文件
　　xlApp.Visible = True '设置EXCEL对象可见（或不可见）
　　Set xlSheet = xlBook.Worksheets("表名") '设置活动工作表
　　xlSheet.Cells(row, col) =值 '给单元格（row,col）赋值
　　xlSheet.PrintOut '打印工作表
　　xlBook.Close (True) '关闭工作簿
　　xlApp.Quit '结束EXCEL对象
　　Set xlApp = Nothing '释放xlApp对象
　　xlBook.RunAutoMacros (xlAutoOpen) '运行EXCEL启动宏
　　xlBook.RunAutoMacros (xlAutoClose) '运行EXCEL关闭宏

　　4、在运用以上VB命令操作EXCEL表时，除非设置EXCEL对象不可见，否则VB程序可继续执行其它操作，也能够关闭EXCEL，同时也可对EXCEL进行操作。但在EXCEL操作过程中关闭EXCEL对象时，VB程序无法知道，如果此时使用EXCEL对象，则VB程序会产生自动化错误。形成VB程序无法完全控制EXCEL的状况，使得VB与EXCEL脱节。

　　二、 EXCEL的宏功能：

　　EXCEL提供一个Visual Basic编辑器，打开Visual Basic编辑器，其中有一工程属性窗口，点击右键菜单的"插入模块"，则增加一个"模块1"，在此模块中可以运用Visual Basic语言编写函数和过程并称之为宏。其中，EXCEL有两个自动宏：一个是启动宏（Sub Auto_Open()），另一个是关闭宏（Sub Auto_Close()）。它们的特性是：当用EXCEL打含有启动宏的工簿时，就会自动运行启动宏，同理，当关闭含有关闭宏的工作簿时就会自动运行关闭宏。但是通过VB的自动化功能来调用EXCEL工作表时，启动宏和关闭宏不会自动运行，而需要在VB中通过命令xlBook.RunAutoMacros (xlAutoOpen)和xlBook.RunAutoMacros (xlAutoClose) 来运行启动宏和关闭宏。

 1 Set xlApp = CreateObject("Excel.Application") '创建EXCEL对象
 2 Set xlBook = xlApp.Workbooks.Open(App.Path & "\文件名.xls") '打开已经存在的EXCEL工件簿文件
 3 xlApp.Visible = True '设置EXCEL对象可见（或不可见）
 4 Set xlSheet = xlBook.Worksheets("sheel1") '设置活动工作表，sheet1表示表名，可以使用字符型变量代替。
 5 xlSheet.Activate '激活工作表，让它处于前台活动中。
 6 xlBook.RunAutoMacros (xlAutoOpen) '运行自动开启宏Macro1()，在EXCEL中加入以下宏
 7 'Sub auto_open() '这是VBA中的自动打开时运行的宏
 8 ' 您要宏自动运行的程序，比如可以call其它的宏，这样VB在打开EXCEL时，使用上面的语句就可以先运行这个宏
 9 'End Sub
10 xlSheet.Cells.Item(行数,列数) =值 '给EXCEL对象中的表的单元格附值。
11 xlSheet.Rows("7:7").Select '选定EXCEL表的第7行
12 xlApp.Selection.Delete shift:=xlUp '删除选定的行
13 xlSheet.Rows(7).Insert '在第7行处插入一行
14 ActiveSheet.PageSetup.CenterHeader ="页眉内容" '给页眉附值
15  xlSheet.Range("f7:h7").Select '选定f7:h7之间的单元格，下面的程序是把它们合并。
16  Application.CutCopyMode = False
17  With xlApp.Selection
18  .HorizontalAlignment = xlGeneral
19  .VerticalAlignment = xlCenter
20  .WrapText = False
21  .Orientation = 0
22  .AddIndent = False
23  .IndentLevel = 0
24  .ShrinkToFit = False
25  .ReadingOrder = xlContext
26  .MergeCells = True
27  End With

如果有自己不会的可以用录制宏的办法，用手功操作一次你要的功能，然后看录下的宏程序，VBA中的宏程序几乎所有的宏在VB中都可以直接使用，关键是对象一定要搞清楚。

VB操作EXCEL的介绍
全面控制 Excel

  1 首先创建 Excel 对象，使用ComObj:
  2 Dim ExcelID as Excel.Application
  3 Set ExcelID as new Excel.Application
  4 1) 显示当前窗口：
  5 ExcelID.Visible := True;
  6 2) 更改 Excel 标题栏：
  7 ExcelID.Caption := '应用程序调用 Microsoft Excel';
  8 3) 添加新工作簿：
  9 ExcelID.WorkBooks.Add;
 10 4) 打开已存在的工作簿：
 11 ExcelID.WorkBooks.Open( 'C:ExcelDemo.xls' );
 12 5) 设置第2个工作表为活动工作表：
 13 ExcelID.WorkSheets[2].Activate;
 14 或 ExcelID.WorkSheets[ 'Sheet2' ].Activate;
 15 6) 给单元格赋值：
 16 ExcelID.Cells[1,4].Value := '第一行第四列';
 17 7) 设置指定列的宽度（单位：字符个数），以第一列为例：
 18 ExcelID.ActiveSheet.Columns[1].ColumnsWidth := 5;
 19 8) 设置指定行的高度（单位：磅）（1磅＝0.035厘米），以第二行为例：
 20 ExcelID.ActiveSheet.Rows[2].RowHeight := 1/0.035; // 1厘米
 21 9) 在第8行之前插入分页符：
 22 ExcelID.WorkSheets[1].Rows[8].PageBreak := 1;
 23 参考代码：ActiveSheet.HPageBreaks(1).Location = Range("A22")
 24 10) 在第8列之前删除分页符：
 25 ExcelID.ActiveSheet.Columns[4].PageBreak := 0;
 26 11) 指定边框线宽度：
 27 ExcelID.ActiveSheet.Range[ 'B3:D4' ].Borders[2].Weight := 3;
 28 1-左 2-右 3-顶 4-底 5-斜( ) 6-斜( / )
 29 12) 清除第一行第四列单元格公式：
 30 ExcelID.ActiveSheet.Cells[1,4].ClearContents;
 31 13) 设置第一行字体属性：
 32 ExcelID.ActiveSheet.Rows[1].Font.Name := '隶书';
 33 ExcelID.ActiveSheet.Rows[1].Font.Color := clBlue;
 34 ExcelID.ActiveSheet.Rows[1].Font.Bold := True;
 35 ExcelID.ActiveSheet.Rows[1].Font.UnderLine := True;
 36 14) 进行页面设置：
 37 a.页眉：
 38 ExcelID.ActiveSheet.PageSetup.CenterHeader := '报表演示';
 39 b.页脚：
 40 ExcelID.ActiveSheet.PageSetup.CenterFooter := '第&P页';
 41 c.页眉到顶端边距2cm：
 42 ExcelID.ActiveSheet.PageSetup.HeaderMargin := 2/0.035;
 43 d.页脚到底端边距3cm：
 44 ExcelID.ActiveSheet.PageSetup.HeaderMargin := 3/0.035;
 45 e.顶边距2cm：
 46 ExcelID.ActiveSheet.PageSetup.TopMargin := 2/0.035;
 47 f.底边距2cm：
 48 ExcelID.ActiveSheet.PageSetup.BottomMargin := 2/0.035;
 49 g.左边距2cm：
 50 ExcelID.ActiveSheet.PageSetup.LeftMargin := 2/0.035;
 51 h.右边距2cm：
 52 ExcelID.ActiveSheet.PageSetup.RightMargin := 2/0.035;
 53 i.页面水平居中：
 54 ExcelID.ActiveSheet.PageSetup.CenterHorizontally := 2/0.035;
 55 j.页面垂直居中：
 56 ExcelID.ActiveSheet.PageSetup.CenterVertically := 2/0.035;
 57 k.打印单元格网线：
 58 ExcelID.ActiveSheet.PageSetup.PrintGridLines := True;
 59 15) 拷贝操作：
 60 a.拷贝整个工作表：
 61 ExcelID.ActiveSheet.Used.Range.Copy;
 62 b.拷贝指定区域：
 63 ExcelID.ActiveSheet.Range[ 'A1:E2' ].Copy;
 64 c.从A1位置开始粘贴：
 65 ExcelID.ActiveSheet.Range.[ 'A1' ].PasteSpecial;
 66 d.从文件尾部开始粘贴：
 67 ExcelID.ActiveSheet.Range.PasteSpecial;
 68 16) 插入一行或一列：
 69 a. ExcelID.ActiveSheet.Rows[2].Insert;
 70 b. ExcelID.ActiveSheet.Columns[1].Insert;
 71 17) 删除一行或一列：
 72 a. ExcelID.ActiveSheet.Rows[2].Delete;
 73 b. ExcelID.ActiveSheet.Columns[1].Delete;
 74 18) 打印预览工作表：
 75 ExcelID.ActiveSheet.PrintPreview;
 76 19) 打印输出工作表：
 77 ExcelID.ActiveSheet.PrintOut;
 78 20) 工作表保存：
 79 If not ExcelID.ActiveWorkBook.Saved then
 80 ExcelID.ActiveSheet.PrintPreview
 81 End if
 82 21) 工作表另存为：
 83 ExcelID.SaveAs( 'C:ExcelDemo1.xls' );
 84 22) 放弃存盘：
 85 ExcelID.ActiveWorkBook.Saved := True;
 86 23) 关闭工作簿：
 87 ExcelID.WorkBooks.Close;
 88 24) 退出 Excel：
 89 ExcelID.Quit;
 90 25) 设置工作表密码:
 91 ExcelID.ActiveSheet.Protect "123", DrawingObjects:=True, Contents:=True, Scenarios:=True
 92 26) EXCEL的显示方式为最大化
 93 ExcelID.Application.WindowState = xlMaximized
 94 27) 工作薄显示方式为最大化
 95 ExcelID.ActiveWindow.WindowState = xlMaximized
 96 28) 设置打开默认工作薄数量
 97 ExcelID.SheetsInNewWorkbook = 3
 98 29) '关闭时是否提示保存(true 保存；false 不保存)
 99 ExcelID.DisplayAlerts = False
100 30) 设置拆分窗口,及固定行位置
101 ExcelID.ActiveWindow.SplitRow = 1
102 ExcelID.ActiveWindow.FreezePanes = True
103 31) 设置打印时固定打印内容
104 ExcelID.ActiveSheet.PageSetup.PrintTitleRows = "$1:$1"
105 32) 设置打印标题
106 ExcelID.ActiveSheet.PageSetup.PrintTitleColumns = ""
107 33) 设置显示方式(分页方式显示)
108 ExcelID.ActiveWindow.View = xlPageBreakPreview
109 34) 设置显示比例
110 ExcelID.ActiveWindow.Zoom = 100
111 35) 让Excel 响应 DDE 请求
112 Ex.Application.IgnoreRemoteRequests = False

用VB操作EXCEL

以下是代码片段：

 1 Private Sub Command3_Click() 
 2 On Error GoTo err1 
 3 Dim i As Long 
 4 Dim j As Long 
 5 Dim objExl As Excel.Application ’声明对象变量 
 6 Me.MousePointer = 11 ’改变鼠标样式 
 7 Set objExl = New Excel.Application ’初始化对象变量 
 8 objExl.SheetsInNewWorkbook = 1 ’将新建的工作薄数量设为1 
 9 objExl.Workbooks.Add ’增加一个工作薄 
10 objExl.Sheets(objExl.Sheets.Count).Name = "book1" ’修改工作薄名称 
11 objExl.Sheets.Add , objExl.Sheets("book1") ‘增加第二个工作薄在第一个之后 
12 objExl.Sheets(objExl.Sheets.Count).Name = "book2" 
13 objExl.Sheets.Add , objExl.Sheets("book2") ‘增加第三个工作薄在第二个之后 
14 objExl.Sheets(objExl.Sheets.Count).Name = "book3" 
15 objExl.Sheets("book1").Select ’选中工作薄<book1> 
16 For i = 1 To 50 ’循环写入数据 
17 For j = 1 To 5 
18 If i = 1 Then 
19 objExl.Selection.NumberFormatLocal = "@" ’设置格式为文本 
20 objExl.Cells(i, j) = " E " & i & j 
21 Else 
22 objExl.Cells(i, j) = i & j 
23 End If 
24 Next 
25 Next 
26 objExl.Rows("1:1").Select ’选中第一行 
27 objExl.Selection.Font.Bold = True ’设为粗体 
28 objExl.Selection.Font.Size = 24 ’设置字体大小 
29 objExl.Cells.EntireColumn.AutoFit ’自动调整列宽 
30 objExl.ActiveWindow.SplitRow = 1 ’拆分第一行 
31 objExl.ActiveWindow. SplitColumn = 0 ’拆分列 
32 objExl.ActiveWindow.FreezePanes = True ’固定拆分
33 
34 objExl.ActiveSheet.PageSetup.PrintTitleRows = "$1:$1" ’设置打印固定行 
35 objExl.ActiveSheet.PageSetup.PrintTitleColumns = "" ’打印标题
36 
37 objExl.ActiveSheet.PageSetup.RightFooter = "打印时间: " & _ 
38 Format(Now, "yyyy年mm月dd日 hh:MM:ss") 
39 objExl.ActiveWindow.View = xlPageBreakPreview ’设置显示方式 
40 objExl.ActiveWindow.Zoom = 100 ’设置显示大小 
41 objExl.ActiveSheet.PageSetup.Orientation = xlLandscape ‘设置打印方向(横向) 
42 ’给工作表加密码 
43 objExl.ActiveSheet.Protect "123", DrawingObjects:=True, _ 
44 Contents:=True, Scenarios:=True 
45 objExl.Application.IgnoreRemoteRequests = False 
46 objExl.Visible = True ’使EXCEL可见 
47 objExl.Application.WindowState = xlMaximized ’EXCEL的显示方式为最大化 
48 objExl.ActiveWindow.WindowState = xlMaximized ’工作薄显示方式为最大化 
49 objExl.SheetsInNewWorkbook = 3 ’将默认新工作薄数量改回3个 
50 Set objExl = Nothing ’清除对象 
51 Me.MousePointer = 0 ’修改鼠标 
52 Exit Sub 
53 err1: 
54 objExl.SheetsInNewWorkbook = 3 
55 objExl.DisplayAlerts = False ’关闭时不提示保存 
56 objExl.Quit ’关闭EXCEL 
57 objExl.DisplayAlerts = True ’关闭时提示保存 
58 Set objExl = Nothing 
59 Me.MousePointer = 0 
60 End Sub

posted @ 2014-03-30 00:23 IT新秀（星火燎原）阅读(852) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

IT新秀（星火燎原）

VB导出Excel报表

公告