效果如下：

原文件內容

轉化后的內容

大致代碼如下：

1. load_it.py

#!/usr/bin/env python
import re
from datetime import datetime
from io import BytesIO
from pathlib import Path
from typing import List, Unionfrom fastapi import HTTPException
from openpyxl import load_workbookRE_SPACES = re.compile(r"\s{2,}")def slim(s: str) -> str:return RE_SPACES.sub(" ", s)class ValidationError(HTTPException):def __init__(self, detail: str, status_code: int = 400):super().__init__(status_code, detail=detail)def remove_author(s: str) -> str:if s := s.replace("作者:\n", "").replace("Administrator:\n", ""):return str(s)return ''def read_excel(filename: Union[Path, str, bytes, BytesIO]):if isinstance(filename, bytes):filename = BytesIO(filename)return load_workbook(filename)def load(filename: Union[Path, str, bytes, BytesIO]):wb = read_excel(filename)sheet_name = "工資表"try:sheet = wb[sheet_name]except KeyError:try:sheet = wb["Sheet1"]except KeyError:raise ValidationError(f"未找到名稱為{sheet_name!r}的工作表")title = sheet.cell(1, 1).value.strip()now = datetime.now()if "月" in title:remark = title.split("年")[-1].strip("表").replace("份", "")else:if (month := now.month - 1) == 0:month = 12remark = f"{month}月工資"day = f"{now:%Y.%m.%d}"lines: List[list] = []for row in range(4, sheet.max_row):xuhao = sheet.cell(row, 1).valueif xuhao and (isinstance(xuhao, int) or xuhao.isdigit()):name = sheet.cell(row, 2).valuetotal = 0if (base := sheet.cell(row, 4).value) is None:base = "/"else:if isinstance(base, str):if base.startswith("="):base = eval(base[1:])else:raise TypeError(f"Expect int value, got: {base=}")total += basecommission_comment = ""  # 提成批注commission_cell = sheet.cell(row, 5)if (commission := commission_cell.value) is None:commission = "/"else:if isinstance(commission, str) and commission.startswith('='):commission = eval(commission[1:])total += commissionif _cc := commission_cell.comment:if _ct := _cc.text:commission_comment = remove_author(_ct)if (attend := sheet.cell(row, 6).value) is None:if (attend := sheet.cell(row, 13).value) is None:attend = "/"if (attend_money := sheet.cell(row, 7).value) is not None:total += attend_moneyattend = attend.strip().strip("+-/").strip()if attend_money > 0:attend += f" +{attend_money}"else:attend += f" {attend_money}"if (late := sheet.cell(row, 8).value) is None:late = "/"else:late = slim(late)if late_money := sheet.cell(row, 9).value:total += late_moneyif late_money > 0:late = f"{late}{late_money}"else:late = late.strip("/") + str(late_money)if subsidy_value := sheet.cell(row, 11).value:  # 補助if isinstance(subsidy_value, str) and subsidy_value.startswith("="):subsidy_value = eval(subsidy_value[1:])try:total += subsidy_valueexcept TypeError:raise ValidationError(f"第{row}行第11列數據異常：預期為數值，得到的是{subsidy_value!r}")subsidy = "/"if _c := sheet.cell(row, 10).comment:if _s := _c.text:subsidy = remove_author(_s)one = [name,base,commission,attend,late,subsidy,total,remark,day,commission_comment,]lines.append(one)return linesdef main():import sysif not sys.argv[1:]:print("No args, do nothing.")returnprint(load(sys.argv[1]))if __name__ == "__main__":main()

?

?2. gen_excel.py

