QTP数据驱动之读取Excel数据
2024-03-30 22:24:40
这个代码的原理是把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
最新文章
- tp框架之登录验证
- SMTP邮箱验证错误解决
- 一些practice和总结（转载）
- DataTemplate和ControlTemplate的关系
- Sublime Text3 激活教程
- Chrome中的消息循环
- 如何增强ArcGIS插值图出图效果
- babel入门基础
- 将html5项目部署到tomcat上，并实现安卓手机访问
- 可视化布局html5
- pip命令安装 pyinstaller失败解决办法
- 第一次有人把5G讲的这么简单明了
- 【转载】 C#读写注册表
- JavaScript（数组、Date、正则）
- 学以致用二十八-----win10安装mysql5.7.24及卸载
- RelativeLayout 布局参数
- 安装vm tools时出现如下问题 The path "/usr/bin/gcc" is not valid path to the
- Linux 文本编辑器 vim
- hdu 1541 (基本树状数组) Stars
- Social media users of the world unite!