通过读取excel sheet1
一、示例接口参数：
{ "referenceList": [{ "isTrusted": true, "keyList": [], "sexTypeChecked": true, "ageChecked": false, "samptypeChecked": false, "sexCode": "1", "referenceLow": "22", "referenceHigh": "222", "referenceShow": "22--222", "crisesHigh": "2222", "crisesLow": "2", "onlyIndex": 1 }], "isFormula": false, "sexTypeChecked": true, "ageChecked": false, "samptypeChecked": false, "businessType": "1", "projectCode": "bm10001", "projectName": "名称", "inputCode": "MC", "referenceLow": "11", "referenceHigh": "111", "referenceShow": "11--111", "crisesLow": "1", "crisesHigh": "1111", "wordResults": [{ "status": "1", "wordDescrips": [], "crisesFlag": "2", "resultStatus": "2", "recheckFlag": "2", "wordDescription": "描述", "resultStatusValue": "2", "crisesFlagValue": "2", "recheckFlagValue": "2", "onlyIndex": 1 }], "orgId": 54, "referenceSelected": "性别", "referenceValueVos": [{ "isTrusted": true, "keyList": [], "sexTypeChecked": true, "ageChecked": false, "samptypeChecked": false, "sexCode": "1", "referenceLow": "22", "referenceHigh": "222", "referenceShow": "22--222", "crisesHigh": "2222", "crisesLow": "2", "onlyIndex": 1 }] }
二、测试用例设计;
实例：
1、第一层中：referenceList、wordResults、referenceValueVos存在嵌套参数，数据存储在同名sheet中，path[2,4]代表同名sheet的3，4行
2、第一个嵌套参数referenceList需要组装的数据
三、实现
# -*- coding: gbk -*- import re import xlrd class ReadReqParams(): """读测试用例文件""" def __init__(self, data): # self.file_path = file_path self.data = data def __tablecells(self, table): for item in table.merged_cells: # 合并单元格坐标 return item def __sheet_others(self, sheetname, beginrows, endrows): """ 获取sheet中从m行到n行的数据 :param sheetname: sheet名称 :param beginrows: 起始行m :param endrows: 结尾行n :return: """ table = self.data.sheet_by_name(sheetname) keys = table.row_values(0) colnum = table.ncols sheet_others_lists = [] for i in range(int(beginrows), int(endrows)): data_dict = {} values = table.row_values(i) for x in range(colnum): data_dict[keys[x]] = values[x] sheet_others_lists.append(data_dict) return sheet_others_lists def __sheet1(self, table): """ 获取sheet1的数据 :param table: :return: """ row1_values = table.row_values(0) rownum = table.nrows colnum = table.ncols if rownum <= 1: print('Can not find TestCase') else: file_lists = [] for i in range(2, rownum): data_dict = {} rowvalues = table.row_values(i) for x in range(colnum): data_dict[row1_values[x]] = rowvalues[x] file_lists.append(data_dict) return file_lists def __update_sheet(self, table, sheet_names): """ 将sheet1中有路径坐标的path[m,n]单元格，使用对应sheet中m,n行数据替换 :param table: :param sheet_names: :return: """ file_lists = self.__sheet1(table) params_lists = [] for data_dict in file_lists: for sheet_name in sheet_names: if sheet_name in data_dict.keys(): position = re.findall('\d', data_dict[sheet_name]) # 匹配入参嵌套对象坐标 sheet_others_lists = self.__sheet_others(sheet_name, position[0], position[1]) data_dict[sheet_name] = sheet_others_lists params_lists.append(data_dict) return params_lists def read_excel_more_sheets(self): """ 获取测试数据 :return: """ # 获取所有sheet名字 sheet_names = self.data.sheet_names() # 打开sheet1 table = self.data.sheet_by_index(0) params_lists = self.__update_sheet(table, sheet_names) return params_lists def read_excel_file(self): """ 读excle文件 """ table = self.data.sheet_by_index(0) keys = table.row_values(0) rownum = table.nrows colnum = table.ncols if rownum <= 1: print('Can not find TestCase') else: file_list = [] for i in range(2, rownum): data_list = {} values = table.row_values(i) for x in range(colnum): data_list[keys[x]] = values[x] file_list.append(data_list) return file_list def judge_isvalid(file_path_testcase): """调用读配置文件""" try: data = xlrd.open_workbook(file_path_testcase) except FileNotFoundError: find_testcase_error = 'Can Not Find TestCase' return find_testcase_error except xlrd.biffh.XLRDError: open_testcase_error = 'Can Not Open TestCase' return open_testcase_error else: sheet_names = data.sheet_names() # 无嵌套请求参数类型 if len(sheet_names) == 1: test_cases = ReadReqParams(data).read_excel_file() # 有嵌套请求参数类型 elif len(sheet_names) > 1: test_cases = ReadReqParams(data).read_excel_more_sheets() else: test_cases = 'No Sheet' return test_cases file_path_testcase = '' test_cases = judge_isvalid(file_path_testcase) # 测试用例集