Python 办公自动化实战批量处理 Excel 与格式转换《Python 实战》应用项目篇 · 第 7 篇作者按本文所有命令均在真实云服务器上手工执行回显为原样复制未做任何修饰或编造。一、背景为什么办公自动化值得单独写一篇在公司里最消耗人力的往往不是「写代码」而是「把一堆 Excel 表拷来拷去、合并、改格式、再导出成别的系统要的 JSON」。我见过一个真实场景月末五个部门各自发来一份考勤兼销售表财务要把它们拼成一张总表、算出每个部门的总额和销冠、再分别导出 CSV 给 BI 系统、导出 JSON 给接口。手工做两小时起步还容易粘错行。Python 处理这类事是降维打击。但「能跑」和「写得专业」是两回事。本篇我用一次完整的实操把三件容易被忽略的事讲透用dataclass给业务数据一个类型安全的模型而不是永远用dict裸奔用openpyxlpandas做批量生成、合并、带格式汇总而不是只会df.to_excel把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_NAMEUbuntu 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,asdictfromtypingimportListdataclassclassEmployeeRecord:员工记录用 dataclass 做类型安全的领域模型。name:strdepartment:strattendance_days:intsales_amount:floatdefattendance_rate(self,total_workdays:int24)-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_fontFont(boldTrue,colorFFFFFF)header_fillPatternFill(solid,fgColor4472C4)fordept,membersinby_dept.items():wbopenpyxl.Workbook()wswb.active ws.titledept headers[员工姓名,出勤天数,销售金额]ws.append(headers)forcinws[1]:c.fontheader_font c.fillheader_fill c.alignmentAlignment(horizontalcenter)forminmembers:ws.append([m.name,m.attendance_days,m.sales_amount])ws.column_dimensions[A].width12ws.column_dimensions[B].width10ws.column_dimensions[C].width12pathos.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:fos.path.join(RAW,f{dept}.xlsx)dfpd.read_excel(f)df.insert(0,部门,dept)frames.append(df)mergedpd.concat(frames,ignore_indexTrue)merged.to_excel(merged_path,indexFalse)合并后的 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(部门):totalgrp[销售金额].sum()avground(grp[销售金额].mean(),2)topgrp.loc[grp[销售金额].idxmax()]summary_rows.append({...})summarypd.DataFrame(summary_rows).sort_values(总销售金额,ascendingFalse)真实汇总结果部门 员工数 总销售金额 平均销售金额 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(engineopenpyxl)在写出后二次加工表头加粗、绿色填充、水平居中并显式设置列宽。withpd.ExcelWriter(report_path,engineopenpyxl)aswriter:summary.to_excel(writer,indexFalse,sheet_name部门汇总)wbwriter.book wswriter.sheets[部门汇总]forcellinws[1]:cell.fontFont(boldTrue,colorFFFFFF)cell.fillPatternFill(solid,fgColor2E7D32)cell.alignmentAlignment(horizontalcenter)forcol,win{A:10,B:8,C:14,D:14,E:10,F:14}.items():ws.column_dimensions[col].widthw光说「设置了」不够我反过来读回文件验证样式是不是真的写进去了真实回显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 会乱码所以用encodingutf-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,16000JSON 导出时我故意先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(orientrecords)更稳。八、踩坑清单坑现象解决办法PEP 668 限制pip install报 externally-managed-environment建 venvpython3 -m venv /root/lab-d/venv后在 venv 内装本地驱动被截断、拿不到回显长命令前台跑外壳超时杀进程输出空以服务器侧pip list为准SSH 驱动改为线程读满至 EOFCSV 中文乱码Windows Excel 打开 UTF-8 CSV 是乱码to_csv(..., encodingutf-8-sig)表头样式没生效以为Font(boldTrue)写了其实没保存用ExcelWriter二次改样式用load_workbook读回验证dataclass序列化直接dict容易混入脏字段用asdict()且经 dataclass 重建做类型校验合并丢「部门」列concat后分不清每行归属读每个文件时df.insert(0,部门,dept)九、总结这一篇把「办公自动化」从「会调 API」推进到「能交付」建模用dataclass把业务数据框死IDE 能提示、运行前就能发现缺参生成openpyxl负责「长得好看」的生产报表表头、填充、列宽运算pandas负责「算得快准」合并、groupby、idxmax 找销冠转换CSV 带 BOM 防乱码JSON 经 dataclass 二次校验防脏数据验证写完用load_workbook读回确认格式真的落盘。五张部门表进一张汇总报表加十份转换文件出全程无手工复制粘贴。下一篇我把它再往前推一步——用 Django 把「文章发布」这种更完整的业务搬到 Web 上。本文实验均在华为云 Flexus X 实例Ubuntu 24.04, Python 3.12.3上真实执行。