通过openpyxl读取表格信息

from openpyxl import load_workbook


class HandleExcel:
    def __init__(self, file_name, sheet_name):
        self.workbook_object = load_workbook(filename=file_name)
        self.sheet_object = self.workbook_object[sheet_name]

    def get_excel_tese_case(self):
        cases_list = []

        datas = list(self.sheet_object.iter_rows(values_only=True))  # 获取Excel表中的所有数据，按行显示，先是第一行的内容

        # 将Excel表中的数据拼成字典

        case_title = datas[0]  # 获取表头
        case_datas = datas[1:]  # 获取表数据
        for case in case_datas:
            result = dict(zip(case_title, case))
            cases_list.append(result)
        self.close_file()
        print(cases_list)
        return cases_list

    def get_ecu_id_dict(self):
        ecu_id_dict = dict()
        cases_list = self.get_excel_tese_case()
        for case in cases_list:
            ecu_id_dict[str(case.get("ECU_ID_HEX"))] = str(case.get('ECU_ID_DEC'))
        print(ecu_id_dict)
        return ecu_id_dict

    def get_ecu_name_dict(self):
        ecu_id_dict = dict()
        cases_list = self.get_excel_tese_case()
        for case in cases_list:
            ecu_id_dict[str(case.get("ECU_ID_DEC"))] = str(case.get('ECU'))
        print(ecu_id_dict)
        return ecu_id_dict

    def get_uds_ecu_ids_by_name(self):
        uds_id_dict = dict()
        cases_list = self.get_excel_tese_case()
        for case in cases_list:
            uds_id_dict[str(case.get('ECU'))] = str(case.get("ECU_ID_HEX"))
        print(uds_id_dict)
        return uds_id_dict

    def get_ecu_names(self):
        ecu_name_list = list()
        cases_list = self.get_excel_tese_case()
        for case in cases_list:
            ecu_name_list.append(str(case.get('ECU')))
        print(ecu_name_list)
        return ecu_name_list

    def close_file(self):
        self.workbook_object.close()


if __name__ == '__main__':
    cl = HandleExcel(file_name='./files/表格名称.xlsx', sheet_name='Sheet1')
    # cl.get_excel_tese_case()
    # ecu_id_dict = cl.get_ecu_id_dict()
    # ecu_name_dict = cl.get_ecu_name_dict()
    # uds_id_dict = cl.get_uds_ecu_ids_by_name()
    ecu_name_list = cl.get_ecu_names()