read_excel.py 5.44 KB
Newer Older
Ao Chen committed
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152
# -*-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):
        """获取指定单元格数据"""
        #" hello world " 在if判断中成立
        value = self.ws[column+str(row)].value
        #第一个value是验证value是否是一个有效的、非空的字符串,并且没有前后空白字符。
        if isinstance(value, str) and value.strip():
            #再次调用 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())