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