使用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
|