使用openpyxl类库进行excel的数据读取,注:openpyxl仅支持xlsx后缀结尾的excel文件。
第一步,先安装openpyxl,直接控制台输入 pip install openpyxl。封装一个ExcelUtils的类。
import openpyxl from openpyxl.worksheet.worksheet import Worksheet class ExcelUtils: def __init__(self,file_name): self.file_name = file_name def openExcel(self,sheetName)->Worksheet: excel = openpyxl.open(self.file_name) sheet_name = excel[sheetName] return sheet_name def readHeader(self,sheetName): sheet_name = self.openExcel(sheetName) headers = [] for header in sheet_name[1]: headers.append(header.value) return headers def readContent(self,sheetName): sheet_name = self.openExcel(sheetName) rows = list(sheet_name.rows) data=[] for row in rows[1:]: row_data = [] for cell in row : row_data.append(cell.value) dict_data = dict(zip(self.readHeader(sheetName),row_data)) data.append(dict_data) return data if __name__ == '__main__': excel_utils = ExcelUtils(r"D:\PycharmProjects\cases.xlsx") sheet1 = excel_utils.readContent("Sheet1") print(sheet1)
第二步,装一个requests的库,pip install requests。简单封装一个HttpUtils的类。
import requests class HttpUtils: def __init__(self): self.session = requests.session() def send_request(self, method, url, params=None, data=None, headers=None, **kwargs): res = self.session.request(method, url, params, data, headers, **kwargs) try: return res.json() except TypeError as e: print(f"不是json格式{e}")
第三步,写一个测试类test_login_case,此文件名必须以test_开头 引入ddt的包,pip install ddt
import unittest import ddt from demo.ExcelUtils import ExcelUtils from demo.HttpUtils import HttpUtils data = ExcelUtils(r"D:\PycharmProjects\cases.xlsx").readContent("Sheet1") print(data) @ddt.ddt class TestCases(unittest.TestCase): @ddt.data(*data) def test_login(self, login_data): data1 = {"account": login_data["account"], "deviceId": login_data["deviceId"], "pwdCyp": login_data["pwdCyp"]} res = HttpUtils().send_request(method=login_data["method"],url=login_data["url"], data=data1) self.assertEqual(res["code"],200) if __name__ == '__main__': unittest.main()
第四步,创建一个run_case文件。
import os import unittest import time test_loader = unittest.TestLoader() path = os.path.dirname(os.path.abspath(__file__)) suite = test_loader.discover(path) creat_file_path = os.path.join(path, "report") if not os.path.exists(creat_file_path): os.mkdir(creat_file_path) current_time = time.time() file_path = os.path.join(creat_file_path, f"report{int(round(current_time*1000))}.txt") with open(file_path,"w",encoding="utf-8") as f: runner = unittest.TextTestRunner(f,verbosity=2) runner.run(suite) if __name__ == '__main__': unittest.main()
其中TextTestRunner可以替换为HtmlTestRunner,具体替换可以参考这个大佬的博客:https://blog.csdn.net/Ben_zhangxw/article/details/115423843