Jmeter 用宏自动执行脚本的代码笔记

最新推荐文章于 2022-06-14 10:13:18 发布
〆WangBenYan゜
最新推荐文章于 2022-06-14 10:13:18 发布
阅读量330
点赞数
分类专栏： Jemter 接口测试
本文链接：https://blog.csdn.net/qq_18808965/article/details/78427402
版权
Jemter 接口测试专栏收录该内容
12 篇文章 1 订阅
订阅专栏
1、代码笔记。
Dim conn, strCnn, cmd, rs


'********************************************************************
'设计说明： 连接Excel数据库（excel文件可以当做数据库读取数据）

'调用举例： ConnectExcel    "***.xls"
'********************************************************************
Function ConnectExcel(ExcelPath)

    Set conn = CreateObject("ADODB.Connection")
    strCnn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " & ExcelPath & ";Extended Properties=excel 8.0;Persist Security Info=False"
    conn.Open strCnn
    Set cmd = CreateObject("ADODB.Command")
    cmd.ActiveConnection = conn
    Set rs = CreateObject("ADODB.Recordset")
    ConnectExcel = conn.State
    
End Function
Function ConnectDBC_PC(SysDBPwds, SysDBUserNames, SysDBDataSources)

    'strCnn = "Provider=MSDASQL.1;Persist Security Info=False;Password=" & SysDBPwd & ";User ID=" & SysDBUserName & ";Data Source=" & SysDBDataSource
    'strConns = "Provider=MSDAORA.1;Password=OMPUATDB;User ID=OMPBASE;Persist Security Info=True;Data Source=" & "(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = 10.202.34.215)(PORT = 1521)))(CONNECT_DATA =(SERVICE_NAME = ompuat)))"
    strCnn = "Provider=MSDASQL.1;Persist Security Info=False;Password=" & (SysDBPwds) & ";User ID=" & SysDBUserNames & ";Data Source=" & SysDBDataSources
    MsgBox strConns
    'If conn.State = 0 Then
            Set conn = CreateObject("ADODB.Connection")
            conn.Open strConns
            Set cmd = CreateObject("ADODB.Command")
            cmd.ActiveConnection = conn
            Set rs = CreateObject("ADODB.Recordset")
    'End If

    ConnectDBC_PC = conn.State

End Function
Function ConnectDBC_P()

    'strCnn = "Provider=MSDASQL.1;Persist Security Info=False;Password=" & SysDBPwd & ";User ID=" & SysDBUserName & ";Data Source=" & SysDBDataSource
    'strConns = "Provider=MSDAORA.1;Password=OMPUATDB;User ID=OMPBASE;Persist Security Info=True;Data Source=" & "(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = 10.202.34.215)(PORT = 1521)))(CONNECT_DATA =(SERVICE_NAME = ompuat)))"
    'strCnns = "Provider=MSDASQL.1;Persist Security Info=False;Password=RPSCORE_SIT;User ID=rps;Data Source=10.202.44.149:3306"
     strConns = "DRIVER={MySql ODBC 5.3 Unicode Driver};SERVER=10.118.65.62;Database=test;Uid=rps;Pwd=rps;Stmt=set names GBK"
     'strConns = "DRIVER={MySql ODBC 5.1  Driver};SERVER=127.0.0.1;Database=test;Uid=rps;Pwd=rps;Stmt=set names GBK"
     
     
    'If conn.State = 0 Then
            Set conn = CreateObject("ADODB.Connection")

            conn.Open strConns

            Set cmd = CreateObject("ADODB.Command")

            cmd.ActiveConnection = conn
            Set rs = CreateObject("ADODB.Recordset")
    'End If

    ConnectDBC_P = conn.State

End Function


'******************************************************************************
'设计说明：断开业务数据库

'调用举例：CloseDB_P
'******************************************************************************
Function CloseDB_P()

    
    If rs.State > 0 Then
       rs.Close
    End If
    Set rs = Nothing
    Set cmd = Nothing
    If conn.State > 0 Then
       conn.Close
    End If
    

    Set conn = Nothing
        
End Function
Function SearchTestDataRows(testcasecode)
   Dim rownum, RowCount
   RowCount = Worksheets("TestData").UsedRange.Cells.Rows.Count
   
   rownum = -1
   For i = 4 To RowCount
     ' MsgBox "cell value = " & Worksheets("TestData").Cells(i, 4).Value & " , exp value = " & testcasecode
      If CStr(Worksheets("TestData").Cells(i, 4).Value) = CStr(testcasecode) Then
         rownum = i
         Exit For
      End If
      
   Next
   SearchTestDataRows = rownum
