批量读取并合并数据

我记录自动化办公踩坑实录帮客户把报表耗时从48小时压到2小时前不久接了个内部提效的活儿客户是一家做跨境电商的中大型公司财务和运营团队每个月底都要对着十几份Excel表手动核对数据、拼接报表。说实话他们之前全靠人工搬运光导数据加排版就得耗上两天一旦哪个单元格格式错了整张表就得重做。我接手的时候看着他们堆成山的原始文件心里直打鼓但既然答应了下来就只能硬着头皮上。这项目不算大主要是跑批和清洗但数据源杂乱模板还经常变稍微不留神就会翻车。方案选型与核心逻辑刚拿到需求的时候我第一反应是用openpyxl直接读写单元格毕竟它支持样式保留模板套用起来最省事。试了一圈发现这方案在处理五万行以上的数据时内存占用直接飙升电脑风扇转得跟直升机似的。当时有方案A和方案B我选了pandas负责数据清洗openpyxl只负责最后的样式填充因为pandas的底层是C语言写的向量化操作跑数快得多而openpyxl只要避开大数据量读取只做模板渲染就稳了。说实话代码写起来并不复杂。我把所有原始表格统一清洗成宽表然后按客户提供的固定模板进行字段映射。核心逻辑大概就是这样pythonimport pandas as pdfrom openpyxl import load_workbookimport os批量读取并合并数据dfs []for file in os.listdir(./raw_data):df pd.read_excel(f./raw_data/{file}, dtype{订单号: str})dfs.append(df)merged_df pd.concat(dfs, ignore_indexTrue)核心统计逻辑summary merged_df.groupby([平台, 店铺]).agg({销售额: sum,退款额: sum,订单量: count}).reset_index()填充模板wb load_workbook(template.xlsx)ws wb.activefor idx, row in summary.iterrows():target_row idx 2ws.cell(rowtarget_row, column2, valuerow[平台])ws.cell(rowtarget_row, column3, valuerow[店铺])ws.cell(rowtarget_row, column4, valuerow[销售额])ws.cell(rowtarget_row, column5, valuerow[退款额])ws.cell(rowtarget_row, column6, valuerow[订单量])wb.save(report_output.xlsx)当时我觉得直接套模板就行结果发现完全错了。我排查了半天发现是pd.concat在数据量大的时候会创建巨大的中间对象而且iterrows()逐行写入单元格在数据突破两万行后IO阻塞直接让进程假死。后来我改了思路不用一次性读入所有文件而是分批次写入一个临时数据库或者直接按条件过滤后再聚合内存瞬间就下来了。有意思的是客户给的模板里有很多合并单元格和下拉验证这部分只能用openpyxl去解析XML结构或者用iter_rows()逐个赋值虽然慢点但保住了格式不崩。我干脆写了个辅助函数把模板的冻结窗格和条件格式单独抽离出来跑完数据再批量应用总算把性能拉回了正常水平。踩坑与排雷记录坑死了的其实是日期格式和动态行数的问题。Excel里的日期是个玄学存进去是文本读出来可能是浮点数pandas默认又把它转成datetime对象。一开始我没注意直接往模板里塞数据结果单元格里全变成了2024-05-12 00:00:00客户一眼就看出不对劲。我试了一圈才发现必须在写入前强制转成字符串并且用strftime指定格式或者在openpyxl里设置单元格类型为date并绑定显示格式。还有个头疼的地方是报表需要按“平台”自动分页生成多个Sheet。客户原话是“每个平台一个独立工作表表头固定下面数据跟着走”。我当时觉得直接循环写入就行结果运行后发现后一个Sheet会把前面的覆盖掉。排查日志才发现wb.create_sheet()虽然能新建但我没正确处理单元格偏移量导致数据直接写到了第1行把表头全挤没了。后来我加了个偏移计数器每次写入新Sheet前重置索引才把数据对齐到正确的起始行。我还顺手把异常捕获做了加强万一某个平台的源文件缺列脚本不会直接崩盘而是记录错误日志继续跑下一个表这样财务那边就算遇到脏数据也不用半夜打电话叫我。说实话这种纯机械重复的活儿真正考验的不是算法多牛逼而是对边界条件的死磕。客户那边最后验收的时候看到两小时跑完以前两天的量直接给我发了个红包。我没收就说顺手的事儿。毕竟帮他们省下的时间够财务多睡会儿懒觉了。自动化这东西前期搭框架费脑后期维护反而省心。本文基于实际项目经验整理欢迎在评论区交流技术问题。