再谈QTP数据驱动

QTP本身有就有很好的DataTable功能，所以我们可以利用以下方式来实现数据驱动：

利用脚本从excel导入Data Table

xls_Sheet_Index = 1
DataTable_Sheet_Index = 1
Set qtapp=CreateObject("quicktest.application") 
qtapp.Launch 
qtapp.Visible=True 
qtapp.Open "d:\iquicktest"
qtapp.Test.DataTable.ImportSheet "d:\1.xls",1,1
Set qtapp=Nothing

这里有一个问题就是QTP11及之前的版本不能支持Excel2007及发后的版本，如果我们用QTP11来做自动化，本身现在大家都是用的Excel2010，或者之后的版本，就会很麻烦。

利用ADO打造Excel数据库

我们会有两种情况的划分：

数据字典的实现

每个用例对应的数据彻底分离出去，输入的数据，甚至于要选择的导航（因为有些导航的文字可能会发生变化）等等。。。这种情形下，每个用例脚本只需要一行数据就可以了,我们可以利用数据字典来实现高效应用：

• 脚本：

Function GetRowDataFromExcel( myXlsFile, mySheet, iRow )

	Dim objExcel, objRS, oDict, j
	
	Const adOpenForwardOnly = 0
	Const adOpenKeyset      = 1
	Const adOpenDynamic     = 2
	Const adOpenStatic      = 3
	
	' Open the object for the Excel file
	Set objExcel = CreateObject( "ADODB.Connection" )
	' IMEX=1 includes cell content of any format; 
	' Connection string updated to open Excel 2007 (.xslx) files.
	objExcel.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & _
	myXlsFile & ";Extended Properties=""Excel 12.0;IMEX=1;HDR=YES;"";"
	
	Set objRS = CreateObject( "ADODB.Recordset" )
	objRS.Open "Select * from [" & mySheet & "$]", objExcel, adOpenStatic
	
	'Move RecordSet to the target Row
	For j = 1 To iRow -1 
		objRS.MoveNext
	Next
	
	'Use a For..Loop to Build Scripting.Dictionary
	Set oDict = CreateObject("Scripting.Dictionary")
	For j = 0 To objRS.Fields.Count - 1
		oDict.Add "" & objRS(j).Name, "" & objRS.Fields(j).Value	
	Next
	
	' Close the file and release the objects
	objRS.Close
	objExcel.Close
	Set objRS    = Nothing
	Set objExcel = Nothing
	
	' Return the results
	Set GetRowDataFromExcel = oDict
	Set oDict = Nothing
End Function

• 调用：

'获得从‘1.xlsx’文件中的‘Sheet1’的第3行数据
Set arrSheet = GetRowDataFromExcel( "c:\1.xlsx", "Sheet1",  3)
msgbox arrSheet("userName") & "_" & arrSheet("Password")

数组的实现

一个用例实现多组数据的应用，例如：登录功能，用户名和密码的各种组合问题。

• 脚本：

Function ReadExcel( myXlsFile, mySheet, my1stCell, myLastCell, blnHeader )

    Dim arrData( ), i, j
    Dim objExcel, objRS
    Dim strHeader, strRange

    Const adOpenForwardOnly = 0
    Const adOpenKeyset      = 1
    Const adOpenDynamic     = 2
    Const adOpenStatic      = 3

    ' Define header parameter string for Excel object
    If blnHeader Then
        strHeader = "HDR=YES;"
    Else
        strHeader = "HDR=NO;"
    End If

    ' Open the object for the Excel file
    Set objExcel = CreateObject( "ADODB.Connection" )
    ' IMEX=1 includes cell content of any format; tip by Thomas Willig.
    ' Connection string updated by Marcel Ni?nkemper to open Excel 2007 (.xslx) files.
    objExcel.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & _
                  myXlsFile & ";Extended Properties=""Excel 12.0;IMEX=1;" & _
                  strHeader & """"

    ' Open a recordset object for the sheet and range
    Set objRS = CreateObject( "ADODB.Recordset" )
    strRange = mySheet & "$" & my1stCell & ":" & myLastCell
    objRS.Open "Select * from [" & strRange & "]", objExcel, adOpenStatic

    ' Read the data from the Excel sheet
    i = 0
    Do Until objRS.EOF
        ' Stop reading when an empty row is encountered in the Excel sheet
        If IsNull( objRS.Fields(0).Value ) Or Trim( objRS.Fields(0).Value ) = "" Then Exit Do
        ' Add a new row to the output array
        ReDim Preserve arrData( objRS.Fields.Count - 1, i )
        ' Copy the Excel sheet's row values to the array "row"
        ' IsNull test credits: Adriaan Westra
        For j = 0 To objRS.Fields.Count - 1
            If IsNull( objRS.Fields(j).Value ) Then
                arrData( j, i ) = ""
            Else
                arrData( j, i ) = Trim( objRS.Fields(j).Value )
            End If
        Next
        ' Move to the next row
        objRS.MoveNext
        ' Increment the array "row" number
        i = i + 1
    Loop

    ' Close the file and release the objects
    objRS.Close
    objExcel.Close
    Set objRS    = Nothing
    Set objExcel = Nothing

    ' Return the results
    ReadExcel = arrData
End Function

• 调用：

'获得从‘1.xlsx’文件中的‘Sheet1’的‘A1：B6’数据
arrSheet = ReadExcel( "c:\1.xlsx", "Sheet1", "A1", "B6", True )
For intCount = 0 To UBound( arrSheet, 2 )
    msgbox arrSheet( 0, intCount ) & vbTab & arrSheet( 1, intCount )
Next