End Function
'================================================================
' 导入测试用例及测试数据
'=================================================================

 Sub ImportDataAll()

    Dim P_Begin_ID, P_End_ID
    Dim Vid
    P_Begin_ID = Worksheets("TestCaseManager").Cells(1, 3).Value
    P_End_ID = Worksheets("TestCaseManager").Cells(2, 3).Value
    'MsgBox (P_Begin_ID & "--" & P_End_ID)
    If P_Begin_ID = "" Or P_End_ID = "" Then
        MsgBox ("请在C1和C2输入框分别输入加载起始值和结束值")
        Exit Sub
    End If
    
    If IsNumeric(P_Begin_ID) = False Or IsNumeric(P_End_ID) = False Then
        MsgBox ("请在C1和C2输入框输入数字")
        Exit Sub
    End If
    
    If P_Begin_ID > P_End_ID Then
        MsgBox ("起始值必须小于等于结束值")
        Exit Sub
    End If
    If P_Begin_ID < 4 Then
        MsgBox ("起始值必须大于等于4")
    End If
    Worksheets("TestCaseManager").Cells(1, 1).Value = "'" & Worksheets("TestCaseManager").Cells(P_Begin_ID, 1).Value
    Worksheets("TestCaseManager").Cells(2, 1).Value = "'" & Worksheets("TestCaseManager").Cells(P_End_ID, 1).Value
'*********       连接数据库     ************************************************************************

    ConnectDBC_P
    
    '查看是否连接成功，成功状态值为1
    If conn.State = 0 Then
       MsgBox "连接OMP数据库失败，请检查网络环境"
    Else
       'MsgBox "连接数据库成功"
    End If
     '   rs.Open "select id from testdata_cmp_panel   where id=20161000006", conn
     '   Do While Not rs.EOF
     '       MsgBox "search result ,id = " & rs("id")
     '   Loop
'*********************************************************************************

    Dim i, j, StrSql_Insert, StrSql_Insert_Log
    Dim row_begin, row_end
    Dim childrow_begin, childrow_end, childrow_index
    Dim childRowStr, childRowArr, childRowCount
    Dim ir, colName, colValue, colStr, colIndex
    Dim V_DataConcatChar, first_char, last_char
    row_begin = P_Begin_ID
    row_end = P_End_ID
    
    If row_begin > 0 Then
      ' row_end = (P_End_ID) - (P_Begin_ID) + row_begin
       MsgBox "row_begin = " & row_begin & ",row_end = " & row_end
        
      For i = row_begin To row_end
        'MsgBox (i)
        P_Number = Worksheets("TestCaseManager").Cells(i, 1).Value
        If IsNumeric(P_Number) = False Or IsNull(P_Number) Or P_Number = "" Then
         GoTo Line1
        Else
        GoTo Line2
        End If
    
