Python自动化合并Excel报表：从手动整理到一键生成

这份第十八天笔记其实是“三十天自动化办公自救计划”的中间产物。头两周我处理了邮件自动提醒、批量重命名、PDF转Word这些杂活但真正让我觉得“值回票价”的是第十八天这次任务把六十多个Excel报表自动合并成一份总表再生成带格式的汇总账本。平时手工做这件事少说要抽出一个下午对格式对得眼睛疼用脚本跑完十几秒出结果还顺手把常见的数据脏乱差问题一并收拾了。如果你也在跟月度报表、日报汇总、多门店数据合并缠斗这则笔记应该能帮你少走不少弯路。我会把第十八天从需求梳理、技术选型到实际踩坑的完整过程都翻出来代码能抄就抄参数能解释就解释争取让你看完就知道怎么复现。1. 第十八天笔记从“月底报表地狱”开始的自动化尝试1.1 为什么偏偏是第十八天这个项目源自一个挺朴素的需求月底做经营分析需要把各个门店发来的日报合并成一张总表再按日期、门店、品类做统计。过去同事们的做法是打开一个个Excel文件把数据区域复制粘贴到汇总表里来回切换窗口有时候还会因为多复制了一行合计而把总数搞错。我接手这件事之后第一个念头不是手动做一遍而是想着能不能写个脚本让电脑替人干这件事。之所以叫“第十八天笔记”是因为我当时给自己定了三十天的自动化学习计划每天选一个真实的办公痛点来攻破。前面十七天其实都在练基本功包括Python的文件操作、pandas的简单用法、Openpyxl的单元格样式设置第十八天则把这些技能点全部串到了一起正好用来解决多表合并这个综合性问题。可以说这一天是把前面积累的东西真正落地的转折点笔记自然也就格外值得记录。1.2 这份笔记能解决什么问题适合谁看如果你每天面对的不是代码而是烦人的表格那这份笔记很对你胃口。它解决的核心问题有三个一是批量读取几十个Excel文件里的指定数据区域而不是一个文件一个文件地打开二是把格式不统一、列名不一致、数据里混着多余符号的问题清洗干净三是最终生成一张“长得像人做的”汇总表包含标题、表头样式、筛选按钮、冻结窗格而不是只给一个冷冰冰的CSV。适合看这份笔记的人包括但不限于经常做月度数据汇总的运营需要把多个部门或者是多个门店报表合并的财务助理以及那些刚学Python、想知道pandas和openpyxl到底能干多少活的新手。即使你之前没怎么碰过代码只要按着里面的逻辑一步步来也能完成一次从手动汇总到自动产出的升级。2. 动手之前把模糊需求翻译成技术方案2.1 需求拆解六十多个Excel文件合并难点到底在哪任何一段程序写之前最怕的不是代码复杂而是需求不清楚。第十八天这天的第一个小时我什么代码都没写只做了一件事就是把“帮我合并报表”这句话拆成机器能听懂的零件。拆开之后发现难点主要集中在三处。第一处是文件格式不统一有的门店把表头放在第一行有的放在第三行甚至还有文件首页是封面第二页才是数据的情况。如果脚本默认读第一行当表头遇到封面页就会读出一堆“某某门店日报”的无关文字。第二处是数据脏乱差金额列里可能带着“元”字日期列看起来是“2024-08-01”实际却是Excel里那种一串数字的序列号还有的表里每个门店末尾都有一个“合计”行合并的时候如果不剔除总和就会翻倍。第三处是输出样式需求领导要的是“一眼能看出总数”的表不是原始数据堆在一起所以最终产品里需要分类汇总、合计列加粗、月总计等要素。这些难点如果不用技术方案翻译人脑会默认用笨办法去处理也就是打开每个文件看一遍逐个修正。但这事其实有规律可循做成脚本反而更稳定不会因为操作者中途接了个电话而漏掉某个文件。2.2 工具选型为什么用pandas加openpyxl而不是VBA面对“处理Excel”这件事很多人第一反应是VBA毕竟微软Office自带的宏功能看起来最“正统”。但我在第十八天选择的是Python具体是pandas加上openpyxl这个选择背后有几个很实际的原因。最开始我想过VBA因为只要在Excel里按个按钮就能运行不需要单独安装环境。但VBA有个致命问题是它的调试体验比较差如果某个门店发来的文件格式跟预期不一致程序跑着跑着就弹一个看不懂的英文报错新手很容易卡死。另一个问题是VBA处理大数据量时速度并不快几十个文件循环打开读取耗时会明显拉长。相比之下pandas用了底层向量化运算读Excel文件本身由专门的引擎处理几十个文件也就几秒读完后续的列转置、去重、合并更是它的拿手好戏。openpyxl则可以负责“包装”工作程序跑完数据合并之后我需要一份像样的Excel成品这一点用pandas自带的to_excel也能做但那种输出默认不带格式没有颜色没有合并单元格标题也不会居中。而openpyxl允许我直接控制单元格字体、颜色、边框、列宽、行高甚至是自动筛选和冻结窗格。这么组合起来pandas解决“算得动”openpyxl解决“长得帅”两个工具各有分工比单一方案顺手得多。2.3 目录结构和脚本前的准备工作在实际写代码之前我先把工作目录规划好。这里有一个非常实用的经验永远不要让脚本直接处理原始文件所在的目录尤其是你所做的工作跟同事有交互的时候。一旦脚本出错把原文件动了或者合并错了想还原就会很麻烦。所以我建了一个清晰的三层目录结构input文件夹专门放原始Excel文件output文件夹放最终生成的汇总表和透视表archive文件夹放脚本运行前的备份。当所有门店报表被丢进input后我做的第一件事不是运行程序而是先把整个input复制一份到archive里存底。这会养成一个习惯就是让每一次自动化处理都有回退的余地。环境方面我用的是Python 3.10安装依赖很简单pip install pandas openpyxl这里稍微提醒一下pandas本身读写Excel时依赖openpyxl或xlrd这样的解析引擎所以哪怕只是想读Excelopenpyxl也一定要装上。我见过不少新手只装了pandas就报ImportError其实就是少装了这个小依赖。3. 第十八天的完整实操记录3.1 准备工作环境、依赖和目录当天下午两点半左右我才正式打开终端。按上午的规划input目录里已经整整齐齐躺着64个门店日报文件文件名大概长这样“A门店_20240801.xlsx”、“B门店_20240802.xlsx”有的文件还会带一些乱码前缀比如“【重要】C门店_20240803.xlsx”。我先在脚本开头导入了需要的库import os import re import glob import datetime import pandas as pd from openpyxl import load_workbook from openpyxl.styles import Font, PatternFill, Alignment from openpyxl.utils import get_column_letter这里把可能用到的库都提前引进来后面不用再补。glob负责匹配文件名re用来处理文件名字里的乱码前缀datetime用于把Excel的日期序列号转成正常日期openpyxl则负责最终的样式处理。我的操作原则是脚本运行之前先跑一个“检查清单”确认目录里确实存在文件并且输入文件数量跟预期一致。如果文件数量不对就不用继续往下跑了先搞清楚少了谁再干活。files glob.glob(input/*.xlsx) print(f共找到 {len(files)} 个Excel文件) if len(files) 60: print(文件数量异常请先检查input目录) exit()不要小看这一步。多花几秒做个数量校验能避免后面输出汇总表时才发现只有80%的数据那才是真的麻烦。3.2 第一步批量抓取文件——用glob代替手工点击普通人的做法是一个一个文件点开我在脚本里用glob把这一批文件变成列表。glob的好处是它支持通配符匹配只要文件名符合“*.xlsx”模式就能被一次性抓进来。当然文件名里那些“【重要】”之类的前缀不能直接删除因为我要用文件名里的门店编码和日期作为合并的依据。于是我在遍历文件列表时顺手提取了门店名和日期for f in files: basename os.path.basename(f) # 假设文件名格式类似A门店_20240801.xlsx match re.search(r(.?)_(\d{8})\.xlsx$, basename) if match: store_name match.group(1) date_str match.group(2) file_date datetime.datetime.strptime(date_str, %Y%m%d).date() else: print(f文件名无法解析请检查: {basename}) continue这里用正则表达式按“门店名下划线8位日期”的规则去匹配文件名。如果门店名里本身就带下划线贪婪匹配可能会出错所以我用了非贪婪模式(.?)保证它能从文件末尾往前找日期格式这样分隔符前面的部分就算门店名里有下划线也没关系。3.3 第二步读取Excel并统一表头——处理表头位置不一致的问题把文件路径准备好之后真正的重头戏来了读取每个Excel文件并跳过没有意义的标题行直接读到数据区域。各家门店报表的表头位置五花八门有的第一行就是“日期 商品 数量 金额”有的前面多加了一层大标题比如“某某门店8月销售日报”下面才是列名。pandas的read_excel方法提供了一个header参数可以指定用第几行作为列名。但对于那些封面型文件直接指定header0会读到“某某门店8月销售日报”这种无意义内容。所以我采用的办法是先不指定表头读取全部内容再扫描前几行找到第一个包含“日期”或“商品”关键字的行作为表头。raw pd.read_excel(file_path, headerNone, nrows10) header_row None for i in range(len(raw)): row_values raw.iloc[i].astype(str).tolist() if any(日期 in x or 商品 in x or 销售 in x for x in row_values): header_row i break if header_row is not None: df pd.read_excel(file_path, headerheader_row) else: print(f找不到表头行请手动检查: {basename}) continue这个方法本质上是先“试探”文件前几行的内容再用“特征词”去匹配真正表头出现的位置。虽然多了一次完整的读取但对几十个文件的体量来说性能影响可以忽略不计。实际效率上跑下来也不慢。但是header位置对了不代表一切正常因为表头下面可能跟着无关的说明行比如“备注本表数据截至当日24点”或者在各个门店数据块之间插着汇总行。所以我在正式读取之后还需要对列名统一化和数据过滤做处理。统一列名这一步在代码里就是一个字典映射column_map { 销售日期: 日期, 日期: 日期, 日期(必填): 日期, 商品名称: 商品, 品类: 品类, 品类名称: 品类, 销售数量: 数量, 数量: 数量, 销售额: 金额, 销售金额: 金额, 金额(元): 金额, } df.rename(columnscolumn_map, inplaceTrue)为什么要多花这一步因为如果各家门店把“金额”写成“销售额”或者“金额(元)”合并的时候如果直接用concatpandas会把它当成两列最后结果是既有“金额”列又有“销售额”列数据拆得七零八落。只有先统一列名后续纵向合并才能真正“接得上”。3.4 第三步数据清洗的几个倔脾气问题报表数据真正读进来以后你会发现“从Excel读出的数据”和“能用机器学习跑的数据”之间还有一段距离。我在第十八天主要处理了四类问题。第一类是“合计”行。几乎每个门店报表的最后一行都是“合计xxxx”或者“总计”合并前必须把这些行过滤掉否则最后再做汇总时合计会被重复计算。我的过滤逻辑是用字符串检测只要某一行里的门店名或品类字段包含“合计”“总计”“小计”就把它丢掉。df df[~df[品类].astype(str).str.contains(合计|总计|小计, naFalse)]第二类是金额里带着“元”或千分位逗号。Excel里看起来很方便的“1,234元”pandas读进来却是字符串“1,234元”直接转成浮点数必然报错。我的处理方式是先把所有可能的符号去掉再批量转换。for col in [金额, 数量]: if col in df.columns: df[col] ( df[col] .astype(str) .str.replace(元, , regexFalse) .str.replace(,, , regexFalse) .str.strip() ) df[col] pd.to_numeric(df[col], errorscoerce)这里的errorscoerce很重要它让无法转换的单元格自动变成NaN而不是抛异常终止整个脚本。数据清洗阶段最常见的错误就是遇到一个脏数据直接让程序崩溃所以转换时一定要有兜底。第三类是Excel日期序列号。某些报表里的日期列看起来是“2024-08-01”但pandas读进来其实是45000这样的序列号。判断这个问题的标志是列的数据类型是int64而不是datetime64。转换逻辑很简单if pd.api.types.is_integer_dtype(df[日期]): df[日期] pd.to_datetime(df[日期], unitD, origin1899-12-30)这个origin等于1899-12-30是Excel日期系统的起点加一天对应一天正好能把序列号映射回真正的日期。第四类是重复的数据行。有时候同一天同一个门店同一个商品会出现两条记录原因是对方工作簿里有隐藏行或者复制粘贴时重复。我直接用drop_duplicates按关键列去重df.drop_duplicates(subset[日期, 门店, 商品, 品类], inplaceTrue)清洗完了之后每个文件的数据行数会明显变少。我习惯在合并之前先看几行被过滤掉的部分确认“合计”行确实被删除而不是把正常数据也误删了。这一步看似啰嗦却能在后续省掉很多核对时间。3.5 第四步合并与统计——从64张小表拼成一张大表清洗完单个文件后面的事情就顺利多了。pandas里把多个DataFrame纵向堆叠只需要一个concatall_data pd.concat(list_of_dfs, ignore_indexTrue)concat是pandas里最简单也最常用的操作之一它会按照相同的列名自动对齐。如果某个文件里没有“备注”列其他文件有合并以后缺少的部分会用NaN补上这正好符合我们的需求。合并之后我又顺手做了一次全局去重和类型校验确保日期列、金额列都转成正确的格式。此时all_data大概长这样日期门店商品品类数量金额2024-08-01A门店经典款T恤服饰323180.52024-08-01B门店休闲裤服饰1215882024-08-01C门店帆布鞋鞋类81032统计透视方面我用的是pandas的pivot_table它可以一步得到按日期、门店、品类的多维汇总pivot_result pd.pivot_table( all_data, values金额, index[日期], columns[品类], aggfuncsum, fill_value0, marginsTrue, margins_name总计, )这个透视表的作用跟Excel里的数据透视表一样但区别在于它是用代码生成的公式逻辑可追踪、可复用而且最后可以导出成独立的Excel页签。marginsTrue会额外生成一行“总计”方便月底汇报时瞄一眼总数。3.6 第五步生成带格式的成品报表——用openpyxl让表格像人做的合并和统计出数据以后第十八天最后一个大坑出现在输出环节。pandas自带的to_excel方法虽然快但它写出的文件几乎没有格式列宽全是默认值长单元格直接溢出到旁边标题和表头没有加粗也没有筛选按钮看起来像半成品。于是我在第十八天采用了“pandas出数openpyxl化妆”的组合流程。先让pandas把数据一次性写入Excel文件再用openpyxl加载同一文件把样式逐个改好。核心流程是with pd.ExcelWriter(output/汇总报表.xlsx, engineopenpyxl) as writer: all_data.to_excel(writer, sheet_name数据明细, indexFalse) pivot_result.to_excel(writer, sheet_name品类透视)然后加载工作簿修改各个sheetwb load_workbook(output/汇总报表.xlsx) ws wb[数据明细] # 表头加粗、加底色 for cell in ws[1]: cell.font Font(boldTrue, colorFFFFFF) cell.fill PatternFill(start_color4472C4, end_color4472C4, fill_typesolid) cell.alignment Alignment(horizontalcenter, verticalcenter) # 列宽自动调整 for i, col in enumerate(ws.columns, 1): max_len max(len(str(c.value)) if c.value else 0 for c in col) ws.column_dimensions[get_column_letter(i)].width max_len 4 # 冻结首行 ws.freeze_panes A2 # 开启自动筛选 ws.auto_filter.ref ws.dimensions wb.save(output/汇总报表.xlsx)这些样式操作的每行代码都可以理解成“对单元格对象的属性赋值”。Font(boldTrue)让字变粗PatternFill给单元格填上蓝底column_dimensions设置列宽freeze_panes把首行固定住auto_filter自动筛选则让表格可以下拉筛选。视觉上是做给领导看的毫不夸张地说用这些代码产出的表格光从表面上看已经跟手工做的没有区别甚至在格式一致性上更胜一筹。3.7 运行结果与性能记录脚本写完以后我用终端直接跑了一遍。输出日志大概是这样的共找到 64 个Excel文件 开始处理: A门店_20240801.xlsx 读取到数据行数: 312清洗后剩余: 298 开始处理: B门店_20240802.xlsx 读取到数据行数: 287清洗后剩余: 276 ... 全部处理完成用时 18.6 秒 合并后的总行数: 18432 透视表已生成品类透视页已写入。64个文件、总计约两万行原始数据从读取到清洗再到输出带格式报表整个流程不到二十秒。这个速度放在人工操作面前几乎没有可比性因为手工光是把这些文件打开再关掉就得花掉好几分钟。整个过程跑完我甚至有一种“之前两个月手工汇总到底在干嘛”的恍惚感。4. 踩过的坑第十八天的问题排查实录4.1 文件打不开被谁占用了第十八天实际调试时第一个脚本报错是PermissionError提示某个文件正被另一个进程使用。原因是我前面为了检查某个门店报表的结构手动在Excel里打开过那个文件然后忘记关掉。Windows系统会锁定被打开的Excel文件Python脚本就没法重写或删除它。排查思路很简单报错信息里会显示具体是哪个文件出问题把它在Excel里关掉就行。从工程角度更稳的做法是在代码里加异常捕获遇到打不开的文件时记录下来而不是直接崩溃。try: df pd.read_excel(file_path, headerheader_row) except PermissionError: print(f文件被占用请关闭后重试: {basename}) continue这件事看起来小但在批量处理72个文件时只要有1个文件被同事打开着整个脚本就可能中断。加一个try和continue以后程序会跳过有问题的文件继续跑最后在日志里统一报告省去了来回沟通的麻烦。4.2 日期变成了四千多第一次跑完合并之后我抽样检查了几行数据发现日期列里居然有45000这种数字。一开始我还以为是特定文件的输入问题后来排查发现是Excel的日期序列号混进来了。这种问题通常发生在某些报表软件自动导出的Excel文件里它们把日期以数字形式存储单元格格式却是文本。解决方式在前面已经写过用pd.to_datetime(unitD, origin1899-12-30)转换即可。这里想强调的是这种“异形数据”不是靠读代码能发现的必须靠人工抽样验证。所以在脚本最后我刻意加了一个“验证模块”输出每种数据类型的统计信息print(all_data.dtypes) print(日期最小值:, all_data[日期].min(), 最大值:, all_data[日期].max())如果日期列还有数字类型print会显示出来一眼就能发现问题。4.3 金额里混着“元”和空格另一个高频问题是金额列里的特殊字符。有些门店的报表导出自财务软件金额字段显示为“1,234.00元”pandas读到这些字符后会把整列识别为字符串一旦你想做求和它要么报错要么直接字符串拼接出一串数字。清洗这列时最容易忽略的是空格尤其是在数字后面加了一个全角空格的情况肉眼几乎看不出来。我的处理办法比较粗暴先把所有非数字、非小数点的字符替换掉df[金额_clean] ( df[金额] .astype(str) .str.replace(r[^\d.\-], , regexTrue) .astype(float) )这里用正则表达式把数字、点号、负号之外的所有字符全部删掉。用这种方法处理那些混杂了“元”“”“,”的数据一次就干净了。4.4 openpyxl写入后文件体积疯狂膨胀有一个现象让我排查了好一阵pandas写好原始数据后文件只有几百KBopenpyxl加完样式保存后文件直接变成好几十MB打开还特别慢。一开始我以为是脚本重复加载了工作簿后来发现是openpyxl在读取和重写工作簿时保留了大量不必要的样式记录并形成了“垃圾样式”累积。解决方案有两个一个是在ExcelWriter阶段就直接指定engineopenpyxl并做好格式规划避免后期二次加载重写另一个是如果只是做简单格式尽量缩小样式作用范围不要对整列设置样式只对实际有数据的行范围设置。我在最终版本里把设置列宽和字体样式的循环限制在前一百行以内实际必要地方像合计行才额外处理这样文件大小就恢复正常了。如果你也要处理几千行数据别盲目对全部行设置花哨格式实用主义一点。4.5 常见问题速查表现象原因解决方法脚本报PermissionError文件被Excel等程序占用关闭打开的文件或用try/except跳过日期显示为45000Excel日期序列号用pd.to_datetime转换origin设为1899-12-30金额列求和报错字符串含元、逗号、空格用正则提取数字字符后转float列名不统一导致合并后多列不同文件同义写法不同先rename统一列名再concat合并后数据翻倍原表自带合计行没有剔除清洗时过滤包含“合计/小计/总计”的行openpyxl重写后文件巨大样式冗余累积限制样式范围或尽量一次写入找不到表头行文件格式特殊封面占多行扫描特征字自动定位真正的表头行4.6 验证结果的两个土办法最后我想专门强调一下“验证”这件事。脚本跑完以后看起来数据都对但实际上有没有漏文件、有没有重复统计我一般不会只看最终汇总数字而是用两个土办法交叉检查。第一个办法是样本核对法从64个原始文件里随机挑出3个用Excel打开手动对其中某个品类求一次和比如“A门店8月5日总共卖了多少金额”然后去脚本输出的总表里搜同一天同一门店的对应数字。对上了再跑下一组样本对不上立刻停下来查清洗逻辑。第二个办法是数量校验法脚本在合并完成时统计一个“有效文件数”我把这个数字跟input目录里实际文件数对比。如果有文件因为格式问题被continue跳过数量就会对不上日志里也会留下警告。这个校验时间成本极低但能挡住绝大部分低级错误。自动化工具真正可怕的地方不是它做不出结果而是它“高效地做出了错误的结果”。所以永远别省掉验证这一步。5. 第十八天之后的体会一个小技巧和一个新坑预告每次跑完这组脚本我都有一种相似的感觉自动化的难点从来不是写代码而是把需求翻译成逻辑、把脏数据摸清楚、把输出样式做得让人满意。第十八天这天最好的收获是让自己养成了一种“先拆需求、再选方案、最后写代码”的路径而不是拿脚本来回试试出来什么算什么。这里再分享一个小技巧在所有代码的最开头先打印一行“当前工作目录是什么”。很多报表脚本报错都是因为脚本在某个默认路径下运行找不到input目录然后花十分钟找原因。加上这一行路径问题一目了然print(当前工作目录:, os.getcwd())另外一个我在第十八天后才意识到的新坑是openpyxl在处理超大数据量时的内存问题。如果你的数据量已经到了几十万行Excel本身的单表行数限制都可能成为瓶颈。到那时候要么改用CSV分块处理要么直接上数据库或专业报表工具不能硬撑。第十九天的计划我已经打算研究一下怎样把SQLite当中间存储让pandas读写大数据量时更从容一些。这些就留到下一篇笔记里再说吧。