Python 办公自动化里Excel 表格处理是需求最集中、可复用价值最高的方向。很多同学已经会用 pandas 读一张表、用 openpyxl 写几个单元格但一旦把人放进真正的工单或“py100—lv2 系列”这类进阶练习里难点往往不是单个 API而是如何处理几十份格式混乱的 Excel、如何跨 Sheet 跨文件合并、如何在保留公式、下拉列表和图表的情况下交付结果、以及脚本上线后如何通过日志快速定位问题。这篇文章用一条完整的“门店销售周报汇总”需求串起整个过程。这个场景覆盖了多文件读取、数据清洗、聚合计算、样式报表生成、批处理和异常排查是办公自动化 Excel 高级应用里很典型的综合练习。1. 先理解 Excel 高级自动化的难点不是读写而是数据形态还原1.1 Excel 同时是数据源和展示层Excel 里的一个单元格落在人眼里是一个值落在 Python 里却可能是字符串、日期、浮点数、公式甚至是空值。普通脚本只要能做到“读出来、写回去”很容易被一段演示代码误以为已经掌握。真正进入高级阶段后复杂性来自 Excel 自身的“双重身份”它是数据源存放表格、分类、统计公式和原始记录。它也是展示层包含合并单元格、空行、字体颜色、列宽、数字格式、批注、图表和下接列表。人眼读取数据时会自动跳过“本页合计”“空行”“暂无数据”这类干扰行程序不会。人眼看到“销售金额”和“销售额”知道是同一列程序把这两个写成不同列名就会直接 KeyError。所以“高级”两个字首要的含义不是多学几个 API而是学会把一张给人看的表格还原成机器可以正确处理的数据结构最后再还原成一份组织良好、格式清楚的 Excel 交付物。1.2 跨文件合并意味着先做结构对齐高级自动化最常见的误判是既然是 Python 处理 Excel就先用 openpyxl 把一个工作簿打开逐行循环再手动拼 DataFrame。这种写法在面对单文件小表格时没问题但面对月底几十家门店上报的周报时就很难维护。这些上报文件往往存在以下不统一现象文件 A 的销售明细从“销售明细”Sheet 的第一行开始。文件 B 的同一个 Sheet 前面多了一行大标题。文件 C 把“店名”写成“门店名称”把“销售金额”写成“销售额”。文件 D 在数据区域下方加了“合计”“制表人”等说明。文件 E 保存时用了.xls格式但扩展名被改成.xlsx。跨文件合并的第一件事不是拼接而是“结构对齐”。也就是先确认每个文件有多少个 Sheet、每个 Sheet 的表头在第几行、列名是否统一、数据区域从哪里开始、哪里结束。1.3 本文的贯穿案例门店销售周报汇总为了让后续代码不悬浮在抽象语法上先定义一个贯穿全文的业务场景每个门店每周提交一份 Excel 周报文件放在input/reports/目录下内部至少有一个“销售明细”Sheet里面记录日期、门店、商品、销售额、订单量和备注。最终需要生成一份《销售周报汇总.xlsx》包含三个能力把多个门店、多个 Sheet 的数据合并起来并清洗掉全空行、重复行和非法数值。统计每个门店的总销售额、总订单量和客单价。再输出一份带表头样式、自动筛选、冻结窗格、数据验证、图表和合计公式的报表。这样整条链路就变成读取结构探查、跨文件合并、数据清洗、指标聚合、Excel 展示层输出、批处理和排错。2. 环境与依赖openpyxl、pandas、xlsxwriter 的分工要分清2.1 Python 环境准备与依赖安装开始前先确认 Python 版本。办公自动化脚本建议使用 Python 3.9 以上版本避免旧版本对类型注解和 pandas 新接口支持不全的问题。python --version然后安装三个基础库pip install pandas openpyxl xlsxwriter如果公司电脑的 pip 不在 PATH 里使用模块方式安装python -m pip install pandas openpyxl xlsxwriter安装后单独验证版本避免代码写完后才发现库版本不一致python -c import pandas, openpyxl; print(pandas.__version__, openpyxl.__version__)对于需要读取旧版.xls文件的场景还要安装xlrd。这里要特别注意xlrd2.0 以上版本只支持读取.xls不支持.xlsx.xlsx应该交给 pandas 配合 openpyxl。2.2 pandas 管数据openpyxl 管展示xlsxwriter 管批量写用错了库是很多脚本出问题的来源。下面这张表可以用来做选型库擅长场景不擅长场景典型用途pandas读取、清理、合并、分组统计精细样式、公式、图表把 Excel 变成 DataFrame 做计算openpyxl读写.xlsx处理单元格、样式、公式、图表、数据验证大批量数据聚合生成带复杂格式的 Excelxlsxwriter高频写入大量数据性能较好读取 Excel、已有文件二次修改从零生成单 Sheet 大文件xlrd读取旧版.xls读取.xlsx兼容老系统导出的文件openpyxl 和 pandas 的配合方式是pandas 负责把结构不统一的表格变成规整的 DataFrame集中处理数据openpyxl 负责在最终保存前补充列宽、颜色、数据验证、公式和图表。不要反过来用 openpyxl 一点点读单元格来实现分组求和那样代码会很冗长而且很容易看错行列。2.3 不同 Excel 格式的读取边界.xlsx本质上是一个 zip 压缩包里面保存着 XML 文件。如果某个“excel”文件无法读取先检查它是不是真的.xlsx。常见情况是用户从网页后台导出后没有“另存为 xlsx”而是直接把.xls重命名成.xlsx打开时 pandas 会报错Excel file format cannot be determined, you must specify an engine manually.处理旧版.xls时明确指定引擎import pandas as pd df pd.read_excel(老数据.xls, enginexlrd)如果机器上没有安装 xlrdpandas 会提示安装缺少的可选依赖。由此可见环境准备不是一次性操作而是要在不同文件格式之间做出判断。2.4 用最小的读取脚本验证全部依赖环境是否正确不要等到完整脚本写完再验证。可以先跑一段最小读取代码import pandas as pd file_path 门店销售周报_上海.xlsx df pd.read_excel(file_path, sheet_name销售明细, header0) print(数据规模:, df.shape) print(列名:, df.columns.tolist()) print(df.head(3))这段代码能同时验证 pandas、openpyxl 和文件路径是否可用。如果列名带空格、表头不在第一行在head(3)里会立刻暴露出来。检查列名这个动作看起来简单却是后续所有字段映射的基础。3. 多 Sheet 和跨文件读取不要直接拼接先探查表头结构3.1 先探查表头header、skiprows、列名要一起确认Excel 数据不一定从 A1 开始。有一种很常见的上报表前两行是大标题和日期第三行才是真正的表头第1行XX集团 2025年1月第2周销售周报 第2行上报时间2025-01-12 第3行日期 门店 商品 销售额 订单量 备注 第4行2025-01-06 上海店 ...如果直接用pd.read_excel(path, header0)会把第一行大标题当作列名后面所有列名变成XX集团、Unnamed: 1之类的无效字段。正确做法是使用header或skiprowsimport pandas as pd df pd.read_excel( 门店销售周报_上海.xlsx, sheet_name销售明细, header2, # 真正的列头在第3行 dtype{门店代码: str} )header的值是第几行作为列名从 0 开始计数。不要舍不得用dtype门店编号、身份证这类字段如果不用字符串读入很容易被 Excel 自动转成科学计数法或丢失前导零。3.2 读取一个工作簿里的全部 Sheet很多上报文件里还有一个“说明”或“汇总”Sheet。如果不检查每个 Sheet 的内容把所有 Sheet 都拼接起来会把说明文字也当成数据。pandas的sheet_nameNone可以一次读取一个文件里的全部 Sheet并以 dict 形式返回import pandas as pd sheet_dict pd.read_excel(门店销售周报_上海.xlsx, sheet_nameNone, header2) for sheet_name, df in sheet_dict.items(): print(sheet_name, df.shape, df.columns.tolist())输出结果能直观判断哪些 Sheet 是纯数据哪些 Sheet 是说明页。这样后续合并时就可以用“是否包含销售额列”作为过滤条件而不是把文件名或 Sheet 名写死在代码里。3.3 跨文件合并并保留来源信息跨文件合并的关键是在每一条数据进入合并队列之前先带上来源文件。否则后期发现某个文件里的异常数据很难反查到源头。下面这段函数会遍历目录下所有.xlsx逐个读取全部 Sheet只保留包含“销售额”列的 Sheet并追加_来源文件和_来源Sheet两列from pathlib import Path import pandas as pd import logging def normalize_columns(df: pd.DataFrame) - pd.DataFrame: df df.copy() df.columns [str(col).strip() for col in df.columns] rename_rule { 销售金额: 销售额, 门店名称: 门店, 店名: 门店, 单量: 订单量, 商品名称: 商品, } df df.rename(columns{ old: new for old, new in rename_rule.items() if old in df.columns }) return df def collect_excel_files(reports_dir: str) - pd.DataFrame: all_frames [] for path in Path(reports_dir).glob(*.xlsx): if path.name.startswith(~$): continue try: sheet_dict pd.read_excel(path, sheet_nameNone, header0) except Exception as exc: logging.warning(读取失败: %s, 原因: %s, path.name, exc) continue for sheet_name, df in sheet_dict.items(): if df is None or df.empty: continue df normalize_columns(df) if 销售额 not in df.columns: continue df[_来源文件] path.name df[_来源Sheet] sheet_name all_frames.append(df) if not all_frames: return pd.DataFrame() return pd.concat(all_frames, ignore_indexTrue)这段代码里有两个容易被忽略的重点过滤 Excel 临时文件~$xxx.xlsx。如果某个文件正被 Excel 开着目录里会残留这种临时文件直接读取会报错。列名归一化不能只在某一个文件里做。真实上报文件往往字段名称并不相同因此要在每个文件读取后立刻把常见别名映射成标准字段。pd.concat执行的是 DataFrame 的行拼接。如果各文件的列集合不一致它不会报错而是会把不存在的列补成 NaN。所以合并不代表万事大吉下一步数据清洗才是重头戏。3.4 列结构对齐是数据层的第一道坎常见的结构问题包括下列几种现象可能原因排查方式列名变成Unnamed: 0表头上方有空行或合并单元格用header/skiprows重新定位读取后日期变成NaT日期是文本或存在混合格式用pd.to_datetime(errorscoerce)门店代码丢失前导零数字按数值类型读取读取时指定dtype{门店代码: str}数据行数少于 Excel 视觉行数Excel 里有全空行或分页行清洗时用dropna(howall)写数据合并代码时要养成一个习惯先打印DataFrame.shape再进入下一步。如果合并前后行数和列数没有达到心理预期说明某个文件的结构没有对齐。4. 数据清洗与指标聚合把脏表格转成可信统计结果4.1 数字、日期、空值和重复值的处理顺序Excel 看起来是一个数字实际存入 DataFrame 后可能有几种形态字符串例如1,200或0.00。浮点数例如1200.0。空值如NaN。None或空字符串。直接对这样的字段做sum()结果很可能是 0 或报错。推荐的清洗顺序是删除全空行。统一列名去掉首尾空格。把日期列转成 datetime 类型。把金额、数量列转成数值类型。删除关键字段缺失的行。去除重复行。记录日志说明每一步删除了多少行。下面是一个可用的清洗函数import pandas as pd def clean_detail(df: pd.DataFrame) - pd.DataFrame: # 全空行删除 df df.dropna(howall).copy() # 列名去空格 df.columns [str(col).strip() for col in df.columns] # 日期与数值字段统一类型errorscoerce 会把非法值变成 NaN df[日期] pd.to_datetime(df[日期], errorscoerce) df[销售额] pd.to_numeric(df[销售额], errorscoerce) df[订单量] pd.to_numeric(df[订单量], errorscoerce) # 关键字段缺失就丢弃这里的策略要看业务规则 df df.dropna(subset[日期, 销售额]) # 重复数据按业务主键去重 df df.drop_duplicates(subset[日期, 门店, 商品], keepfirst) # 去掉负数和明显不合理的金额这是示例规则实际按业务调整 df df[df[销售额] 0] return df注意errorscoerce会把脏数据悄悄变成NaN如果之后又用dropna过滤掉日志里最好记录清理前后行数print(清洗前:, len(df), 清洗后:, len(clean_df))不要使用“裸 dropna 删全表”的策略。删除数据前至少应该知道删除范围是不是预期的。4.2 条件统计和透视表完成门店汇总数据清洗完成后用 pandas 做汇总比用 openpyxl 逐行累加要快。下面的代码实现按门店聚合总销售额、总订单量并计算客单价store_summary clean_df.groupby(门店, as_indexFalse).agg( 总销售额(销售额, sum), 总订单量(订单量, sum), ) store_summary[客单价] store_summary[总销售额] / store_summary[总订单量] store_summary store_summary.sort_values(总销售额, ascendingFalse)如果希望得到一周 7 天的横向透视表可以使用pivot_tableweek_view pd.pivot_table( clean_df, index门店, columns日期, values销售额, aggfuncsum, fill_value0, )透视表适合人看groupby 的结果适合继续参与计算和制图。两者可以并存一个写入汇总 Sheet一个作为中间结果输出。4.3 中间结果单独输出让清洗过程可追溯数据清洗逻辑一旦复杂肉眼很难判断结果是否正确。一个实用的习惯是输出一份“清洗中间件”比如 CSVclean_df.to_csv(output/clean_detail.csv, indexFalse, encodingutf-8-sig)使用utf-8-sig编码是为了让 Excel 直接打开 CSV 时不会出现中文乱码。中间文件不一定要保留到生产环境但它能大幅缩短排错时间。生产环境即使不保留脚本日志里也应记录“清洗前 X 行清洗后 Y 行跳过文件清单”。5. 生成带样式、公式、下拉框和图表的最终报表5.1 先写数据再用 openpyxl 增强结构在 Excel 自动化里pandas 写数据和 openpyxl 设置样式应该分开做。先让 pandas 把两个 Sheet 写进去out_path output/销售周报汇总.xlsx with pd.ExcelWriter(out_path, engineopenpyxl) as writer: clean_df.to_excel(writer, sheet_name销售明细, indexFalse) store_summary.to_excel(writer, sheet_name门店汇总, indexFalse)写完后工作簿已经存在。接着用 openpyxl 重新打开这份文件继续做样式、公式和数据验证。from openpyxl import load_workbook wb load_workbook(out_path) ws_summary wb[门店汇总]这种“先写数据再补展示”的思路把数据计算和展示样式解耦。如果后续报表样式调整不需要重新跑一遍 groupby。5.2 表头样式、冻结窗格、列宽和自动筛选企业交付的 Excel 通常需要带表头底色、加粗字体、边框、冻结首行、自动筛选和适当列宽。二三十行代码就能完成from openpyxl.styles import Font, PatternFill, Alignment, Border, Side def style_sheet(ws): header_fill PatternFill(solid, fgColorD9E1F2) header_font Font(name微软雅黑, size11, boldTrue, color1F4E79) thin_side Side(stylethin, colorBFBFBF) border Border(leftthin_side, rightthin_side, topthin_side, bottomthin_side) for cell in ws[1]: cell.fill header_fill cell.font header_font cell.alignment Alignment(horizontalcenter, verticalcenter) cell.border border ws.freeze_panes A2 if ws.max_row 1: ws.auto_filter.ref ws.dimensions # 只对外壳表格设置固定列宽不要在超大数据明细上循环 widths {A: 18, B: 14, C: 14, D: 14, E: 14} for col_name, width in widths.items(): ws.column_dimensions[col_name].width width style_sheet(ws_summary)这里有一个关键取舍表头样式可以放在明细 Sheet但逐单元格计算列宽的函数不适合用在几十万行明细上。大量单元格的列宽计算会造成明显的性能损耗。一般来说汇总 Sheet 数据行少适合精细排版明细 Sheet Python办公自动化Excel高级处理：多表合并与样式报表实战
Python 办公自动化里Excel 表格处理是需求最集中、可复用价值最高的方向。很多同学已经会用 pandas 读一张表、用 openpyxl 写几个单元格但一旦把人放进真正的工单或“py100—lv2 系列”这类进阶练习里难点往往不是单个 API而是如何处理几十份格式混乱的 Excel、如何跨 Sheet 跨文件合并、如何在保留公式、下拉列表和图表的情况下交付结果、以及脚本上线后如何通过日志快速定位问题。这篇文章用一条完整的“门店销售周报汇总”需求串起整个过程。这个场景覆盖了多文件读取、数据清洗、聚合计算、样式报表生成、批处理和异常排查是办公自动化 Excel 高级应用里很典型的综合练习。1. 先理解 Excel 高级自动化的难点不是读写而是数据形态还原1.1 Excel 同时是数据源和展示层Excel 里的一个单元格落在人眼里是一个值落在 Python 里却可能是字符串、日期、浮点数、公式甚至是空值。普通脚本只要能做到“读出来、写回去”很容易被一段演示代码误以为已经掌握。真正进入高级阶段后复杂性来自 Excel 自身的“双重身份”它是数据源存放表格、分类、统计公式和原始记录。它也是展示层包含合并单元格、空行、字体颜色、列宽、数字格式、批注、图表和下接列表。人眼读取数据时会自动跳过“本页合计”“空行”“暂无数据”这类干扰行程序不会。人眼看到“销售金额”和“销售额”知道是同一列程序把这两个写成不同列名就会直接 KeyError。所以“高级”两个字首要的含义不是多学几个 API而是学会把一张给人看的表格还原成机器可以正确处理的数据结构最后再还原成一份组织良好、格式清楚的 Excel 交付物。1.2 跨文件合并意味着先做结构对齐高级自动化最常见的误判是既然是 Python 处理 Excel就先用 openpyxl 把一个工作簿打开逐行循环再手动拼 DataFrame。这种写法在面对单文件小表格时没问题但面对月底几十家门店上报的周报时就很难维护。这些上报文件往往存在以下不统一现象文件 A 的销售明细从“销售明细”Sheet 的第一行开始。文件 B 的同一个 Sheet 前面多了一行大标题。文件 C 把“店名”写成“门店名称”把“销售金额”写成“销售额”。文件 D 在数据区域下方加了“合计”“制表人”等说明。文件 E 保存时用了.xls格式但扩展名被改成.xlsx。跨文件合并的第一件事不是拼接而是“结构对齐”。也就是先确认每个文件有多少个 Sheet、每个 Sheet 的表头在第几行、列名是否统一、数据区域从哪里开始、哪里结束。1.3 本文的贯穿案例门店销售周报汇总为了让后续代码不悬浮在抽象语法上先定义一个贯穿全文的业务场景每个门店每周提交一份 Excel 周报文件放在input/reports/目录下内部至少有一个“销售明细”Sheet里面记录日期、门店、商品、销售额、订单量和备注。最终需要生成一份《销售周报汇总.xlsx》包含三个能力把多个门店、多个 Sheet 的数据合并起来并清洗掉全空行、重复行和非法数值。统计每个门店的总销售额、总订单量和客单价。再输出一份带表头样式、自动筛选、冻结窗格、数据验证、图表和合计公式的报表。这样整条链路就变成读取结构探查、跨文件合并、数据清洗、指标聚合、Excel 展示层输出、批处理和排错。2. 环境与依赖openpyxl、pandas、xlsxwriter 的分工要分清2.1 Python 环境准备与依赖安装开始前先确认 Python 版本。办公自动化脚本建议使用 Python 3.9 以上版本避免旧版本对类型注解和 pandas 新接口支持不全的问题。python --version然后安装三个基础库pip install pandas openpyxl xlsxwriter如果公司电脑的 pip 不在 PATH 里使用模块方式安装python -m pip install pandas openpyxl xlsxwriter安装后单独验证版本避免代码写完后才发现库版本不一致python -c import pandas, openpyxl; print(pandas.__version__, openpyxl.__version__)对于需要读取旧版.xls文件的场景还要安装xlrd。这里要特别注意xlrd2.0 以上版本只支持读取.xls不支持.xlsx.xlsx应该交给 pandas 配合 openpyxl。2.2 pandas 管数据openpyxl 管展示xlsxwriter 管批量写用错了库是很多脚本出问题的来源。下面这张表可以用来做选型库擅长场景不擅长场景典型用途pandas读取、清理、合并、分组统计精细样式、公式、图表把 Excel 变成 DataFrame 做计算openpyxl读写.xlsx处理单元格、样式、公式、图表、数据验证大批量数据聚合生成带复杂格式的 Excelxlsxwriter高频写入大量数据性能较好读取 Excel、已有文件二次修改从零生成单 Sheet 大文件xlrd读取旧版.xls读取.xlsx兼容老系统导出的文件openpyxl 和 pandas 的配合方式是pandas 负责把结构不统一的表格变成规整的 DataFrame集中处理数据openpyxl 负责在最终保存前补充列宽、颜色、数据验证、公式和图表。不要反过来用 openpyxl 一点点读单元格来实现分组求和那样代码会很冗长而且很容易看错行列。2.3 不同 Excel 格式的读取边界.xlsx本质上是一个 zip 压缩包里面保存着 XML 文件。如果某个“excel”文件无法读取先检查它是不是真的.xlsx。常见情况是用户从网页后台导出后没有“另存为 xlsx”而是直接把.xls重命名成.xlsx打开时 pandas 会报错Excel file format cannot be determined, you must specify an engine manually.处理旧版.xls时明确指定引擎import pandas as pd df pd.read_excel(老数据.xls, enginexlrd)如果机器上没有安装 xlrdpandas 会提示安装缺少的可选依赖。由此可见环境准备不是一次性操作而是要在不同文件格式之间做出判断。2.4 用最小的读取脚本验证全部依赖环境是否正确不要等到完整脚本写完再验证。可以先跑一段最小读取代码import pandas as pd file_path 门店销售周报_上海.xlsx df pd.read_excel(file_path, sheet_name销售明细, header0) print(数据规模:, df.shape) print(列名:, df.columns.tolist()) print(df.head(3))这段代码能同时验证 pandas、openpyxl 和文件路径是否可用。如果列名带空格、表头不在第一行在head(3)里会立刻暴露出来。检查列名这个动作看起来简单却是后续所有字段映射的基础。3. 多 Sheet 和跨文件读取不要直接拼接先探查表头结构3.1 先探查表头header、skiprows、列名要一起确认Excel 数据不一定从 A1 开始。有一种很常见的上报表前两行是大标题和日期第三行才是真正的表头第1行XX集团 2025年1月第2周销售周报 第2行上报时间2025-01-12 第3行日期 门店 商品 销售额 订单量 备注 第4行2025-01-06 上海店 ...如果直接用pd.read_excel(path, header0)会把第一行大标题当作列名后面所有列名变成XX集团、Unnamed: 1之类的无效字段。正确做法是使用header或skiprowsimport pandas as pd df pd.read_excel( 门店销售周报_上海.xlsx, sheet_name销售明细, header2, # 真正的列头在第3行 dtype{门店代码: str} )header的值是第几行作为列名从 0 开始计数。不要舍不得用dtype门店编号、身份证这类字段如果不用字符串读入很容易被 Excel 自动转成科学计数法或丢失前导零。3.2 读取一个工作簿里的全部 Sheet很多上报文件里还有一个“说明”或“汇总”Sheet。如果不检查每个 Sheet 的内容把所有 Sheet 都拼接起来会把说明文字也当成数据。pandas的sheet_nameNone可以一次读取一个文件里的全部 Sheet并以 dict 形式返回import pandas as pd sheet_dict pd.read_excel(门店销售周报_上海.xlsx, sheet_nameNone, header2) for sheet_name, df in sheet_dict.items(): print(sheet_name, df.shape, df.columns.tolist())输出结果能直观判断哪些 Sheet 是纯数据哪些 Sheet 是说明页。这样后续合并时就可以用“是否包含销售额列”作为过滤条件而不是把文件名或 Sheet 名写死在代码里。3.3 跨文件合并并保留来源信息跨文件合并的关键是在每一条数据进入合并队列之前先带上来源文件。否则后期发现某个文件里的异常数据很难反查到源头。下面这段函数会遍历目录下所有.xlsx逐个读取全部 Sheet只保留包含“销售额”列的 Sheet并追加_来源文件和_来源Sheet两列from pathlib import Path import pandas as pd import logging def normalize_columns(df: pd.DataFrame) - pd.DataFrame: df df.copy() df.columns [str(col).strip() for col in df.columns] rename_rule { 销售金额: 销售额, 门店名称: 门店, 店名: 门店, 单量: 订单量, 商品名称: 商品, } df df.rename(columns{ old: new for old, new in rename_rule.items() if old in df.columns }) return df def collect_excel_files(reports_dir: str) - pd.DataFrame: all_frames [] for path in Path(reports_dir).glob(*.xlsx): if path.name.startswith(~$): continue try: sheet_dict pd.read_excel(path, sheet_nameNone, header0) except Exception as exc: logging.warning(读取失败: %s, 原因: %s, path.name, exc) continue for sheet_name, df in sheet_dict.items(): if df is None or df.empty: continue df normalize_columns(df) if 销售额 not in df.columns: continue df[_来源文件] path.name df[_来源Sheet] sheet_name all_frames.append(df) if not all_frames: return pd.DataFrame() return pd.concat(all_frames, ignore_indexTrue)这段代码里有两个容易被忽略的重点过滤 Excel 临时文件~$xxx.xlsx。如果某个文件正被 Excel 开着目录里会残留这种临时文件直接读取会报错。列名归一化不能只在某一个文件里做。真实上报文件往往字段名称并不相同因此要在每个文件读取后立刻把常见别名映射成标准字段。pd.concat执行的是 DataFrame 的行拼接。如果各文件的列集合不一致它不会报错而是会把不存在的列补成 NaN。所以合并不代表万事大吉下一步数据清洗才是重头戏。3.4 列结构对齐是数据层的第一道坎常见的结构问题包括下列几种现象可能原因排查方式列名变成Unnamed: 0表头上方有空行或合并单元格用header/skiprows重新定位读取后日期变成NaT日期是文本或存在混合格式用pd.to_datetime(errorscoerce)门店代码丢失前导零数字按数值类型读取读取时指定dtype{门店代码: str}数据行数少于 Excel 视觉行数Excel 里有全空行或分页行清洗时用dropna(howall)写数据合并代码时要养成一个习惯先打印DataFrame.shape再进入下一步。如果合并前后行数和列数没有达到心理预期说明某个文件的结构没有对齐。4. 数据清洗与指标聚合把脏表格转成可信统计结果4.1 数字、日期、空值和重复值的处理顺序Excel 看起来是一个数字实际存入 DataFrame 后可能有几种形态字符串例如1,200或0.00。浮点数例如1200.0。空值如NaN。None或空字符串。直接对这样的字段做sum()结果很可能是 0 或报错。推荐的清洗顺序是删除全空行。统一列名去掉首尾空格。把日期列转成 datetime 类型。把金额、数量列转成数值类型。删除关键字段缺失的行。去除重复行。记录日志说明每一步删除了多少行。下面是一个可用的清洗函数import pandas as pd def clean_detail(df: pd.DataFrame) - pd.DataFrame: # 全空行删除 df df.dropna(howall).copy() # 列名去空格 df.columns [str(col).strip() for col in df.columns] # 日期与数值字段统一类型errorscoerce 会把非法值变成 NaN df[日期] pd.to_datetime(df[日期], errorscoerce) df[销售额] pd.to_numeric(df[销售额], errorscoerce) df[订单量] pd.to_numeric(df[订单量], errorscoerce) # 关键字段缺失就丢弃这里的策略要看业务规则 df df.dropna(subset[日期, 销售额]) # 重复数据按业务主键去重 df df.drop_duplicates(subset[日期, 门店, 商品], keepfirst) # 去掉负数和明显不合理的金额这是示例规则实际按业务调整 df df[df[销售额] 0] return df注意errorscoerce会把脏数据悄悄变成NaN如果之后又用dropna过滤掉日志里最好记录清理前后行数print(清洗前:, len(df), 清洗后:, len(clean_df))不要使用“裸 dropna 删全表”的策略。删除数据前至少应该知道删除范围是不是预期的。4.2 条件统计和透视表完成门店汇总数据清洗完成后用 pandas 做汇总比用 openpyxl 逐行累加要快。下面的代码实现按门店聚合总销售额、总订单量并计算客单价store_summary clean_df.groupby(门店, as_indexFalse).agg( 总销售额(销售额, sum), 总订单量(订单量, sum), ) store_summary[客单价] store_summary[总销售额] / store_summary[总订单量] store_summary store_summary.sort_values(总销售额, ascendingFalse)如果希望得到一周 7 天的横向透视表可以使用pivot_tableweek_view pd.pivot_table( clean_df, index门店, columns日期, values销售额, aggfuncsum, fill_value0, )透视表适合人看groupby 的结果适合继续参与计算和制图。两者可以并存一个写入汇总 Sheet一个作为中间结果输出。4.3 中间结果单独输出让清洗过程可追溯数据清洗逻辑一旦复杂肉眼很难判断结果是否正确。一个实用的习惯是输出一份“清洗中间件”比如 CSVclean_df.to_csv(output/clean_detail.csv, indexFalse, encodingutf-8-sig)使用utf-8-sig编码是为了让 Excel 直接打开 CSV 时不会出现中文乱码。中间文件不一定要保留到生产环境但它能大幅缩短排错时间。生产环境即使不保留脚本日志里也应记录“清洗前 X 行清洗后 Y 行跳过文件清单”。5. 生成带样式、公式、下拉框和图表的最终报表5.1 先写数据再用 openpyxl 增强结构在 Excel 自动化里pandas 写数据和 openpyxl 设置样式应该分开做。先让 pandas 把两个 Sheet 写进去out_path output/销售周报汇总.xlsx with pd.ExcelWriter(out_path, engineopenpyxl) as writer: clean_df.to_excel(writer, sheet_name销售明细, indexFalse) store_summary.to_excel(writer, sheet_name门店汇总, indexFalse)写完后工作簿已经存在。接着用 openpyxl 重新打开这份文件继续做样式、公式和数据验证。from openpyxl import load_workbook wb load_workbook(out_path) ws_summary wb[门店汇总]这种“先写数据再补展示”的思路把数据计算和展示样式解耦。如果后续报表样式调整不需要重新跑一遍 groupby。5.2 表头样式、冻结窗格、列宽和自动筛选企业交付的 Excel 通常需要带表头底色、加粗字体、边框、冻结首行、自动筛选和适当列宽。二三十行代码就能完成from openpyxl.styles import Font, PatternFill, Alignment, Border, Side def style_sheet(ws): header_fill PatternFill(solid, fgColorD9E1F2) header_font Font(name微软雅黑, size11, boldTrue, color1F4E79) thin_side Side(stylethin, colorBFBFBF) border Border(leftthin_side, rightthin_side, topthin_side, bottomthin_side) for cell in ws[1]: cell.fill header_fill cell.font header_font cell.alignment Alignment(horizontalcenter, verticalcenter) cell.border border ws.freeze_panes A2 if ws.max_row 1: ws.auto_filter.ref ws.dimensions # 只对外壳表格设置固定列宽不要在超大数据明细上循环 widths {A: 18, B: 14, C: 14, D: 14, E: 14} for col_name, width in widths.items(): ws.column_dimensions[col_name].width width style_sheet(ws_summary)这里有一个关键取舍表头样式可以放在明细 Sheet但逐单元格计算列宽的函数不适合用在几十万行明细上。大量单元格的列宽计算会造成明显的性能损耗。一般来说汇总 Sheet 数据行少适合精细排版明细 Sheet