Line2:
'结束结束    14  versionid   FunctionModule  TestCaseCode    TestCaseDesc    WorkDirectorys  JmxFileName TestDataType    Testdata    expResult   actResult   testResult  testResultDesc
'是否运行    系统编码    版本    功能模块    用例编码    用例描述    工作目录    jmx文件名（不带后缀）   数据类型  测试数据    预期结果    实际结果    测试结果    测试结果描述
'Y   IBS V3.11   清单上传    TestCase001 正常运单    E:\\AUTOTEST\\jemeter\\IBU\\JmeterAuto\\jmx\\   IBS-OrderService-auto   IBS-OrderService-auto       OK



        '获取主表的数据
        v_id = Worksheets("TestCaseManager").Cells(i, 1).Value  '2 ID
        'MsgBox "V_id = " & V_id
        V_isrun = Worksheets("TestCaseManager").Cells(i, 2).Value  '是否运行
        If V_isrun = "" Or IsNull(V_isrun) Then
            V_isrun = "N"
        End If
        V_syscode = Worksheets("TestCaseManager").Cells(i, 3).Value '系统编码
        V_versionid = Worksheets("TestCaseManager").Cells(i, 4).Value '版本
        V_FunctionModule = Worksheets("TestCaseManager").Cells(i, 5).Value '功能模块
        V_TestCaseCode = Worksheets("TestCaseManager").Cells(i, 6).Value '用例编码
        V_TestCaseDesc = Worksheets("TestCaseManager").Cells(i, 7).Value '用例名称
        V_WorkDirectorys = Worksheets("TestCaseManager").Cells(i, 8).Value '工作目录
        V_JmxFileName = Worksheets("TestCaseManager").Cells(i, 9).Value 'jmx文件名
        V_TestDataType = Worksheets("TestCaseManager").Cells(i, 10).Value '测试数据类型
        V_Testdata = Worksheets("TestCaseManager").Cells(i, 11).Value '测试数据
        V_expResult = Worksheets("TestCaseManager").Cells(i, 12).Value '预期结果
        V_actResult = Worksheets("TestCaseManager").Cells(i, 13).Value ' 实际结果
        V_testResult = Worksheets("TestCaseManager").Cells(i, 14).Value '测试结果
        V_testResultDesc = Worksheets("TestCaseManager").Cells(i, 15).Value '测试结果描述
       
        StrSql_delete = "delete from `jmetertestcasemanager`   where TestCaseCode = '" & V_TestCaseCode & "'"
        'MsgBox StrSql_delete
        conn.Execute StrSql_delete
        
        StrSql_delete = "delete from `jmetertestdatamanager`   where TestCaseCode = '" & V_TestCaseCode & "'"
        'MsgBox StrSql_delete
        conn.Execute StrSql_delete
        'MsgBox "删除主表成功"

        
       StrSql_Insert = "insert into `jmetertestcasemanager`(" _
                        & " `id`  ,`isrun` ,`syscode`,`versionid`,`FunctionModule`,`TestCaseCode`,`TestCaseDesc`,`WorkDirectorys`,`JmxFileName`,`TestDataType`," _
                        & "`Testdata`,`expResult` ,`actResult`,`testResult`,`testResultDesc`" _
                         & " )" _
                        & "values(" & v_id & ",'" & V_isrun & "','" & V_syscode & "','" & V_versionid & "','" & V_FunctionModule & "','" & V_TestCaseCode & "','" & V_TestCaseDesc & "','" & V_WorkDirectorys & "','" & V_JmxFileName & "', " _
                        & "'" & V_TestDataType & "','" & V_Testdata & "','" & V_expResult & "','" & V_actResult & "','" & V_testResult & "','" & V_testResultDesc & "')"
    
       'MsgBox StrSql_Insert
       ' Worksheets("加载巴枪数据-模型").Cells(100, 8).Value = StrSql_Insert
        conn.Execute StrSql_Insert
        'MsgBox "插主表成功"
        
        childRowStr = SearchTestDataRows(V_TestCaseCode)
       
        'MsgBox "childRowStr = " & childRowStr
        If Not IsNull(childRowStr) Or (childRowStr <> "") Then
                childrow_index = CInt(childRowStr)
               
                'MsgBox "childrow_index = " & childrow_index
               '获取子表的数据
                j = childrow_index
                VD_id = Worksheets("TestData").Cells(j, 1).Value
                VD_TestCaseCode = Worksheets("TestData").Cells(j, 4).Value '用例编码
                VD_versionid = Worksheets("TestData").Cells(j, 5).Value ' 版本
                 'MsgBox "VD_versionid = " & VD_versionid
                For ir = 8 To 255
                    colStr = Worksheets("TestData").Cells(j, ir).Value
                    If Not IsNull(colStr) And (colStr <> "") And Len(colStr) > 0 And (Not IsEmpty(colStr)) Then
                        ' MsgBox "colStr = " & colStr
                        'colStr = CStr(colStr)
                        colIndex = InStr(colStr, "=")
                        'MsgBox "colIndex = " & colIndex
                        If colIndex > 0 Then
                            colName = Left(colStr, colIndex - 1)
                            colValue = Mid(colStr, colIndex + 1)
                            
                            first_char = Left(colName, 1)
                            last_char = Right(colName, 1)
                            'MsgBox "first_char = " & first_char & " , last_char = " & last_char
                            If V_TestDataType = "XML" Or V_TestDataType = "xml" Then
                                If first_char = "<" And last_char = ">" Then
                                    V_DataConcatChar = "INS"
                                    'MsgBox "colName 1 = " & colName
                                    colName = Mid(colName, 2, Len(colName) - 2)
                                    'MsgBox "colName 2 = " & colName
                                    
                                Else
                                    V_DataConcatChar = "="
                                End If
                            ElseIf V_TestDataType = "JSON" Or V_TestDataType = "json" Then
                                V_DataConcatChar = ":"
                            End If
                            ' MsgBox "V_DataConcatChar = " & V_DataConcatChar
                            
                           
                            '新增数据
                            StrSql_Insert = "insert into `jmetertestdatamanager`(" _
                                              & "`versionid` ,`TestCaseCode`,`DataType`, `DataConcatChar`,`ColName`,`colValue`" _
                                               & " ) values( '" & VD_versionid & "','" & VD_TestCaseCode & "','" & V_TestDataType & "','" & V_DataConcatChar & "','" & colName & "','" & colValue & "')"
                                'MsgBox "StrSql_Insert = " & StrSql_Insert
                            conn.Execute StrSql_Insert
                        End If
                    Else
                        Exit For
                    End If
    
    
                Next
        End If

        
        
        newHour = Hour(Now())
        newMinute = Minute(Now())
        newSecond = Second(Now()) + 1
        waitTime = TimeSerial(newHour, newMinute, newSecond)
        Application.Wait waitTime

        
Line1:
        Next
    
    End If
    
    
    'MsgBox (i & "--" & row_end)
    
    If i = row_end + 1 Then
        MsgBox ("jemeter测试用例及测试数据导入数据库成功！")
    End If
    
    
    
    'rs.Close
    'Set rs = Nothing
    'conn.Close
    'Set conn = Nothing
    CloseDB_P

End Sub
'================================================================
' 导入测试数据
'=================================================================
 Sub ImportTestData_Click()

    Dim P_Begin_ID, P_End_ID
    Dim Vid
    P_Begin_ID = Worksheets("TestData").Cells(1, 3).Value
    P_End_ID = Worksheets("TestData").Cells(2, 3).Value
    'MsgBox (P_Begin_ID & "--" & P_End_ID)
    If P_Begin_ID = "" Or P_End_ID = "" Then
        MsgBox ("请在C1和C2输入框分别输入加载起始值和结束值")
        Exit Sub
    End If
    
    If IsNumeric(P_Begin_ID) = False Or IsNumeric(P_End_ID) = False Then
        MsgBox ("请在C1和C2输入框输入数字")
        Exit Sub
    End If
    
    If P_Begin_ID > P_End_ID Then
        MsgBox ("起始值必须小于等于结束值")
        Exit Sub
    End If
    If P_Begin_ID < 4 Then
        MsgBox ("起始值必须大于等于4")
    End If
    Worksheets("TestData").Cells(1, 1).Value = "'" & Worksheets("TestData").Cells(P_Begin_ID, 1).Value
    Worksheets("TestData").Cells(2, 1).Value = "'" & Worksheets("TestData").Cells(P_End_ID, 1).Value
    
    
    
