工作中常用的QTP操作Excel函数

前言

本文只是对工作中常用的EOM相关函数的整理,并不是要写个大而全的操作手册,如果想对EOM有更多的了解可以参考QTP的帮助文档或查看QTP安装目录\CodeSamplesPlus\UsingExcel.vbs文件。

自动化测试中,为了达到数据与脚本分离的目的,我们需要一种能够统一管理测试数据的工具。QTP自带的DataTable非常好用,但是不利于管理和维护,这时候Excel就进入了我们的视线,QTP提供了对Excel的强大支持,通过EOM(Excel Automation Object Model)即Excel自动化对象模型来对Excel进行操作,下面就介绍一下我在工作中经常用到的Excel操作函数,也当作一次整理和总结。

写单元格

  • 功能:完成对Excel中指定单元格的写入

  • 参数:路径,sheet,单元格行号列号,需写入的值

在D盘根目录下新建qtp.xlsx文件。

 '向Excel中指定单元格写入数据
Function write_cell(filename,sheetname,number_of_row,number_of_column,value) 'filename:Excel完整路径
'sheetname:sheet名称
Dim excelapp,excelsheet,excelbook
'生成Excel对象
set excelapp=createobject("Excel.Application")
'打开Excel
set excelbook=excelapp.WorkBooks.open(filename)
'切换sheet
Set excelsheet=excelbook.Worksheets(sheetname)
'写入值
excelsheet.cells(number_of_row,number_of_column)=value
'保存退出Excel
excelapp.Workbooks().Save
excelapp.Workbooks().Close
excelapp.Quit
Set excelapp=nothing
End Function write_cell "D:\qtp.xlsx","sheet1",,,"hello,qtp"

执行后Excel中第一行第一列的值已设置

工作中常用的QTP操作Excel函数

设置单元格样式

  • 功能:设置单元格的颜色、字体等

  • 参数:路径,sheet,单元格行号列号,字体颜色,背景颜色,对齐方式,字体,字号

 Function set_style(filename,sheetname,number_of_row,number_of_column,font_index,back_index,align,font_name,font_size)
Dim excelapp,excelsheet,excelbook
set excelapp=createobject("Excel.Application")
set excelbook=excelapp.WorkBooks.open(filename)
Set excelsheet=excelbook.Worksheets(sheetname)
'设置背景色
excelsheet.Cells(number_of_row,number_of_column).Interior.ColorIndex=back_index
'设置字体颜色
excelsheet.Cells(number_of_row,number_of_column).Font.ColorIndex=font_index
'设置字体类型
excelsheet.Cells(number_of_row,number_of_column).Font.Name=font_name
'设置字体大小
excelsheet.Cells(number_of_row,number_of_column).Font.Size=font_size
'设置单元格排列方式
excelsheet.Cells(number_of_row,number_of_column).HorizontalAlignment=align
'保存关闭Excel
excelapp.Workbooks().Save
excelapp.Workbooks().Close
excelapp.Quit
End Function '红色背景,黄色字体,居中显示,微软雅黑18号字体
set_style "D:\qtp.xlsx","sheet1",,,,,,"微软雅黑",

效果图,关于颜色的Index值可以参考颜色index列表

工作中常用的QTP操作Excel函数

写多行多列

  • 功能:有时测试中需要大批量的写入数据,每个单元格独立写入耗时耗力,通过数组形式一次写入则方便许多

  • 参数:路径,sheet,写入的数组

 '写多行多列
Function write_multiple(filename,sheetname,arr) 'filename:Excel完整路径
'sheetname:sheet名称
Dim excelapp,excelsheet,excelbook
'生成Excel对象
set excelapp=createobject("Excel.Application")
'打开Excel
set excelbook=excelapp.WorkBooks.open(filename)
'切换sheet
Set excelsheet=excelbook.Worksheets(sheetname)
'写入值
For i= to ubound(arr,)
For j= to ubound(arr,)
excelsheet.cells(i,j)=arr(i,j)
Next
Next
'保存退出Excel
excelapp.Workbooks().Save
excelapp.Workbooks().Close
excelapp.Quit
Set excelapp=nothing
End Function '定义二维数组
Dim arr(,)
arr(,)="hello"
arr(,)="qtp"
arr(,)="hi"
arr(,)="test"
'写入Excel
write_multiple "D:\qtp.xlsx","sheet1",arr

工作中常用的QTP操作Excel函数

读取Excel文件以数组形式返回

  • 功能:以二维数组的形式返回Excel中的数据

  • 参数:路径,sheet

 '读取Excel文件,以数组形式返回
