QTP 对excel 的操作

1. EOM -> Excel Automation Object Model 即Excel自动化模型对象

在自动化测试中，我们常利用Excel对数据跟脚本进行分离，也就是平常所说的数据驱动。当然了，QTP自带的dataTable也很好用，但是作为一个自动化攻城狮怎么能不会EOM呢。

下面看一段我写的代码，里面包含了一些基本操作。我写的注释也比较详细，相信通过阅读代码一定不难理解：

Dim fileName,filePath
Dim excelApp,fso,sheet,shell
Set excelApp = CreateObject("excel.Application")
Set fso = CreateObject("Scripting.FileSystemObject")
Set shell = CreateObject("WScript.Shell")
fileName = "d:\1.xls"
filePath = "d:\1"
excelApp.Visible = True
excelApp.Workbooks.Add '新增一个workbook
excelApp.Save fileName '保存workbook
excelApp.Quit  '关闭
excelApp.Workbooks.Open fileName  '打开之前保存的excel文件
excelApp.Visible = True '一旦关闭之后，visible又变成了false，需要重置为true
Set sheet = excelApp.Sheets.Add  '新增一个sheet
sheet.name = "sunyu"  '给新增的sheet命名
sheet.cells(1,1) = "hello world"

'下面这段SendKeys的功能是另存为功能，原先的文件还是存在的，此时保存的文件将是xlsx文件
'有人会问为什么不用excelApp.Save 这个方法，我试过在用这个方法时会遇到问题
'大概是因为vbs创建的xls文件保存时会遇到格式的问题，而且save是保存，不是另存为
'关于这一点如果有知道的朋友可以告诉我原因 我的邮箱dieinthemoon@163.com
shell.SendKeys "^s"
shell.SendKeys filePath
shell.SendKeys "{ENTER}"
shell.SendKeys "%Y"

'MsgBox  excelApp.Workbooks.Count '进程中xls的数量；同时让程序停止一下，看看运行中的效果
excelApp.Quit
'删除掉后缀为xls的文件，避免下次运行时冲突
If fso.FileExists(fileName) Then
    fso.DeleteFile fileName
End If
Set excelApp = Nothing
Set fso = Nothing
Set shell = Nothing
Set sheet = Nothing

需要注意的是，我使用的Office2007来测试的。用2010应该也不会有问题，前提是你建立的文件后缀必须是.xls，如果你创建文件时后缀改成.xlsx的话，就会出问题。此时你无法打开你建立的xlsx文件，我在网上也找了一些资料，但是还没有发现好的解决办法。这里我采用了shell.SendKeys "^s"，会默认为另存为功能，自动保存为xlsx格式的文件，并且此时文件是可以打开不会报错的。

这是直接创建xlsx文件后试图打开文件时的错误截图：

正常运行后得到的效果图如下，里面正确输入了数据，并且sheet名字已经改了。

或许你编码的时候发现sheet对象并不能“点”出方法来，有个比较实用的技巧，打开excel，然后点ALT+F11，你可以在里面“点”出你想要的方法来，接下来就复制粘贴吧。

2. 动态运行并加载宏代码

宏的主要用途是可以让用户自行定义一些操作，并通过计算机进行自动化，省去用户很多繁琐重复的操作。

首先在之前的那个excel里加入一段宏代码，功能很简单，弹出一个msgbox

然后在QTP里直接调用：

Set exc = CreateObject("excel.application")

exc.Workbooks.Open "d:\1.xlsm"

Set sheet = exc.Sheets.Item(1)

sheet.sy

exc.ActiveWorkbook.Close

exc.Quit

Set exc = Nothing

Set sheet = Nothing

这里我打开的是一个xlsm格式的文件，这是一个包含宏运算格式的excel文件。程序很简单，只是为了说明excel宏里面的所有方法函数都是可以通过QTP来调用的。

动态加载宏方式一：字符串加载方式

Set exc = CreateObject("excel.application")

Set book = exc.Workbooks.Add

Set module = book.VBProject.VBComponents.Item("ThisWorkBook")

Dim newCode

newCode = "Function sy()" + vbnewline

newCode = newCode + "msgbox ""hello sunyu"""+ vbnewline

newCode = newCode + "End Function"

module.CodeModule.AddFromString newCode

book.sy

book.Close false

exc.Quit

Set exc = nothing

注释1：第一遍执行，出现错误如下：

这是需要在excel里设置的，具体的将Trust Center里的下列选项勾选上就可以了：

再次运行，得到结果”hello sunyu”。