'*********       连接数据库     ************************************************************************

    ConnectDBC_P
    
    '查看是否连接成功，成功状态值为1
    If conn.State = 0 Then
       MsgBox "连接数据库失败，请检查网络环境"
    Else
       'MsgBox "连接数据库成功"
    End If
     '   rs.Open "select id from testdata_cmp_panel   where id=20161000006", conn
     '   Do While Not rs.EOF
     '       MsgBox "search result ,id = " & rs("id")
     '   Loop
'*********************************************************************************

    Dim i, j, StrSql_Insert, StrSql_Insert_Log
    Dim row_begin, row_end
    Dim childrow_begin, childrow_end
    Dim childRowStr, childRowArr, childRowCount
    Dim ir
    Dim V_DataConcatChar, first_char, last_char
    
    row_begin = P_Begin_ID
    row_end = P_End_ID
    If row_begin > 2 Then
       'MsgBox "row_begin = " & row_begin & ",row_end = " & row_end
        
      For j = row_begin To row_end
        'MsgBox (i)
        P_Number = Worksheets("TestData").Cells(j, 1).Value
        If IsNumeric(P_Number) = False Or IsNull(P_Number) Or P_Number = "" Then
            GoTo Line1
        Else
           GoTo Line2
        End If
    
Line2:
        

        VD_id = Worksheets("TestData").Cells(j, 1).Value
        VD_TestCaseCode = Worksheets("TestData").Cells(j, 4).Value '用例编码
        VD_versionid = Worksheets("TestData").Cells(j, 5).Value ' 版本
        V_TestDataType = Worksheets("TestData").Cells(j, 8).Value ' 版本
        
        StrSql_delete = "delete from `jmetertestdatamanager`   where TestCaseCode = '" & VD_TestCaseCode & "'"
        'MsgBox StrSql_delete
        conn.Execute StrSql_delete
        For ir = 9 To 255

            colStr = Worksheets("TestData").Cells(j, ir).Value
            If Not IsNull(colStr) And (colStr <> "") And Len(colStr) > 0 And (Not IsEmpty(colStr)) Then
                   'colIndex = InStr(colStr, "=", 1)
                   colIndex = InStr(colStr, "=")
                   If colIndex > 0 Then
                       colName = Left(colStr, colIndex - 1)
                       colValue = Mid(colStr, colIndex + 1)
                       
                       first_char = Left(colName, 1)
                       last_char = Right(colName, 1)
                      ' MsgBox "first_char = " & first_char & ",last_char = " & last_char & " , colName = " & colName
                       If V_TestDataType = "XML" Or V_TestDataType = "xml" Then
                           If first_char = "<" And last_char = ">" Then
                               V_DataConcatChar = "INS"
                              colName = Mid(colName, 2, Len(colName) - 2)
                           Else
                               V_DataConcatChar = "="
                           End If
                       ElseIf V_TestDataType = "JSON" Or V_TestDataType = "json" Then
                           V_DataConcatChar = ":"
                       End If

                      
                       '新增数据
                       StrSql_Insert = "insert into `jmetertestdatamanager`(" _
                                         & " `versionid` ,`TestCaseCode`,`DataType`, `DataConcatChar`,`ColName`,`colValue`" _
                                          & " ) values('" & VD_versionid & "','" & VD_TestCaseCode & "','" & V_TestDataType & "','" & V_DataConcatChar & "','" & colName & "','" & colValue & "')"
                        'MsgBox "StrSql_Insert = " & StrSql_Insert
                       conn.Execute StrSql_Insert
                   End If
            Else
                Exit For
            End If
        Next
        newHour = Hour(Now())
        newMinute = Minute(Now())
        newSecond = Second(Now()) + 1
        waitTime = TimeSerial(newHour, newMinute, newSecond)
        Application.Wait waitTime
        
Line1:
    Next

End If
'MsgBox (i & "--" & row_end)

If j = row_end + 1 Then
    MsgBox ("jemeter测试数据导入数据库成功！")
End If

'rs.Close
'Set rs = Nothing
'conn.Close
'Set conn = Nothing
CloseDB_P
'Application.ScreenUpdating = True
'Application.EnableEvents = True

End Sub

'================================================================
' 执行测试用例-导入数据库及数据库状态变更
'=================================================================

 Sub ImportRunDataAll()

    Dim P_Begin_ID, P_End_ID, StrSql_update
    Dim Vid, nextjmxname, nextworkdirctorys
    Dim runcount
    P_Begin_ID = Worksheets("TestCaseManager").Cells(1, 3).Value
    P_End_ID = Worksheets("TestCaseManager").Cells(2, 3).Value
    'MsgBox (P_Begin_ID & "--" & P_End_ID)
    If P_Begin_ID = "" Or P_End_ID = "" Then
        MsgBox ("请在C1和C2输入框分别输入加载起始值和结束值")
        Exit Sub
    End If
    
    If IsNumeric(P_Begin_ID) = False Or IsNumeric(P_End_ID) = False Then
        MsgBox ("请在C1和C2输入框输入数字")
        Exit Sub
    End If
    
    If P_Begin_ID > P_End_ID Then
        MsgBox ("起始值必须小于等于结束值")
        Exit Sub
    End If
    If P_Begin_ID < 4 Then
        MsgBox ("起始值必须大于等于4")
    End If
    Worksheets("TestCaseManager").Cells(1, 1).Value = "'" & Worksheets("TestCaseManager").Cells(P_Begin_ID, 1).Value
    Worksheets("TestCaseManager").Cells(2, 1).Value = "'" & Worksheets("TestCaseManager").Cells(P_End_ID, 1).Value
    '----------------------------------------------------------------------
    '更新测试统计表中的运行开始时间及运行日期
    Worksheets("Summary").Cells(2, 2).Value = Date

    SummaryTestDate = Date
    Worksheets("Summary").Cells(3, 2).Value = Now
    ScriptStartTime = Now
    '----------------------------------------------------------------------
    
