定义Excel列对象
class ExcelColumn:
"""
定义Excel中的列
参数:
name (str): 列的名称。
width (int | None, 可选): 列的宽度。默认为 None。
required (bool, 可选): 指示列是否必需。默认为 False。
mapping_factory (Callable | dict | None, 可选): 实例的映射工厂。
可以是可调用对象、字典或 None。默认为 None。
update (bool, 可选): 指示列是否应该更新，用于ExcelWriter写入的时候是否覆盖写入。默认为 True。
"""
def __init__(self, name: str, *, width: int | None = None, required=False,
mapping_factory: Callable | dict | None = None, update=True):
self.name = name if not required else f'*{name}'
self.width = width
self.required = required
self.mapping_keys = []
self.mapping_reverse = None
if not mapping_factory:
self.mapping = lambda x: x
elif isinstance(mapping_factory, dict):
mapping_factory_reverse = {v: k for k, v in mapping_factory.items()}
self.mapping = lambda x: mapping_factory.get(x)
self.mapping_reverse = lambda x: mapping_factory_reverse.get(x)
self.mapping_keys = list(mapping_factory.keys())
else:
self.mapping = mapping_factory
self.update = update
定义Excel模板元类
class ExcelTemplateMetaclass(type):
def __new__(cls, name, bases, attrs):
if name == 'ExcelTemplate':
return type.__new__(cls, name, bases, attrs)
columns = dict() # 保存Excel的列信息
for k, v in attrs.items():
if isinstance(v, ExcelColumn):
columns[k] = v
for k in columns.keys():
attrs.pop(k)
attrs['__columns__'] = columns
return type.__new__(cls, name, bases, attrs)
定义Excel模板基类
class ExcelTemplate(metaclass=ExcelTemplateMetaclass):
__sheet_name__ = 'Sheet1'
__columns__: dict[str, ExcelColumn]
headers = {
'Content-Type': 'application/vnd.ms-excel',
'Content-Disposition': f'attachment;filename=download.xlsx'
}
@staticmethod
async def get_download_data(query: QuerySet, limit: int=None):
"""传入一个querySet，最后导对应的数据，limit为最多允许导出的数据"""
if not limit:
limit = settings.EXCEL_DOWNLOAD_LIMIT
pagination = query.limit(limit).offset(0) # 最多允许导出100W的数据
return await pagination.all()
@classmethod
def _get_upload_title_mapping(cls):
return {v.name: k for k, v in cls.__columns__.items()}
@classmethod
def _get_upload_value_mappings(cls):
return {v.name: v.mapping for k, v in cls.__columns__.items()}
@staticmethod
def _get_data_validation(columnIndex, excelColumn: ExcelColumn):
dv = DataValidation(
type='list',
formula1=f'"{",".join(excelColumn.mapping_keys)}"',
allow_blank=not excelColumn.required
)
dv.error = f"{excelColumn.name}只能从str({excelColumn.mapping_keys})中选择"
dv.errorTitle = "无效的输入"
dv.prompt = '请从下拉框中选择数据'
dv.promptTitle = f"{excelColumn.name}选择"
columnName = get_column_letter(columnIndex)
dv.add(f'{columnName}{2}:{columnName}{11}') # 数据验证区域,2-11行
return dv
@classmethod
def _set_data_validation(cls, sheet):
for index, (k, v) in enumerate(cls.__columns__.items(), start=1):
if v.mapping_keys:
dv = cls._get_data_validation(index, v)
sheet.data_validations.append(dv)
@classmethod
def get_title(cls):
return [val for val, _ in cls._get_title()]
@classmethod
def get_update_title(cls):
return [k for k, v in cls.__columns__.items() if v.update]
@classmethod
def _get_title(cls):
return [
(v.name, v.width) for k, v in cls.__columns__.items()
]
@classmethod
def _get_title_mapping(cls):
return {v.name: k for k, v in cls.__columns__.items()}
@classmethod
def get_template(cls) -> BytesIO:
file = BytesIO()
excel = ExcelWriter(file)
sheet = excel.write(cls.__sheet_name__, [], title=cls.__columns__.values())
cls._set_data_validation(sheet)
excel.save()
file.seek(0)
return file
@classmethod
def get_template_as_stream_response(cls) -> StreamingResponse:
return StreamingResponse(cls.get_template(), media_type='xls/xlsx', headers=cls.headers)
@classmethod
def save_template_as_file(cls, filename: str):
with open(filename, 'wb') as f:
f.write(cls.get_template().getvalue())
@classmethod
def read(cls, file: bytes | str) -> "":
def get_value(key, value):
mapping = value_mappings.get(key)
if not mapping:
return value
return mapping(value)
def get_title(key):
return title_mapping.get(key, key)
title_mapping = cls._get_upload_title_mapping()
value_mappings = cls._get_upload_value_mappings()
if isinstance(file, bytes):
data = BytesIO()
data.write(file)
data.seek(0)
excel = ExcelReader(data)
else:
excel = ExcelReader(file)
return (
{get_title(k): get_value(k, v) for k, v in row.items()}
for row in excel.read(sheet=cls.__sheet_name__) if any(row.values())
)
@classmethod
def write(cls, data):
def translate_row_values(row_values):
"""将行数据转换为excel可以显示的值，主要是为了将后台枚举的数字转换为用户可读的字符"""
result = {}
for k, v in dict(row_values).items():
# 没有定义的列不会被导入到Excel中
if k not in cls.__columns__:
continue
out_key = cls.__columns__[k].name
# 处理时区问题，Excel保存时不支持时区参数
if isinstance(v, datetime):
result[out_key] = v.replace(tzinfo=None)
continue
# 将枚举数据转换为具体的值
mapping_reverse = cls.__columns__.get(k).mapping_reverse
if mapping_reverse:
result[out_key] = mapping_reverse(v)
continue
result[out_key] = v
return result
file = BytesIO()
excel = ExcelWriter(file)
sheet = excel.write(
cls.__sheet_name__,
(translate_row_values(row) for row in data), # 处理待写入的数据
title=cls.__columns__.values()
)
cls._set_data_validation(sheet)
excel.save()
file.seek(0)
return file
@classmethod
def write_as_stream_response(cls, data):
"""保存问一个文件响应对象，用于返回给前端"""
return StreamingResponse(cls.write(data), media_type='xls/xlsx', headers=cls.headers)
@classmethod
def write_as_file(cls, filename: str, data):
"""将数据保存为一个文件"""
with open(filename, 'wb') as f:
f.write(cls.write(data).getvalue())
使用ExcelTemplate管理模板
class OrderExcelTemplate(ExcelTemplate):
id = ExcelColumn('id', required=True)
orderNo = ExcelColumn('订单号', required=True)
price = ExcelColumn('价格', required=False)
OrderExcelTemplate.save_template_as_file('order_template.xlsx')
