尧图网站开发 
1. 项目概述当Excel遇上Python效率革命就此开始如果你每天的工作都离不开Excel尤其是需要处理几十上百个文件手动打开、复制粘贴、汇总计算那感觉就像是在用勺子挖隧道。我干了十多年数据分析深知这种重复劳动的痛苦。直到我开始用Python来处理这些海量Excel数据才发现原来那些需要加班到深夜的工作现在喝杯咖啡的功夫就能搞定。这个项目就是把我这些年用Python批量处理Excel的实战经验从核心思路到完整代码毫无保留地分享给你。无论你是财务、运营、市场分析还是学生只要你有批量处理Excel的需求这篇内容就是为你量身定制的“效率加速器”。我们不会只讲空洞的理论而是直接上手用最流行的pandas和openpyxl库带你一步步构建一个从文件遍历、数据读取、清洗转换到批量输出的完整自动化流程。你会发现Python不是程序员的专属它完全可以成为你手中最趁手的办公利器。2. 核心工具选型为什么是Pandas和Openpyxl工欲善其事必先利其器。面对Python中众多的Excel处理库新手很容易眼花缭乱。我踩过坑也做过大量对比最终将核心工具锁定在pandas和openpyxl的组合上。这不是随意的选择背后有非常实际的考量。2.1 Pandas数据操作的“瑞士军刀”Pandas严格来说不是一个专门的Excel库它是一个强大的数据分析库。但正是因为它强大的DataFrame数据结构让它处理表格数据变得无比高效。核心优势内存计算与向量化操作。Pandas的DataFrame将数据加载到内存中后续的筛选、计算、分组聚合等操作都是基于内存的向量化运算速度比在Excel里写公式或VBA循环快几个数量级。比如对一列10万行的数据做求和pandas的df[‘column’].sum()几乎是瞬间完成。丰富的内置函数。数据清洗去重、填充空值、转换数据类型转换、列拆分合并、分析分组、透视表、合并多个DataFrame的拼接等功能应有尽有API设计也非常人性化。与Excel的桥梁pandas的read_excel()和to_excel()函数是其处理Excel的入口和出口。它底层可以调用openpyxl或xlrd等引擎来读写文件自身则专注于数据的处理逻辑。2.2 Openpyxl精细化控制Excel的“手术刀”如果说pandas是负责宏观数据搬运和加工的大卡车那么openpyxl就是能进行微观细胞级操作的精密手术刀。核心优势读写一切Excel属性。openpyxl可以直接读写单元格的样式字体、颜色、边框、公式、批注、图表、甚至冻结窗格和打印设置。这是pandas的to_excel方法比较薄弱的地方。适用场景当你需要生成格式复杂的报告或者需要读取包含公式、特定格式的模板文件时openpyxl是不可或缺的。例如将处理好的数据填入一个预设好公式和格式的报表模板中。性能注意openpyxl在读写非常大的文件如超过50万行时可能会比较慢且耗内存。对于纯大数据处理优先使用pandas。2.3 其他工具简析与避坑指南xlrd / xlwt这是比较老的库xlrd读在2.0版本后不再支持.xlsx格式只支持旧的.xls格式这是一个巨坑很多老教程还在用新手照着做一定会报错。所以除非你处理的是上古时期的.xls文件否则请直接忽略它们。xlsxwriter一个只写的库功能强大适合创建带有复杂格式和图表的新Excel文件但不能读取。常作为pandas的写入引擎之一。我们的选择策略纯数据处理读→计算→写优先使用pandas。设置engine‘openpyxl’即可。需要保留或设置复杂格式用openpyxl加载工作簿进行格式操作或者用pandas处理数据后再用openpyxl进行精细的格式美化。超大文件处理考虑使用pandas的chunksize参数分块读取或者使用专门的库如dask。实操心得对于90%的批量处理场景pandasopenpyxl的组合足以应对。我的标准工作流是用pandas做所有脏活累活数据清洗、计算最后如果需要精美格式再用openpyxl对生成的文件“化妆”。安装也非常简单pip install pandas openpyxl。3. 项目实战构建一个完整的批量处理脚本光说不练假把式。下面我们以一个真实的场景为例构建一个完整的脚本。假设你是一个区域销售分析师每天需要处理全国30个分公司发来的销售日报30个独立的Excel文件你的任务是将这些文件汇总计算每个产品的总销售额和平均单价并生成一份格式清晰的汇总报告和每个分公司的数据概览。3.1 环境准备与文件结构首先确保你的Python环境已经就绪。我强烈建议使用Anaconda发行版它集成了数据科学所需的绝大多数包包括pandas和openpyxl。如果不用Anaconda用pip安装也很简单。假设你的文件结构如下项目文件夹/ │ 批量处理脚本.py │ └───销售数据_原始/ │ │ 北京分公司_销售日报_20231027.xlsx │ │ 上海分公司_销售日报_20231027.xlsx │ │ 广州分公司_销售日报_20231027.xlsx │ │ ... (共30个文件) │ └───输出结果/ │ (脚本运行后将在此文件夹生成结果文件)3.2 核心代码模块拆解我们的脚本将按模块构建这样逻辑清晰也便于你未来修改和复用。3.2.1 模块一智能遍历与读取文件第一步不是直接读文件而是先找到所有需要处理的文件。这里要考虑到文件名的规范性。import os import pandas as pd from openpyxl import load_workbook from openpyxl.styles import Font, Alignment, Border, Side def find_excel_files(folder_path, suffix.xlsx): 查找指定文件夹下所有指定后缀的Excel文件。 参数: folder_path: 目标文件夹路径 suffix: 文件后缀默认为.xlsx 返回: 一个包含文件完整路径的列表 excel_files [] # os.walk会遍历文件夹内所有子文件夹 for root, dirs, files in os.walk(folder_path): for file in files: if file.endswith(suffix): full_path os.path.join(root, file) excel_files.append(full_path) print(f在文件夹 {folder_path} 中找到 {len(excel_files)} 个Excel文件。) return excel_files注意事项使用os.path.join来拼接路径而不是直接用字符串加号这能保证代码在Windows、Mac、Linux上都能正常运行。os.walk是递归遍历如果你确定文件都在一级目录下可以用os.listdir加判断速度更快。文件名可能包含空格或中文pandas和openpyxl都能很好处理但路径本身最好避免中文以防一些极端情况。3.2.2 模块二统一数据读取与初步清洗每个分公司的表格格式应该基本一致但难免有意外。我们需要一个健壮的读取函数。def read_and_clean_excel(file_path): 读取单个Excel文件并进行初步数据清洗。 假设每个文件只有一个工作表且表头在第一行。 try: # 使用pandas读取默认读取第一个工作表 # engineopenpyxl 确保支持.xlsx格式 df pd.read_excel(file_path, engineopenpyxl) # 基础清洗步骤 # 1. 去除列名中的空格和换行符 df.columns df.columns.str.strip().str.replace(\n, ) # 2. 去除完全为空的行和列 df.dropna(howall, inplaceTrue) df.dropna(axis1, howall, inplaceTrue) # 3. 从文件名中提取分公司名称假设文件名格式为“分公司名_销售日报_日期.xlsx” file_name os.path.basename(file_path) branch_name file_name.split(_)[0] # 获取“北京分公司” df[数据来源_分公司] branch_name # 新增一列标记数据来源 print(f成功读取并清洗文件: {file_name}) return df except Exception as e: print(f读取文件 {file_path} 时出错: {e}) # 返回一个空的DataFrame避免程序中断 return pd.DataFrame()为什么这么做try...except批量处理中单个文件出错不应该导致整个任务失败。捕获异常并记录让其他文件能继续处理。str.strip()原始数据中列名前后可能有空格这会导致后续按列名索引失败。新增“数据来源”列这是数据合并后的“生命线”在汇总后你依然能知道每行数据来自哪个分公司便于溯源和分区域分析。3.2.3 模块三核心数据处理逻辑这是业务逻辑的核心。我们假设每个文件的数据包含以下列产品编码、产品名称、销售数量、销售单价、销售额。def process_data(df): 对单个DataFrame进行业务逻辑处理。 计算每个产品的总销售额和平均单价。 if df.empty: return df # 确保数值列是数字类型非数字的强制转换为NaN numeric_columns [销售数量, 销售单价, 销售额] for col in numeric_columns: if col in df.columns: df[col] pd.to_numeric(df[col], errorscoerce) # 分组聚合计算按产品编码和名称分组 # 注意这里假设‘销售额’列已存在。如果不存在需要先计算df[‘销售额’] df[‘销售数量’] * df[‘销售单价’] grouped_df df.groupby([产品编码, 产品名称], as_indexFalse).agg({ 销售数量: sum, 销售额: sum, 数据来源_分公司: lambda x: , .join(sorted(set(x))) # 统计该产品在哪些分公司有销售 }) # 计算平均单价注意是总销售额/总数量不是单价的平均 grouped_df[平均单价] grouped_df[销售额] / grouped_df[销售数量] # 处理除零错误 grouped_df[平均单价] grouped_df[平均单价].replace([float(inf), -float(inf)], None) # 重命名列让输出更易懂 grouped_df.rename(columns{ 销售数量: 总销售数量, 销售额: 总销售额 }, inplaceTrue) # 对总销售额进行排序降序 grouped_df.sort_values(by总销售额, ascendingFalse, inplaceTrue) return grouped_df关键点解析pd.to_numeric(..., errors‘coerce’)这是数据清洗的黄金法则。原始Excel里经常混入“-”、“暂无”、“N/A”等文本直接计算会报错。这个函数会将无法转换的值变成NaN空值保证后续计算顺利进行。groupby().agg()这是pandas的灵魂操作相当于Excel的数据透视表。它高效地完成了按产品分类汇总的工作。平均单价的计算逻辑业务上产品的平均单价应该是总销售额除以总数量而不是对“销售单价”列求平均。因为一次交易中可能包含多个单价用加权平均更准确。这里体现了对业务的理解。3.2.4 模块四多文件批量汇总现在我们把前几个模块串起来处理整个文件夹。def batch_process_folder(input_folder, output_folder): 批量处理文件夹内所有Excel文件的主函数。 # 1. 查找文件 all_files find_excel_files(input_folder) if not all_files: print(未找到任何Excel文件程序退出。) return # 2. 初始化一个空列表用于存放每个文件的处理结果 list_of_dfs [] # 3. 循环处理每个文件 for file_path in all_files: raw_df read_and_clean_excel(file_path) if not raw_df.empty: processed_df process_data(raw_df) # 为每个分公司的结果添加一个标识列虽然process_data里已有但这里可以加更细的 processed_df[原始文件名] os.path.basename(file_path) list_of_dfs.append(processed_df) # 4. 合并所有结果 if list_of_dfs: # 使用concat合并ignore_indexTrue重置索引 final_summary_df pd.concat(list_of_dfs, ignore_indexTrue) # 可以对合并后的数据再做一次整体聚合例如全国每个产品的总销额 national_summary final_summary_df.groupby([产品编码, 产品名称], as_indexFalse).agg({ 总销售数量: sum, 总销售额: sum }).sort_values(by总销售额, ascendingFalse) # 5. 输出结果 output_path_summary os.path.join(output_folder, 全国销售汇总_明细.xlsx) output_path_national os.path.join(output_folder, 全国销售汇总_产品维度.xlsx) # 使用pandas的to_excel写入默认引擎就是openpyxl final_summary_df.to_excel(output_path_summary, indexFalse) national_summary.to_excel(output_path_national, indexFalse) print(f\n处理完成) print(f- 详细汇总已保存至: {output_path_summary}) print(f- 产品维度总览已保存至: {output_path_national}) # 6. (可选) 调用格式美化函数 format_excel_report(output_path_national) return final_summary_df, national_summary else: print(所有文件处理失败或未包含有效数据。) return None, None3.2.5 模块五使用Openpyxl进行格式美化用pandas生成的数据是“素颜”用openpyxl可以快速“上妆”让报告更专业。def format_excel_report(file_path): 使用openpyxl美化Excel报告。 设置标题行样式调整列宽设置数字格式。 wb load_workbook(file_path) ws wb.active # 定义样式 header_font Font(boldTrue, colorFFFFFF, size12) header_fill PatternFill(start_color366092, end_color366092, fill_typesolid) # 深蓝色填充 thin_border Border(leftSide(stylethin), rightSide(stylethin), topSide(stylethin), bottomSide(stylethin)) center_aligned Alignment(horizontalcenter, verticalcenter) money_format #,##0.00 # 千分位保留两位小数 # 应用标题行样式 for cell in ws[1]: # ws[1] 表示第一行 cell.font header_font cell.fill header_fill cell.alignment center_aligned cell.border thin_border # 调整列宽简单自适应 for column in ws.columns: max_length 0 column_letter column[0].column_letter for cell in column: try: if len(str(cell.value)) max_length: max_length len(str(cell.value)) except: pass adjusted_width min(max_length 2, 50) # 最大宽度限制为50 ws.column_dimensions[column_letter].width adjusted_width # 设置数字格式假设金额在第4列及之后这里需要根据实际列调整 # 例如如果‘总销售额’、‘平均单价’是数字列 for row in ws.iter_rows(min_row2): # 从第二行开始 # 假设‘总销售额’在D列第4列‘平均单价’在E列第5列 row[3].number_format money_format # D列 row[4].number_format money_format # E列 # 为所有数据单元格添加边框 for cell in row: cell.border thin_border wb.save(file_path) print(f已对文件 {os.path.basename(file_path)} 进行格式美化。)3.3 主程序入口最后我们提供一个简洁的主程序入口方便直接运行。if __name__ __main__: # 配置你的输入输出文件夹路径 input_folder ./销售数据_原始 # 替换为你的原始数据文件夹路径 output_folder ./输出结果 # 替换为你希望保存结果的文件夹路径 # 如果输出文件夹不存在则创建 if not os.path.exists(output_folder): os.makedirs(output_folder) # 执行批量处理 detail_df, summary_df batch_process_folder(input_folder, output_folder) # 可以在这里添加更多后续操作比如发送邮件等 # if summary_df is not None: # print(f处理了 {len(detail_df)} 条明细记录汇总了 {len(summary_df)} 种产品。)4. 高级技巧与性能优化当数据量从几十个文件变成几百个或者单个文件有几十万行时基础的脚本可能会变慢甚至内存溢出。下面分享几个进阶技巧。4.1 处理超大型Excel文件pandas的read_excel默认会将整个工作表读入内存。对于几百MB的文件这很吃力。分块读取pandas的read_excel函数有一个chunksize参数可以指定每次读取的行数返回一个迭代器。chunk_size 10000 chunk_iter pd.read_excel(‘huge_file.xlsx‘, engine‘openpyxl‘, chunksizechunk_size) processed_chunks [] for chunk in chunk_iter: # 对每个块进行清洗和处理 cleaned_chunk clean_data(chunk) processed_chunks.append(cleaned_chunk) # 最后将所有块合并 final_df pd.concat(processed_chunks, ignore_indexTrue)指定列/行读取如果只关心部分数据使用usecols参数指定需要读取的列用skiprows跳过不必要的行头能极大减少内存占用和读取时间。df pd.read_excel(‘file.xlsx‘, usecols‘A:C, E:G‘, skiprows3) # 只读A-C和E-G列跳过前3行4.2 利用多进程加速如果你的电脑是多核CPU并且文件之间处理相互独立可以使用Python的multiprocessing库进行并行处理速度提升显著。from multiprocessing import Pool, cpu_count def process_single_file(file_path): 包装之前的数据处理函数使其适用于多进程map df read_and_clean_excel(file_path) result_df process_data(df) result_df[‘原始文件名‘] os.path.basename(file_path) return result_df def batch_process_parallel(input_folder): all_files find_excel_files(input_folder) # 根据CPU核心数创建进程池通常留一个核心给系统 num_processes max(1, cpu_count() - 1) with Pool(processesnum_processes) as pool: # 使用pool.map并行执行函数 results pool.map(process_single_file, all_files) # 合并结果 final_df pd.concat([r for r in results if not r.empty], ignore_indexTrue) return final_df注意事项多进程适用于计算密集型任务且每个任务独立。如果任务需要频繁读写同一个磁盘或内存区域可能因资源竞争导致速度下降甚至出错。并行处理时打印日志可能会混乱需要小心处理。4.3 错误处理与日志记录在生产环境中一个健壮的脚本必须有完善的错误处理和日志。精细化异常捕获不要只用except Exception可以捕获更具体的异常如FileNotFoundError、PermissionError、KeyError列名不存在、ValueError数据转换错误等并做出不同处理。使用logging模块用logging模块替代print可以方便地控制日志级别DEBUG, INFO, WARNING, ERROR并输出到文件便于事后排查。import logging logging.basicConfig(levellogging.INFO, format‘%(asctime)s - %(levelname)s - %(message)s‘, handlers[logging.FileHandler(‘batch_process.log‘), logging.StreamHandler()]) def read_and_clean_excel(file_path): try: df pd.read_excel(file_path) logging.info(f“成功读取文件: {file_path}“) return df except FileNotFoundError: logging.error(f“文件不存在: {file_path}“) except Exception as e: logging.exception(f“读取文件 {file_path} 时发生未知错误“) # 会记录完整的异常堆栈 return pd.DataFrame()5. 常见问题与排查技巧实录在实际操作中你一定会遇到各种报错和奇怪的现象。下面是我总结的“排坑手册”。5.1 读取文件时报错错误信息可能原因解决方案FileNotFoundError文件路径错误或文件不存在。使用os.path.exists(file_path)检查路径。注意相对路径和绝对路径。在脚本开头打印当前工作目录os.getcwd()。PermissionError文件被其他程序如Excel打开或没有读取权限。关闭Excel或其他占用程序。检查文件权限。xlrd.biffh.XLRDError: Excel xlsx file not supported使用了过时的xlrd库读取.xlsx文件。安装openpyxl并在read_excel中指定engine‘openpyxl‘。或者升级pandas。KeyError: “[‘列名’] not in index”DataFrame中不存在你指定的列名。打印df.columns查看实际列名。检查列名是否有空格、大小写不一致。用df.columns.str.strip()清理。ValueError: Unable to parse string “N/A” at position 123数值列中混入了非数字字符串。使用pd.to_numeric(..., errors‘coerce‘)进行强制转换将错误值转为NaN。5.2 数据处理中的“坑”坑1合并后数据翻倍或丢失现象使用pd.concat或merge后行数变得异常。排查检查每个待合并的DataFrame的索引和列名是否一致。合并前使用df.reset_index(dropTrue)重置索引是个好习惯。检查merge时用的连接键on参数是否有重复值或空值。坑2分组groupby结果不符合预期现象分组后的数量、求和等结果和Excel手动计算对不上。排查首先检查分组键groupby的列是否有空格或不可见字符。其次检查用于计算的列是否真的都是数值类型用df.dtypes查看非数值类型参与计算会被忽略。最后确认聚合函数sum,mean是否是你想要的pandas默认会忽略NaN值。坑3内存溢出MemoryError现象处理大文件时程序崩溃。解决1. 使用chunksize分块读取。2. 只读取必要的列usecols。3. 及时删除不再用的大变量del big_df; gc.collect()。4. 将数值列转换为占用内存更小的类型如int32,float32使用df.astype()。5.3 写入文件时的注意事项Sheet名称问题to_excel的sheet_name参数不能包含: \ / ? * [ ]这些字符且长度有限制。编码问题如果数据包含中文在Windows下默认编码可能有问题。可以在to_excel时指定encoding‘utf-8-sig‘这样用Excel打开不会乱码。性能问题向一个已存在的Excel文件追加数据使用openpyxl直接操作单元格会非常慢。更好的做法是先将所有数据在pandas中处理好一次性写入或者使用pd.ExcelWriter配合mode‘a‘追加模式和if_sheet_exists‘overlay‘参数。5.4 一个实用的调试技巧在脚本的关键节点将中间结果输出到Excel或CSV看一眼比任何打印都管用。# 在怀疑出问题的步骤后保存中间结果 debug_df.to_excel(‘debug_step_1.xlsx‘, indexFalse) print(debug_df.head()) # 查看前几行 print(debug_df.shape) # 查看数据形状 (行数 列数) print(debug_df.dtypes) # 查看每列数据类型最后再分享一个我个人的习惯永远先在小样本数据上测试。从原始文件夹里复制3-5个文件到一个测试文件夹用测试文件夹路径运行脚本。确认逻辑正确、结果无误后再放到全量数据上运行。这能为你节省大量因一个小错误而重跑全量数据的时间。 
1. 项目概述当Excel遇上Python效率革命就此开始如果你每天的工作都离不开Excel尤其是需要处理几十上百个文件手动打开、复制粘贴、汇总计算那感觉就像是在用勺子挖隧道。我干了十多年数据分析深知这种重复劳动的痛苦。直到我开始用Python来处理这些海量Excel数据才发现原来那些需要加班到深夜的工作现在喝杯咖啡的功夫就能搞定。这个项目就是把我这些年用Python批量处理Excel的实战经验从核心思路到完整代码毫无保留地分享给你。无论你是财务、运营、市场分析还是学生只要你有批量处理Excel的需求这篇内容就是为你量身定制的“效率加速器”。我们不会只讲空洞的理论而是直接上手用最流行的pandas和openpyxl库带你一步步构建一个从文件遍历、数据读取、清洗转换到批量输出的完整自动化流程。你会发现Python不是程序员的专属它完全可以成为你手中最趁手的办公利器。2. 核心工具选型为什么是Pandas和Openpyxl工欲善其事必先利其器。面对Python中众多的Excel处理库新手很容易眼花缭乱。我踩过坑也做过大量对比最终将核心工具锁定在pandas和openpyxl的组合上。这不是随意的选择背后有非常实际的考量。2.1 Pandas数据操作的“瑞士军刀”Pandas严格来说不是一个专门的Excel库它是一个强大的数据分析库。但正是因为它强大的DataFrame数据结构让它处理表格数据变得无比高效。核心优势内存计算与向量化操作。Pandas的DataFrame将数据加载到内存中后续的筛选、计算、分组聚合等操作都是基于内存的向量化运算速度比在Excel里写公式或VBA循环快几个数量级。比如对一列10万行的数据做求和pandas的df[‘column’].sum()几乎是瞬间完成。丰富的内置函数。数据清洗去重、填充空值、转换数据类型转换、列拆分合并、分析分组、透视表、合并多个DataFrame的拼接等功能应有尽有API设计也非常人性化。与Excel的桥梁pandas的read_excel()和to_excel()函数是其处理Excel的入口和出口。它底层可以调用openpyxl或xlrd等引擎来读写文件自身则专注于数据的处理逻辑。2.2 Openpyxl精细化控制Excel的“手术刀”如果说pandas是负责宏观数据搬运和加工的大卡车那么openpyxl就是能进行微观细胞级操作的精密手术刀。核心优势读写一切Excel属性。openpyxl可以直接读写单元格的样式字体、颜色、边框、公式、批注、图表、甚至冻结窗格和打印设置。这是pandas的to_excel方法比较薄弱的地方。适用场景当你需要生成格式复杂的报告或者需要读取包含公式、特定格式的模板文件时openpyxl是不可或缺的。例如将处理好的数据填入一个预设好公式和格式的报表模板中。性能注意openpyxl在读写非常大的文件如超过50万行时可能会比较慢且耗内存。对于纯大数据处理优先使用pandas。2.3 其他工具简析与避坑指南xlrd / xlwt这是比较老的库xlrd读在2.0版本后不再支持.xlsx格式只支持旧的.xls格式这是一个巨坑很多老教程还在用新手照着做一定会报错。所以除非你处理的是上古时期的.xls文件否则请直接忽略它们。xlsxwriter一个只写的库功能强大适合创建带有复杂格式和图表的新Excel文件但不能读取。常作为pandas的写入引擎之一。我们的选择策略纯数据处理读→计算→写优先使用pandas。设置engine‘openpyxl’即可。需要保留或设置复杂格式用openpyxl加载工作簿进行格式操作或者用pandas处理数据后再用openpyxl进行精细的格式美化。超大文件处理考虑使用pandas的chunksize参数分块读取或者使用专门的库如dask。实操心得对于90%的批量处理场景pandasopenpyxl的组合足以应对。我的标准工作流是用pandas做所有脏活累活数据清洗、计算最后如果需要精美格式再用openpyxl对生成的文件“化妆”。安装也非常简单pip install pandas openpyxl。3. 项目实战构建一个完整的批量处理脚本光说不练假把式。下面我们以一个真实的场景为例构建一个完整的脚本。假设你是一个区域销售分析师每天需要处理全国30个分公司发来的销售日报30个独立的Excel文件你的任务是将这些文件汇总计算每个产品的总销售额和平均单价并生成一份格式清晰的汇总报告和每个分公司的数据概览。3.1 环境准备与文件结构首先确保你的Python环境已经就绪。我强烈建议使用Anaconda发行版它集成了数据科学所需的绝大多数包包括pandas和openpyxl。如果不用Anaconda用pip安装也很简单。假设你的文件结构如下项目文件夹/ │ 批量处理脚本.py │ └───销售数据_原始/ │ │ 北京分公司_销售日报_20231027.xlsx │ │ 上海分公司_销售日报_20231027.xlsx │ │ 广州分公司_销售日报_20231027.xlsx │ │ ... (共30个文件) │ └───输出结果/ │ (脚本运行后将在此文件夹生成结果文件)3.2 核心代码模块拆解我们的脚本将按模块构建这样逻辑清晰也便于你未来修改和复用。3.2.1 模块一智能遍历与读取文件第一步不是直接读文件而是先找到所有需要处理的文件。这里要考虑到文件名的规范性。import os import pandas as pd from openpyxl import load_workbook from openpyxl.styles import Font, Alignment, Border, Side def find_excel_files(folder_path, suffix.xlsx): 查找指定文件夹下所有指定后缀的Excel文件。 参数: folder_path: 目标文件夹路径 suffix: 文件后缀默认为.xlsx 返回: 一个包含文件完整路径的列表 excel_files [] # os.walk会遍历文件夹内所有子文件夹 for root, dirs, files in os.walk(folder_path): for file in files: if file.endswith(suffix): full_path os.path.join(root, file) excel_files.append(full_path) print(f在文件夹 {folder_path} 中找到 {len(excel_files)} 个Excel文件。) return excel_files注意事项使用os.path.join来拼接路径而不是直接用字符串加号这能保证代码在Windows、Mac、Linux上都能正常运行。os.walk是递归遍历如果你确定文件都在一级目录下可以用os.listdir加判断速度更快。文件名可能包含空格或中文pandas和openpyxl都能很好处理但路径本身最好避免中文以防一些极端情况。3.2.2 模块二统一数据读取与初步清洗每个分公司的表格格式应该基本一致但难免有意外。我们需要一个健壮的读取函数。def read_and_clean_excel(file_path): 读取单个Excel文件并进行初步数据清洗。 假设每个文件只有一个工作表且表头在第一行。 try: # 使用pandas读取默认读取第一个工作表 # engineopenpyxl 确保支持.xlsx格式 df pd.read_excel(file_path, engineopenpyxl) # 基础清洗步骤 # 1. 去除列名中的空格和换行符 df.columns df.columns.str.strip().str.replace(\n, ) # 2. 去除完全为空的行和列 df.dropna(howall, inplaceTrue) df.dropna(axis1, howall, inplaceTrue) # 3. 从文件名中提取分公司名称假设文件名格式为“分公司名_销售日报_日期.xlsx” file_name os.path.basename(file_path) branch_name file_name.split(_)[0] # 获取“北京分公司” df[数据来源_分公司] branch_name # 新增一列标记数据来源 print(f成功读取并清洗文件: {file_name}) return df except Exception as e: print(f读取文件 {file_path} 时出错: {e}) # 返回一个空的DataFrame避免程序中断 return pd.DataFrame()为什么这么做try...except批量处理中单个文件出错不应该导致整个任务失败。捕获异常并记录让其他文件能继续处理。str.strip()原始数据中列名前后可能有空格这会导致后续按列名索引失败。新增“数据来源”列这是数据合并后的“生命线”在汇总后你依然能知道每行数据来自哪个分公司便于溯源和分区域分析。3.2.3 模块三核心数据处理逻辑这是业务逻辑的核心。我们假设每个文件的数据包含以下列产品编码、产品名称、销售数量、销售单价、销售额。def process_data(df): 对单个DataFrame进行业务逻辑处理。 计算每个产品的总销售额和平均单价。 if df.empty: return df # 确保数值列是数字类型非数字的强制转换为NaN numeric_columns [销售数量, 销售单价, 销售额] for col in numeric_columns: if col in df.columns: df[col] pd.to_numeric(df[col], errorscoerce) # 分组聚合计算按产品编码和名称分组 # 注意这里假设‘销售额’列已存在。如果不存在需要先计算df[‘销售额’] df[‘销售数量’] * df[‘销售单价’] grouped_df df.groupby([产品编码, 产品名称], as_indexFalse).agg({ 销售数量: sum, 销售额: sum, 数据来源_分公司: lambda x: , .join(sorted(set(x))) # 统计该产品在哪些分公司有销售 }) # 计算平均单价注意是总销售额/总数量不是单价的平均 grouped_df[平均单价] grouped_df[销售额] / grouped_df[销售数量] # 处理除零错误 grouped_df[平均单价] grouped_df[平均单价].replace([float(inf), -float(inf)], None) # 重命名列让输出更易懂 grouped_df.rename(columns{ 销售数量: 总销售数量, 销售额: 总销售额 }, inplaceTrue) # 对总销售额进行排序降序 grouped_df.sort_values(by总销售额, ascendingFalse, inplaceTrue) return grouped_df关键点解析pd.to_numeric(..., errors‘coerce’)这是数据清洗的黄金法则。原始Excel里经常混入“-”、“暂无”、“N/A”等文本直接计算会报错。这个函数会将无法转换的值变成NaN空值保证后续计算顺利进行。groupby().agg()这是pandas的灵魂操作相当于Excel的数据透视表。它高效地完成了按产品分类汇总的工作。平均单价的计算逻辑业务上产品的平均单价应该是总销售额除以总数量而不是对“销售单价”列求平均。因为一次交易中可能包含多个单价用加权平均更准确。这里体现了对业务的理解。3.2.4 模块四多文件批量汇总现在我们把前几个模块串起来处理整个文件夹。def batch_process_folder(input_folder, output_folder): 批量处理文件夹内所有Excel文件的主函数。 # 1. 查找文件 all_files find_excel_files(input_folder) if not all_files: print(未找到任何Excel文件程序退出。) return # 2. 初始化一个空列表用于存放每个文件的处理结果 list_of_dfs [] # 3. 循环处理每个文件 for file_path in all_files: raw_df read_and_clean_excel(file_path) if not raw_df.empty: processed_df process_data(raw_df) # 为每个分公司的结果添加一个标识列虽然process_data里已有但这里可以加更细的 processed_df[原始文件名] os.path.basename(file_path) list_of_dfs.append(processed_df) # 4. 合并所有结果 if list_of_dfs: # 使用concat合并ignore_indexTrue重置索引 final_summary_df pd.concat(list_of_dfs, ignore_indexTrue) # 可以对合并后的数据再做一次整体聚合例如全国每个产品的总销额 national_summary final_summary_df.groupby([产品编码, 产品名称], as_indexFalse).agg({ 总销售数量: sum, 总销售额: sum }).sort_values(by总销售额, ascendingFalse) # 5. 输出结果 output_path_summary os.path.join(output_folder, 全国销售汇总_明细.xlsx) output_path_national os.path.join(output_folder, 全国销售汇总_产品维度.xlsx) # 使用pandas的to_excel写入默认引擎就是openpyxl final_summary_df.to_excel(output_path_summary, indexFalse) national_summary.to_excel(output_path_national, indexFalse) print(f\n处理完成) print(f- 详细汇总已保存至: {output_path_summary}) print(f- 产品维度总览已保存至: {output_path_national}) # 6. (可选) 调用格式美化函数 format_excel_report(output_path_national) return final_summary_df, national_summary else: print(所有文件处理失败或未包含有效数据。) return None, None3.2.5 模块五使用Openpyxl进行格式美化用pandas生成的数据是“素颜”用openpyxl可以快速“上妆”让报告更专业。def format_excel_report(file_path): 使用openpyxl美化Excel报告。 设置标题行样式调整列宽设置数字格式。 wb load_workbook(file_path) ws wb.active # 定义样式 header_font Font(boldTrue, colorFFFFFF, size12) header_fill PatternFill(start_color366092, end_color366092, fill_typesolid) # 深蓝色填充 thin_border Border(leftSide(stylethin), rightSide(stylethin), topSide(stylethin), bottomSide(stylethin)) center_aligned Alignment(horizontalcenter, verticalcenter) money_format #,##0.00 # 千分位保留两位小数 # 应用标题行样式 for cell in ws[1]: # ws[1] 表示第一行 cell.font header_font cell.fill header_fill cell.alignment center_aligned cell.border thin_border # 调整列宽简单自适应 for column in ws.columns: max_length 0 column_letter column[0].column_letter for cell in column: try: if len(str(cell.value)) max_length: max_length len(str(cell.value)) except: pass adjusted_width min(max_length 2, 50) # 最大宽度限制为50 ws.column_dimensions[column_letter].width adjusted_width # 设置数字格式假设金额在第4列及之后这里需要根据实际列调整 # 例如如果‘总销售额’、‘平均单价’是数字列 for row in ws.iter_rows(min_row2): # 从第二行开始 # 假设‘总销售额’在D列第4列‘平均单价’在E列第5列 row[3].number_format money_format # D列 row[4].number_format money_format # E列 # 为所有数据单元格添加边框 for cell in row: cell.border thin_border wb.save(file_path) print(f已对文件 {os.path.basename(file_path)} 进行格式美化。)3.3 主程序入口最后我们提供一个简洁的主程序入口方便直接运行。if __name__ __main__: # 配置你的输入输出文件夹路径 input_folder ./销售数据_原始 # 替换为你的原始数据文件夹路径 output_folder ./输出结果 # 替换为你希望保存结果的文件夹路径 # 如果输出文件夹不存在则创建 if not os.path.exists(output_folder): os.makedirs(output_folder) # 执行批量处理 detail_df, summary_df batch_process_folder(input_folder, output_folder) # 可以在这里添加更多后续操作比如发送邮件等 # if summary_df is not None: # print(f处理了 {len(detail_df)} 条明细记录汇总了 {len(summary_df)} 种产品。)4. 高级技巧与性能优化当数据量从几十个文件变成几百个或者单个文件有几十万行时基础的脚本可能会变慢甚至内存溢出。下面分享几个进阶技巧。4.1 处理超大型Excel文件pandas的read_excel默认会将整个工作表读入内存。对于几百MB的文件这很吃力。分块读取pandas的read_excel函数有一个chunksize参数可以指定每次读取的行数返回一个迭代器。chunk_size 10000 chunk_iter pd.read_excel(‘huge_file.xlsx‘, engine‘openpyxl‘, chunksizechunk_size) processed_chunks [] for chunk in chunk_iter: # 对每个块进行清洗和处理 cleaned_chunk clean_data(chunk) processed_chunks.append(cleaned_chunk) # 最后将所有块合并 final_df pd.concat(processed_chunks, ignore_indexTrue)指定列/行读取如果只关心部分数据使用usecols参数指定需要读取的列用skiprows跳过不必要的行头能极大减少内存占用和读取时间。df pd.read_excel(‘file.xlsx‘, usecols‘A:C, E:G‘, skiprows3) # 只读A-C和E-G列跳过前3行4.2 利用多进程加速如果你的电脑是多核CPU并且文件之间处理相互独立可以使用Python的multiprocessing库进行并行处理速度提升显著。from multiprocessing import Pool, cpu_count def process_single_file(file_path): 包装之前的数据处理函数使其适用于多进程map df read_and_clean_excel(file_path) result_df process_data(df) result_df[‘原始文件名‘] os.path.basename(file_path) return result_df def batch_process_parallel(input_folder): all_files find_excel_files(input_folder) # 根据CPU核心数创建进程池通常留一个核心给系统 num_processes max(1, cpu_count() - 1) with Pool(processesnum_processes) as pool: # 使用pool.map并行执行函数 results pool.map(process_single_file, all_files) # 合并结果 final_df pd.concat([r for r in results if not r.empty], ignore_indexTrue) return final_df注意事项多进程适用于计算密集型任务且每个任务独立。如果任务需要频繁读写同一个磁盘或内存区域可能因资源竞争导致速度下降甚至出错。并行处理时打印日志可能会混乱需要小心处理。4.3 错误处理与日志记录在生产环境中一个健壮的脚本必须有完善的错误处理和日志。精细化异常捕获不要只用except Exception可以捕获更具体的异常如FileNotFoundError、PermissionError、KeyError列名不存在、ValueError数据转换错误等并做出不同处理。使用logging模块用logging模块替代print可以方便地控制日志级别DEBUG, INFO, WARNING, ERROR并输出到文件便于事后排查。import logging logging.basicConfig(levellogging.INFO, format‘%(asctime)s - %(levelname)s - %(message)s‘, handlers[logging.FileHandler(‘batch_process.log‘), logging.StreamHandler()]) def read_and_clean_excel(file_path): try: df pd.read_excel(file_path) logging.info(f“成功读取文件: {file_path}“) return df except FileNotFoundError: logging.error(f“文件不存在: {file_path}“) except Exception as e: logging.exception(f“读取文件 {file_path} 时发生未知错误“) # 会记录完整的异常堆栈 return pd.DataFrame()5. 常见问题与排查技巧实录在实际操作中你一定会遇到各种报错和奇怪的现象。下面是我总结的“排坑手册”。5.1 读取文件时报错错误信息可能原因解决方案FileNotFoundError文件路径错误或文件不存在。使用os.path.exists(file_path)检查路径。注意相对路径和绝对路径。在脚本开头打印当前工作目录os.getcwd()。PermissionError文件被其他程序如Excel打开或没有读取权限。关闭Excel或其他占用程序。检查文件权限。xlrd.biffh.XLRDError: Excel xlsx file not supported使用了过时的xlrd库读取.xlsx文件。安装openpyxl并在read_excel中指定engine‘openpyxl‘。或者升级pandas。KeyError: “[‘列名’] not in index”DataFrame中不存在你指定的列名。打印df.columns查看实际列名。检查列名是否有空格、大小写不一致。用df.columns.str.strip()清理。ValueError: Unable to parse string “N/A” at position 123数值列中混入了非数字字符串。使用pd.to_numeric(..., errors‘coerce‘)进行强制转换将错误值转为NaN。5.2 数据处理中的“坑”坑1合并后数据翻倍或丢失现象使用pd.concat或merge后行数变得异常。排查检查每个待合并的DataFrame的索引和列名是否一致。合并前使用df.reset_index(dropTrue)重置索引是个好习惯。检查merge时用的连接键on参数是否有重复值或空值。坑2分组groupby结果不符合预期现象分组后的数量、求和等结果和Excel手动计算对不上。排查首先检查分组键groupby的列是否有空格或不可见字符。其次检查用于计算的列是否真的都是数值类型用df.dtypes查看非数值类型参与计算会被忽略。最后确认聚合函数sum,mean是否是你想要的pandas默认会忽略NaN值。坑3内存溢出MemoryError现象处理大文件时程序崩溃。解决1. 使用chunksize分块读取。2. 只读取必要的列usecols。3. 及时删除不再用的大变量del big_df; gc.collect()。4. 将数值列转换为占用内存更小的类型如int32,float32使用df.astype()。5.3 写入文件时的注意事项Sheet名称问题to_excel的sheet_name参数不能包含: \ / ? * [ ]这些字符且长度有限制。编码问题如果数据包含中文在Windows下默认编码可能有问题。可以在to_excel时指定encoding‘utf-8-sig‘这样用Excel打开不会乱码。性能问题向一个已存在的Excel文件追加数据使用openpyxl直接操作单元格会非常慢。更好的做法是先将所有数据在pandas中处理好一次性写入或者使用pd.ExcelWriter配合mode‘a‘追加模式和if_sheet_exists‘overlay‘参数。5.4 一个实用的调试技巧在脚本的关键节点将中间结果输出到Excel或CSV看一眼比任何打印都管用。# 在怀疑出问题的步骤后保存中间结果 debug_df.to_excel(‘debug_step_1.xlsx‘, indexFalse) print(debug_df.head()) # 查看前几行 print(debug_df.shape) # 查看数据形状 (行数 列数) print(debug_df.dtypes) # 查看每列数据类型最后再分享一个我个人的习惯永远先在小样本数据上测试。从原始文件夹里复制3-5个文件到一个测试文件夹用测试文件夹路径运行脚本。确认逻辑正确、结果无误后再放到全量数据上运行。这能为你节省大量因一个小错误而重跑全量数据的时间。