'*********       连接数据库     ************************************************************************

    ConnectDBC_P
    
    '查看是否连接成功，成功状态值为1
    If conn.State = 0 Then
       MsgBox "连接OMP数据库失败，请检查网络环境"
    Else
       'MsgBox "连接数据库成功"
    End If
     '   rs.Open "select id from testdata_cmp_panel   where id=20161000006", conn
     '   Do While Not rs.EOF
     '       MsgBox "search result ,id = " & rs("id")
     '   Loop
'*********************************************************************************

    Dim i, j, k, StrSql_Insert, StrSql_Insert_Log
    Dim row_begin, row_end
    Dim childrow_begin, childrow_end, childrow_index
    Dim childRowStr, childRowArr, childRowCount
    Dim ir, colName, colValue, colStr, colIndex
    Dim V_DataConcatChar, first_char, last_char
    
    row_begin = P_Begin_ID
    row_end = P_End_ID
    '----------------------------------------------------------------
    ' 将测试用例及测试数据表中所有行的执行状态更新为不执行 N
    '----------------------------------------------------------------
    StrSql_update = "update  `jmetertestcasemanager`   set isrun='N'"
    'MsgBox StrSql_update
    conn.Execute StrSql_update
    StrSql_update = "update  `jmetertestdatamanager`   set isrun='N'"
    'MsgBox StrSql_delete
    conn.Execute StrSql_update
    '----------------------------------------------------------------
    '删除runningTestCase中的数据
    deleteAllData
    '----------------------------------------------------------------
     
    
    If row_begin > 0 Then
      ' row_end = (P_End_ID) - (P_Begin_ID) + row_begin
       'MsgBox "row_begin = " & row_begin & ",row_end = " & row_end
      runcount = 0
      For i = row_begin To row_end
        'MsgBox (i)
        P_Number = Worksheets("TestCaseManager").Cells(i, 1).Value
        If IsNumeric(P_Number) = False Or IsNull(P_Number) Or P_Number = "" Then
         GoTo Line1
        Else
        GoTo Line2
        End If
    
