这个代码的原理是把Excel的数据当做数据库里的数据一样处理，可以对Excel用select来检索需要的数据，然后把数据以键值对的形式保存到oDict里，方便在用例层来调用
Class oDataDic
Private oDic
Public oWorkBookPath
Public oSheetName
Public oRowNo
Private Sub Class_Initialize
'oWorkBookPath = getTestDataFromQC_QTP("业务系统测试数据")
oWorkBookPath = Environment.Value("ProductDir") & "\业务系统测试数据.xls" 'strTestDataPath
End Sub
Public Default Function Load(oSheetName, oRowNo)
With Me
.oWorkBookPath = oWorkBookPath
.oSheetName = oSheetName
.oRowNo = oRowNo
End With
BuildContext
Set Load = oDic
End Function
Public Function GetExcelRecordCount(oSheetName, CaseNO)
Dim oConn, oRS, arrData, x
Set oConn = CreateObject("ADODB.Connection")
Set oRS = CreateObject("ADODB.RecordSet")
oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & oWorkBookPath & ";" & _
"Extended Properties=""Excel 8.0;HDR=Yes;"";"
If IsNumeric(CaseNO) Then
sQuery = "select * from [" & oSheetName & "$]" & " where 测试用例编号 = " & CaseNO
else
sQuery = "select * from [" & oSheetName & "$]" & " where 测试用例名称 = " & "'" & CaseNO & "'"
End If
oRS.Open sQuery, oConn, , ,
' rows = oRS.Index
GetExcelRecordCount = oRS.RecordCount
' num = oRS.GetRows
' Dim x
' For x= 2 To oRS.RecordCount + 1
' If CStr(oRS.Fields(0)) = CaseNO Then
' msgbox x
' CStr(oRS.Fields(1))
' Exit For
' Else
' oRS.MoveNext
' End If
' Next
' Set oDic = CreateObject("Scripting.Dictionary")
'
' For x = 0 To oRS.Fields.Count - 1
' With oDic
' .Add "" & oRS(x).Name, "" & oRS.Fields(x)
' End With
' Next
'
' Set GetExcelRecordCount = oDic
End Function
Public Function LoadExcelRecord(oSheetName, CaseNO, oRowNo)
' On Error Resume Next
Dim oConn, oRS, arrData, x
Set oConn = CreateObject("ADODB.Connection")
Set oRS = CreateObject("ADODB.RecordSet")
oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & oWorkBookPath & ";" & _
"Extended Properties=""Excel 8.0;HDR=Yes;"";"
If IsNumeric(CaseNO) Then
sQuery = "select * from [" & oSheetName & "$]" & " where 测试用例编号 = " & CaseNO
else
sQuery = "select * from [" & oSheetName & "$]" & " where 测试用例名称 = " & "'" & CaseNO & "'"
End If
oRS.Open sQuery, oConn, , ,
' For x= 2 To oRS.RecordCount + 1
' If CStr(oRS.Fields(0)) = CaseNO Then
' msgbox x
' CStr(oRS.Fields(1))
' Exit For
' Else
' oRS.MoveNext
' End If
' Next
For x= To oRowNo - : oRS.MoveNext : Next
Set oDic = CreateObject("Scripting.Dictionary")
For x = To oRS.Fields.Count -
With oDic
.Add "" & oRS(x).Name, "" & oRS.Fields(x)
' .Add cstr(oRS(x).Name), cstr(oRS.Fields(x))
End With
Next
Set LoadExcelRecord = oDic
End Function
Private Function BuildContext
Dim oConn, oRS, arrData, x
Set oConn = CreateObject("ADODB.Connection")
Set oRS = CreateObject("ADODB.RecordSet")
oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & Me.oWorkBookPath & ";" & _
"Extended Properties=""Excel 8.0;HDR=Yes;"";"
sQuery = "select * from [" & Me.oSheetName & "$]"
oRS.Open sQuery, oConn, , ,
For x= To oRowNo - : oRS.MoveNext : Next
Set oDic = CreateObject("Scripting.Dictionary")
For x = To oRS.Fields.Count -
With oDic
.Add "" & oRS(x).Name, "" & oRS.Fields(x)
' .Add cstr(oRS(x).Name), cstr(oRS.Fields(x))
End With
Next
' Set oDic = Nothing
' Set oRS = Nothing
' Set oConn =Nothing
End Function
' Private Property Let oDic(ByVal val)
' Set oDic = val
' End Property
'
' Private Property Get oDic()
' Set oDic = oDic
' End Property
End Class
Set mDataContext = New oDataDic