QTP對(duì)Excel的操作(EOM)

1.       EOM -> Excel Automation Object Model 即Excel自動(dòng)化模型對(duì)象

在自動(dòng)化測(cè)試中，我們常利用Excel對(duì)數(shù)據(jù)跟腳本進(jìn)行分離，也就是平常所說的數(shù)據(jù)驅(qū)動(dòng)。當(dāng)然了，QTP自帶的dataTable也很好用，但是作為一個(gè)自動(dòng)化攻城獅怎么能不會(huì)EOM呢。

下面看一段我寫的代碼，里面包含了一些基本操作。我寫的注釋也比較詳細(xì)，相信通過閱讀代碼一定不難理解：

DimfileName,filePath
DimexcelApp,fso,sheet,shell
SetexcelApp=CreateObject("excel.Application")
Setfso=CreateObject("Scripting.FileSystemObject")
Setshell=CreateObject("WScript.Shell")
fileName="d:\1.xls"
filePath="d:\1"
excelApp.Visible=True
excelApp.Workbooks.Add'新增一個(gè)workbook
excelApp.SavefileName'保存workbook
excelApp.Quit'關(guān)閉
excelApp.Workbooks.OpenfileName'打開之前保存的excel文件
excelApp.Visible=True'一旦關(guān)閉之后，visible又變成了false，需要重置為true
Setsheet=excelApp.Sheets.Add'新增一個(gè)sheet
sheet.name="sunyu"'給新增的sheet命名
sheet.cells(1,1)="helloworld"

'下面這段SendKeys的功能是另存為功能，原先的文件還是存在的，此時(shí)保存的文件將是xlsx文件
'有人會(huì)問為什么不用excelApp.Save這個(gè)方法，我試過在用這個(gè)方法時(shí)會(huì)遇到問題
'大概是因?yàn)?/span>vbs創(chuàng)建的xls文件保存時(shí)會(huì)遇到格式的問題，而且save是保存，不是另存為
'關(guān)于這一點(diǎn)如果有知道的朋友可以告訴我原因我的郵箱dieinthemoon@163.com
shell.SendKeys"^s"
shell.SendKeysfilePath
shell.SendKeys"{ENTER}"
shell.SendKeys"%Y"

'MsgBoxexcelApp.Workbooks.Count'進(jìn)程中xls的數(shù)量；同時(shí)讓程序停止一下，看看運(yùn)行中的效果
excelApp.Quit
'刪除掉后綴為xls的文件，避免下次運(yùn)行時(shí)沖突
Iffso.FileExists(fileName)Then
fso.DeleteFilefileName
EndIf
SetexcelApp=Nothing
Setfso=Nothing
Setshell=Nothing
Setsheet=Nothing

需要注意的是，我使用的Office2007來測(cè)試的。用2010應(yīng)該也不會(huì)有問題，前提是你建立的文件后綴必須是.xls，如果你創(chuàng)建文件時(shí)后綴改成.xlsx的話，就會(huì)出問題。此時(shí)你無法打開你建立的xlsx文件，我在網(wǎng)上也找了一些資料，但是還沒有發(fā)現(xiàn)好的解決辦法。這里我采用了shell.SendKeys?"^s"，會(huì)默認(rèn)為另存為功能，自動(dòng)保存為xlsx格式的文件，并且此時(shí)文件是可以打開不會(huì)報(bào)錯(cuò)的。

這是直接創(chuàng)建xlsx文件后試圖打開文件時(shí)的錯(cuò)誤截圖：

正常運(yùn)行后得到的效果圖如下，里面正確輸入了數(shù)據(jù)，并且sheet名字已經(jīng)改了。

或許你編碼的時(shí)候發(fā)現(xiàn)sheet對(duì)象并不能“點(diǎn)”出方法來，有個(gè)比較實(shí)用的技巧，打開excel，然后點(diǎn)ALT+F11，你可以在里面“點(diǎn)”出你想要的方法來，接下來就復(fù)制粘貼吧。

2.       動(dòng)態(tài)運(yùn)行并加載宏代碼

宏的主要用途是可以讓用戶自行定義一些操作，并通過計(jì)算機(jī)進(jìn)行自動(dòng)化，省去用戶很多繁瑣重復(fù)的操作。