Line2:
        '初始化待执行的测试用例数据，根据jmxname及工作目录分开统计
        
        '----------------------------------------------------------------
        '获取主表的数据
        '----------------------------------------------------------------
        v_id = Worksheets("TestCaseManager").Cells(i, 1).Value  '2 ID
        'MsgBox "V_id = " & V_id
        V_isrun = Worksheets("TestCaseManager").Cells(i, 2).Value  '是否运行
        If V_isrun = "" Or IsNull(V_isrun) Then
            V_isrun = "N"
        End If
        If V_isrun = "Y" Or V_isrun = "y" Then
            runcount = runcount + 1
        '----------------------------------------------------------------
        '复制测试结果到 runningTestCase
            k = Sheets("runningTestCase").Cells(Rows.Count, 1).End(xlUp).Row + 1
            Sheets("TestCaseManager").Rows(i).Copy Sheets("runningTestCase").Range("a" & k)
        '----------------------------------------------------------------
        End If
        V_syscode = Worksheets("TestCaseManager").Cells(i, 3).Value '系统编码
        V_versionid = Worksheets("TestCaseManager").Cells(i, 4).Value '版本
        V_FunctionModule = Worksheets("TestCaseManager").Cells(i, 5).Value '功能模块
        V_TestCaseCode = Worksheets("TestCaseManager").Cells(i, 6).Value '用例编码
        V_TestCaseDesc = Worksheets("TestCaseManager").Cells(i, 7).Value '用例名称
        V_WorkDirectorys = Worksheets("TestCaseManager").Cells(i, 8).Value '工作目录
        V_JmxFileName = Worksheets("TestCaseManager").Cells(i, 9).Value 'jmx文件名
        V_TestDataType = Worksheets("TestCaseManager").Cells(i, 10).Value '测试数据文件名
        V_Testdata = Worksheets("TestCaseManager").Cells(i, 11).Value '测试数据
        V_expResult = Worksheets("TestCaseManager").Cells(i, 12).Value '预期结果
        V_actResult = Worksheets("TestCaseManager").Cells(i, 13).Value ' 实际结果
        V_testResult = Worksheets("TestCaseManager").Cells(i, 14).Value '测试结果
        V_testResultDesc = Worksheets("TestCaseManager").Cells(i, 15).Value '测试结果描述
        
        V_JmxFileName_next = Worksheets("TestCaseManager").Cells(i + 1, 9).Value '下一行jmx文件名
        V_WorkDirectorys_next = Worksheets("TestCaseManager").Cells(i + 1, 8).Value '下行工作目录
       
        StrSql_delete = "delete from `jmetertestcasemanager`   where TestCaseCode = '" & V_TestCaseCode & "'"
        'MsgBox StrSql_delete
        conn.Execute StrSql_delete
        
        StrSql_delete = "delete from `jmetertestdatamanager`   where TestCaseCode = '" & V_TestCaseCode & "'"
        'MsgBox StrSql_delete
        conn.Execute StrSql_delete
        'MsgBox "删除主表成功"

        
       StrSql_Insert = "insert into `jmetertestcasemanager`(" _
                        & " `id`  ,`isrun`,`syscode`,`versionid`,`FunctionModule`,`TestCaseCode`,`TestCaseDesc`,`WorkDirectorys`,`JmxFileName`,`TestDataType`," _
                        & "`Testdata`,`expResult` ,`actResult`,`testResult`,`testResultDesc`" _
                         & " )" _
                        & "values(" & v_id & ",'" & V_isrun & "','" & V_syscode & "','" & V_versionid & "','" & V_FunctionModule & "','" & V_TestCaseCode & "','" & V_TestCaseDesc & "','" & V_WorkDirectorys & "','" & V_JmxFileName & "', " _
                        & "'" & V_TestDataType & "','" & V_Testdata & "','" & V_expResult & "','" & V_actResult & "','" & V_testResult & "','" & V_testResultDesc & "')"
    
       'MsgBox StrSql_Insert
       ' Worksheets("加载巴枪数据-模型").Cells(100, 8).Value = StrSql_Insert
        conn.Execute StrSql_Insert
        'MsgBox "插主表成功"
        
        childRowStr = SearchTestDataRows(V_TestCaseCode)
        'MsgBox "childRowStr = " & childRowStr
        If Not IsNull(childRowStr) Or (childRowStr <> "") Then
                childrow_index = CInt(childRowStr)
               
               ' MsgBox "childrow_index = " & childrow_index
               '获取子表的数据
                 j = childrow_index
                VD_id = Worksheets("TestData").Cells(j, 1).Value
                VD_TestCaseCode = Worksheets("TestData").Cells(j, 4).Value '用例编码
                VD_versionid = Worksheets("TestData").Cells(j, 5).Value ' 版本
                VD_isrun = V_isrun
                For ir = 8 To 255

                    colStr = Worksheets("TestData").Cells(j, ir).Value
                   
                    If Not IsNull(colStr) And (colStr <> "") And Len(colStr) > 0 And (Not IsEmpty(colStr)) Then
     
                           colIndex = InStr(colStr, "=")
                           'MsgBox "colIndex = " & colIndex
                        
                           If colIndex > 0 Then
                               colName = Left(colStr, colIndex - 1)
                               colValue = Mid(colStr, colIndex + 1)
                               first_char = Left(colName, 1)
                               last_char = Right(colName, 1)
                               
                               If V_TestDataType = "XML" Or V_TestDataType = "xml" Then
                                   If first_char = "<" And last_char = ">" Then
                                       V_DataConcatChar = "INS"
                                       colName = Mid(colName, 2, Len(colName) - 2)
                                   Else
                                       V_DataConcatChar = "="
                                   End If
                               ElseIf V_TestDataType = "JSON" Or V_TestDataType = "json" Then
                                   V_DataConcatChar = ":"
                               End If
                
                               
                               'MsgBox "colName = " & colName & " , colValue = " & colValue
                               '新增数据
                               StrSql_Insert = "insert into `jmetertestdatamanager`(" _
                                                 & " `versionid` ,`TestCaseCode`,`DataType`, `DataConcatChar`,`ColName`,`colValue`,`isrun`" _
                                                  & " ) values('" & VD_versionid & "','" & VD_TestCaseCode & "','" & V_TestDataType & "','" & V_DataConcatChar & "','" & colName & "','" & colValue & "','" & VD_isrun & "')"
                                  ' MsgBox "StrSql_Insert = " & StrSql_Insert
                               conn.Execute StrSql_Insert
                           End If
                    Else
                        Exit For
                    End If
    
    
                Next
        End If
       ' 执行测试用例
        'MsgBox "cur jmxname = " & (V_WorkDirectorys & V_JmxFileName) & " next jmx = " & (V_WorkDirectorys_next & V_JmxFileName_next)
        'If (V_WorkDirectorys & V_JmxFileName) <> (V_WorkDirectorys_next & V_JmxFileName_next) Then
        If StrComp((V_WorkDirectorys & V_JmxFileName), (V_WorkDirectorys_next & V_JmxFileName_next), vbBinaryCompare) <> 0 Then
            'MsgBox "runcount = " & runcount
            executeJmx V_WorkDirectorys, V_JmxFileName, runcount, V_syscode
            runcount = 0
        End If
        
        
        newHour = Hour(Now())
        newMinute = Minute(Now())
        newSecond = Second(Now()) + 1
        waitTime = TimeSerial(newHour, newMinute, newSecond)
        Application.Wait waitTime

            
