def importclick(self):
try:
self.CaseNum = []
# 导入文件名称
self.filepath, filetype = QFileDialog.getOpenFileName(
self, "请导入测试用例", '', 'Excel files(*.xlsx)')
if self.filepath == '':
if self.label_10.text() == '请选择excel.xlsx格式表格导入':
self.filepath = self.newfile
self.label_10.setText('请选择excel.xlsx格式表格导入')
else:
self.newfile = self.filepath
self.newfile = self.filepath.replace('/', '\\')
# 显示文件名称
self.label_10.setText(os.path.basename(self.newfile))
# 打开excel
self.book = load_workbook(self.newfile)
# 获取工作表标签
self.sheets = self.book.sheetnames
self.CaseSheet = self.sheets[0]
# 判断是否为测试用例模板
isCase = ParseExcel(self.filepath).getRowValue(
self.CaseSheet, 2)
isCase1 = [
'序号', '用例编号', '用例工作表', '用例标题', '预期结果', '是否执行', '执行结束时间'
]
if set(isCase1) < set(isCase):
# 获取用例总数，只计算运行的用例
isimplement = ParseExcel(self.newfile).getColumnValue(
self.CaseSheet, testCase_Isimplement)
for i, v in enumerate(isimplement):
if v == 'y':
self.CaseNum.append(
ParseExcel(self.newfile).getCellValue(
self.CaseSheet, i + 2, testCase_Sheet))
# 清空
self.comboBox_2.clear()
self.comboBox_2.addItem('全部')
# 把工作表标签赋值到用例模块选择
# self.sheets = self.sheets.pop(0)
if len(self.sheets) > 1:
del (self.sheets[0])
self.comboBox_2.addItems(self.sheets)
# 导入用例后先显示全部的用例数量
CaseNums = len(self.CaseNum) * int(self.spinBox.text())
self.label_15.setText(str(CaseNums))
else:
self.label_10.setText('请选择excel.xlsx格式表格导入')
QMessageBox.about(self, "提示", "用例格式错误，请重新导入用例")
except Exception as e:
print(e)
def main():
parseE = ParseExcel()
parseE.loadWorkBook(file_path)
sheetObj = parseE.getSheetByName(u"API")
activeList = parseE.getColumn(sheetObj, API_active)
for idx, cell in enumerate(activeList[1:], 2):
if cell.value == "y":
# 需要执行的接口所在行的行对象
rowObj = parseE.getRow(sheetObj, idx)
apiName = rowObj[API_apiName - 1].value
requestUrl = rowObj[API_requestUrl - 1].value
requestMethod = rowObj[API_requestMethod - 1].value
paramsType = rowObj[API_paramsType - 1].value
apiTestCaseFileName = rowObj[API_apiTestCaseFileName - 1].value
# 下一步读用例sheet表，准备执行测试用例
caseSheetObj = parseE.getSheetByName(apiTestCaseFileName)
caseActiveObj = parseE.getColumn(caseSheetObj, CASE_active)
for c_idx, col in enumerate(caseActiveObj[1:], 2):
if col.value == "y":
# 说明此case行需要执行
caseRowObj = parseE.getRow(caseSheetObj, c_idx)
requestData = caseRowObj[CASE_requestData - 1].value
relyData = caseRowObj[CASE_relyData - 1].value
dataStore = caseRowObj[CASE_dataStore - 1].value
if relyData:
# 发送接口请求之前，先做依赖数据的处理
requestData = "%s" %GetKey.get(eval(requestData), eval(relyData))
# 拼接接口请求参数，发送接口请求
httpC = HttpClient()
print requestMethod, requestUrl, paramsType, requestData
response = httpC.request(requestMethod = requestMethod,
requestUrl = requestUrl,
paramsType = paramsType,
requestData = requestData
)
responseData = response.json()
# 存储依赖数据
if dataStore:
RelyDataStore.do(eval(dataStore),apiName, c_idx - 1, eval
(requestData),responseData)
# 比对结果
else:
print "用例被忽略执行"
else:
print "接口被设置忽略执行"
def get_test_case_sheet_names(test_data_excel_path):
#定义ParseExcel对象
parseExcel = ParseExcel(test_data_excel_path)
#设置当前sheet为第一个sheet
parseExcel.set_sheet_by_index(0)
#存放需要运行的测试用例序号，和测试用例sheet名
test_case_to_run_sheet_names = []
#遍历测试用例集的每行组成的列表
for row in parseExcel.get_all_rows_values():
#如果测试用例sheet名不为空且此行需要执行的话
if row[test_case_test_step_sheet_name_col_no] and \
row[test_case_is_executed_col_no].lower() == "y":
#测试用例集序号和测试用例sheet名组成的元组
test_sheet_name_tuple = row[test_case_row_no_clo_no],row[test_case_test_step_sheet_name_col_no]
#元组加入列表
test_case_to_run_sheet_names.append(test_sheet_name_tuple)
return test_case_to_run_sheet_names
def test_cases_from_test_data_sheet(test_data_excel_path,test_data_sheet_name):
parseExcel = ParseExcel(test_data_excel_path)
#根据sheet名设置当前sheet
parseExcel.set_sheet_by_name(test_data_sheet_name)
info("当前的测试用例sheet名:%s" %test_data_sheet_name)
#存放所有的接口测试用例
test_cases = []
#获取所有的测试用例
#遍历每行数据
for row in parseExcel.get_all_rows_values():
#如果需要执行
if row[test_data_is_executed_col_no] and row[test_data_is_executed_col_no].lower() == "y":
#获取测试用例序号,测试用例接口名，请求数据，断言数据，正则匹配表达式
test_case = row[test_data_row_no_col_no],row[test_data_interface_name_col_no],row[test_data_request_data_col_no],\
row[test_data_assert_word_col_no],row[test_data_correlate_regx_col_no]
#加入列表
test_cases.append(test_case)
return test_cases
class HandleTestCase():
def __init__(self,testFilePath):
self.wb=ParseExcel()
self.wb.loadWorkBook(testFilePath)
def getTestAPIInfo(self,rowNum=None):
#rowNum指定API的具体行
#wb=ParseExcel()
#wb.loadWorkBook(testFilePath)
API_SheetObj=self.wb.getSheetByName("API")
#print(API_SheetObj)
testAPIAllInfo=self.wb.getRow(API_SheetObj,rowNum)
print("testAPIAllInfo--->",testAPIAllInfo)
APIName=testAPIAllInfo[1].value
RequestUrl=testAPIAllInfo[2].value
RequestMethod=testAPIAllInfo[3].value
ParamsType=testAPIAllInfo[4].value
CaseSheetName=testAPIAllInfo[5].value
Active=testAPIAllInfo[6].value
#print(APIName,RequestUrl,RequestMethod,ParamsType,CaseSheetName,Active)
APIsheet_rows=self.wb.getRowsNumber(API_SheetObj)
if Active=="y":
testCaseSheetObj=self.wb.getSheetByName(CaseSheetName)
print("testCaseSheetObj--->",testCaseSheetObj)
testsheet_rows=self.wb.getRowsNumber(testCaseSheetObj)
return testCaseSheetObj,APIName,RequestUrl, RequestMethod,ParamsType,CaseSheetName,Active,testsheet_rows,APIsheet_rows
else:
print("该接口%s,%s不用执行"%(APIName,CaseSheetName))
def getTestCaseInfo(self,testCaseSheet,rowNum):
#rowNum指定testcasesheet的具体行
testCaseAllInfo=self.wb.getRow(testCaseSheet,rowNum)
print("testCaseAllInfo--->",testCaseAllInfo)
RequestData=testCaseAllInfo[0].value
RelyData = testCaseAllInfo[1].value
ResponseCode=testCaseAllInfo[2].value
DataStore=testCaseAllInfo[4].value
CheckPoint=testCaseAllInfo[5].value
Active=testCaseAllInfo[6].value
if Active=="y":
#print(RequestData,RelyData,ResponseCode,DataStore,Active)
return eval(RequestData),RelyData,ResponseCode,DataStore,eval(CheckPoint),Active
else:
print("[%s]第%s条用例不用执行"%(testCaseSheet.title,(rowNum-1)))
return None
from Utils.ParseExcel import ParseExcel
def writeResult(wb, sheetObj, errorinfo, response_data, result, rowNo):
wb.writeCell(sheetObj, content=response_data, rowNo=rowNo, colsNo=4)
print("4444444444444444444444444444")
wb.writeCell(sheetObj, content=result, rowNo=rowNo, colsNo=8)
print("555555555555555555555555555")
if eval(errorinfo):
wb.writeCell(sheetObj, content=errorinfo, rowNo=rowNo, colsNo=9)
print("666666666666666666666666666")
print("write down")
if __name__ == "__main__":
wb = ParseExcel()
wb.loadWorkBook(
r"E:\我的坚果云\framework\practice\excel_interface\TestData\testData.xlsx")
ob = wb.getSheetByName("注册接口用例")
writeResult(wb, ob, "{}", "{}", "pass", 2)
def main():
wb = ParseExcel()
wb.loadWorkBook(
r"E:\framework\practice\excel_interface\TestData\testData.xlsx")
hc = HandleTestCase(os.path.join(ProjectPath, "TestData\\testData.xlsx"))
for i in range(2, 7): #需要算出api总表的行数
allinfo = hc.getTestAPIInfo(i)
if allinfo:
testsheet = allinfo[0]
#print(hc.getTestCaseInfo(testsheet, 2))
APIName = allinfo[1]
requestUrl = allinfo[2]
requestMethod = allinfo[3]
ParamsType = allinfo[4]
testsheet_rows = allinfo[7]
for j in range(2, testsheet_rows + 1):
#需要算出每个casesheet的行数
sub = hc.getTestCaseInfo(testsheet, j)
if sub:
#eval(RequestData),RelyData,ResponseCode,DataStore,CheckPoint,Active
print("sub", sub)
hr = HttpRequest()
RequestData = sub[0]
if "username" in RequestData:
RequestData["username"] = RequestData["username"] + str(
getUniqnum())
RelyData = sub[1]
ResponseCode = sub[2]
DataStore = sub[3]
CheckPoint = sub[4]
if RelyData:
RequestData = getRelyData(REQUEST_DATA, RESPONSE_DATA,
RequestData, eval(RelyData))
if APIName == "querycontent":
RequestData = str(RequestData[list(
RequestData.keys())[0]])
print(requestUrl, requestMethod, RequestData, ParamsType)
response = hr.httpRequest(requestUrl,
requestMethod,
RequestData,
ParamsType,
cookies=None,
headere=None)
print("response", response.text)
if DataStore:
print("dddddddddddddddddddddddddssssssss", RequestData)
dataStore(APIName, j - 1, eval(DataStore), RequestData,
json.loads(response.text))
if response.status_code == ResponseCode:
assert_result = assertResult(json.loads(response.text),
CheckPoint)
print("assert_reuslt", assert_result)
else:
print("response_code不是200")
print("assert_result[1]", str(assert_result[1]))
#writeResult(hc.wb, testsheet, "{}", "{}", "pass", 2)
writeResult(hc.wb, testsheet, str(assert_result[1]),
str(response.text),
"pass" if assert_result[0] else "fail", j)
print("-" * 100)
print("REQUEST_DATA", REQUEST_DATA)
print("RESPONSE_DATA", RESPONSE_DATA)
def __init__(self,testFilePath):
self.wb=ParseExcel()
self.wb.loadWorkBook(testFilePath)
def RunTest(self):
"""
将gui界面的参数传入yaml文件中,运行脚本
:return:
"""
try:
# logger = Logger('logger').getlog()
parameter = CONFIG_PATH + 'Parameter.yaml'
if self.lineEdit.text() == '' or self.lineEdit.text() is None:
QMessageBox.about(self, "提示", "IP地址不能为空")
elif re.match(r"^(?:[0-9]{1,3}\.){3}[0-9]{1,3}$",
self.lineEdit.text()) is None and re.match(
r'[^\s]*[.com|.cn]',
self.lineEdit.text()) is None:
QMessageBox.about(self, "提示", "请输入正确IP地址")
elif self.newfile == '':
QMessageBox.about(self, "提示", "请导入测试用例")
else:
try:
ParseExcel(self.filepath).wb[self.CaseSheet].cell(
50, 50, '测试')
ParseExcel(self.filepath).wb.save(self.filepath)
except PermissionError:
QMessageBox.about(self, "提示", "请先关闭用例文件，再运行测试用例")
raise
# # 总用例数显示
# if self.comboBox_2.currentText() == '全部':
# # 获取用例总数
# CaseNums = len(self.CaseNum) * int(self.spinBox.text())
# self.label_15.setText(str(CaseNums))
# else:
# self.CaseList = []
# for i in self.CaseNum:
# if i == self.comboBox_2.currentText():
# self.CaseList.append(i)
# self.label_15.setText(str(len(self.CaseList)))
if len(self.CaseNum) != 0 or len(self.CaseList) != 0:
if self.comboBox.currentText() == 'Google Chrome':
# 获取浏览器版本号
# self.listWidget.insertItem(0, '获取浏览器版本号中，请稍等...')
self.driver = webdriver.Chrome(
executable_path=DRIVERS_PATH + 'chrome\\' +
'70.0.3538.97\\chromedriver.exe')
self.driver.get('chrome://version/')
self.version = self.driver.find_element_by_css_selector(
'#version > span:nth-child(1)').text
self.driver.quit()
num = [
'70', '71', '72', '73', '74', '75', '76', '77',
'78'
]
for i in num:
if i == self.version[:2]:
YamlWrite().Write_Yaml_Updata(
CONFIG_PATH + 'Parameter.yaml', 'Version',
self.version[:2]) # 将value写入ip.yaml文件中
# 重置时间
self.H = 0
self.M = 0
self.S = 0
# 重置日志显示框
self.listWidget.clear()
# 清除一些稍后写入的数据
YamlWrite().Write_Yaml_Updata(
parameter, 'CaseNum', 0)
# 写入IP
YamlWrite().Write_Yaml_Updata(
parameter, 'IP', self.lineEdit.text())
# 写入浏览器类型
YamlWrite().Write_Yaml_Updata(
parameter, 'Browser',
self.comboBox.currentText())
# 写入是否生成测试报告
YamlWrite().Write_Yaml_Updata(
parameter, 'ReportAddress',
self.label_9.text())
# 写入循环次数
YamlWrite().Write_Yaml_Updata(
parameter, 'loop', self.spinBox.text())
# 获取导入的测试用例路径
YamlWrite().Write_Yaml_Updata(
parameter, 'ImportAddress', self.newfile)
# 获取模块信息
YamlWrite().Write_Yaml_Updata(
parameter, 'Moudle',
self.comboBox_2.currentText())
# 多线程启动
# 已执行用例数线程
# self.case = threading.Thread(target=self.display_case)
# 运行用例线程
if self.radioButton.isChecked() is True:
self.testcase = threading.Thread(
target=TestPaperless().RunReport)
if self.radioButton_2.isChecked() is True:
self.testcase = threading.Thread(
target=TestPaperless().TestCase)
# 时间，用例运行计数
self.cttime = threading.Thread(
target=self.CaseTime)
# # 输出显示
# self.output = threading.Thread(target=self.OutPut)
# if self.Case_num == 0:
# self.case.setDaemon(True)
# self.case.start()
if self.testcase.isAlive() is False:
self.testcase.setDaemon(True)
self.testcase.start()
# if self.OutPut_num == 0:
# self.output.setDaemon(True)
# self.output.start()
self.timer.timeout.connect(self.OutPut)
self.timer.start(200)
# 启动 按钮点击后，禁用，并置灰
self.toolButton_3.setEnabled(False)
self.toolButton_3.setStyleSheet(
"QToolButton{font:12pt '宋体';background-color:#8e7f7c;color: white;border-radius:3px;}"
)
# 暂停 按钮变成蓝色，启用
self.toolButton_4.setEnabled(True)
self.toolButton_4.setStyleSheet(
"QToolButton{font:12pt '宋体';background-color:#419AD9;color: white;border-radius:3px;}"
)
# 导入 按钮禁用 置灰
self.toolButton.setEnabled(False)
self.toolButton.setStyleSheet(
"QToolButton{font:12pt '宋体';background-color:#8e7f7c;color: white;border-radius:3px;}"
)
self.cttime.setDaemon(True)
self.cttime.start()
break
elif int(self.version[:2]) < 70 or int(
self.version[:2]) > 78:
QMessageBox.about(self, "提示",
"浏览器版本不符合，请更新浏览器版本号")
break
else:
# 重置时间
self.H = 0
self.M = 0
self.S = 0
# 重置日志显示框
self.listWidget.clear()
# 清除一些稍后写入的数据
YamlWrite().Write_Yaml_Updata(parameter, 'CaseNum', 0)
# 写入IP
YamlWrite().Write_Yaml_Updata(parameter, 'IP',
self.lineEdit.text())
# 写入浏览器类型
YamlWrite().Write_Yaml_Updata(
parameter, 'Browser', self.comboBox.currentText())
# 写入是否生成测试报告
YamlWrite().Write_Yaml_Updata(parameter,
'ReportAddress',
self.label_9.text())
# 写入循环次数
YamlWrite().Write_Yaml_Updata(parameter, 'loop',
self.spinBox.text())
# 获取导入的测试用例路径
YamlWrite().Write_Yaml_Updata(parameter,
'ImportAddress',
self.newfile)
# 获取模块信息
YamlWrite().Write_Yaml_Updata(
parameter, 'Moudle', self.comboBox_2.currentText())
# 多线程启动
# 已执行用例数线程
# self.case = threading.Thread(target=self.display_case)
# 运行用例线程
if self.radioButton.isChecked() is True:
self.testcase = threading.Thread(
target=TestPaperless().RunReport)
if self.radioButton_2.isChecked() is True:
self.testcase = threading.Thread(
target=TestPaperless().TestCase)
# 时间，用例运行计数
self.cttime = threading.Thread(target=self.CaseTime)
# # 输出显示
# self.output = threading.Thread(target=self.OutPut)
# if self.Case_num == 0:
# self.case.setDaemon(True)
# self.case.start()
if self.testcase.isAlive() is False:
self.testcase.setDaemon(True)
self.testcase.start()
# if self.OutPut_num == 0:
# self.output.setDaemon(True)
# self.output.start()
self.timer.timeout.connect(self.OutPut)
self.timer.start(200)
# 启动 按钮点击后，禁用，并置灰
self.toolButton_3.setEnabled(False)
self.toolButton_3.setStyleSheet(
"QToolButton{font:12pt '宋体';background-color:#8e7f7c;color: white;border-radius:3px;}"
)
# 暂停 按钮变成蓝色，启用
self.toolButton_4.setEnabled(True)
self.toolButton_4.setStyleSheet(
"QToolButton{font:12pt '宋体';background-color:#419AD9;color: white;border-radius:3px;}"
)
# 导入 按钮禁用 置灰
self.toolButton.setEnabled(False)
self.toolButton.setStyleSheet(
"QToolButton{font:12pt '宋体';background-color:#8e7f7c;color: white;border-radius:3px;}"
)
self.cttime.setDaemon(True)
self.cttime.start()
else:
QMessageBox.about(self, '提示', '可运行用例数为0，请检查用例')
except Exception as e:
print(e)
def main():
#定义ParseExcel对象
parseExcel = ParseExcel(test_data_file)
#
test_results_for_html_report = []
#获取测试用例集中的所有测试用例sheet名
test_sheet_names = get_test_case_sheet_names(test_data_file)
# print(test_sheet_names
#遍历每个测试用例sheet，test_sheet_name为一个元组，包含序号和sheet名
for test_sheet_name in test_sheet_names:
print("*" * 20)
#测试用例集对应的测试执行结果
flag = True
#设置当前sheet
parseExcel.set_sheet_by_name(test_sheet_name[1])
#获取测试用例sheet的所有测试用例,test_sheet_name是元组（用例编号，sheet名）
test_cases = test_cases_from_test_data_sheet(test_data_file,
test_sheet_name[1])
#遍历每个测试用例
for test_case in test_cases:
print("----" * 20)
#获取接口名和请求数据
interface_name = eval(test_case[1])
request_data = test_case[2]
# print(interface_name,request_data)
start_time = time.time()
#发送接口请求
res, data = send_request(interface_name, request_data)
end_time = time.time()
info("接口响应信息:%s" % res.text)
#写响应信息到文件,行列都是从1开始
parseExcel.write_cell_value(
int(test_case[0]) + 1, test_data_response_data_col_no + 1,
res.text)
info("断言值: %s" % test_case[3])
#写测试用例执行时间
parseExcel.write_current_time(
int(test_case[0]) + 1, test_data_executed_time_col_no + 1)
try:
#利用正则表达式断言接口是否成功
assert_word = test_case[3]
#断言失败，抛断言异常
if not re.search(assert_word, res.text):
raise AssertionError
#写成功的测试结果
print("hhhhhhh", test_case[0])
parseExcel.write_cell_value(int(test_case[0]) + 1,
test_data_test_result_col_no + 1,
"成功",
style="green")
#
test_results_for_html_report.append(
(res.url, data, res.text,
int(end_time - start_time) * 1000, test_case[3], "成功"))
except AssertionError as e:
#写失败的测试结果
parseExcel.write_cell_value(int(test_case[0]) + 1,
test_data_test_result_col_no + 1,
"失败",
style="red")
test_results_for_html_report.append(
(res.url, data, res.text,
int(end_time - start_time) * 1000, test_case[3], "失败"))
flag = False
except Exception as e:
#写失败的测试结果
parseExcel.write_cell_value(int(test_case[0]) + 1,
test_data_test_result_col_no + 1,
"失败",
style="red")
test_results_for_html_report.append(
(res.url, data, res.text,
int(end_time - start_time) * 1000, test_case[3], "失败"))
flag = False
info("接口执行耗时:[%s] 毫秒" % (end_time - start_time))
#写入接口执行耗时
parseExcel.write_cell_value(
int(test_case[0]) + 1, test_data_test_elapse_time_col_no + 1,
str(int(end_time - start_time) * 1000))
#处理关联数据，并存储关联数据到全局变量
#获取关联正则表达式
regx = test_case[4]
if regx:
var_name = regx.split('||')[0]
regx_info = regx.split("||")[1]
#提取的关联数据不为空的话
if re.search(regx_info, res.text).group(1):
#提取响应中的关联数据
var_value = re.search(regx_info, res.text).group(1)
#存入全局变量的值
global_vars[var_name] = var_value
info("提取的响应变量:%s,变量值:%s" % (var_name, var_value))
info("全局变量: %s" % global_vars)
#切换到测试用例集sheet
parseExcel.set_sheet_by_index(0)
if flag:
#写测试用例集测试执行结果
parseExcel.write_cell_value(int(test_sheet_name[0]) + 1,
test_case_executed_result_col_no,
"成功",
style="green")
else:
parseExcel.write_cell_value(int(test_sheet_name[0]) + 1,
test_case_executed_result_col_no,
"失败",
style="red")
#写测试用例集测试执行时间
parseExcel.write_current_time(
int(test_sheet_name[0]) + 1, test_case_executed_time_col_no)
mail = Mail(mail_host, mail_user, mail_pass, sender)
#发送邮件
mail.send_mail(receivers, "接口测试报告", "接口测试报告", test_data_file)