注释2：book.Close false ，这句代码中false代表不保存就关闭。

动态加载宏方式二：文本文件加载方式

Set exc = CreateObject("excel.application")

Set book = exc.Workbooks.Add

Set module = book.VBProject.VBComponents.Item("ThisWorkBook")

module.CodeModule.AddFromFile "d:\sy.bas"

book.sy

book.Close false

exc.Quit

Set exc = nothing

BAS格式的文件时Excel导出的宏文件格式，运行这段代码前先把函数sy保存在一个BAS文件，放在D盘中，然后动态调用即可。由于我们之前已经把”Trust access to the VBA project object model”这个选项选上了，所以我们此次运行脚本没有遇到错误，但是这样每次都手工去做的话，我们的脚本移植能力就太差了，换一台电脑我们的脚本就会出错，还需要再次去设置，这就违背了我们自动化的初衷。

我们可以在脚本里对这个操作进行初始化，利用WSH对象去操作注册表：

Set shell = CreateObject("WScript.shell")

'写入注册表，设置对VBProject为信任

shell.RegWrite "HKEY_CURRENT_USER\Software\Microsoft\Office\12.0\Excel\Security\AccessVBOM",1,"REG_DWORD"

Set exc = CreateObject("excel.application")

Set book = exc.Workbooks.Add

Set module = book.VBProject.VBComponents.Item("ThisWorkBook")

module.CodeModule.AddFromFile "d:\sy.bas"

book.sy

book.Close false

exc.Quit

Set exc = nothing

'释放注册表，以保证下次执行时没有问题

shell.RegDelete "HKEY_CURRENT_USER\Software\Microsoft\Office\12.0\Excel\Security\AccessVBOM"

Set shell = nothing

如下图，AccessVBOM是VBProject信任开关，1为信任，0为不信任，默认值为0

数字签名和宏安全设置都有相应的设置：以office 2002为例： HKEY_CURRENT_USER\Software\Microsoft\Office\10.0\Excel\Security中有三个键值： Level:宏安全等级，1为低，3为高，其它为中，默认值为3； AccessVBOM（这是office2002开始才有的）:信任对于VBE的访问（XP中才有的），1为信任，0为不信任，默认值为0； DontTrustInstalledFiles：信任所有安装的加载宏和模板，0为信任，1为不信任，默认值为0； HKEY_CURRENT_USER\Software\Microsoft\VBA\Trusted中存放的就是信任的数字签名，默认值为无可信任数字签名，可以有多种数字签名。不同版本设置稍有不同。

在注册表中分别对应版本 主键

97： 在HKEY_CURRENT_USER\Software\Microsoft\Office\8.0\Excel下

2000： 在HKEY_CURRENT_USER\Software\Microsoft\Office\9.0\Excel下

2002： 在HKEY_CURRENT_USER\Software\Microsoft\Office\10.0\Excel下

2003： 在HKEY_CURRENT_USER\Software\Microsoft\Office\11.0\Excel下

2007： 在HKEY_CURRENT_USER\Software\Microsoft\Office\12.0\Excel下，

一般来说，初次安装这些键值都为空，只有涉及到操作时才会创建。用操作注册表的方法可以实现“宏安全”设置，但不能进行对“VBE访问信任”设置。

3. 利用ADO打造Excel数据库

首先在D盘里创建一个Excel表格如下：

需要说明的是，ADO操作Excel数据源的话，会默认每个Sheet为一张表，而第一行即为字段名，从第二行开始为字段值。

之前我的博客里有讲过怎样连接mysql的数据库了，excel的连接方法非常相似，可以参考我之前的文章：

http://www.cnblogs.com/ryansunyu/archive/2012/09/06/2673861.html

这里就不多说了，直接贴代码

Dim strConn,strSql

Dim conn,connRst

strConn ="DSN=test;DBQ=D:\1.xlsx;DefaultDir=D:;DriverId=1046;FIL=excel 12.0;MaxBufferSize=2048;PageTimeout=5;"

strSql = "select * from [sunyu$]"

Set conn = CreateObject("adodb.Connection")

conn.Open strConn

Set connRst = conn.Execute(strSql)

While Not connRst.EOF

msgbox connRst.Fields("userName")

connRst.MoveNext

Wend

Set conn = nothing

Set connRst = nothing

效果是遍历了userName这一例里的所有值。后续我会利用Dictionary对象将Excel数据库读取的对象封装起来，这样使用会更方便。

欢迎您关注我的博客：http://www.cnblogs.com/ryansunyu/