Line1:
        Next
    
    End If
    '-----------------------------------------------------------
    ' 更新测试统计中的结束时间及运行时长
    Worksheets("Summary").Cells(4, 2).Value = Now
    SummaryTestEndTime = Now
    Worksheets("Summary").Cells(5, 2).Value = DateDiff("s", ScriptStartTime, Now) & "秒" 'Calu duration
    
    'MsgBox (i & "--" & row_end)
    
    If i = row_end + 1 Then
        MsgBox ("jemeter执行的测试用例完成！")
    End If



'rs.Close
'Set rs = Nothing
'conn.Close
'Set conn = Nothing
CloseDB_P
'Application.ScreenUpdating = True
'Application.EnableEvents = True

End Sub


Sub executeJmx(jmxpath, jmxname, groupnum, syscode)
    Dim jmxfullname, ps, cmdstr
    jmxfullname = jmxpath & "jmx\\" & jmxname & ".jmx"

    cmdstr = jmxpath & "jmeterauto.bat " & groupnum & " " & syscode & " " & jmxfullname & " " & jmxpath
    'MsgBox cmdstr
    Call Shell("cmd.exe /k " & cmdstr & "  /e", vbNormalFocus)
    'Set DosExec = objshell.Exec("cmd.exe /k " & cmdstr)
   ' Set DosExec = objshell.Exec("cmd.exe /c" & " E:\AUTOTEST\\jemeter\\IBU\\JmeterAuto\\jmeterauto.bat 2 IBS E:\\AUTOTEST\\jemeter\\IBU\JmeterAuto\\jmx\\IBS-OrderService-auto.jmx E:\\AUTOTEST\\jemeter\\IBU\JmeterAuto\\  /e", vbNormalFocus)
   'Call Shell("cmd.exe /k " & " E:\AUTOTEST\\jemeter\\IBU\\JmeterAuto\\jmeterauto.bat 2 IBS E:\\AUTOTEST\\jemeter\\IBU\JmeterAuto\\jmx\\IBS-OrderService-auto.jmx E:\\AUTOTEST\\jemeter\\IBU\JmeterAuto\\  /e", vbNormalFocus)
   
   'Call Shell("cmd.exe /c xcopy c:\tmp.d c:\temp /e", vbNormalFocus)
   


End Sub
Sub activew()
     MyAppID = Shell("C:\Program Files\Microsoft Office\OFFICE11\WINWORD.EXE", 1)
    AppActivate MyAppID
End Sub

Sub getTestResult()


    Dim P_Begin_ID, P_End_ID, StrSql_update
    Dim Vid, nextjmxname, nextworkdirctorys
    Dim runcount
    P_Begin_ID = Worksheets("TestCaseManager").Cells(1, 3).Value
    P_End_ID = Worksheets("TestCaseManager").Cells(2, 3).Value
    'MsgBox (P_Begin_ID & "--" & P_End_ID)
    If P_Begin_ID = "" Or P_End_ID = "" Then
        MsgBox ("请在C1和C2输入框分别输入加载起始值和结束值")
        Exit Sub
    End If
    
    If IsNumeric(P_Begin_ID) = False Or IsNumeric(P_End_ID) = False Then
        MsgBox ("请在C1和C2输入框输入数字")
        Exit Sub
    End If
    
    If P_Begin_ID > P_End_ID Then
        MsgBox ("起始值必须小于等于结束值")
        Exit Sub
    End If
    If P_Begin_ID < 4 Then
        MsgBox ("起始值必须大于等于4")
    End If
    Worksheets("TestCaseManager").Cells(1, 1).Value = "'" & Worksheets("TestCaseManager").Cells(P_Begin_ID, 1).Value
    Worksheets("TestCaseManager").Cells(2, 1).Value = "'" & Worksheets("TestCaseManager").Cells(P_End_ID, 1).Value
'*********       连接数据库     ************************************************************************

    ConnectDBC_P
    
    '查看是否连接成功，成功状态值为1
    If conn.State = 0 Then
       MsgBox "连接OMP数据库失败，请检查网络环境"
    Else
       'MsgBox "连接数据库成功"
    End If
     '   rs.Open "select id from testdata_cmp_panel   where id=20161000006", conn
     '   Do While Not rs.EOF
     '       MsgBox "search result ,id = " & rs("id")
     '   Loop