Function read_excel(filename,sheetname)
Dim excelapp,excelsheet,excelbook,number_of_row,number_of_column,arr()
set excelapp=createobject("Excel.Application")
excelapp.DisplayAlerts=Flase
set excelbook=excelapp.WorkBooks.open(filename)
Set excelsheet=excelbook.Worksheets(sheetname)
'获得当前sheet总行数和列数
number_of_row=excelsheet.UsedRange.Rows.Count
number_of_column=excelsheet.UsedRange.Columns.Count
'设置数组范围
ReDim arr(number_of_row,number_of_column)
'将当前sheet中的数据放入数组
For i= to number_of_row
For j= to number_of_column
arr(i,j)=excelsheet.cells(i,j)
Next
Next read_excel=arr
'保存关闭Excel
excelapp.Workbooks().Save
excelapp.Workbooks().Close
excelapp.DisplayAlerts=true
excelapp.Quit
End Function Dim data
data=read_excel("D:\qtp.xlsx","sheet1")
For i= to ubound(data,)
For j= to ubound(data,)
print "第"&i&"行第"&j&"列="&data(i,j)
Next
Next

获取数据在Excel中的起始和结束行号

  • 功能:获取字符串在Excel中开始和结束的行号

  • 参数:路径,sheet,匹配字符串

 '匹配脚本名称在Excel‘中占几行
Function get_range(filename,sheetname,pattern_str)
Dim excelapp,excelsheet,excelbook,number_of_row,arr(),number_of_begin,number_of_end,reg
'设置数组范围
ReDim arr(,)
set excelapp=createobject("Excel.Application")
excelapp.DisplayAlerts=Flase
set excelbook=excelapp.WorkBooks.open(filename)
Set excelsheet=excelbook.Worksheets(sheetname)
'获得当前sheet总行数
number_of_row=excelsheet.UsedRange.Rows.Count
'生成正则表达式对象
Set reg=new RegExp
reg.Pattern=pattern_str
reg.IgnoreCase=false '查找开始及终止行数
For i= to number_of_row
if reg.Test(excelsheet.cells(i,)) then
number_of_begin=i
For j=number_of_begin to number_of_row
if not reg.Test(excelsheet.cells(j,)) then
number_of_end=j-
Exit for
End if
If j=number_of_row Then
number_of_end=number_of_row
End If
Next
Exit for
End if
Next '起始行数
arr(,)=number_of_begin
'结束行数
arr(,)=number_of_end
get_range=arr
'保存关闭Excel
excelapp.Workbooks().Save
excelapp.Workbooks().Close
excelapp.DisplayAlerts=true
excelapp.Quit End Function

将qtp.xlsx中写入如下内容

工作中常用的QTP操作Excel函数

调用函数查找test开头的字符串在文件中的起止行号

Dim begin_end
begin_end=get_range("D:\qtp.xlsx","sheet1","test[0-9]+")
print "从第"&begin_end(,)&"行开始到第"&begin_end(,)&"行结束"

工作中常用的QTP操作Excel函数

Excel操作类

如果对某一Excel的操作很集中,可以将对Excel的操作写成类的形式

 Class OperateExcel

     Public filename,sheetname,excelapp,excelsheet,excelbook
'实现构造方法,类实例化时自动执行
Public default Function initilize(path,name)
filename=path
sheetname=name
setExcelModel
set initilize=Me
End Function Private function setExcelModel()
set excelapp=createobject("Excel.Application")
set excelbook=excelapp.WorkBooks.open(filename)
Set excelsheet=excelbook.Worksheets(sheetname)
End Function Function writeCell(number_of_row,number_of_column,value)
excelsheet.cells(number_of_row,number_of_column)=value
End Function Function setColor(number_of_row,number_of_column,back_index)
'设置指定单元格背景颜色
excelsheet.Cells(number_of_row,number_of_column).Interior.ColorIndex=back_index
End Function Function readExcel
content
...
End Function
..... Function closeExcel
'保存关闭Excel
excelapp.Workbooks().Save
excelapp.Workbooks().Close
excelapp.Quit
End Function End Class Function get_class(excelname,sheet)
set get_class=(new OperateExcel)(excelname,sheet)
End Function

这样使用起来很方便,只要记得在操作完成后调用关闭方法就可以了

 Dim o_e
set o_e=get_class("D:\qtp.xlsx","sheet1")
o_e.writeCell ,,"kddddkk"
o_e.setColor ,,
'关闭Excel对象
o_e.closeExcel
上一篇:web自动化1-selenium简介及环境搭建


下一篇:Python requests.post方法中data与json参数区别