QTP对Excel的操作(EOM)
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/
