Python知识分享：openpyxl导入Excel工具类

不知道说啥了，直接上干货

from openpyxl import load_workbook

class ExcelUtils:

    """
    导入Excel
    header_dic: 表头字典（表头字段大于等于Excel中的表头）
    {
        'name': '姓名',
        'sex': '性别',
        'age': 'age',
        'email': '邮箱'
    }
    """
    def import_excel(self, *, content: bytes=None, filename: str=None, header_dic: dict) -> list:
        if content:
            # 创建临时文件
            filename = "content_temp.xlsx"
            # 将字节码转为文件
            with open(filename, "wb") as f:
                f.write(content)
        elif filename:
            pass
        else:
            raise Exception("请传入有效的Excel")
        # 加载excel
        try:
            wb = load_workbook(filename=filename)
        except Exception as e:
            raise Exception("请传入有效的Excel")
        # 获取标签页
        sheet = wb.sheetnames[0]
        # 得到整个标签页的数据
        values = wb[sheet].values
        # 记录读取的行标
        start_row = 0
        # 记录表头列标
        header_col = {}
        # 记录最终数据
        data_list = []

        for val in values:
            start_row += 1
            # 表头
            if start_row == 1:
                if set(val).difference(set(header_dic.values())):
                    # 排除空列
                    new_val = tuple(x for x in val if x is not None)
                    if set(new_val).difference(set(header_dic.values())):
                        raise Exception("Excel表头与标准不一致")
                    else:
                        for key, title in header_dic.items():
                            col = 0
                            for item in val:
                                if item and item == title:
                                    header_col.update({key: col})
                                    break
                                col += 1
            else:
                # 跳过空行
                if not all(item is None for item in val):
                    # 记录每一行的数据
                    new_model = {}
                    for key, col in header_col.items():
                        new_model.update({key: val[col]})
                    data_list.append(new_model)

        wb.close()
        return data_list


excel = ExcelUtils()
header_dic = {
    'name': '姓名',
    'sex': '性别',
    'age': 'age',
    'email': '邮箱',
    'addr': '地址',
    'time': '时间'
}
data = excel.import_excel(filename="test.xlsx", header_dic=header_dic)
print(data)