VB操作Excel.doc

VB操作Excel
在工程中引用Microsoft Excel类型库从"工程"菜单中选择"引用"栏；选择Microsoft Excel 9.0 Object Library（EXCEL2000），然’后选择"确定"。

‘打开Excel文件
Dim xlApp As Excel.Application '定义EXCEL类
Dim xlBook As Excel.Workbook '定义工件簿类
Dim xlsheet As Excel.Worksheet '定义工作表类
Set xlApp = CreateObject("Excel.Application") '创建EXCEL应用类
Set xlBook = xlApp.Workbooks.Open(App.Path + "\银行导出表.xls") '打开EXCEL工作簿
Set xlsheet = xlBook.Worksheets(1) '打开EXCEL工作表
xlsheet.Activate '激活工作表

‘对EXCEL的各项操作

‘获得数据的行数
xlsheet.UsedRange.Rows.Count

‘在第一列前增加一行
xlsheet.Columns(1).Insert

‘关闭Excel文件
xlBook.Close (True) '关闭EXCEL工作簿
xlApp.Quit '关闭EXCEL
Set xlApp = Nothing '释放EXCEL对象
VB中操作Excel的一般做法（读取）
1、定义Excel操作变量
Dim objExcelFile As Excel.Application
Dim objWorkBook As Excel.Workbook
Dim objImportSheet As Excel.Worksheet
2、打开Excel进程，并打开目标Excel文件
Set objExcelFile = New Excel.Application
objExcelFile.DisplayAlerts = False
Set objWorkBook = objExcelFile.Workbooks.Open(strFileName)
Set objImportSheet = objWorkBook.Sheets(1)
3、获取Excel有效区域的行和列数
intLastColNum = objImportSheet.UsedRange.Columns.Count
intLastRowNum = objImportSheet.UsedRange.Rows.Count
4、逐行读取Excel中数据
由于前两行为Header部分，所以需要从第三行读取
如果第1到第10个单元格的值均为空或空格，则视为空行
For intCountI = 3 To intLastRowNum
''Check if Empty Data Row
blnNullRow = True
For intI = 1 To 10
If Trim$(objImportSheet.Cells(intCountI, intI).Value) <> "" Then
blnNullRow = False
End If
Next intI
若不是空行，则进行读取动作，否则继续向后遍历Excel中的行
If blnNullRow = False Then
获取单元格中的数据，做有效性Check，并将合法数据创建为实体存入对象数组中
objImportSheet.Cells(intCountI, 1).Value
……
End If
Next intCountI
5、退出Excel进程，并关闭Excel相关操作对象
objExcelFile.Quit
Set objWorkBook = Nothing
Set objImportSheet = Nothing
Set objExcelFile = Nothing
vb操作Excel
*************一个标准的
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Set xlApp = CreateObject("Excel.Application") '创建EXCEL对象
Set xlBook = xlApp.Workbooks.Open("d:\vbExcel.xls") '打开已经存在的EXCEL工件簿文件
xlApp.Visible = True '设置EXCEL对象可见（或不可见）
Set xlSheet = xlBook.Worksheets("Sheet1") '设置活动工作表
'xlSheet.Cells(Row, Col) = 值 '给单元格（row,col）赋值
xlSheet.Cells(5, 5) = "Neok"
xlSheet.PrintOut '打印工作表
xlBook.Close (True) '关闭工作簿
xlApp.Quit '结束EXCEL对象
Set xlApp = Nothing '释放xlApp对象
xlBook.RunAutoMacros (xlAutoOpen) '运行EXCEL启动宏
xlBook.RunAutoMacros (xlAutoClose) '运行EXCEL关闭宏
**********************
启动 Excel
Dim objExcel As Excel.Application
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True'设置EXCEL对象可见（或不可见）
创建有一个工作表的工作簿
Dim objWorkBook As Excel.WorkBook
objExcel.SheetsInNewWorkbook = 1
Set objWorkbook = objExcel.Workbooks.Add
打开已经存在的 EXCEL 工件簿文件
Set objWorkbook=objExcel.Workbooks.Open("文件名")
设置活动工作表
Dim objSheet As Excel.Worksheet
Set objSheet = objExcel.Worksheets("表名")
给单元格（row,col）赋值
objSheet.Cells(row, col) =值
给合并的单元格赋值，如（A3：A9）
objSheet.Range("A3:A9") =值
运行 EXCEL 宏
objWorkbook.RunAutoMacros ("宏名")
插入一行
objSheet.Rows("1:1").Insert Shift:=xlDown
保存 EXCEL 文件
objWorkbook.Save
另存为
objWorkbook.SaveAs("文件名")
关闭工作簿
objWorkbook.Close (True)
结束 EXCEL 对象
objExcel.Quit
如果要求在退出应用程序后EXCEL不提示用户是否保存已修改的
文件,则可使用如下语句：
objExcel.DisplayAlerts = False
objExcel.Quit
objExcel.DisplayAlerts = True
释放 objExcel 对象
Set objExcel = Nothing
注意： 在工程中运用 Excel 对象模型，必须引用 Excel Object Library
工程--> 引用 加入 Excel Object Library在 VB 中操作 EXCEL 对象，如为可见，在操作中手式关闭 EXCEL，而 VB 程序无法知道，如果此时使用 EXCEL 对象，则VB程序会产生错误。形成 VB 程序无法完全控制 EXCEL 的状况，使得 VB 与EXCEL 脱节。在编程时应注意加以防止出现出现这种情况。
全面控制Excel
首先创建Excel对象，使用ComObj:
Dim ExcelID as Excel.Application
Set ExcelID as new Excel.Application

