python 数据导出为excel

python 数据导出为excel

工作中需要把数据导入到excel中，记录一下操作方式

---

一、需要使用的第三方包

openpyxl

二、使用步骤

1.引入库

代码如下（示例）：

import datetime
import os
from typing import List, Dict, Any
from flask import current_app
from openpyxl import Workbook
from openpyxl.styles import Alignment

from app.configs.constant import 
from app.utils.helpers import get_uuid
from collections import namedtuple

Title = namedtuple("Title", ["start", "end", "name", "field_name"])
DIR_PATH, UPLOAD_PATH = "xxxx/xxx","xxx/xxx"  # 定义目录地址

class OrderExportMeta(type):
    def __init__(cls, what, base, dict):
        cells = []

        fields = {}

        for k, v in dict.items():
            if isinstance(v, Title):
                cells.append(v)

                if v.field_name is not None:
                    fields[v.field_name] = v

        cls.__cells__ = cells
        cls.__fields__ = fields

        super().__init__(what, base, dict)


class OrderExport(metaclass=OrderExportMeta):
    file_dir = "default"

    def __init__(self):

        self.wb = Workbook()
        self.ws = self.wb.active
        self.th = datetime.datetime.now()

        self.file_name = f"{get_uuid()}.xlsx"
        self.relative_path = f"/export/{self.file_dir}/{self.th.year}/{str(self.th.month).zfill(2)}"
        self.path = os.path.abspath(DIR_PATH + UPLOAD_PATH + self.relative_path)

        if not os.path.exists(self.path):
            os.makedirs(self.path)

    @property
    def fields(self):
        return self.__fields__

    @property
    def cells(self):
        return self.__cells__

    def create_excel_title(self):

        for cell in self.cells:
            if cell.start != cell.end:
                self.ws.merge_cells(f"{cell.start}:{cell.end}")
            th_cell = self.ws[cell.start]
            th_cell.value = cell.name
            th_cell.alignment = Alignment(horizontal='center', vertical='center')
	def get_uuid(self,non_separator=False):
		"""生成文件名序列号"""
	    uid = str(uuid.uuid4())
	    if non_separator:
	        uid = uid.replace("-", "")
    	return uid
    def save(self, datas: List[Dict[str, Any]], start_row=2, sub_path="inventory_order"):
        self.create_excel_title()

        for data in datas:
            for k, v in data.items():
                title = self.fields.get(k)

                if title:
                    self.ws[f"{title.start[:-1]}{str(start_row)}"] = v
            start_row += 1

        path = os.path.abspath(self.path + "/" + self.file_name)
        self.wb.save(path)
        self.wb.close()

        return f"{current_app.config['EXPORT_FILE_HOST']}/dms-app-assets-ms{self.relative_path}/{self.file_name}"



class BaseInfoExport(OrderExport):
    """自定义导出的数据结构"""
    file_dir = 'xxx/xxxx' # 自定义导出的目录地
    A1_A1 = Title(start="A1", end="A1", name="名字", field_name="name")
    B1_B1 = Title(start="B1", end="B1", name="编码", field_name="code")
    C1_C1 = Title(start="C1", end="C1", name="年纪", field_name="age")
    .....
    AA1_AA1 = Title(start="AA1", end="AA1", name="地址", field_name="地址") #超过z列的写法


if __name__ == "__main__":
	exporter = BaseInfoExport()
	data = {} # 字典结构数据
	excel_url = exporter.save(data)

---

总结

例如：以上就是今天要讲的内容，本文仅仅简单介绍了openpyxl的使用