简介面向需要批量处理Excel的Python学习者与办公自动化人员这是一套基于pandas与openpyxl的Excel批量处理示例资源。内容以可直接运行的代码为主线从安装依赖库、加载工作簿、获取全部工作表名称开始逐步讲解遍历所有工作表、对指定列实施乘法计算、依据另一列条件筛选后再修改以及分块读写与inplace参数优化性能等操作并给出写入新文件以保留原始数据的保存方式。示例代码注释完整、逻辑清晰涵盖条件判断、数据清洗等常见扩展思路便于读者根据实际业务调整列名和计算规则。压缩包整体约3.14MB内容精炼既适合刚接触pandas的初学者对照学习也能帮助有批量表格处理需求的技术人员快速迁移应用到实际项目中。目前已有123人学习对希望用Python提升表格处理效率的用户具有不错的参考价值。1. 200 个工作表的 Excel 文件别再用 CtrlH 一个个点了当一份 Excel 文件里塞了 200 多个工作表而且每个表里的固定位置都要改同一批内容时手工操作就变成了灾难切表、查找、替换、保存再切下一张手指比脑子先累。这个标题对应的场景通常来自数据上报汇总、财务月报展平、或者是某个业务系统导出的“一表一机构”文件。真正的需求不是把表格打开看一眼而是用一种可重复、可审计的方式对全部工作表执行结构一致的内容修改。用 Python 处理这件事的常规方案是 openpyxl它可以直接读写 .xlsx不需要本机安装 Office能够在保留原有格式的前提下批量遍历工作表、修改单元格、甚至把处理后文件重新打 zip 包分发。适合的人群是手里有大量 Excel 文件要定期维护的运营、财务、数据分析师以及想把这套流程固化成脚本的后端开发。记住一个边界它读写的是单元格数据不是可视化界面你要改什么、改成什么必须先在代码里写清楚。2. 开工前先看清工作簿结构用 Python 列出 Excel 所有工作表并备份2.1 最小依赖为什么用 openpyxl 处理 200 个工作表而不是 VBA 或 pandas处理这种文件我先说结论如果没有启用宏的需求首选 openpyxl。VBA 也能做到但 VBA 的脚本跟着文件走执行时容易触发安全设置而且不方便做版本管理。pandas 虽然读取 excel 很爽但pd.read_excel()默认只能读取单个工作表需要配合sheet_nameNone才能拿到所有表而且 pandas 写回文件时会把原有格式覆盖掉——对包含图表、合并单元格、条件格式的报表来说不可接受。openpyxl 是纯 Python 库底层直接解析 xlsx即 zip 包里的 XML 结构修改单元格后调用wb.save()写回。它会在内存中保留工作簿对象对 200 个表的常规文本替换和数值改写内存占用一般可控。如果你的文件超过 50MB、每个 sheet 都是上万多行再考虑用read_onlyTrue模式做只读扫描或者改用 xlwings 驱动 Office 程序后台处理。下面是我在处理这类需求时常用的依赖列表直接存到requirements.txt库版本建议用途openpyxl3.1.0读写 xlsx遍历工作表修改单元格pandas仅做辅助筛选复杂条件分析时先用 pandas 算再回填xlrd2.0只读 xls处理老式 .xls 文件时配合使用py7zr 或 zipfile标准库即可最终打包成 zip 压缩包2.2 先读文件统计工作表数量与名称拿到一份带 200 多个工作表的 Excel不要急着改成目标功能代码。第一步永远是“盘点”确认工作表数量、名称、前几行长什么样以及有没有隐藏表。这一步能避免后续因为某个 sheet 名是空字符串或包含特殊字符导致脚本崩溃。from openpyxl import load_workbook file_path 200_sheets_workbook.xlsx wb load_workbook(file_path, read_onlyTrue, data_onlyFalse) sheet_names wb.sheetnames print(f工作表总数: {len(sheet_names)}) for i, name in enumerate(sheet_names[:10], start1): print(f第{i}个: {name!r}) print(...) print(f最后5个: {[name for name in sheet_names[-5:]]}) hidden [name for name in wb.sheetnames if wb[name].sheet_state hidden] print(f隐藏工作表: {hidden}) wb.close()这里把read_only设为True表示只允许读取不允许修改遍历大量工作表时内存占用更低。data_onlyFalse拿到的是带公式的表达式比如SUM(A1:A10)如果设成True则返回最近一次 Excel 计算后的缓存值。注意 openpyxl 不会重新计算公式所以当你要修改数据源单元格时最好基于公式模式操作否则缓存值不会自动更新。工作表的读取顺序就是文件里的物理顺序不是按名称字母排序。如果你希望按名称排序处理先执行sorted(sheet_names)否则后面替换内容时顺序可能不符合预期。2.3 改内容前的备份与只读校验200 多个工作表意味着文件可能很大操作出错后很难撤销。我一般会先压缩备份一份原文件再基于副本修改。这不仅是防止改坏也方便前后对比 diff。cp 200_sheets_workbook.xlsx 200_sheets_workbook_backup_$(date %Y%m%d_%H%M%S).xlsx如果你担心磁盘上有多个同类型文件用 Python 脚本统一备份成 zip 更稳妥import shutil from datetime import datetime src 200_sheets_workbook.xlsx ts datetime.now().strftime(%Y%m%d_%H%M%S) dst fbackup/{src.replace(.xlsx, f_{ts}.xlsx)} shutil.copy2(src, dst) print(f备份完成: {dst})备份之后做一次“可读性校验”用上一小节的代码重新加载一遍副本确认没有损坏、每个 sheet 都能访问到。这一步看起来多余但能提前暴露两种情况一是文件被其他程序占用二是某个 sheet 数据源异常导致 openpyxl 解析报错。等到写完修改代码才发现这些问题排错成本就高了。3. 批量更改 Excel 工作表的三种内容修改模式与参数细节3.1 模式一全表扫描按旧值统一替换最通用的需求是在所有工作表中把某些固定文本比如“旧部门名”替换成新值同时不允许动到公式结构。模式一的实现思路是双层循环外层遍历wb.sheetnames内层用iter_rows()遍历每个 sheet 的所有已用单元格判断.value是否等于目标旧值是则写入新值。from openpyxl import load_workbook wb load_workbook(200_sheets_workbook_backup.xlsx) old_value 华东大区 new_value 华东一区 changed_count 0 for sheet_name in wb.sheetnames: ws wb[sheet_name] # min_row1 表示从第一行开始实际可按需调整 for row in ws.iter_rows(min_row1): for cell in row: if cell.value old_value: cell.value new_value changed_count 1 print(f[{sheet_name}] 替换完成累计改动 {changed_count} 处) wb.save(200_sheets_workbook_modified.xlsx) print(全部保存完成)注意iter_rows()默认只返回有内容的最小矩形区域。如果某个单元格是在旧版本 Excel 中被设置过样式但无值的它可能不被遍历到这通常不影响替换。若要同时处理公式中的文本引用不能用单元格.value的相等判断要改成if isinstance(cell.value, str) and old_value in cell.value但这样替换会破坏公式语法我不建议对公式做字符串替换而应改公式引用的单元格内容。这个模式的优点是逻辑简单、对格式无感知缺点是大表性能偏低。如果文件里某几个 sheet 有 5 万行数据逐单元格比较 Python 字符串会明显变慢。优化手段是缩小扫描范围先确认旧值大概率出现在哪几列把iter_rows限定到这几列# 只扫描 B 列到 D 列步长更快 for row in ws.iter_rows(min_row1, min_col2, max_col4): for cell in row: ...3.2 模式二按工作表名称定向改写整行或整列有时候所有表结构相同但不是每个表都需要改。比如“上海”表只改第 2 行、“北京”表只改第 3 行。这时不要做全表扫描直接根据sheet_name分支处理能减少无效遍历。from openpyxl import load_workbook wb load_workbook(200_sheets_workbook_modified.xlsx) target_rows { 上海: [2, 5], 北京: [3], 广州: [2, 3, 4], } for sheet_name, rows in target_rows.items(): if sheet_name not in wb.sheetnames: print(f跳过: 工作簿中不存在 {sheet_name}) continue ws wb[sheet_name] for row_idx in rows: for col_idx in range(1, ws.max_column 1): cell ws.cell(rowrow_idx, columncol_idx) if cell.value is not None: # 数字原地乘 1.05字符串则在末尾加标记 if isinstance(cell.value, (int, float)): cell.value round(cell.value * 1.05, 2) elif isinstance(cell.value, str): cell.value cell.value [已复核] wb.save(200_sheets_workbook_modified.xlsx)这段代码里ws.max_column是当前工作表最后一列有内容的列号如果前面某些行存在格式残留max_column可能大于实际数据列数。严谨做法是每一行单独计算row_cells 0遇到连续 None 就停止。200 个 sheet 的行列总数一般可控但如果总单元格数超过几十万建议先用ws.calculate_dimension()确认使用范围。3.3 模式三用 sheet 名作为索引写入差异化内容真正高频的场景是“一表一机构”每个工作表的名称是机构编号或人员姓名需要在固定单元格写入该机构自己的特定值。这种需求的特点是改什么内容完全由 sheet 名决定。此时用一个字典维护映射关系最直观。from openpyxl import load_workbook wb load_workbook(region_report.xlsx) # 模拟每个机构对应的年度目标值 target_data { 华东: {A2: 120, B2: 一级}, 华北: {A2: 98, B2: 二级}, 华南: {A2: 150, B2: 一级}, } for sheet_name in wb.sheetnames: if sheet_name not in target_data: print(f警告: {sheet_name} 没有配置目标数据跳过) continue ws wb[sheet_name] for cell_addr, value in target_data[sheet_name].items(): ws[cell_addr] value # 写入来源标记方便后期核对 ws[H1] fauto-updated from {sheet_name} wb.save(region_report_filled.xlsx)这里有两个实操细节第一映射字典不要硬编码在代码里建议从外部 JSON 或另一个 Excel 读取这样改配置不需要改脚本第二写入新值后如果目标单元格本来带数字格式但写入了字符串格式可能变成“文本”形式后续 Excel 统计会出错。稳妥做法是在写入后显式重设cell.number_format 0.00等格式字符串。补充一张 openpyxl 加载参数的速查表在 200 个表的场景里几乎每次都会用到参数默认值作用与建议read_onlyFalseTrue 时只读遍历节省内存但无法修改单元格write_onlyFalseTrue 时只能追加写行适合新建上报大文件data_onlyFalseTrue 返回公式缓存值False 返回公式字符串keep_vbaFalse打开带宏的 .xlsm 时必须设 Truerich_textFalse保留富文本格式用于复杂单元格样式4. 200 工作表批量实战条件改写、跨表合并与性能控制4.1 场景把每个工作表中“销量”列的数值统一上调报表里经常出现“统一调价”“同比增长”这类批量数值改动。假设每个 sheet 的第 5 列是销量第 1 行是表头从第 2 行开始是数据我们要将所有表中的销量提高 10%并且把“状态”列从“待审核”改为“已审核”。from openpyxl import load_workbook wb load_workbook(sales_2025.xlsx) # 只处理符合命名规则的工作表 target_sheets [name for name in wb.sheetnames if name.startswith(区域)] for sheet_name in target_sheets: ws wb[sheet_name] for row in ws.iter_rows(min_row2, min_col1, max_colws.max_column): sales_cell row[4] # 第5列是销量 status_cell row[6] # 第7列是状态 if isinstance(sales_cell.value, (int, float)): sales_cell.value round(sales_cell.value * 1.10, 2) if status_cell.value 待审核: status_cell.value 已审核 ws.sheet_state visible # 如果之前有隐藏表顺手显示出来再处理 wb.save(sales_2025_updated.xlsx) print(f共处理 {len(target_sheets)} 个工作表)这里的参数值得细说iter_rows(min_row2, max_colws.max_column)是从第二行起到“最后一列有内容”为止遍历。但如果某个 sheet 只填了前两列ws.max_column返回的是这个 sheet 的最大列有时候是 25因为有格式残留这时row[6]访问第 7 列可能会因row元组长度不足而 IndexError。处理办法是用ws.cell(rowrow_idx, column7)代替解包把行号可控地传入。4.2 场景把 200 个表按关键列合并到一张总表有时候“更改内容”不只发生在原文件里而是要把 200 个工作表中的数据抽出来汇成总表。做法是用write_onlyTrue创建一个新工作簿逐 sheet 读取原文件再写入这样既能控制内存峰值又能保留原文件不动。from openpyxl import load_workbook, Workbook src_path per_region_data.xlsx dst_path all_regions_summary.xlsx src_wb load_workbook(src_path, read_onlyTrue) dst_wb Workbook(write_onlyTrue) dst_ws dst_wb.create_sheet(title汇总) header_written False for sheet_name in src_wb.sheetnames: ws src_wb[sheet_name] for row in ws.iter_rows(values_onlyTrue): # 跳过全空行 if all(cell is None for cell in row): continue if not header_written: dst_ws.append([来源表] list(row)) header_written True else: dst_ws.append([sheet_name] list(row)) src_wb.close() dst_wb.save(dst_path) print(f合并完成输出文件: {dst_path})write_onlyTrue模式下不能用dst_ws[A1] xxx这种坐标赋值只能用append()追加整行。它的好处是写入时几乎不占内存200 个 sheet、每个 5000 行左右的数据量可以很流畅地跑完。合并后的总表行数可能是几十万行Excel 打开会有点慢所以最后通常还要用 pandas 做一次去重或分类汇总再把结果写回。需要注意values_onlyTrue返回的单元格数据是“无格式”的日期会变成 datetime 对象、百分比会变成浮点。这样合并的优点是干净缺点是丢了原样式。如果想保留某些列的格式就得回到普通模式用copy模块复制单元格样式或者只针对特定列做number_format单独设置。4.3 性能控制200 个 sheet 为什么越跑越慢以及怎么解决很多人遇到的问题是脚本刚开始几个 sheet 很快后面越来越慢最后甚至卡死。原因通常是这三个一是wb.save()次数过多——每次保存都会把整个工作簿序列化一次200 个 sheet 在循环里保存 200 次等于重复写 200 次完整 XML二是单元格修改后没有及时释放大对象内存持续累积三是对公式单元格重复赋值触发 openpyxl 内部的 dirty 标记判断。正确做法是“先全部改完最后只保存一次”。对所有 sheet 的修改都集中在内存里的wb对象上全部循环结束后再调用wb.save()。如果文件实在太大比如超过 200MB可以拆分成几个逻辑批次每批 50 个 sheet 保存成一个临时文件最后用 zipfile 把临时文件打包而不是强行一次全部加载。# 分批保存示例每 50 个 sheet 存一个临时文件 import math all_sheets wb.sheetnames batch_size 50 batches math.ceil(len(all_sheets) / batch_size) for batch_idx in range(batches): batch_sheets all_sheets[batch_idx * batch_size:(batch_idx 1) * batch_size] part_wb load_workbook(source.xlsx, read_onlyTrue) part_wb.save(fpart_{batch_idx}.xlsx) part_wb.close()这段示意代码的重点是read_onlyTrue加载源文件后直接save()实现“半复制”的效果这样实际修改时只需要打开对应百分比的数据量从根上避免一次加载全部工作表。5. 收尾把改好的 .xlsx 打包为 zip并做完整性校验5.1 用 zipfile 打包修改后的 Excel附带校验信息Excel 的 .xlsx 本身就是一个 zip 包但你交付给使用方时通常需要把“原始文件 修改后文件 变更说明”打包在一个 zip 里方便对方一次拿全。用 Python 的 zipfile 标准库可以做到import zipfile import os files_to_zip [ 200_sheets_workbook_backup.xlsx, 200_sheets_workbook_modified.xlsx, 修改说明.txt, ] with zipfile.ZipFile(交付包_20250601.zip, w, zipfile.ZIP_DEFLATED) as zf: for f in files_to_zip: if os.path.exists(f): zf.write(f, arcnamef) # arcname 指定包内路径 print(f已打包: {f}) else: print(f跳过缺失文件: {f}) with zipfile.ZipFile(交付包_20250601.zip, r) as zf: for info in zf.infolist(): print(f{info.filename} original_size{info.file_size} compressed{info.compress_size})这里ZIP_DEFLATED是压缩算法对 xlsx 这种已经内部压缩过的文件来说压缩率有限但能起到“容器”作用。参数arcname如果不写zip 里会保留绝对路径容易把本机目录结构一并打出去所以务必指定成纯文件名。5.2 重新加载修改后的工作簿验证 200 个工作表是否完整打包不是终点。交付前我至少会做三层验证一是重新打开修改后的 xlsx确认工作表数量没变、名称顺序没乱二是抽查几个关键单元格的值三是检查是否有意外产生的空工作表。from openpyxl import load_workbook wb load_workbook(200_sheets_workbook_modified.xlsx, read_onlyTrue) expected_count 215 actual_count len(wb.sheetnames) assert actual_count expected_count, f工作表数量异常: {actual_count} ! {expected_count} # 抽查关键 sheet 的固定位置 for sheet_name in [华东, 华北, 华南]: if sheet_name in wb.sheetnames: val wb[sheet_name][A2].value print(f{sheet_name} A2 {val}) # 统计全空 sheet防止误操作产生空表 empty_sheets [] for name in wb.sheetnames: ws wb[name] if ws.max_row 1 and ws.max_column 1 and ws[A1].value is None: empty_sheets.append(name) print(f空工作表: {empty_sheets}) wb.close()这个脚本里read_onlyTrue模式下依然可以像普通模式一样用ws[A2]取值openpyxl 会自动解析对应坐标不用担心性能。断言失败时脚本直接抛异常适合挂进 CI 或定时任务里做回归检查。最后一层校验是 zip 包的 CRC 完整性用zipfile.ZipFile打开后迭代.infolist()对每个文件执行zf.read(info)如果能读出来且不报BadZipFile说明压缩包没有在传输过程中损坏。这一步对交付场景尤为重要因为 .xlsx 内部结构一旦损坏Excel 很可能直接提示“文件格式或扩展名无效”比数据错位更难排查。本文还有配套的精品资源点击获取