"""
封装读取表单测试用例的方法
"""
from openpyxl import load_workbook
class MyExcel:
def __init__(self,excel_path,sheet_name):
"""
打开工作簿,选择一个表单
"""
wb=load_workbook(excel_path)#加载一个excel,得到工作簿对象
self.sheet=wb[sheet_name]
def read_data(self):
"""
读取excel表单数据
"""
print('self.sheet:',self.sheet,'type(self.sheet)',type(self.sheet))
#self.sheet: <Worksheet "Sheet1"> type(self.sheet) <class 'openpyxl.worksheet.worksheet.Worksheet'>
print('生成器self.sheet.values:',self.sheet.values)
#生成器self.sheet.values: <generator object Worksheet.values at 0x00000248E4E3ABA0>
data=list(self.sheet.values)#将生成器转换为列表
print('将生成器转换为列表data:',data)
#将生成器转换为列表data: [('姓名', '性别'), ('张三', '男'), ('李四', '女')]
header=data[0]
case_data=[]
for row in data[1:]:
row_dict=dict(zip(header,row))#将头部和用例打包
case_data.append(row_dict)
# print('case_data:',case_data)
return case_data
#返回的是一个列表,列表中每一条数据都是一个用例
from common.excel_util import MyExcel#导入封装的读取excel
def test_readexcel():
myexcel=MyExcel('D:\APP\pycharm\pythonProject\huacepyest\data\lest.xlsx','Sheet1')
# MyExcel.read_data(r'D:\APP\pycharm\pythonProject\huacepyest\data')
cases = myexcel.read_data()
print(cases)#[{'姓名': '张三', '性别': '男'}, {'姓名': '李四', '性别': '女'}]
再改进下:
@pytest.mark.parametrize('cases',MyExcel(r'C:\Users\Administrator\PycharmProjects\pythonProject2\data\test.xlsx','Sheet1').read_data())
def test_readexcel(cases):
# myexcel=MyExcel(r'C:\Users\Administrator\PycharmProjects\pythonProject2\data\test.xlsx','Sheet1').read_data()
# cases = myexcel.read_data()
print('姓名',cases['姓名'])
print('性别',cases['性别'])
执行结果:
![](https://i-blog.csdnimg.cn/blog_migrate/d77e9deb9fbc16d252a66891b8d0d9c4.png)