QTP测试数据管理-Excel+Dictionary

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