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 关闭EXCELSet xlApp = Nothing 释放EXCEL对象VB中操作Excel的一般做法（读取）1、定义Excel操作变量Dim objExcelFile As Excel.Application Dim objWorkBook As Excel.Workbook Dim objImportSheet As Excel.Worksheet2、打开Excel进程，并打开目标Excel文件Set objExcelFile = New Excel.ApplicationobjExcelFile.DisplayAlerts = FalseSet objWorkBook = objExcelFile.Workbooks.Open(strFileName)Set objImportSheet = objWorkBook.Sheets(1)3、获取Excel有效区域的行和列数intLastColNum = objImportSheet.UsedRange.Columns.CountintLastRowNum = objImportSheet.UsedRange.Rows.Count4、逐行读取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 intCountI5、退出Excel进程，并关闭Excel相关操作对象objExcelFile.QuitSet objWorkBook = NothingSet objImportSheet = NothingSet objExcelFile = Nothingvb操作Excel*一个标准的Dim xlApp As Excel.ApplicationDim xlBook As Excel.WorkbookDim xlSheet As Excel.WorksheetSet 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) = NeokxlSheet.PrintOut 打印工作表xlBook.Close (True) 关闭工作簿xlApp.Quit 结束EXCEL对象Set xlApp = Nothing 释放xlApp对象xlBook.RunAutoMacros (xlAutoOpen) 运行EXCEL启动宏xlBook.RunAutoMacros (xlAutoClose) 运行EXCEL关闭宏*启动 ExcelDim objExcel As Excel.ApplicationSet objExcel = CreateObject(Excel.Application)objExcel.Visible = True设置EXCEL对象可见（或不可见）创建有一个工作表的工作簿Dim objWorkBook As Excel.WorkBookobjExcel.SheetsInNewWorkbook = 1Set objWorkbook = objExcel.Workbooks.Add打开已经存在的 EXCEL 工件簿文件Set objWorkbook=objExcel.Workbooks.Open(文件名)设置活动工作表Dim objSheet As Excel.WorksheetSet 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 = FalseobjExcel.QuitobjExcel.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.ApplicationSet ExcelID as new Excel.Application1)显示当前窗口： ExcelID.Visible:=True;2)更改Excel标题栏： ExcelID.Caption:=应用程序调用MicrosoftExcel;3)添加新工作簿： ExcelID.WorkBooks.Add;4)打开已存在的工作簿： ExcelID.WorkBooks.Open(C:ExcelDemo.xls);5)设置第2个工作表为活动工作表： ExcelID.WorkSheets2.Activate; 或ExcelID.WorkSheetsSheet2.Activate;6)给单元格赋值： ExcelID.Cells1,4.Value:=第一行第四列;7)设置指定列的宽度（单位：字符个数），以第一列为例： ExcelID.ActiveSheet.Columns1.ColumnsWidth:=5;8)设置指定行的高度（单位：磅）（1磅0.035厘米），以第二行为例： ExcelID.ActiveSheet.Rows2.RowHeight:=1/0.035;/1厘米9)在第8行之前插入分页符： ExcelID.WorkSheets1.Rows8.PageBreak:=1;10)在第8列之前删除分页符： ExcelID.ActiveSheet.Columns4.PageBreak:=0;11)指定边框线宽度： ExcelID.ActiveSheet.RangeB3:D4.Borders2.Weight:=3; 1-左 2-右 3-顶4-底 5-斜() 6-斜(/)12)清除第一行第四列单元格公式： ExcelID.ActiveSheet.Cells1,4.ClearContents;13)设置第一行字体属性： ExcelID.ActiveSheet.Rows1.Font.Name:=隶书; ExcelID.ActiveSheet.Rows1.Font.Color :=clBlue; ExcelID.ActiveSheet.Rows1.Font.Bold :=True; ExcelID.ActiveSheet.Rows1.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.RangeA1:E2.Copy;c.从A1位置开始粘贴： ExcelID.ActiveSheet.Range.A1.PasteSpecial;d.从文件尾部开始粘贴： ExcelID.ActiveSheet.Range.PasteSpecial;16)插入一行或一列：a.ExcelID.ActiveSheet.Rows2.Insert;b.ExcelID.ActiveSheet.Columns1.Insert;17)删除一行或一列：a.ExcelID.ActiveSheet.Rows2.Delete;b.ExcelID.ActiveSheet.Columns1.Delete;18)打印预览工作表： ExcelID.ActiveSheet.PrintPreview;19)打印输出工作表： ExcelID.ActiveSheet.PrintOut;20)工作表保存： IfnotExcelID.ActiveWorkBook.Savedthen ExcelID.ActiveSheet.PrintPreview Endif21)工作表另存为： ExcelID.SaveAs(C:ExcelDemo1.xls);22)放弃存盘： ExcelID.ActiveWorkBook.Saved:=True;23)关闭工作簿： ExcelID.WorkBooks.Close;24)退出Excel： ExcelID.Quit;25)设置工作表密码: ExcelID.ActiveSheet.Protect123,DrawingObjects:=True,Contents:=True,Scenarios:=True26)EXCEL的显示方式为最大化 ExcelID.Application.WindowState=xlMaximized 27)工作薄显示方式为最大化 ExcelID.ActiveWindow.WindowState=xlMaximized 28)设置打开默认工作薄数量 ExcelID.SheetsInNewWorkbook=329)关闭时是否提示保存(true保存；false不保存) ExcelID.DisplayAlerts=False 30)设置拆分窗口,及固定行位置 ExcelID.ActiveWindow.SplitRow=1 ExcelID.ActiveWindow.FreezePanes=True31)设置打印时固定打印内容 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 选中工作薄 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.Protect123,DrawingObjects:=True, _ Contents:=True,Scenarios:=True objExl.Application.IgnoreRemoteRequests=False objExl.Visible=True 使EXCEL可见 objExl.Application.WindowState=xlMaximizedEXCEL的显示方式为最大化 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 SubVB6.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.ApplicationDim xlbook As Excel.Workbook 定义Excel工作簿对象Dim xlsheet As Excel.Worksheet 定义Excel工作表对象Dim rssalary As New ADODB.Recordset 定义记录集rssalary.Open SQLStrsalary, CNstring, adOpenStatic, adLockReadOnlyrssalary 为已计算好的工资数据记录集。SQLStrsalary 是SQL语句（如SELECT * FROM SALARY）。Cnstring 是连接字符串（如Provider=SQLOLEDB.1;IntegratedSecurity=SSPI;Persist Security Info=False;Initial Catalog=DBname;Data Source=servername）。Set VBExcel = CreateObject(excel.application)VBExcel.Visible = True根据操作人员是否需要见到Excel此处可设为TRUE 或FALSESet xlbook = VBExcel.Workbooks.Open(ExcelFile)ExcelFile为事先设计好的Excel模板文件（包括路径）Set xlsheet = xlbook.Worksheets(salary)Salary 为Excel模板工作表的名字xlsheet.Activate下面是向单元格中填写数据（以一个简单的工资打印为例）：xh = 1Row = 6 从第6行开始是工资数据，1-5 行为工资单表头SumRow = Row SumRow用来存放合计的起始行Do While Not rssalary.EOFCol = 1xlsheet.Cells(Row, Col).Value = xhCol = Col + 1xlsheet.Cells(Row, Col).Value = rssalary(name)Col = Col + 1xlsheet.Cells(Row, Col).Value = Str(rssalary(jngz).Value)Col = Col + 1xlsheet.Cells(Row, Col).Value = Str(rssalary(jbdx).Value)Col = Col + 1xlsheet.Cells(Row, Col).Value = Str(rssalary(gwgz).Value)Col = Col + 1xlsheet.Cells(Row, Col).Value = Str(rssalary(zygz).Value)Col = Col + 1xlsheet.Cells(Row, Col).Value = Str(rssalary(jfgz).Value)Col = Col + 1xlsheet.Cells(Row, Col).Value = Str(rssalary(yfgz).Value)xh = xh + 1Row = Row + 1rssalary.MoveNextLoop以上可将各人的工资项打印出来，下面是合计（仅以一列的合计为例，填写公式）：Col = 1xlsheet.Cells(Row, Col).Value = Col = Col + 1xlsheet.Cells(Row, Col).Value = 合计Col = Col + 1HeJi = =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可以自动将单元格中内容的字体缩小来适合所在的单元格的宽度。也可以将模板中所有可能用到的单元格格式的对齐方式中的“缩小字体填充”项选中，然后将不需要缩小字体填充的单元