本文已参与「新人创作礼」活动，一起开启掘金创作之路。
需求背景：根据前端传来的数据，生成固定格式的Excel表，保存到服务器，并返回下载链接
依赖包版本：
[tool.poetry.dependencies]
python = "^3.10"
fastapi = "^0.75.2"
uvicorn = "^0.17.6"
xlrd = "^2.0.1"
xlwt = "^1.3.0"
python-dotenv = "^0.20.0"
openpyxl = "^3.0.9"
aioredis = {extras = ["hiredis"], version = "^2.0.1"}
httpx = "^0.22.0"
gunicorn = "^20.1.0"
loguru = "^0.6.0"
关键模块代码如下：
from datetime import datetime
from enum import Enum, IntEnum
from pathlib import Path
from typing import List, Optional, Tuple, Union
from openpyxl import Workbook
from openpyxl.styles import Alignment, Border, Font, Side
from settings import BASE_DIR, MEDIA_ROOT
MAI_TOU_ICON = "⬆️"
MONEY_FMT = "￥#,##0.00"
CENTER_ALIGN = Alignment(horizontal="center", vertical="center")
class ColorEnum(str, Enum):
red = "FF0000"
class CollumnEnum(IntEnum):
xuhao = 1
customer = 2
factory = 3
size = 4
juan = 5
auto_jian = 6
price = 7
total = 8
pack = 9
mai_tou = 10
def thin_border() -> Border:
thin = Side(border_style="thin", color="000000")
return Border(top=thin, left=thin, right=thin, bottom=thin)
def write_order_num(ws, value: str) -> None:
cell = ws["B1"]
cell.value = value
cell.font = Font(size=36, underline="singleAccounting")
cell.alignment = CENTER_ALIGN
def write_special(ws, value: str) -> None:
cell = ws["A2"]
cell.value = value
# cell = ws.cell(2, 1, value)
cell.font = Font(color=ColorEnum.red)
cell.alignment = Alignment(vertical="center")
def write_date(ws, value: str) -> None:
cell = ws.cell(1, 10, value)
cell.alignment = Alignment(horizontal="right")
cell.font = Font(size=16)
def fill_row_one_and_row_two(ws, order_num, special, now) -> Border:
ws.merge_cells("B1:I1")
ws.merge_cells("A2:J2")
ws.row_dimensions[1].height = 51.2
ws.row_dimensions[2].height = 29.1
col_widths = [7, 14.15, 18.14, 26.71, 12.92, 12.92, 11.86, 13.48, 39.73, 35.86]
first_ord = ord("A")
for col_ord, width in enumerate(col_widths, first_ord):
ws.column_dimensions[chr(col_ord)].width = width
write_order_num(ws, order_num)
write_special(ws, special)
date = f"日期：{now:%m.%d} "
write_date(ws, date)
border = thin_border()
cell_range = ws["A1":"J2"] # type:ignore
for row in cell_range:
for cell in row:
cell.border = border
return border
def fill_title(ws, border) -> None:
ws.row_dimensions[4].height = 44
titles = "序号 客户款号 工厂款号 尺寸 卷数 件数 单价 总金额 包装要求⚠️ 唛头"
for col, t in enumerate(titles.split(), 1):
cell = ws.cell(4, col, t)
cell.border = border
cell.alignment = CENTER_ALIGN
cell.font = Font(size=16)
def fill_body(ws, border, lines) -> int:
start_row = 5
row_height = 124.5
body_font = Font(size=12)
mai_font = Font(size=36)
mai_align = Alignment(horizontal="center", vertical="center", wrap_text=True)
for row, data in enumerate(lines, start_row):
ws.row_dimensions[row].height = row_height
col_value = {c + 1: v for _, c, v in data}
if size := col_value.get(CollumnEnum.size):
if isinstance(size, int) or size.isdigit():
col_value[CollumnEnum.size] = f"0.45*{size}米/卷"
col_value[CollumnEnum.total] = f"=E{row}*G{row}"
if (v := col_value[CollumnEnum.mai_tou]).startswith(MAI_TOU_ICON):
v = v.replace(MAI_TOU_ICON, "").strip()
col_value[CollumnEnum.mai_tou] = MAI_TOU_ICON + "\n" + v
for col in CollumnEnum:
cell = ws.cell(row, col, col_value.get(col, ""))
cell.border = border
cell.alignment = CENTER_ALIGN
cell.font = body_font
ws[f"G{row}"].number_format = ws[f"H{row}"].number_format = MONEY_FMT
ws[f"J{row}"].font = mai_font
ws[f"J{row}"].alignment = mai_align
return row
def fill_tail(ws, border, row) -> None:
formula = "=SUM({0}5:INDEX({0}:{0},ROW()-1))"
col_value = {"B": "合计", "E": None, "H": None}
tail_font = Font(size=12)
ws.row_dimensions[row].height = 24
for col in "ABCDEFGHIJ":
if (value := col_value.get(col, "")) is None:
value = formula.format(col)
cell = ws[f"{col}{row}"]
cell.value = value
cell.border = border
cell.alignment = CENTER_ALIGN
cell.font = tail_font
ws[f"H{row}"].number_format = MONEY_FMT
def run(
lines: List[List[Tuple[int, int, str]]],
fname: Optional[Union[Path, str]] = None,
order_num: Optional[str] = None,
special="特殊事项： 纸管+纸箱+唛头和货号",
) -> str:
now = datetime.now()
wb = Workbook()
ws = wb.active
border = fill_row_one_and_row_two(ws, order_num, special, now)
ws.row_dimensions[3].height = 16.8
fill_title(ws, border)
last_row = fill_body(ws, border, lines)
fill_tail(ws, border, last_row + 1)
if fname is None:
fname = MEDIA_ROOT / f"order_{order_num}_{now:%Y%m%d%H%M%S}.xls"
wb.save(fname)
return str(fname).replace(str(BASE_DIR) + "/", "") # 返回相对路径
def main():
from data_sample import datas
num = len(list(MEDIA_ROOT.glob("*"))) + 1
fn = run(datas, None, f"305-{num}")
print(fn)
if __name__ == "__main__":
main()
