Python 办公自动化实战：批量处理 Excel 与格式转换

Python 办公自动化实战：批量处理 Excel 与格式转换

《Python 实战》应用项目篇 · 第 7 篇
作者按：本文所有命令均在真实云服务器上手工执行，回显为原样复制，未做任何修饰或编造。

一、背景：为什么办公自动化值得单独写一篇

在公司里，最消耗人力的往往不是「写代码」，而是「把一堆 Excel 表拷来拷去、合并、改格式、再导出成别的系统要的 JSON」。我见过一个真实场景：月末五个部门各自发来一份考勤兼销售表，财务要把它们拼成一张总表、算出每个部门的总额和销冠、再分别导出 CSV 给 BI 系统、导出 JSON 给接口。手工做，两小时起步，还容易粘错行。

Python 处理这类事是降维打击。但「能跑」和「写得专业」是两回事。本篇我用一次完整的实操，把三件容易被忽略的事讲透：

1. 用dataclass给业务数据一个类型安全的模型，而不是永远用dict裸奔；
2. 用openpyxl+pandas做批量生成、合并、带格式汇总，而不是只会df.to_excel；
3. 把xlsx批量转换成csv/json时，搞清楚编码、orient、中文这些坑。

下面所有操作都在/root/lab-d/office下进行，全程真实回显。

二、实验环境（真实回显）

我在一台华为云 Flexus X 实例上操作，系统为 Ubuntu 24.04，自带 Python 3.12，但因为 Ubuntu 24.04 启用了 PEP 668 的 externally-managed-environment 限制，不能直接pip install到系统 Python，所以先建了 venv。依赖版本如下（真实pip list截取）：

== OS == PRETTY_NAME="Ubuntu 24.04.4 LTS" == Python == Python 3.12.3 == 关键依赖 == Django 6.0.7 numpy 2.5.1 opencv-python-headless 5.0.0.93 openpyxl 3.1.5 pandas 3.0.5

环境准备的小插曲：这台机器到 PyPI 镜像的网速只有约 1 MB/s，第一次我用一个前台命令装完全部依赖，本地驱动脚本因为运行时间过长被外壳截断、没拿到回显，但服务器侧其实已经装完了。后来我发现 openpyxl/Django/numpy/opencv/pandas 一个个Requirement already satisfied—— 也就是白担心一场。结论：依赖是否装好，永远以服务器侧pip list为准，不要被本地截断开头的空输出骗了。这也是我后来把 SSH 驱动改成「线程读满直至 EOF」的原因。

三、用 dataclass 定义类型安全的员工记录

很多教程一上来就是rows = []然后往里塞dict。小规模没问题，但一旦字段多起来，row["sales"]拼错成row["sale"]只有运行时才报错，IDE 也给不了提示。我先用dataclass把「员工记录」这个领域模型钉死：

fromdataclassesimportdataclass,asdictfromtypingimportList@dataclassclassEmployeeRecord:"""员工记录：用 dataclass 做类型安全的领域模型。"""name:strdepartment:strattendance_days:intsales_amount:floatdefattendance_rate(self,total_workdays:int=24)->float:returnround(self.attendance_days/total_workdays,4)

好处有三个：第一，字段名和类型一目了然；第二，可以用asdict()直接序列化给 JSON，不用手写字典；第三，后续如果加employee_id: int，所有构造点都会立刻暴露缺参。我用它批量构建了 15 条记录（5 个部门 × 3 人）：

DEPT_DATA={"销售部":[("张伟",22,85000),("李娜",21,92000),("王芳",20,76000)],"市场部":[("刘洋",23,54000),("陈静",22,61000),("赵磊",19,48000)],"研发部":[("孙强",24,38000),("周敏",23,42000),("吴昊",22,35000)],"客服部":[("郑爽",21,29000),("冯雪",20,31000),("何军",22,27000)],"行政部":[("许婷",23,18000),("邓超",22,21000),("曹颖",21,16000)],}defbuild_records()->List[EmployeeRecord]:records=[]fordept,membersinDEPT_DATA.items():forname,days,salesinmembers:records.append(EmployeeRecord(name,dept,days,float(sales)))returnrecords

真实运行输出（节选）：

[数据] 通过 dataclass EmployeeRecord 构建 15 条类型安全记录