1)显示当前窗口：
  ExcelID.Visible:=True;
2)更改Excel标题栏：
  ExcelID.Caption:='应用程序调用MicrosoftExcel';
3)添加新工作簿：
  ExcelID.WorkBooks.Add;
4)打开已存在的工作簿：
  ExcelID.WorkBooks.Open('C:\Excel\Demo.xls');
5)设置第2个工作表为活动工作表：
  ExcelID.WorkSheets[2].Activate;
或ExcelID.WorkSheets['Sheet2'].Activate;
6)给单元格赋值：
  ExcelID.Cells[1,4].Value:='第一行第四列';
7)设置指定列的宽度（单位：字符个数），以第一列为例：
  ExcelID.ActiveSheet.Columns[1].ColumnsWidth:=5;
8)设置指定行的高度（单位：磅）（1磅＝0.035厘米），以第二行为例：
  ExcelID.ActiveSheet.Rows[2].RowHeight:=1/0.035;//1厘米
9)在第8行之前插入分页符：
  ExcelID.WorkSheets[1].Rows[8].PageBreak:=1;
10)在第8列之前删除分页符：
  ExcelID.ActiveSheet.Columns[4].PageBreak:=0;
11)指定边框线宽度：
  ExcelID.ActiveSheet.Range['B3:D4'].Borders[2].Weight:=3;
  1-左 2-右 3-顶4-底 5-斜(\) 6-斜(/)
12)清除第一行第四列单元格公式：
  ExcelID.ActiveSheet.Cells[1,4].ClearContents;
13)设置第一行字体属性：
  ExcelID.ActiveSheet.Rows[1].Font.Name:='隶书';
  ExcelID.ActiveSheet.Rows[1].Font.Color  :=clBlue;
  ExcelID.ActiveSheet.Rows[1].Font.Bold :=True;
  ExcelID.ActiveSheet.Rows[1].Font.UnderLine:=True;
14)进行页面设置：
a.页眉：
  ExcelID.ActiveSheet.PageSetup.CenterHeader:='报表演示';
b.页脚：
  ExcelID.ActiveSheet.PageSetup.CenterFooter:='第&P页';
c.页眉到顶端边距2cm：
  ExcelID.ActiveSheet.PageSetup.HeaderMargin:=2/0.035;
d.页脚到底端边距3cm：
  ExcelID.ActiveSheet.PageSetup.HeaderMargin:=3/0.035;
e.顶边距2cm：
  ExcelID.ActiveSheet.PageSetup.TopMargin:=2/0.035;
f.底边距2cm：
  ExcelID.ActiveSheet.PageSetup.BottomMargin:=2/0.035;
g.左边距2cm：
  ExcelID.ActiveSheet.PageSetup.LeftMargin:=2/0.035;
h.右边距2cm：
  ExcelID.ActiveSheet.PageSetup.RightMargin:=2/0.035;
i.页面水平居中：
  ExcelID.ActiveSheet.PageSetup.CenterHorizontally:=2/0.035;
j.页面垂直居中：
  ExcelID.ActiveSheet.PageSetup.CenterVertically:=2/0.035;
k.打印单元格网线：
  ExcelID.ActiveSheet.PageSetup.PrintGridLines:=True;
15)拷贝操作：
a.拷贝整个工作表：
  ExcelID.ActiveSheet.Used.Range.Copy;
b.拷贝指定区域：
  ExcelID.ActiveSheet.Range['A1:E2'].Copy;
