''' 1.打开用例文件 2.根据传参读取对应的sheet 3.根据传参指定的列获取对应的用例数据-多列,通过*['标题','请请求'] *args方式传递 4.获取到的数据可以转字典的转成字典,不需要的保持原数据 5.能根据用例编号列对应的名称和编号进行筛选用例 6.数据放回形式,列表套列表[['名称','编号','标题'],[],[]] ''' import json from openpyxl import load_workbook from utils.handle_path import data_path from pathlib import Path def get_excel_data(file_path, sheet_name, case_name, *args, run_case=None): res_list = [] # 存放最终结果 [[],[],[]] col_data_index = [] # 存放指定列的序号 run_case_list = [] # 实际运行的case列表 work_book = load_workbook(file_path) # 打开文件 work_sheet = work_book[sheet_name] col_data_one = [row_data[0].value for row_data in work_sheet.rows] # 获取第0列的数据 row_data_one = [col_data[0].value for col_data in work_sheet.columns] # 获取第0行的数据 for col_name in args: # 根据传入的列的数据获取列编号 *['标题','请求参数','响应结果'] col_data_index.append(row_data_one.index(col_name)) if run_case is None or 'all' in run_case or run_case == []: run_case_list = col_data_one else: for case in run_case: if '-' in case: start, end = case.split('-') for num in range(int(start), int(end)): run_case_list.append(f'{case_name}{num:0>3}') else: run_case_list.append(f'{case_name}{case:0>3}') # print(work_sheet.cell(1, 1).value) # cell(1,1)从1开始 for row_idx, row in enumerate(col_data_one, 1): # 遍历第0列获取行号和第0列第N行的值 # print('row_idx:', row_idx, 'row:', row, run_case_list) if case_name in str(row) and str(row) in run_case_list: # 去掉第一行 row_data_list = [] for col_idx in col_data_index: row_data_list.append(is_json(work_sheet.cell(row_idx, col_idx + 1).value)) # print(row_idx, col_idx + 1) res_list.append(row_data_list) return res_list def is_json(data_str): try: return json.loads(data_str) except: return data_str if __name__ == '__main__': res = get_excel_data(Path(data_path / 'test.xlsx'), '登录模块', 'Login', *['用例编号', '标题', '请求参数', '响应预期结果'], run_case=['all', "001", "003"]) print(res)
标签:case,run,openpyxl,list,excel,列表,data,col,row From: https://www.cnblogs.com/dyjnicole/p/17236558.html