前言
本文只是对工作中常用的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中第一行第一列的值已设置
设置单元格样式
功能：设置单元格的颜色、字体等
参数：路径，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列表
写多行多列
功能：有时测试中需要大批量的写入数据，每个单元格独立写入耗时耗力，通过数组形式一次写入则方便许多
参数：路径，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
读取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中写入如下内容
调用函数查找test开头的字符串在文件中的起止行号
Dim begin_end
begin_end=get_range("D:\qtp.xlsx","sheet1","test[0-9]+")
print "从第"&begin_end(,)&"行开始到第"&begin_end(,)&"行结束"
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