处理表格类数据这件事几乎每个办公党、数据分析师、运营都躲不开。Excel和CSV格式的批量处理更是日常工作中最常见的需求。Python在这块的优势非常明显——能用脚本替代重复机械化操作而且跑得比人手工操作快得多。这篇文章我想把实际工作中积攒下来的批量处理经验、代码模板、还有那些坑一次性讲清楚。无论你是刚接触Python的办公人员还是已经写过不少脚本的开发者这篇文章都能给你带来一些可以直接用的思路和代码。1. 工具选型与准备搞清楚该用什么库1.1 核心库的选择逻辑做Excel和CSV批量处理Python生态里常用的库就那么几个pandas、openpyxl、xlrd、csv。很多人一上来就纠结要不要学pandas我的建议是必须学而且要当成主力工具。因为它对表格数据的处理能力是其他库无法比拟的读文件、清洗数据、合并汇总、导出结果几行代码就能搞定。pandas底层依赖其他库来操作Excel文件——读xlsx文件依赖openpyxl或者老版本的xlrd读xls文件依赖xlrd写xlsx文件依赖openpyxl。所以实际项目中我几乎都是搭配使用pandas做主逻辑openpyxl做精细化的样式控制。这里必须要提一个容易踩坑的点xlrd自从2.0版本之后就已经不再支持xlsx格式了只支持老的xls格式。如果你用pandas读取xlsx时报错说找不到引擎八成是xlrd版本太新导致的不兼容。解决办法很简单安装openpyxl并在读取时指定engineopenpyxl。1.2 环境准备与安装命令安装环境的操作没啥技术含量但版本管理如果没做好后面会很痛。我建议先建一个独立的虚拟环境别把项目依赖和系统Python环境混在一起。命令行执行下面这段就能搞定python -m venv excel_env # Windows系统激活虚拟环境 excel_env\Scripts\activate # macOS / Linux系统激活虚拟环境 source excel_env/bin/activate pip install pandas openpyxl xlrd xlwt把pandas、openpyxl、xlrd、xlwt都装上基本就能覆盖绝大部分场景了。xlwt是用来写老版xls文件的虽然现在用得不多了但偶尔遇到一些老的业务系统只认xls格式有它就方便多了。提示如果你的电脑上同时装了Python 2和Python 3命令行里可能需要用python3和pip3来区分版本。检查版本是否安装成功执行python -c import pandas; print(pandas.__version__)能输出版本号就说明环境OK。2. 批量文件的定位与遍历脚本的第一步2.1 文件路径的获取方式批量处理的第一步是让程序知道要处理哪些文件。一种笨办法是把所有文件名硬编码到代码里但这种做法我强烈不建议——文件一多就得改代码而且容易出错。更合理的方案是让脚本自己去扫描目录自动找出所有符合条件的文件。标准做法是用glob模块或pathlib模块。比如一个文件夹里放着几十个CSV文件名字分别是“销售数据_202401.csv”“销售数据_202402.csv”这样的格式用glob一行就能把所有匹配的文件路径抓出来import glob # 获取当前目录下所有CSV文件 csv_files glob.glob(销售数据_*.csv) for file in csv_files: print(file)glob的语法很简单星号*代表任意多个字符问号?代表任意单个字符。如果目录里还有子文件夹需要递归搜索的话用**配合参数就能实现。不过我更推荐pathlib它是Python 3.4之后引入的面向对象路径处理库写法更现代代码也更优雅from pathlib import Path # 递归获取data目录下所有Excel文件 data_dir Path(data) excel_files list(data_dir.rglob(*.xlsx)) for f in excel_files: print(f, f.name, f.stem, f.suffix)f.name拿到的是带后缀的文件名f.stem是不带后缀的文件名f.suffix是扩展名。这几个属性在实际处理中非常有用——比如你想把“销售数据_202401.xlsx”这个文件里的内容合并进最终结果表时可以用f.stem解析出“202401”这个时间标记然后生成新的一列。2.2 文件名的分类与匹配技巧实际项目里文件名往往蕴含着很重要的业务信息比如时间、地区、渠道、数据批次。批量处理时善用文件名信息能省下大量手工拆解的时间。比如“华东区_2024年1月.xlsx”这样的文件中间用下划线分隔了“地区”和“时间”两个维度处理时用字符串分割就能把这两个维度提取出来直接作为数据列写入结果表。from pathlib import Path def parse_filename(filepath): 从文件名中解析出业务信息 fname Path(filepath).stem # 例如: 华东区_2024年1月 parts fname.split(_) region parts[0] # 华东区 period parts[1] # 2024年1月 return region, period这种匹配思路应用到真实项目中你会发现脚本的通用性大大提升——新增一个月的数据文件不需要改代码直接丢进文件夹跑一遍就行。3. CSV文件的批量处理与常见坑3.1 编码问题是CSV处理的第一大痛点CSV文件看似简单——纯文本、逗号分隔但实际上处理起来坑非常多最大的坑就是编码问题。中文环境下的CSV文件常见的编码有utf-8、utf-8-sig、gbk、gb2312等。用pandas读取时如果不指定编码默认是按utf-8解码的一旦文件实际是gbk编码程序直接就报UnicodeDecodeError。我自己踩过好多次这个坑后来总结出一个比较稳妥的处理办法写一个自动识别编码的函数读文件时先探测编码格式再读取。chardet库可以完成这个任务虽然它不适合处理超大文件但对日常的几MB数据文件来说性能完全够用import chardet import pandas as pd def detect_encoding(filepath): 自动检测文件编码 with open(filepath, rb) as f: data f.read(10000) # 取前1万字节做检测就足够了 result chardet.detect(data) return result[encoding] # 读取CSV前先检测编码 filepath data.csv enc detect_encoding(filepath) df pd.read_csv(filepath, encodingenc)注意用utf-8-sig编码生成CSV文件可以在文件开头写入一个BOM标记字节顺序标记这样用Excel直接打开CSV时中文不会乱码。这是一个非常有用的技巧——utf-8生成的CSV用Excel打开中文会变成乱码但utf-8-sig不会。3.2 CSV批量读取、清洗与合并批量处理一批CSV文件时最典型的场景就是合并汇总——把几十个结构相同的CSV文件合并成一个总表。思路很直接循环读取每个文件用pd.concat拼接起来。但这里有个重要细节如果每个CSV文件都带着表头合并时要注意只保留一个表头否则结果表里会出现几十行“列名”。import pandas as pd import glob def merge_csv_files(pattern): 批量合并CSV文件为一个DataFrame all_data [] for filepath in glob.glob(pattern): df pd.read_csv(filepath, encodingutf-8-sig) all_data.append(df) result pd.concat(all_data, ignore_indexTrue) return result # 合并当前目录下的所有CSV combined_df merge_csv_files(*.csv) print(combined_df.shape)ignore_indexTrue这个参数是必须写的它的作用是让合并后的DataFrame自动生成新的索引从0开始编号而不是把每个文件原有的索引也拼在一起导致索引重复混乱。合并完成后对数据的清洗操作就有很大发挥空间了——比如去掉完全重复的行填充缺失值统一日期格式这些操作在pandas里都是一行代码的事情# 去除完全重复的行 combined_df combined_df.drop_duplicates() # 数值列的空值填充为0 combined_df[销售额] combined_df[销售额].fillna(0) # 日期字符串统一为 datetime 类型 combined_df[日期] pd.to_datetime(combined_df[日期])3.3 大CSV文件的性能优化思路总有人问“我有几个GB的CSV文件用pandas读取直接内存爆了怎么办”出现这种情况多半是没搞清楚pandas处理大文件的正确姿势。read_csv有一个chunksize参数可以分块读取——每块处理完就释放内存不会一次性把整个文件全部装进内存import pandas as pd chunk_size 10000 # 每次读1万行 processed_chunks [] for chunk in pd.read_csv(huge_file.csv, chunksizechunk_size): # 对每个分块做需要的处理 chunk[销售额] chunk[销售额].fillna(0) processed_chunks.append(chunk) # 最后再合并结果 result pd.concat(processed_chunks, ignore_indexTrue)这种方法虽然没有从根本上减少计算量但大幅度降低了内存峰值。如果你的数据量到了几十GB级别那就不能再用pandas这套方案了要换成polars或者dask或者干脆用数据库来搞定——不过这是另一个话题了日常办公场景下到不了那个量级。4. Excel文件的批量处理与格式保留4.1 openpyxl和pandas在Excel处理上的分工处理Excel文件时pandas和openpyxl各有擅长。pandas擅长做数据层面的批量处理——读取、计算、透视、汇总速度也快openpyxl擅长做文件层面的精细化控制——合并单元格、设置单元格格式、字体颜色、边框。实际项目里的分工是先用pandas批量处理数据拿到最终结果表然后交给openpyxl做格式美化再保存成正式交付的报告文件。举个例子几十个Excel文件里的销售明细要汇总成一张月度报表最终呈现出来的报表需要有标题行、加粗的列名、合计行这些格式操作交给openpyxl最顺手。from openpyxl import Workbook from openpyxl.styles import Font, PatternFill, Alignment def save_with_format(df, output_path): 将DataFrame保存为带格式的Excel文件 wb Workbook() ws wb.active # 写入表头 headers list(df.columns) ws.append(headers) # 表头文字加粗 背景填充 for cell in ws[1]: cell.font Font(boldTrue) cell.fill PatternFill(start_colorDDEBF7, end_colorDDEBF7, fill_typesolid) # 写入数据行 for row in df.itertuples(indexFalse): ws.append(list(row)) wb.save(output_path)4.2 多工作簿、多工作表的批量操作批量处理Excel的另一个高频场景是一个文件夹里几十个Excel文件每个文件里又有多个Sheet工作表想把所有Sheet的所有数据都汇总起来。直接用pandas的read_excel默认只读第一个Sheet所以循环里要用sheet_nameNone或sheet_name传入要读取的工作表名import pandas as pd from pathlib import Path def read_all_sheets(filepath): 读取Excel文件的所有工作表 # sheet_nameNone 时返回一个 {sheet名: DataFrame} 的字典 sheets_dict pd.read_excel(filepath, sheet_nameNone, engineopenpyxl) return sheets_dict # 遍历目录下所有xlsx文件 all_data [] for excel_file in Path(data).glob(*.xlsx): sheets read_all_sheets(excel_file) for sheet_name, df in sheets.items(): df[来源文件] excel_file.name # 记录来源文件 df[来源工作表] sheet_name # 记录来源工作表 all_data.append(df) final pd.concat(all_data, ignore_indexTrue)加“来源文件”和“来源工作表”两列是个很实用的小技巧。万一合并后的数据有问题你可以顺着这两列回溯到原始文件去核对——在做数据审计或排查问题时这个信息比黄金还贵。4.3 保留公式与保留数值的选择处理Excel时还会遇到一个选择数据里包含公式你是要保留公式还是只要算好的数值用openpyxl直接打开文件时默认读取的就是公式本身比如SUM(A1:A10)而pandas的read_excel读取的则是公式计算后的缓存值。按我个人经验数据汇总分析一般取数值结果就够了——公式本身在批量处理过程中意义不大反而可能因为引用了其他单元格导致处理出错。但如果你要做的是报表改造需要保留原来的公式结构那就要用openpyxl的data_onlyFalse默认方式读取再原样写回。5. 批量处理与合并的原理为什么pandas是主力5.1 DataFrame的核心概念与优势pandas之所以在表格处理领域无人能敌核心是它提供了一种叫DataFrame的数据结构。你可以把它理解成一个内存里的超级Excel表格——有行索引、有列名支持各种花式查询和聚合。底层的计算用到了面向数组的向量化操作比Python原生用for循环逐行遍历要快得多。举个例子你要把“销售额”列的所有空值填成0用Python原生语法遍历一遍可能得写好几行代码用pandas就是一行df[销售额].fillna(0)。这套基操熟练之后处理一张几万行的表基本就是毫秒级的事情。5.2 合并多个文件时的索引处理细节前面讲过合并文件时用pd.concat但concat只能做纵向或横向的简单拼接。如果两个文件有公共列比如都有“订单ID”想要按这个公共列把两个表的数据拼到一起需要用的就是pd.merge操作类似于SQL里的JOIN概念# 按订单ID关联两个表类似于SQL的INNER JOIN merged pd.merge(df_order, df_customer, on订单ID, howleft)合并的原则很简单明确主表、明确关联键、明确合并方式how参数。日常场景里最常用的是howleft——左表记录全保留右表只取能匹配上的数据匹配不上右表字段就是空值。这个操作在汇总业务数据、匹配客户信息时非常高频建议多练几遍。6. 数据处理中的异常捕获与日志记录6.1 常见异常类型与catch方式批量处理几十甚至几百个文件最怕的情况是脚本跑到第57个文件时崩了前面56个都白跑。处理大量文件时程序必须有抗错能力——某个文件有问题单独跳过并记录不要让整个批次中断掉。实操上就是在循环里给每个文件的处理逻辑套上try-except块import traceback failed_files [] for filepath in all_files: try: df pd.read_excel(filepath, engineopenpyxl) # ... 其他处理逻辑 ... except Exception as e: failed_files.append((filepath, str(e))) print(f处理失败: {filepath}, 错误: {e}) traceback.print_exc() # 打印完整错误堆栈方便排查 # 批量处理完成后统一查看失败清单 print(f失败文件数: {len(failed_files)}) for f, err in failed_files: print(f)把失败的文件路径和错误信息统一记录下来跑完之后再去逐个排查这是多文件批量处理的标准操作。6.2 断点续跑与幂等设计如果被处理的文件特别多或者单个文件的数据量特别大脚本运行时间可能会很长。这时候有一个更高级的优化思路处理前先检查目标输出的中间结果是否已经存在如果已存在就跳过该文件不重复处理。这种“断点续跑”的设计不仅能防止程序意外中断前功尽弃还能在重新执行脚本时节省大量时间。from pathlib import Path processed_flag_dir Path(processed_flags) processed_flag_dir.mkdir(exist_okTrue) for filepath in all_files: flag_file processed_flag_dir / f{Path(filepath).stem}.done if flag_file.exists(): print(f跳过已完成文件: {filepath}) continue # 执行真正的处理逻辑 # 处理成功后生成标记文件 flag_file.touch()这个逻辑非常实用尤其是处理文件数量多、一次跑不完的情况。程序中断后重新启动所有已完成的文件会被自动跳过直接从断点继续。7. 导出与归档批量输出带时间戳的结果文件7.1 按业务维度拆分成多个输出文件批量处理的最终结果有时候是一个大汇总表有时候却是要拆分成多个小文件。比如你已经把所有门店的销售明细汇总起来了现在要把每个门店的结果单独存成一个Excel文件发给对应的人。这时可以用groupby循环实现一键拆分import pandas as pd from pathlib import Path output_dir Path(output) output_dir.mkdir(exist_okTrue) # 按门店名称分组每组保存为一个独立文件 for store_name, group_df in df.groupby(门店名称): safe_name store_name.replace(/, _) # 处理文件名中不能包含的字符 output_path output_dir / f门店_{safe_name}_销售汇总.xlsx group_df.to_excel(output_path, indexFalse, engineopenpyxl) print(f已导出: {output_path})7.2 自动生成带日期的归档目录工作的最后一个步骤往往是把处理结果归档保存方便以后查阅。我的做法是结果文件统一放入以当天日期命名的文件夹里这样每天跑一次脚本就会自动生成一个归档目录历史记录一天都不会乱。from datetime import datetime today_str datetime.now().strftime(%Y%m%d) archive_dir Path(archive) / today_str archive_dir.mkdir(parentsTrue, exist_okTrue) # 最终结果写入归档目录 output_path archive_dir / 处理结果.xlsx final_df.to_excel(output_path, indexFalse, engineopenpyxl)8. 性能优化的三个层次由易到难的实际操作8.1 语法层面的优化向量化替代循环很多人处理数据时习惯写for循环逐行遍历DataFrame这个习惯在数据量大的时候会让脚本慢到一个难以忍受的程度。pandas的向量化操作是可以直接用整列做运算的效率比逐行遍历高出百倍都不止# 低效写法 for i, row in df.iterrows(): if row[销售额] 1000: df.loc[i, 级别] 高 # 高效写法向量化 布尔索引 df[级别] 低 df.loc[df[销售额] 1000, 级别] 高第二种写法是利用了pandas的底层数组运算执行效率远高于逐行遍历。iterrows()这类方法在数据量大之后是明显的性能瓶颈能不用就不用。8.2 文件读写层面的优化减少重复IO批量处理涉及大量文件读写时建议先把所有数据读入内存处理完再一次批量写出。频繁打开文件、保存文件是非常耗时的操作。如果文件格式允许优先考虑用CSV做中间交换格式——CSV读写的速度显著快于Excel格式。8.3 当单机性能不再够用时如果数据量大到单机处理都吃力了那就要考虑用多进程并行处理。Excel和CSV的读取、简单清洗都是典型的IO密集型操作用multiprocessing或者concurrent.futures可以实现真正多核并行from concurrent.futures import ProcessPoolExecutor def process_one_file(filepath): df pd.read_csv(filepath, encodingutf-8-sig) # 这里是对单个文件的业务处理逻辑 return df all_files glob.glob(data/*.csv) with ProcessPoolExecutor(max_workers4) as executor: results list(executor.map(process_one_file, all_files))多进程的核心是让每个CPU内核各干各的活。但注意不要让每个进程都跑特别重的内存密集型任务否则可能因为内存争抢带来反效果。9. 常见问题速查批量处理Excel与CSV的7个高频坑9.1 编码与格式类问题问题现象可能原因解决办法UnicodeDecodeErrorCSV文件编码不是utf-8用chardet检测编码或用gbk读取Excel打开生成的文件后中文乱码CSV文件是utf-8编码保存时改用utf-8-sig编码带BOM头读取xlsx时报错找不到引擎缺少openpyxl或xlrd版本不匹配安装openpyxl读取时指定engineopenpyxl文件路径包含中文和空格pandas无法解析用pathlib.Path对象传入路径或调整路径分隔符9.2 数据与流程类问题问题现象可能原因解决办法合并后表头重复出现多个文件的头行没有被识别读取时指定header0合并时设置ignore_indexTrue日期变成了时间戳或字符串日期类数据没有正确解析读取时指定parse_dates[日期列名]数字列出现空值或逗号数值列中混入了非数值内容pd.to_numeric(..., errorscoerce)强制转换结果文件太大保存迟滞单文件数据量过大拆分输出或先压缩中间数据去掉用不到的列10. 最后批量处理的心智模型与经验我处理表格文件这几年来最大的体会不是工具本身而是流程设计的重要性。拿到需求后先想清楚四件事输入是什么、输出是什么、中间要做哪些处理、遇到异常怎么兜底。把这四件事用注释写进代码最前面后面再大的脚本都不会乱。另外一个非常实用的经验我放在最后说批量处理脚本永远不要只写一份。把读文件、处理、导出拆成三个独立函数你下次处理一个类似场景时直接把函数搬过去改改参数就能复用。这比每次从头写脚本节省的时间真的是以天计算的。拿批处理Excel和CSV这个场景来说从批量读取、清洗合并、异常兜底到自动归档整套流程其实走的就是这个模式。希望这篇文章里的代码和思路能直接用到你的下一个实际任务里。以后碰到几十个文件要处理的时候沉着一点——先想清楚流程再让代码帮你跑。