c.从A1位置开始粘贴：
  ExcelID.ActiveSheet.Range.['A1'].PasteSpecial;
d.从文件尾部开始粘贴：
  ExcelID.ActiveSheet.Range.PasteSpecial;
16)插入一行或一列：
a.ExcelID.ActiveSheet.Rows[2].Insert;
b.ExcelID.ActiveSheet.Columns[1].Insert;
17)删除一行或一列：
a.ExcelID.ActiveSheet.Rows[2].Delete;
b.ExcelID.ActiveSheet.Columns[1].Delete;
18)打印预览工作表：
  ExcelID.ActiveSheet.PrintPreview;
19)打印输出工作表：
  ExcelID.ActiveSheet.PrintOut;
20)工作表保存：
  IfnotExcelID.ActiveWorkBook.Savedthen
    ExcelID.ActiveSheet.PrintPreview
  Endif
21)工作表另存为：
  ExcelID.SaveAs('C:\Excel\Demo1.xls');
22)放弃存盘：
  ExcelID.ActiveWorkBook.Saved:=True;
23)关闭工作簿：
  ExcelID.WorkBooks.Close;
24)退出Excel：
  ExcelID.Quit;
25)设置工作表密码:
  ExcelID.ActiveSheet.Protect"123",DrawingObjects:=True,Contents:=True,Scenarios:=True
26)EXCEL的显示方式为最大化
  ExcelID.Application.WindowState=xlMaximized 
27)工作薄显示方式为最大化
  ExcelID.ActiveWindow.WindowState=xlMaximized
28)设置打开默认工作薄数量
  ExcelID.SheetsInNewWorkbook=3
29)'关闭时是否提示保存(true保存；false不保存)
  ExcelID.DisplayAlerts=False
30)设置拆分窗口,及固定行位置
  ExcelID.ActiveWindow.SplitRow=1
  ExcelID.ActiveWindow.FreezePanes=True
31)设置打印时固定打印内容
  ExcelID.ActiveSheet.PageSetup.PrintTitleRows="$1:$1"
32)设置打印标题
  ExcelID.ActiveSheet.PageSetup.PrintTitleColumns=""
33)设置显示方式(分页方式显示)
  ExcelID.ActiveWindow.View=xlPageBreakPreview
34)设置显示比例
  ExcelID.ActiveWindow.Zoom=100
35)让Excel响应DDE请求
  Ex.Application.IgnoreRemoteRequests=False
用VB操作EXCEL示例代码
  Private Sub Command3_Click()
  On Error GoTo err1
  Dim i As Long
  Dim j As Long
  Dim objExl As Excel.Application '声明对象变量
  Me.MousePointer=11 '改变鼠标样式
  Set objExl=New Excel.Application'初始化对象变量
  objExl.SheetsInNewWorkbook=1  '将新建的工作薄数量设为1
  objExl.Workbooks.Add'增加一个工作薄
  objExl.Sheets(objExl.Sheets.Count).Name="book1"  '修改工作薄名称 
  objExl.Sheets.Add,objExl.Sheets("book1")‘增加第二个工作薄在第一个之后
  objExl.Sheets(objExl.Sheets.Count).Name="book2" 
  objExl.Sheets.Add,objExl.Sheets("book2")‘增加第三个工作薄在第二个之后
  objExl.Sheets(objExl.Sheets.Count).Name="book3" 

  objExl.Sheets("book1").Select  '选中工作薄<book1>
  For i=1 To 50'循环写入数据
  For j=1 To 5
  If i=1 Then
    objExl.Selection.NumberFormatLocal="@"  '设置格式为文本 
    objExl.Cells(i,j)="E"&i&j
  Else
    objExl.Cells(i,j)=i&j
  EndIf
  Next
  Next
  objExl.Rows("1:1").Select '选中第一行
  objExl.Selection.Font.Bold=True '设为粗体
  objExl.Selection.Font.Size=24  '设置字体大小
  objExl.Cells.EntireColumn.AutoFit  '自动调整列宽 
  objExl.ActiveWindow.SplitRow=1  '拆分第一行
  objExl.ActiveWindow.SplitColumn=0  '拆分列
  objExl.ActiveWindow.FreezePanes=True '固定拆分objExl.ActiveSheet.PageSetup.PrintTitleRows="$1:$1"  '设置打印固定行
  objExl.ActiveSheet.PageSetup.PrintTitleColumns=""'打印标题objExl.ActiveSheet.PageSetup.RightFooter="打印时间:"&_
  Format(Now,"yyyy年mm月dd日hh:MM:ss")
  objExl.ActiveWindow.View=xlPageBreakPreview'设置显示方式
  objExl.ActiveWindow.Zoom=100  '设置显示大小
  '给工作表加密码
  objExl.ActiveSheet.Protect"123",DrawingObjects:=True,  _
  Contents:=True,Scenarios:=True
  objExl.Application.IgnoreRemoteRequests=False
  objExl.Visible=True  '使EXCEL可见
  objExl.Application.WindowState=xlMaximized'EXCEL的显示方式为最大化
  objExl.ActiveWindow.WindowState=xlMaximized'工作薄显示方式为最大化
  objExl.SheetsInNewWorkbook=3  '将默认新工作薄数量改回3个 
  Set objExl=Nothing'清除对象
  Me.MousePointer=0 '修改鼠标
  ExitSub
  err1:
    objExl.SheetsInNewWorkbook=3

  objExl.DisplayAlerts=False  '关闭时不提示保存
  objExl.Quit'关闭EXCEL
  objExl.DisplayAlerts=True '关闭时提示保存
  Set objExl=Nothing
  Me.MousePointer=0
  End Sub