四、批量生成 5 个部门 Excel 文件

接下来按部门生成 5 个.xlsx。这里我特意用openpyxl直接写，而不是pandas，因为要顺手把表头做成「白字蓝底居中」——这正是真实办公场景里领导要看的样子。每个文件三列：员工姓名、出勤天数、销售金额。

defgen_dept_files(records:List[EmployeeRecord]):by_dept={}forrinrecords:by_dept.setdefault(r.department,[]).append(r)header_font=Font(bold=True,color="FFFFFF")header_fill=PatternFill("solid",fgColor="4472C4")fordept,membersinby_dept.items():wb=openpyxl.Workbook()ws=wb.active ws.title=dept headers=["员工姓名","出勤天数","销售金额"]ws.append(headers)forcinws[1]:c.font=header_font c.fill=header_fill c.alignment=Alignment(horizontal="center")forminmembers:ws.append([m.name,m.attendance_days,m.sales_amount])ws.column_dimensions["A"].width=12ws.column_dimensions["B"].width=10ws.column_dimensions["C"].width=12path=os.path.join(RAW,f"{dept}.xlsx")wb.save(path)print(f"[生成]{path}({len(members)}名员工)")

真实运行输出：

[生成] /root/lab-d/office/raw/销售部.xlsx (3 名员工) [生成] /root/lab-d/office/raw/市场部.xlsx (3 名员工) [生成] /root/lab-d/office/raw/研发部.xlsx (3 名员工) [生成] /root/lab-d/office/raw/客服部.xlsx (3 名员工) [生成] /root/lab-d/office/raw/行政部.xlsx (3 名员工) [生成] 共 5 个部门文件，位于 /root/lab-d/office/raw

五个文件就这样落盘了。注意这里用的是openpyxl原生写入——它对单元格样式的控制力比pandas强得多，适合「生产报表」；而下面的合并汇总我交给pandas，因为它擅长二维表运算。两者配合，才是正解。

五、批量读取、合并与汇总统计

真实业务里，这五个文件是别人发来的，我得把它们读进来拼成一张大表。用pandas.read_excel逐个读，再concat纵向拼接，并补一列「部门」：

frames=[]fordeptinDEPT_DATA:f=os.path.join(RAW,f"{dept}.xlsx")df=pd.read_excel(f)df.insert(0,"部门",dept)frames.append(df)merged=pd.concat(frames,ignore_index=True)merged.to_excel(merged_path,index=False)

合并后的 15 行（真实回显）：

部门 员工姓名 出勤天数 销售金额 销售部 张伟 22 85000 销售部 李娜 21 92000 销售部 王芳 20 76000 市场部 刘洋 23 54000 市场部 陈静 22 61000 市场部 赵磊 19 48000 研发部 孙强 24 38000 研发部 周敏 23 42000 研发部 吴昊 22 35000 客服部 郑爽 21 29000 客服部 冯雪 20 31000 客服部 何军 22 27000 行政部 许婷 23 18000 行政部 邓超 22 21000 行政部 曹颖 21 16000

接着做汇总：groupby("部门")算总额、均值，并用idxmax找出每个部门的销冠（TOP员工）。我把结果按总额降序排列，更像一张给领导看的报表：

fordept,grpinmerged.groupby("部门"):total=grp["销售金额"].sum()avg=round(grp["销售金额"].mean(),2)top=grp.loc[grp["销售金额"].idxmax()]summary_rows.append({...})summary=pd.DataFrame(summary_rows).sort_values("总销售金额",ascending=False)

真实汇总结果：

部门 员工数 总销售金额 平均销售金额 TOP员工 TOP员工销售额 销售部 3 253000 84333.33 李娜 92000 市场部 3 163000 54333.33 陈静 61000 研发部 3 115000 38333.33 周敏 42000 客服部 3 87000 29000.00 冯雪 31000 行政部 3 55000 18333.33 邓超 21000

读这张表能直接讲故事：销售部总额 25.3 万、销冠李娜 9.2 万；行政部垫底但邓超是部门内相对最高的。数据一合并，结论自己就出来了。

六、写出带格式的汇总报表（不只是能打开）

