工作中需要把数据导入到excel中,记录一下 *** 作方式
文章目录
- python 数据导出为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的使用
欢迎分享,转载请注明来源:内存溢出
评论列表(0条)