使用QTP自动化测试中,用到对excel的读写操作,这里把一些常用对excel操作的方法进行了归纳,总结。(对excel格式设置的常用操作这里没有进行总结。)
Function DataToExcel(byval filepath,byval filename) Dim objExcel,exlSheet,exlBook,fso,exApp,rows,cols '关闭所有excel 'SystemUtil.CloseProcessByName("excel.exe") Set objExcel = createobject("Excel.Application") Set fso = createobject("scripting.filesystemobject") Set exApp = getObject(,"excel.application") '关闭所有打开的excel文件 If TypeName(exApp) = "Application" Then For each objBook in exApp.workbooks msgbox objBook.FullName objBook.close Next End If Set exApp = nothing If fso.FileExists(filepath & filename) Then Set exlBook =objExcel.Workbooks.Open(filepath & filename) else Set exlBook = objExcel.Workbooks.Add End If objExcel.DisplayAlerts = false 'set exlBook = objExcel.Workbooks.Add '创建excel '获取指定工作表 Set exlSheet = exlBook.Worksheets("sheet1") '或Set exlSheet = exlBook.worksheets(1).activate exlSheet.cells(1,1).value = "aa" '获取excel可用的范围 rows = exlSheet.usedrange.rows.count cols = exlSheet.usedrange.columns.count '设置单元格的列度 exlSheet.Columns("A").ColumnWidth = 20 '设置单元格的行高 exlSheet.Range("A1").RowHeight = 15 exlBook.SaveAs(filepath & filename) 'excel另存为 'objExcel.SaveWorkspace '保存excel文件 'exlBook.close '关闭sheet页面 objExcel.Quit Set exlSheet = nothing Set exlBook = nothing Set objExcel = nothing Set fso = nothing End Function Call DataToExcel ("D:\Documents\Desktop\","hello.xls")
补充:
excel文件另存为的操作:
1.工作表对象的SaveAs方法
exlBook.SaveAs(filepath & filename)
2.通过WScript对象实现:
Set shell = CreateObject("WScript.shell")
shell.SendKeys "^S"
shell.SendKeys filepath
shell.SendKeys "{enter}"
shell.SendKeys "%Y"