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