很多人to_excel完事，但交付物如果表头不突出、列宽挤成一团，显得很不专业。我用了pd.ExcelWriter(engine="openpyxl")在写出后二次加工：表头加粗、绿色填充、水平居中，并显式设置列宽。

withpd.ExcelWriter(report_path,engine="openpyxl")aswriter:summary.to_excel(writer,index=False,sheet_name="部门汇总")wb=writer.book ws=writer.sheets["部门汇总"]forcellinws[1]:cell.font=Font(bold=True,color="FFFFFF")cell.fill=PatternFill("solid",fgColor="2E7D32")cell.alignment=Alignment(horizontal="center")forcol,win{"A":10,"B":8,"C":14,"D":14,"E":10,"F":14}.items():ws.column_dimensions[col].width=w

光说「设置了」不够，我反过来读回文件验证样式是不是真的写进去了（真实回显）：

SHEET: 部门汇总 列宽: {'A': 10.0, 'B': 8.0, 'C': 14.0, 'D': 14.0, 'E': 10.0, 'F': 14.0} 表头字体加粗: [True, True, True, True, True, True] 表头填充色: ['002E7D32', '002E7D32', '002E7D32', '002E7D32', '002E7D32', '002E7D32'] 第1行内容: ['部门', '员工数', '总销售金额', '平均销售金额', 'TOP员工', 'TOP员工销售额']

002E7D32正是我设的绿色（AARRGGBB，前面00是 alpha）。这步「读回验证」很重要：自动化脚本最怕「以为成功了」，用openpyxl.load_workbook再读一次，能堵住九成的格式幻觉。

七、xlsx → csv → json 批量格式转换

报表要给不同系统消费。BI 喜欢 CSV，接口喜欢 JSON。我把合并表导出 CSV，再把各部门数据导出成结构化 JSON。

CSV 导出要注意中文编码：Windows 的 Excel 打开 UTF-8 无 BOM 的 CSV 会乱码，所以用encoding="utf-8-sig"带 BOM。真实 CSV 内容：

部门,员工姓名,出勤天数,销售金额 销售部,张伟,22,85000 销售部,李娜,21,92000 销售部,王芳,20,76000 市场部,刘洋,23,54000 市场部,陈静,22,61000 市场部,赵磊,19,48000 研发部,孙强,24,38000 研发部,周敏,23,42000 研发部,吴昊,22,35000 客服部,郑爽,21,29000 客服部,冯雪,20,31000 客服部,何军,22,27000 行政部,许婷,23,18000 行政部,邓超,22,21000 行政部,曹颖,21,16000

JSON 导出时，我故意先read_excel读回、再用EmployeeRecord这个 dataclass 重建为类型安全对象，最后asdict序列化——而不是df.to_dict()直接丢出去。这样即使上游 Excel 多了一列脏数据，重建环节就会报错，而不是把脏数据悄悄喂给接口。销售部 JSON 真实内容：

[{"name":"张伟","department":"销售部","attendance_days":22,"sales_amount":85000.0},{"name":"李娜","department":"销售部","attendance_days":21,"sales_amount":92000.0},{"name":"王芳","department":"销售部","attendance_days":20,"sales_amount":76000.0}]

（市场部、研发部、客服部、行政部同理各生成一份，回显略。）

关键结论：CSV 用utf-8-sig防 Excel 乱码；JSON 用dataclass做二次类型校验，比to_dict(orient="records")更稳。

八、踩坑清单

九、总结

这一篇把「办公自动化」从「会调 API」推进到「能交付」：

• 建模：用dataclass把业务数据框死，IDE 能提示、运行前就能发现缺参；
• 生成：openpyxl负责「长得好看」的生产报表（表头、填充、列宽）；
• 运算：pandas负责「算得快准」（合并、groupby、idxmax 找销冠）；
• 转换：CSV 带 BOM 防乱码，JSON 经 dataclass 二次校验防脏数据；
• 验证：写完用load_workbook读回，确认格式真的落盘。

五张部门表进，一张汇总报表加十份转换文件出，全程无手工复制粘贴。下一篇我把它再往前推一步——用 Django 把「文章发布」这种更完整的业务搬到 Web 上。

本文实验均在华为云 Flexus X 实例（Ubuntu 24.04, Python 3.12.3）上真实执行。