# -*-coding:utf-8 -*- # # --------------------------------------------------------------------------- # ProjectName: test64 # FileName: read_excel.py # Author: lao_zhao # Datetime: 2024/12/26 14:05 # Description: # # --------------------------------------------------------------------------- import openpyxl from APIAutoTest_v3 import log_decorator from APIAutoTest_v3.common.read_basic_ini import ReadBasicIni from APIAutoTest_v3.common.read_json import read_json from APIAutoTest_v3.common.read_user_ini import ReadUserIni class ReadExcel: @log_decorator def __init__(self, username): """获取excel和所有json文件的路径,及excel工作表的名称,获取工作簿和工作表,获取所有json文件数据对应的python对象""" # 创建ReadBasicIni类对象,主要是用来获取被测系统的域名 self.basic_ini = ReadBasicIni() # 创建ReadUserIni类对象-获取的是用户的数据 self.ini = ReadUserIni(username) case_data_path = self.ini.get_file_path("case") expect_data_path = self.ini.get_file_path("expect") sql_data_path = self.ini.get_file_path("sql") excel_path = self.ini.get_file_path("excel") table_name = self.ini.get_table_name("name") wb = openpyxl.load_workbook(excel_path) self.ws = wb[table_name] self.case_data_dict = read_json(case_data_path) self.expect_data_dict = read_json(expect_data_path) self.sql_data_dict = read_json(sql_data_path) @log_decorator def __get_cell_value(self, column, row): """获取指定单元格数据""" value = self.ws[column+str(row)].value if isinstance(value, str) and value.strip(): return value.strip() @log_decorator def module_name(self, row): """根据行,获取模块名称""" return self.__get_cell_value("b", row) @log_decorator def api_name(self, row): """根据行,获取接口名称""" return self.__get_cell_value("c", row) @log_decorator def case_title(self, row): """根据行,获取用例的标题""" return self.__get_cell_value("d", row) @log_decorator def case_level(self, row): """根据行,获取用例的等级""" return self.__get_cell_value("e", row) @log_decorator def case_url(self, row): """根据行,获取用例的url""" path = self.__get_cell_value("f", row) if path: return self.basic_ini.get_host("bpm") + path @log_decorator def case_mime(self, row): """根据行,获取用例的媒体类型""" mime = self.__get_cell_value("g", row) if mime: return mime.lower() @log_decorator def case_req(self, row): """根据行,获取用例的请求方法""" return self.__get_cell_value("h", row) @log_decorator def case_data(self, row): """根据行,获取用例数据""" case_data_key = self.__get_cell_value("i", row) if case_data_key: module_name = self.module_name(row) api_name = self.api_name(row) return self.case_data_dict[module_name][api_name][case_data_key] @log_decorator def expect_data(self, row): """根据行,获取期望数据""" expect_data_key = self.__get_cell_value("j", row) if expect_data_key: module_name = self.module_name(row) api_name = self.api_name(row) return self.expect_data_dict[module_name][api_name][expect_data_key] @log_decorator def sql_data(self, row): """根据行,获取sql数据""" sql_data_key = self.__get_cell_value("l", row) if sql_data_key: module_name = self.module_name(row) api_name = self.api_name(row) return self.sql_data_dict[module_name][api_name][sql_data_key] @log_decorator def sql_type(self, row): """根据行,获取sql语句的类型""" sql_statement = self.__get_cell_value("k", row) if sql_statement: return sql_statement.lower() @log_decorator def update_key(self, row): """根据行,获取更新的key""" return self.__get_cell_value("m", row) @log_decorator def get_data(self): """获取所有的测试数据,存放在二维列表中""" list_data = [] for row in range(2, self.ws.max_row+1): url = self.case_url(row) mime = self.case_mime(row) req = self.case_req(row) case = self.case_data(row) expect = self.expect_data(row) sql_type = self.sql_type(row) sql = self.sql_data(row) update = self.update_key(row) module = self.module_name(row) api = self.api_name(row) title = self.case_title(row) level = self.case_level(row) if url is not None and req is not None and expect is not None: list_data.append([module, api, title, level, url, mime, req, case, expect, sql_type, sql, update]) else: return list_data if __name__ == '__main__': excel = ReadExcel("zs") print(excel.get_data())