首先在之前的那個(gè)excel里加入一段宏代碼，功能很簡單，彈出一個(gè)msgbox

然后在QTP里直接調(diào)用：

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

這里我打開的是一個(gè)xlsm格式的文件，這是一個(gè)包含宏運(yùn)算格式的excel文件。程序很簡單，只是為了說明excel宏里面的所有方法函數(shù)都是可以通過QTP來調(diào)用的。

動(dòng)態(tài)加載宏方式一：字符串加載方式

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：第一遍執(zhí)行，出現(xiàn)錯(cuò)誤如下：

這是需要在excel里設(shè)置的，具體的將Trust Center里的下列選項(xiàng)勾選上就可以了：

再次運(yùn)行，得到結(jié)果”hello sunyu”。

注釋2：book.Close false ，這句代碼中false代表不保存就關(guān)閉。

動(dòng)態(tài)加載宏方式二：文本文件加載方式

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格式的文件時(shí)Excel導(dǎo)出的宏文件格式，運(yùn)行這段代碼前先把函數(shù)sy保存在一個(gè)BAS文件，放在D盤中，然后動(dòng)態(tài)調(diào)用即可。由于我們之前已經(jīng)把”Trust access to the VBA project object model”這個(gè)選項(xiàng)選上了，所以我們此次運(yùn)行腳本沒有遇到錯(cuò)誤，但是這樣每次都手工去做的話，我們的腳本移植能力就太差了，換一臺(tái)電腦我們的腳本就會(huì)出錯(cuò)，還需要再次去設(shè)置，這就違背了我們自動(dòng)化的初衷。

我們可以在腳本里對(duì)這個(gè)操作進(jìn)行初始化，利用WSH對(duì)象去操作注冊(cè)表：

Set shell = CreateObject("WScript.shell")

'寫入注冊(cè)表，設(shè)置對(duì)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

'釋放注冊(cè)表，以保證下次執(zhí)行時(shí)沒有問題

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

Set shell = nothing

如下圖，AccessVBOM是VBProject信任開關(guān)，1為信任，0為不信任，默認(rèn)值為0

數(shù)字簽名和宏安全設(shè)置都有相應(yīng)的設(shè)置： 以office 2002為例： HKEY_CURRENT_USER\Software\Microsoft\Office\10.0\Excel\Security中有三個(gè)鍵值： Level:宏安全等級(jí)，1為低，3為高，其它為中，默認(rèn)值為3； AccessVBOM（這是office2002開始才有的）:信任對(duì)于VBE的訪問（XP中才有的），1為信任，0為不信任，默認(rèn)值為0； DontTrustInstalledFiles：信任所有安裝的加載宏和模板，0為信任，1為不信任，默認(rèn)值為0； HKEY_CURRENT_USER\Software\Microsoft\VBA\Trusted中存放的就是信任的數(shù)字簽名，默認(rèn)值為無可信任數(shù)字簽名，可以有多種數(shù)字簽名。 不同版本設(shè)置稍有不同。

在注冊(cè)表中分別對(duì)應(yīng) 版本 主鍵

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下，

一般來說，初次安裝這些鍵值都為空，只有涉及到操作時(shí)才會(huì)創(chuàng)建。 用操作注冊(cè)表的方法可以實(shí)現(xiàn)“宏安全”設(shè)置，但不能進(jìn)行對(duì)“VBE訪問信任”設(shè)置。

3.       利用ADO打造Excel數(shù)據(jù)庫

首先在D盤里創(chuàng)建一個(gè)Excel表格如下：

需要說明的是，ADO操作Excel數(shù)據(jù)源的話，會(huì)默認(rèn)每個(gè)Sheet為一張表，而第一行即為字段名，從第二行開始為字段值。

之前我的博客里有講過怎樣連接mysql的數(shù)據(jù)庫了，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這一例里的所有值。后續(xù)我會(huì)利用Dictionary對(duì)象將Excel數(shù)據(jù)庫讀取的對(duì)象封裝起來，這樣使用會(huì)更方便。

歡迎您關(guān)注我的博客：http://www.cnblogs.com/ryansunyu/