VB6.0调用Excel制作任意表格
在VB中制作报表一般来讲有三种方法：
1、直接使用VB6.0中自带的Data Report 来做，这种方法有很大的局限性，对于比较规则的报表，但对于比较复杂的报表，比如说一张报表，上部分是人员的工资，下部分是人员的各日的考勤，使用这种方法就无能为力。
2、使用直接的打印方法，即Printer.print ，这种方法直接向打印机打印，就象过去使用Foxpro似的，需要进行打印机定位，并且在Windows下，不同的字符数字所占的宽度也不相同，所以这种方法使用起来非常麻烦。
3、使用微软的Excel。本文重点讨论用Excel来制作报表所涉及的问题。我们用Excel做报表时，不仅是将所要的内容填到Excel的工作表中，而且还需要填写公式、定义合适的格式（格线、字体、对齐）等。
Excel的每张工作表是由若干行、若干列构成的，行列的交叉形成的小格称作单元格。我们正好可以用这些现成的单元格来做我们的报表的相应的格。为了程序简单，可以将报表的模板做好，如表头，相应的行高、列宽、字体、对齐调整好（事实上，这些也可以通过程序来实现，只是麻烦点。），然后利用VB程序来操纵Excel。
这里我们以SQL SERVER 7.0为后台数据。首先定义好需要的变量，在VB6.0的菜单“工程\引用”中确保 Microsoft Excel 8.0 Object Library（在Excel2000中，此处为Excel 9.0）复选框选中。
再向Excel工作表中填入数据：
Dim VBExcel As Excel.Application
Dim xlbook As Excel.Workbook 定义Excel工作簿对象
Dim xlsheet As Excel.Worksheet 定义Excel工作表对象
Dim rssalary As New ADODB.Recordset 定义记录集
rssalary.Open SQLStrsalary, CNstring, adOpenStatic, adLockReadOnly
rssalary 为已计算好的工资数据记录集。
SQLStrsalary 是SQL语句（如SELECT * FROM SALARY）。
Cnstring 是连接字符串（如Provider=SQLOLEDB.1;Integrated
Security=SSPI;Persist Security Info=False;
Initial Catalog=DBname;Data Source=servername）。
Set VBExcel = CreateObject("excel.application")
VBExcel.Visible = True
根据操作人员是否需要见到Excel此处可设为TRUE 或FALSE
Set xlbook = VBExcel.Workbooks.Open(ExcelFile)
ExcelFile为事先设计好的Excel模板文件（包括路径）
Set xlsheet = xlbook.Worksheets("salary")
Salary 为Excel模板工作表的名字
xlsheet.Activate
下面是向单元格中填写数据（以一个简单的工资打印为例）：
xh = 1
Row = 6 从第6行开始是工资数据，1-5 行为工资单表头
SumRow = Row SumRow用来存放合计的起始行
Do While Not rssalary.EOF
Col = 1
xlsheet.Cells(Row, Col).Value = xh
Col = Col + 1
xlsheet.Cells(Row, Col).Value = rssalary("name")
Col = Col + 1
xlsheet.Cells(Row, Col).Value = Str(rssalary("jngz").Value)
Col = Col + 1
xlsheet.Cells(Row, Col).Value = Str(rssalary("jbdx").Value)
Col = Col + 1
xlsheet.Cells(Row, Col).Value = Str(rssalary("gwgz").Value)
Col = Col + 1
xlsheet.Cells(Row, Col).Value = Str(rssalary("zygz").Value)
Col = Col + 1
xlsheet.Cells(Row, Col).Value = Str(rssalary("jfgz").Value)
Col = Col + 1
xlsheet.Cells(Row, Col).Value = Str(rssalary("yfgz").Value)
xh = xh + 1
Row = Row + 1
rssalary.MoveNext
Loop
以上可将各人的工资项打印出来，下面是合计（仅以一列的合计为例，填写公式）：
Col = 1
xlsheet.Cells(Row, Col).Value = ""
Col = Col + 1
xlsheet.Cells(Row, Col).Value = "合计"
Col = Col + 1
HeJi = "=SUM(c" + Trim(Str(SumRow)) + ":c" + Trim(Str(Row - 1)) + ")"
xlsheet.Cells(Row, Col).Value = HeJi
填入公式，HeJi是一个字符串变量。
如果用到的汇总是求平均、记数等，可用相应的Excel函数来替代上面的SUM，如COUNT、AVERAGE等。
因为Excel中所见到的格线，其实并不是实际存在的线，只是为了分割各个单元格；而如果在Exce的选项中设置上有网格线的话，那么每一个格的四周都有线，并不一定能符合实际的需要。因此，如果只是某些地方需要画线，可用以下方法为某个单元格加上边框：
xlsheet.Cells(Row, Col).Borders(xlTop).Weight = xlThin
其中Borders 的参数可为：xlTop，xlBottom，xlRight，xlLeft 代表位置。
它的Weight 的值可为：xlThin，xlMedium,xlThick.... 代表线粗。
在Excel 中，如果某个单元格的宽度小于其中内容的长度，其中的内容将显示一串“＃”，真正的内容就看不到了，我们可用以下的方法来解决：
xlsheet.Cells(Row, Col).ShrinkToFit = true
这样Excel可以自动将单元格中内容的字体缩小来适合所在的单元格的宽度。也可以将模板中所有可能用到的单元格格式的对齐方式中的“缩小字体填充”项选中，然后将不需要缩小字体填充的单元格设为：
　 xlsheet.Cells(Row, Col).ShrinkToFit =false
由于Excel中对于对齐方式(即靠左、右,上、下等)有它自己默认的方式，如数字自动在单元格中靠右，文字自动靠左。如果需要中单元格的内容在某个单元格中的对齐方式可以这样来做：
水平对齐：xlsheet.Cells(Row, Col).HorizontalAlignment = xlCenter
其中xlCenter处可为xlLeft、xlRight。
垂直对齐：xlsheet.Cells(Row, Col). VerticalAlignment = xlCenter
其中xlCenter处可为xlTop、xlBottom。
如果需要用到单元格的内容在多个单元格中居中（跨列居中），可以这样来实现(下例可实现第一行的A到H列居中)：
Dim Total As Range
RangeCells = "a" + RTrim(LTrim(Str(1))) + ":" + “h” + RTrim(LTrim(Str(1)))
Set Total = Range(RangeCells)
Total.Select
Total.HorizontalAlignment = xlHAlignCenterAcrossSelection
然后在这几个单元格中的最前一个单元填入实际内容。
xlsheet.Cells(1, 1).Value = “实际内容”
有时我们可能会用到改变字体及尺寸，可以通过以下方法实现：
Sheet1.Cells(Row,Col).Font.Name = "隶书"
Sheet1.Cells(Row,Col).Font.Size = 20
在单元格中输入数字性的文本，如05182381104，写到Excel的单元格中时，Excel会自动将其视为数字，从而变为5182381104。要解决这个问题，可这样做：
xlsheet.Cells(Row, Col).Value="'"+"05182381104"
上句中的两个双引号中间是一个单引号。
另外，如果一张Excel工作表的长度超过了所设定的打印机的纸张的长度，Excel将自动分页，第二页就会没有表头。在出现这种情况下，如果表头是模板中固定好的，可在模板中设置好，方法是在Excel的菜单“文件\页面设置”中来设置。如果表头是在程序中形成的，可用下面的方法：
xlsheet.PageSetup.PrintTitleRows = rows("1:6").Address
这里“1:6”是根据表头所在的行来自行设定的。
xlsheet.PageSetup.PrintTitleColumns = Columns("A:p").Address
这里“A:P”是根据表头所在的列来设定的。
通过上面这些方法来制做我们平常所见的报表，应该是不会有太大的问题。其实，我们在Excel软件中所能做到的，在VB中都可以通过调用来实现。
12