#!/usr/bin/env python
from datetime import datetime
from pathlib import Path
from typing import List, Optional, Tuple, Unionimport xlrd
import xlwt
from xlutils.copy import copy as xls_copyfrom load_it import load, read_excel, remove_author
from settings import BASE_DIR, MEDIA_ROOTSAMPLE = "salary_tips.xls"
DataType = Union[int, float, str, None]def cell_style(is_top: bool = False, is_bottom: bool = False, has_border=True):"""單元格樣式"""style = xlwt.XFStyle()# 字體大小，11為字號，20為衡量單位# font = xlwt.Font()style.font.height = 20 * 9align = xlwt.Alignment()# 0x01(左端對齊)、0x02(水平方向上居中對齊)、0x03(右端對齊)align.horz = 0x02# 0x00(上端對齊)、 0x01(垂直方向上居中對齊)、0x02(底端對齊)align.vert = 0x01# 設置自動換行align.wrap = 1style.alignment = align# 設置邊框# 細實線:1，小粗實線:2，細虛線:3，中細虛線:4，大粗實線:5，雙線:6，細點虛線:7# 大粗虛線:8，細點劃線:9，粗點劃線:10，細雙點劃線:11，粗雙點劃線:12，斜點劃線:13if has_border:borders = xlwt.Borders()borders.left = 2borders.right = 2borders.top = 1 + is_topborders.bottom = 1 + is_bottomstyle.borders = bordersreturn styledef boom(tips: List[List[Tuple[int, int, DataType]]]) -> str:"""將數據填入模板生成Excel表"""sample = BASE_DIR / SAMPLExls = xls_copy(xlrd.open_workbook(sample, formatting_info=True))ws = xls.get_sheet(0)style = cell_style()top_style = cell_style(is_top=True)bottom_style = cell_style(is_bottom=True)plain_style = cell_style(has_border=False)last_index = 8for datas in tips:for i, d in enumerate(datas[:-1]):if i == 0:ws.write(*d, top_style)elif i == last_index:ws.write(*d, bottom_style)else:ws.write(*d, style)if _tc := datas[-1]:row, col, text = _tcif text:ws.write_merge(row, row, col - 1, col, text, plain_style)fname = MEDIA_ROOT / f"gzt_{datetime.now():%Y%m%d%H%M%S}.xls"try:xls.save(fname)except TypeError as e:print("May be you can look at this to fix it:")print("https://blog.csdn.net/zhangvalue/article/details/105170305")raise ereturn str(fname).replace(str(BASE_DIR), "")  # 返回相對路徑def build_tips(lines: List[List[DataType]]):row_delta = 10  # 每隔10行填下一排的數據col_delta = 3  # 每隔3列填下一組數據line_tip = 5  # 每行有5個工資條row_begin = 0  # 從第一行開始col_begin = 1  # 從第二列開始填數據（第一列是固定的表頭）tips = []for tip_index, tip in enumerate(lines):first_row = row_begin + tip_index // line_tip * row_deltacol_index = col_begin + tip_index % line_tip * col_deltad = [(row_index + first_row, col_index, value)for row_index, value in enumerate(tip)]tips.append(d)return tipsdef burn_life(content: bytes) -> str:return boom(build_tips(load(content)))def dear_sister(content: bytes, origin_name: Optional[str] = None) -> str:"""2022-04-04 親愛的妹妹想要一個可以把批注提取出來的"""wb = read_excel(content)sheet = wb.worksheets[0]count = 0# openpyxl的行和列都是從1開始for row in range(1, sheet.max_row):for col in range(1, sheet.max_column):cell = sheet.cell(row, col)if comment := cell.comment:if text := comment.text:cell.value = remove_author(text)count += 1if origin_name:fname = MEDIA_ROOT / f"{Path(origin_name).stem}-批注提取{count}.xls"else:fname = MEDIA_ROOT / f"批注提取{count}.xls"wb.save(fname)return str(fname).replace(str(BASE_DIR), "")  # 返回相對路徑def main():import sysif not sys.argv[1:]:print("No args, do nothing.")returnif (p := Path(sys.argv[1])).is_file():lines = load(p.read_bytes())else:day = f"{datetime.now():%Y.%m.%d}"ss = ["狄仁杰",1600,360,"休5天，請假7.5天 -400","遲到3次共16分鐘","扣社保-373\n工齡+100\n漏刷卡6次-300",987,"12月工資",day,]lines = [ss, ss]print(boom(build_tips(lines)))if __name__ == "__main__":main()

? ??