使用内置函数读取
import re
class RecordFirstLinePlace:
CordPlace = {} # 记录每一行title在列的位置,使用索引取值时会使用其中记录的数据
class TableTreating:
def __init__(self, filename):
self.filename = filename
self.filepath = filename
self.wb = load_workbook(self.filepath)
self.test_data = []
def read_excel(self, sheet_name):
work_sheet_data = self.wb[sheet_name].rows
work_sheet_title = [title_value.value for title_value in next(work_sheet_data)]
title_place = [i for i in range(1, len(work_sheet_title) + 1)]
RecordFirstLinePlace.CordPlace[sheet_name] = dict(list(zip(work_sheet_title, title_place)))
return work_sheet_data, work_sheet_title
def get_all_data(self, config_var):
sheet_names = self.wb.sheetnames + ['others']
model = config_var.get('mode').get(self.filename)
if model.get('others') == 'skip':
sheet_name_dict = model
else:
sheet_name_dict = {sheet_name: 'all' for sheet_name in sheet_names}
sheet_name_dict.update(model)
for key in sheet_name_dict:
if key not in sheet_names:
raise KeyError(f'sheetName = {key} is not in {self.filepath}, please check run_model key is right')
for sheet_name, model in sheet_name_dict.items():
if model == 'all':
sheet_row_generator, sheet_title = self.read_excel(sheet_name)
test_data_list = list(map(lambda row_data:
{**dict(zip(sheet_title, [cell.value for cell in row_data])),
'sheetName': sheet_name}, list(sheet_row_generator)))
self.test_data += test_data_list
elif isinstance(model, list):
sheet_row_generator, sheet_title = self.read_excel(sheet_name)
all_row_list = list(sheet_row_generator)
row_list = [all_row_list[index-1] for index in model]
test_data_list = list(map(lambda row_data:
{**dict(zip(sheet_title, [cell.value for cell in row_data])),
'sheetName': sheet_name}, row_list))
self.test_data += test_data_list
elif not model or sheet_name=='others':
pass
else:
raise ValueError(f'run_model={model}, value must be "all" or list, list just contains int value')
return self.test_data
def get_index_data(self, index):
index_list = index.split('.')
work_sheet_data, work_sheet_title = self.read_excel(index_list[0])
title_place = RecordFirstLinePlace.CordPlace[index_list[0]][index_list[1]]
data_object = [[cell_value for cell_value in row] for row in work_sheet_data]
split_place = eval(index_list[2]) if re.findall(r'^\d+$', index_list[2]) else index_list[2]
index_row_data = list(filter(lambda x: x[title_place - 1].value == split_place, data_object))
index_row_data_value = [[cell.value for cell in row] for row in index_row_data]
test_data = list(map(lambda x: {**dict(zip(work_sheet_title, x)), 'sheetName':index_list[0]}, index_row_data_value))
return test_data
run_model数据记录在配置文件可依据手动配置每个数据表的用例读取方式,间接控制用例执行方式
yaml数据读取
安装第三方库:pip install yaml-config -i https://pypi.douban.com
import yaml
with open('config.yaml', 'r', encoding='utf8') as f:
content = f.read()
res = yaml.load(content, yaml.FullLoader)
print(res)
>>>{'model': {'FirstRoom.xlsx': {'others': None, 'sheet_one': 'all'}}}
扩展:读取模式
others:非表单名称,控制未做特殊说明的其他表单执行方式,当等于skip时表示未记录表单用例全部跳过,否则其他表单用例全部读取。
表单名:值为all时全部执行,为列表[2,3,4]时读取caseid=2,3,4的测试用例,为None时跳过
① 调用方法读取excel数据,读取sheet_one表单所有测试数据
run_model = {'mode': {'FirstRoom.xlsx': {'others': None, 'sheet_one': 'all'}}}
res = TableTreating('FirstRoom.xlsx').get_all_data(run_model)
print(res)
② 读取sheet_one表单case_id=2,3
run_model = {'mode': {'FirstRoom.xlsx': {'others': None, 'sheet_one': [2,3]}}}
res = TableTreating('FirstRoom.xlsx').get_all_data(run_model)
print(res)
③ 索引取值, 取表中某个表单列下某个值得行数据,如索引:sheet_one.CaseId.2,读取改文件下sheet_one表单中列名为CaseId值为2的一行数据,若该列有多个值为2则会全部读取
res = TableTreating('FirstRoom.xlsx').get_index_data('sheet_one.CaseId.2')
print(res)
>>>[{'CaseId': 2, 'url': '/settings/user/info', 'CaseName': '查询当前用户信息', 'yapi_id': 1392, 'req_query': '{}', 'req_body': 'None', 'saveKey': None, 'jsonDone': '["message"]', 'expect': '["操作成功"]', 'sheetName': 'sheet_one'}]
res = TableTreating('FirstRoom.xlsx').get_index_data('sheet_one.url./settings/user/info')
print(res)
>>>[{'CaseId': 2, 'url': '/settings/user/info', 'CaseName': '查询当前用户信息', 'yapi_id': 1392, 'req_query': '{}', 'req_body': 'None', 'saveKey': None, 'jsonDone': '["message"]', 'expect': '["操作成功"]', 'sheetName': 'sheet_one'}]