pytest_src/Data/DataAccess.py

# -*- coding: UTF-8 -*-
"""
@Project ：pytest_src 
@File    ：DataAccess.py
@Author  ：haojiang
@Date    ：2022/9/24 15:43 
"""
from enum import Enum

from openpyxl import load_workbook

CHECK_LIST_FILE = r'./Data/CheckList.xlsx'

class ColumHeader(Enum):
    Hardware = 0
    Project = 1
    Module = 2
    ConfigurationItem = 3
    Type = 4
    ExpectValue = 5
    SearchPattern = 6
    SearchText = 7
    SearchRule = 8


class ItemType(Enum):
    FIXED_VALUE = 0
    CUSTOM_PATTERN = 1
    DEPENDENCY_PATTERN = 2

class HardwareType(Enum):
    ALL = 0
    GW04 = 1
    CCP = 2

class ProjectType(Enum):
    ALL = 0
    AS33 = 1
    AS33P = 2

ItemRule = {
    ItemType.FIXED_VALUE: (
        ColumHeader.Module.value,
        ColumHeader.ConfigurationItem.value,
        ColumHeader.ExpectValue.value),
    ItemType.CUSTOM_PATTERN: (
        ColumHeader.Module.value,
        {
            'xpath_str': ColumHeader.SearchPattern.value,
            'xpath_text': ColumHeader.SearchText.value,
            'xpath_rule': ColumHeader.SearchRule.value
        },
        ColumHeader.ConfigurationItem.value,
        ColumHeader.ExpectValue.value),
    ItemType.DEPENDENCY_PATTERN: (
        ColumHeader.Module.value,
        ColumHeader.ConfigurationItem.value,
        ColumHeader.ExpectValue.value,
        ColumHeader.SearchPattern.value
    )

}


def get_item_by_item_type(item_type: ItemType, row_data):
    ret_data = list()
    for pattern in ItemRule[item_type]:
        if isinstance(pattern, dict):
            pattern_bk = dict()
            for k, v in pattern.items():
                pattern_bk[k] = row_data[v].value
        else:
            pattern_bk = row_data[pattern].value
        ret_data.append(pattern_bk)

    return tuple(ret_data)

def get_xlsx_data_by_filter(hardware: HardwareType, project: ProjectType, item_type: ItemType):

    wb = load_workbook(CHECK_LIST_FILE)
    sheet1 = wb.active

    ret_data_table = list()

    for row in sheet1.iter_rows():
        if row[ColumHeader.Type.value].value == item_type.name \
                and (row[ColumHeader.Hardware.value].value == hardware.name
                     or row[ColumHeader.Hardware.value].value == HardwareType.ALL.name) \
                and (row[ColumHeader.Project.value].value == project.name
                     or row[ColumHeader.Project.value].value == ProjectType.ALL.name):
            ret_data_table.append(get_item_by_item_type(item_type, row))

    return ret_data_table




if __name__ == '__main__':
    table = get_xlsx_data_by_filter(ItemType.CUSTOM_PATTERN)
    print(table)