【软件测试自动化-QTP NOTE 14】 == 如何创建高效的Test-Data Dictionary 2nd ==

为了高效地从Excel返回数据，有两种比较好的方法：1. 使用遍历  2.SQL 查询
这次以SQL查询为例，因为它表现更为高效。

用Excel,我们可以高效的使用DataClass类来建立我们的Dictionary并且和我们手工创建Dictionary一样不需要太多的资源。

Public DataBuilder

Class DataClass
 
'声明变量

    Public FileName     'Excel WorkBook
    Public vSheet       'Excel WorkSheet
    Public iRow         'Excel Row where test data is contained
    
    '------------------------------------------------------
    ' Name: Load函数
    '
    ' Purpose: TestData Dictionary创建
    '
    ' Input:
    '    FileName : Workbook路径
    '    vSheet   : Worksheet文件名以及路径
    '    iRow     : 数据返回的行数
    '
    ' Output:
    '     Object- Scripting.Dictionary
    '------------------------------------------------------
    Public Property Get Load(FileName, vSheet, iRow)
 
        If Not CreateObject("Scripting.FileSystemObject").FileExists(FileName) Then
            Reporter.ReportEvent micFail, "Load", "文件 '" & FileName & "' 不存在。"
            Exit Property
        End If
 
         With Me
            .FileName = FileName
            .vSheet = vSheet
            .iRow = iRow
        End With
 
        Set Load = BuildHashTable
    End Property
 
    '------------------------------------------------------
    ' Name: BuildHashTable函数
    '
    ' Purpose: 连接Excel后SQL查询，Test Data Dictionary 
    '
    ' Input:
    '    None
    '
    ' Output:
    '     None
    '------------------------------------------------------
    Private Property Get BuildHashTable  '注：不能被Class外函数调用
        Set BuildHashTable = Nothing
 
        Dim oConn, oRS, arrData, x, oDict, sQuery
        CONST adOpenStatic = 3      '静态游标，只是数据的一个快照，用户对记录说做的修改，增加或删除记录都不会反映到记录集中。支持向前或向后移动
        CONST adLockOptimistic = 3  '只有在调用Update方法时才锁定记录集，而在此前的其他操作仍可对当前记录进行更改、插入和删除等 
        CONST adCmdText = "&H0001"  'adCmdText是将CommandText作为命令或存储过程调用的文本化定义进行计算
 
        'On Error Resume Next
            Set oConn = CreateObject("ADODB.Connection")
            Set oRS = CreateObject("ADODB.RecordSet")
 
            'Open Connection
            oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" &_
                        "Data Source=" & Me.FileName & ";" & _
                        "Extended Properties=""Excel 8.0;HDR=Yes;"";"
 
            'SQL 查询
            sQuery = "Select * From [" & Me.vSheet & "$]"
 
            'Run query against WorkBook
            oRS.Open sQuery, oConn, 3, 3, 1
 
            If Err.Number > 0 Then
                Reporter.ReportEvent micFail, "BuildHashTable", Err.Description
                Err.Clear : Exit Property
            End If
 
            'Move RecordSet to the target Row
            For x = 2 to Me.iRow - 1 : oRS.MoveNext : Next
 
            Set 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
 
            'Error here generally occurs if there is an invalid character
            'in the Heading -> (oRS(x).Name)
            If Err.Number > 0 Then
                Reporter.ReportEvent micFail, "BuildHashTable", Err.Description
                Err.Clear : Exit Property
            End If
 
            Set BuildHashTable = oDict
        On Error Goto 0    
    End Property
 
End Class
 
'New instance
Set DataBuilder = New DataClass