relevantcodes.com的《QTP: Creating a Highly Efficient Test-Data Dictionary》这篇文章介绍了如何使用ADO读取Excel测试数据,并存储到Dictionary对象中:
http://relevantcodes.com/qtp-creating-a-highly-efficient-test-data-dictionary/
定义一个类clsTestData,负责从Excel读取数据到Dictionary对象中:
'Class clsTestData: Stored in Function Library
Class clsTestData
Private mDict 'Local Instance of Scripting.Dictionary
Public sWorkBook 'Excel WorkBook
Public vSheet 'Excel WorkSheet
Public iRow 'Excel Row where test data is contained
'Loads the Dictionary Object
Public Default Function Load(sWorkBook, vSheet, iRow)
With Me
.sWorkBook = sWorkBook
.vSheet = vSheet
.iRow = iRow
End With
BuildContext : Set Load = oDict
End Function
'Builds the Dictionary Object from Excel
Private Function BuildContext
Dim oConn, oRS, arrData, x
CONST adOpenStatic = 3
CONST adLockOptimistic = 3
CONST adCmdText = "&H0001"
Set oConn = CreateObject("ADODB.Connection")
Set oRS = CreateObject("ADODB.RecordSet")
'Open Connection
oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" &_
"Data Source=" & Me.sWorkBook & ";" & _
"Extended Properties=""Excel 8.0;HDR=Yes;"";"
'Query
sQuery = "Select * From [" & Me.vSheet & "$]"
'Run query against WorkBook
oRS.Open sQuery, oConn, 3, 3, 1
'Move RecordSet to the target Row
For x = 2 to iRow - 1 : oRS.MoveNext : Next
oDict = CreateObject("Scripting.Dictionary")
'Use a For..Loop to Build Scripting.Dictionary
For x = 0 to oRS.Fields.Count - 1
With oDict
.Add "" & oRS(x).Name, "" & oRS.Fields(x)
End With
Next
End Function
Private Property Let oDict(ByVal Val)
Set mDict = Val
End Property
Private Property Get oDict()
Set oDict = mDict
End Property
End Class
Set mDataContext = New clsTestData
使用的例子如下所示:
Dim sWorkbook, vSheet, iRow
sWorkBook = Environment.Value("TestDir") &"../../Test.xls" 'WorkBook
vSheet = "Sheet1" 'WorkSheet
iRow = 2 'Row Number
'This loads the Test Data to mDict
tStart = Timer
Set mDict = mDataContext.Load(sWorkBook, vSheet, iRow) 'Took 0.156 seconds to build
Print "Time taken to load Dictionary: " & Timer - tStart
'Launch IE Browser
SystemUtil.Run "iexplore.exe", "http://newtours.demoaut.com", "", "", 3
'Action Code:
With Browser("title:=Welcome: Mercury Tours", "index:=").Page("micclass:=Page")
.WebEdit("name:=userName").Set mDict("Username")
.WebEdit("name:=password").Set mDict("Password")
.Image("name:=login").Click
End With
With Browser("title:=Find a Flight.*", "index:=").Page("micclass:=Page")
If .Exist(15) Then
.WebRadioGroup("name:=tripType").Select mDict("Trip Type")
.WebList("name:=passCount").Select mDict("Passenger Count")
.WebList("name:=fromPort").Select mDict("From Port")
.WebList("name:=fromMonth").Select mDict("From Month")
.WebList("name:=fromDay").Select mDict("From Day")
End If
End With