'*********************************************************************************

    Dim i, j, StrSql_Insert, StrSql_Insert_Log
    Dim row_begin, row_end
    Dim childrow_begin, childrow_end, childrow_index
    Dim childRowStr, childRowArr, childRowCount
    Dim ir, colName, colValue, colStr, colIndex
    Dim tempstr
    row_begin = P_Begin_ID
    row_end = P_End_ID
    Dim passcount, failcount, totalcount
    passcount = 0
    failcount = 0
    totalcount = 0
    '----------------------------------------------------------------
    '删除runningTestCase中的数据
    deleteAllData
    '----------------------------------------------------------------
    
   RowCount = Worksheets("TestCaseManager").UsedRange.Cells.Rows.Count
   
   rownum = -1
   For i = 4 To RowCount
    tempstr = Worksheets("TestCaseManager").Cells(i, 1).Value
    
    If IsNumeric(tempstr) = False Or IsNull(tempstr) Or tempstr = "" Then
       Exit For
    Else
        totalcount = totalcount + 1
        V_isrun = Worksheets("TestCaseManager").Cells(i, 2).Value  '是否运行
        
        V_TestCaseCode = Worksheets("TestCaseManager").Cells(i, 6).Value '用例编码
        If V_isrun = "" Or IsNull(V_isrun) Or V_isrun = "N" Or V_isrun = "n" Then
            Worksheets("TestCaseManager").Cells(i, 11).Value = ""
            Worksheets("TestCaseManager").Cells(i, 13).Value = ""
            Worksheets("TestCaseManager").Cells(i, 14).Value = ""
            Worksheets("TestCaseManager").Cells(i, 15).Value = ""
        Else
        
          strSQL = "select group_concat(colnfo) colnfo,testdata,actResult,expResult,testResult,TestResultDesc,TestCaseCode from" _
            & "( " _
            & "select concat(t.ColName,'=',t.ColValue) colnfo,tm.testdata,tm.actResult,tm.expResult,tm.testResult,tm.TestResultDesc,tm.TestCaseCode  " _
            & " from jmetertestdatamanager t  ,jmetertestcasemanager tm  " _
            & " where t.isrun='Y'and t.TestCaseCode='" & V_TestCaseCode & "' and t.TestCaseCode = tm.TestCaseCode" _
            & ") tall  group by TestCaseCode"
            'MsgBox "strsql = " & strsql
            rs.Open strSQL, conn
                
            Do While Not rs.EOF
                Testdata = rs("testdata")
                colnfo = rs("colnfo")
                actResult = rs("actResult")
                expResult = rs("expResult")
                testResult = rs("testResult")
                TestResultDesc = rs("TestResultDesc")
                If testResult = "true" Or testResult = "TRUE" Then
                    passcount = passcount + 1
                Else
                    failcount = failcount + 1
                End If
               
                Worksheets("TestCaseManager").Cells(i, 11).Value = Testdata
                
                Worksheets("TestCaseManager").Cells(i, 13).Value = actResult
                Worksheets("TestCaseManager").Cells(i, 14).Value = testResult
                Worksheets("TestCaseManager").Cells(i, 15).Value = TestResultDesc
                
                
                Exit Do
                 ' rs.movenext
            Loop
            rs.Close
    '----------------------------------------------------------------
    '复制测试结果到 runningTestCase
          k = Sheets("runningTestCase").Cells(Rows.Count, 1).End(xlUp).Row + 1
          Sheets("TestCaseManager").Rows(i).Copy Sheets("runningTestCase").Range("a" & k)
    '----------------------------------------------------------------

            
        End If
        

    End If
      
   Next
   ' 写入测试概要统计数据
   Worksheets("Summary").Cells(6, 2).Value = totalcount 'passcount, failcount, totalcount
   Worksheets("Summary").Cells(7, 2).Value = passcount
   Worksheets("Summary").Cells(8, 2).Value = failcount
   
   CloseDB_P
   
End Sub


Sub deleteAllData()
   
   RowCount = Worksheets("runningTestCase").UsedRange.Cells.Rows.Count
   row_end = RowCount
   If row_end >= 4 Then
    Worksheets("runningTestCase").Rows("4:" & row_end).Delete
   End If
   
   
   
End Sub
Sub copyRunningTestInfo()

    '删除runningTestCase中的数据
    deleteAllData
    row_end = Worksheets("TestCaseManager").UsedRange.Cells.Rows.Count
    '----------------------------------------------------------------
    row_begin = 4
    
    If row_begin > 0 Then
      For i = row_begin To row_end
            tempstr = Worksheets("TestCaseManager").Cells(i, 1).Value
            If IsNumeric(tempstr) = False Or IsNull(tempstr) Or tempstr = "" Then
               Exit For
            Else
                V_isrun = Worksheets("TestCaseManager").Cells(i, 2).Value  '是否运行
                If V_isrun = "Y" Or V_isrun = "y" Then
                    k = Sheets("runningTestCase").Cells(Rows.Count, 1).End(xlUp).Row + 1
                    Sheets("TestCaseManager").Rows(i).Copy Sheets("runningTestCase").Range("a" & k)
                End If
           End If
     Next
   End If
End Sub



'********************************************************************
'设计说明： 写SummaryReport

'调用举例： WriteSummaryReport
'********************************************************************
Function WriteSummaryReport()

    ConnectExcel TestReportPath
    
    '获取当前Test通过数量
    strSQL = "Select Count(1) from [runningTestCase$] Where FinalResult like 'Y%'"
    PassNum = ExecuteQuery(strSQL)(0)
    '获取当前Test失败数量
    strSQL = "Select Count(1) from [runningTestCase$] Where FinalResult like 'F%'"
    FailNum = ExecuteQuery(strSQL)(0)
    '获取当前Test警告数量
    strSQL = "Select Count(1) from [runningTestCase$] Where FinalResult like 'W%'"
    WarningNum = ExecuteQuery(strSQL)(0)
    
    CloseDB_P


    
End Function
〆WangBenYan゜
关注
0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
Jmeter 用宏自动执行脚本的代码笔记

1、代码笔记。Dim conn, strCnn, cmd, rs'********************************************************************'设计说明：连接Excel数据库（excel文件可以当做数据库读取数据）'调用举例： ConnectExcel "***.xls"'****************
复制链接

扫一扫
专栏目录