excel⽤例接⼝⾃动化要求：根据excel中的测试⽤例，执⾏接⼝测试，并将结果录⼊excel，邮件发送给相关⼈思路如下：1.读取excel，保存测试⽤例中的内容2.根据excel中的请求url，method，data，调⽤接⼝，并保存返回报⽂3.读取返回报⽂，和检查点对⽐，不⼀致则不通过，⼀致则通过，并保存测试结果4.将测试结果和保存报⽂写⼊excel中5.测试结束后将excel变成附件发邮件需要⽤到xlrd，openpyxl，requests，json模块这是excelway,py⽂件，将读取excel⽤例和修改写⼊测试结果和返回报⽂的函数放在这个⽂件⾥import xlrd,openpyxl,jsondef readExcel(excel_path):'''读取excel表中测试⽤例的函数:param excel_path: 传⼊excel⽂件路径:return: 返回该excel中第⼀个sheet中全部的测试⽤例'''try:book = xlrd.open_workbook(excel_path) #打开excelexcept Exception as e:print('excel路径错误或⽂件⽆法打开')else:sheet = book.sheet_by_index(0) #取第⼀个sheet页all_test_cases = []for row_num in range(1,sheet.nrows): #从第2⾏开始获取所有⾏excel内容row=sheet.row_values(row_num)all_test_cases.append(row) #将excel的每⼀条测试⽤例添加到all_test_casesreturn all_test_casesdef updateExcel(excel_path,result_list,responses_list):book = openpyxl.load_workbook(excel_path)sheet = book.activefor col8 in range(len(result_list)): #将测试结果写⼊第⼋列sheet.cell(col8+2,8,result_list[col8])for col9 in range(len(responses_list)): #将返回报⽂写⼊第九列sheet.cell(col9+2,9,json.dumps(responses_list[col9],ensure_ascii=False))book.save(excel_path)这是HttpRequest.py⽂件，将post、get请求的操作放在这个⽂件下，定义为⼀个类import requestsclass MyRequest:def__init__(self,url,method='get',data=None,headers=None,is_json=False):method = method.lower()self.url = urlself.data = dataself.headers = headersself.is_json = is_jsonif hasattr(self,method):getattr(self,method)()def get(self):try:req = requests.get(self.url,self.data,headers=self.headers).json()except Exception as e:self.response = {"error":"接⼝请求出错%s"%e}else:self.response = reqdef post(self):try:if self.is_json:req = requests.post(self.url,json=self.data,headers=self.headers).json()else:req = requests.post(self.url, self.data, headers=self.headers).json()except Exception as e:self.response = {"error": "接⼝请求出错%s" % e}else:self.response = req这是mail.py⽂件，将发送邮件的函数放⼊⽂件中import yagmaildef send_mail():smtp = yagmail.SMTP(host='',user='xxx@',password='xxxxx',)smtp.send(to='xxxx@',cc='xxxx@',subject='测试报告',contents='请查收测试报告',attachments=[r'C:\Users\ght\PycharmProjects\untitled\homework\testcase.xlsx'] )send_mail()接⼝⾃动化脚本如下，执⾏脚本时，需导⼊excelway,HttpRequest,mail import excelwayimport HttpRequestimport mailimport json,threadpooldef get_data_dic(data):'''将测试⽤例中传⼊参数data'a=sdaf,b=1'这样格式的字符串转化为字典{'a':'1','b':'2'}:param data:传⼊测试数据，格式为a=1,b=2:return:返回字典格式'''str1 = []data_list = data.split(',')for d in data_list:str1.append(d.split('='))return dict(str1)def res_check(res,res_check):'''⽐对接⼝返回值是否和预期结果⼀致，⼀致则返回“通过”，不⼀致则返回“失败”:param res: 接⼝返回数据:param res_check: 检查值:return: 测试结果通过或不通过'''res =json.dumps(res,ensure_ascii=False) #字典转化为字符串res = res.replace('": "',"=").replace('": ',"=") #格式转换为a=1,b=2这样，⽅便与检查点匹配 res_check = res_check.split(',')for r in res_check:if r in res:passelse:return'失败'return'通过'def interfaceTest(all_test_cases):'''将测试⽤例逐条执⾏，获得返回报⽂和测试结果，并传⼊updateExcel函数:param all_test_cases: 传⼊所有的测试⽤例的list'''responses_list = [] #存返回报⽂的listresult_list =[] #存测试结果的listfor case in all_test_cases:try:test_project = case[0]test_module = case[1]url = case[2]method = case[3]data = get_data_dic(case[4])test_description = case[5]test_check = case[6]except Exception as e:return('测试⽤例格式不正确%s'%e)else:response = HttpRequest.MyRequest(url=url,method=method,data=data).response responses_list.append(response)res = res_check(response,test_check) #判断返回结果符合检查点if'通过'in res:result_list.append('通过')else:result_list.append('失败')excelway.updateExcel(excel_path, result_list, responses_list)if__name__ == '__main__':excel_path = 'testcase.xlsx'all_test_cases = excelway.readExcel(excel_path)interfaceTest(all_test_cases)mail.send_mail()print('完成')。
