Python 办公自动化实战：用 pandas 与 openpyxl 批量处理 Excel 报表

在办公场景里，Excel 大概是每个人“又爱又恨”的工具。爱是因为它足够灵活，字段调整、格式修改、公式计算都能快速完成；恨是因为当数据表从几张变成几十张，当需求从“看数据”变成“日报、周报、月报、多表核对、批量拆分”时，手动操作 Excel 就容易变成重复劳动，而且越忙越容易出错。

如果你已经掌握了一点 Python 基础，想从“能写循环、能 print”进阶到“让 Python 真正帮你处理电脑里的 Excel 报表”，这篇文章就是一次比较系统的实战梳理。我会按办公自动化的真实场景，讲解如何用 Python 完成 Excel 批量读取、多表合并、跨表匹配合并、按条件拆分多个报表，以及带格式汇总表的自动生成。文中代码都会标注文件路径和运行思路，你可以沿着案例直接练习。

需要提前说明的是：本文定位是“办公自动化 Excel 高级”，默认你已经知道 Python 的变量、列表、字典、for 循环和函数等基本语法，不会讲解代码基础，而是把重点放在 Excel 文件的批量化处理思路上。

1. 从“手动表格”到“Python 办公自动化”：先想清楚要解决什么问题

1.1 办公自动化 Excel 的真实需求特征

在日常办公中，Excel 操作大致可以分成三类。

第一类是纯交互型操作，比如临时录入数据、调整某几行的颜色、手工核对几个数。这类任务量少、灵活性高，用 Python 反而麻烦。第二类是重复型操作，比如每周都要把 20 张分店表格复制汇总到总表，然后按月份拆出不同 sheet。第三类是计算型操作，比如多张表之间需要匹配客户编号、汇总销售额、统计排名。现实中的办公自动化“高级需求”，大多数集中在第二类和第三类的组合场景。

更具体地说，常见的 Python 办公自动化 Excel 需求包括：

• 批量读取一个文件夹下的所有 Excel 文件，并把它们合并成一张总表。
• 根据某一列的编号去另一张表中匹配对应信息，实现类似 Excel VLOOKUP 的效果。
• 按某一列的不同值，把数据拆分成多个工作表或多个独立文件。
• 对表格数据做清洗，比如去除空格、处理缺失值、规范日期格式。
• 自动生成带表头、设置列宽、加边框、高亮关键行的格式报表。
• 在报表生成后让结果保留公式或另存为 PDF/CSV 等特定格式。

如果这些操作靠纯手工完成，流程非常机械：打开文件、全选、复制、粘贴、另存为。数据量少还好，数据一多、文件一多，哪怕只是其中一步出现了错位，后期核对数据会非常痛苦。

1.2 为什么是 Python，而不是只用 Excel 公式或 VBA

你可能会想：Excel 本身有 VLOOKUP、数据透视表、Power Query，为什么还要用 Python？

回答这个问题需要从使用边界来看。Excel 的 VLOOKUP 和透视表适合“当前工作簿内”或“少量文件间”的即时操作，它依赖 Excel 界面，适合单个文件级别的问题。但办公自动化经常要面对的是：

• 需要遍历某个目录下几十个文件。
• 这些文件的命名、sheet 位置可能不完全一致。
• 数据清洗规则很多，要反复执行。
• 最后要同时输出多个目标文件，而不是只在一个文件里看结果。

Python 的优势不在“单次操作”上，而在于流程化、批量化、可重复。同样的逻辑写好以后，今天可以用，下周还能用，换一批数据依然能跑。这样就能把机械重复工作从人肉操作变成程序处理，而且 Python 的 pandas 等库能把 Excel 处理封装成比较接近数据库操作的体验。

VBA 虽然也可以做到类似效果，但 Python 的语法更贴近日常编程，排错和测试手段更丰富，并且可以无缝衔接后续的邮件自动发送、企业微信通知、数据库读写等更高阶的流程。因此在办公自动化领域，Python + Excel 的组合已经成为很多数据分析师、运营、行政、财务人员必备的技能。

1.3 本文涉及的第三方库边界

本文主要涉及两个非常成熟的库：

• pandas：用于读取 Excel、数据清洗、聚合、合并、拆分，是整个处理流程的核心。
• openpyxl：用于精细控制 Excel 文件格式，例如调整列宽、单元格背景色、边框、字体等。

另外，如果操作系统没有自带 Python 环境，还需要先安装 Python；如果需要在 vscode 或 PyCharm 中运行，还需要完成对应编辑器环境配置。下文会按步骤说明这些准备内容。

2. 环境准备：搭建基础的 Python Excel 开发环境

2.1 操作系统与 Python 版本要求

本文代码基于 Windows/macOS 均可以运行。示例以本地开发为主，不涉及服务器的复杂环境。

Python 版本方面，建议使用 3.8 及以上版本。如果你还没有安装 Python，可以到 Python 官网下载对应系统版本的安装包。这里有一个容易忽略的细节：Windows 安装时务必勾选“Add Python to PATH”，否则后面在命令行输入 python 会提示找不到命令。

如果你已经安装了较老的 2.x 版本，或者系统同时存在多个 Python 版本，建议先统一版本，避免后续安装 pandas 时出现混淆。可以在命令行执行：

python --version

如果输出类似：

Python 3.10.5

说明当前环境可用。如果提示找不到命令，则需要检查环境变量，或者使用 python3 命令重试。

2.2 安装第三方库

Excel 处理功能不在 Python 标准库中，需要用 pip 安装。建议在命令行中执行以下命令：

pip install pandas openpyxl

如果不确定当前是否已经安装，可以执行：

pip list | findstr pandas pip list | findstr openpyxl

Linux/macOS 环境下 findstr 需要换成 grep：

pip list | grep pandas pip list | grep openpyxl

如果项目中有多个环境，例如使用 conda、venv 等虚拟环境，需要先激活对应的虚拟环境再做安装。本文不假定你用某个具体的包管理工具，但工程上强烈建议用虚拟环境隔离依赖，不要直接装在系统 Python 全局环境里。

2.3 IDE 选择建议

办公自动化脚本并不复杂，用 PyCharm、vscode、Jupyter Notebook 都可以。我个人的建议是：

• 如果更偏数据分析，可以用 Jupyter Notebook，一边运行一边看输出，方便调试。
• 如果更偏工程化脚本，推荐 vscode 或 PyCharm。

在 vscode 中运行 Python 脚本之前，需要先安装 Python 扩展，并在命令面板中选择正确的解释器。否则可能出现“已经用 pip 安装了 pandas，但 vscode 里 import 仍然报错”的问题。这个问题绝大多数不是代码错误，而是没有选中正确的 Python 环境，下文常见问题部分会专门说明。

2.4 准备一个干净的测试目录

为了便于复现，建议先创建一个独立文件夹，比如：

D:/excel_auto_demo/

下面再细化两个子目录：

D:/excel_auto_demo/input/ # 存放原始 Excel 文件 D:/excel_auto_demo/output/ # 存放生成结果

然后可以在 input 中创建几张模拟表格，或者直接复制手头已有的 Excel 文件。准备好目录后，本文所有代码都围绕这个结构展开。

3. 核心基础：pandas 操作 Excel 的高频知识点解析

3.1 read_excel 与 to_excel 的核心参数

pandas 读取 Excel 文件最常用的函数是read_excel。下面这段代码虽然简单，但要把其中参数理解清楚：

import pandas as pd # 文件路径 path = "input/销售明细_202301.xlsx" df = pd.read_excel( path, sheet_name="销售明细", # 可以指定工作表名称 header=0, # 第 0 行作为列名 dtype={"订单号": str}, # 订单号按字符串读入，避免变成科学计数 )

这里有几个高频参数值得展开。

sheet_name可以传工作表名称，也可以传工作表索引。如果是多个工作表，还可以传列表，例如sheet_name=["1 月", "2 月"]，读取结果会是一个 dict，key 是工作表名称，value 是对应 DataFrame。这个参数在做循环读取时非常常用。

header用来设置第几行作为列名。很多表格前几行是标题或说明文字，不处理的话，列名会变成“Unnamed: 0”之类的占位符。

dtype用来指定列的数据类型。Excel 里的订单号、身份证号、账号等纯数字列，默认很容易被读成 int64 或 float64，导致数据精度受影响；把它们强制指定为 str，是比较稳妥的做法。

写入 Excel 时，最常用的是to_excel：

df.to_excel("output/结果.xlsx", sheet_name="汇总", index=False)

index=False非常关键，否则 pandas 会默认把 DataFrame 的行号写到 Excel 第一列，生成一列莫名其妙的索引列。

3.2 工作表与工作簿的操作边界

很多初学者容易混淆 pandas 和 openpyxl 的功能边界。

pandas 更加关注“表格数据”本身，适合做数据筛选、合并、统计，导出成 Excel 时也能控制列名和 sheet 名称，但它默认不会精细地设置 Excel 单元格颜色、列宽、合并单元格等样式。

openpyxl 则相反，它是直接面向 Excel 工作簿的库，可以修改单元格的值、读取工作表、设置字体、边框、列宽、行高、条件格式等。如果你的场景是“把数据写进一个已经设计好的模板 Excel，并按固定样式生成报表”，光靠 pandas 通常不够，往往需要先 pandas 处理完数据，再用 openpyxl 打开结果文件做样式调整。

完整流程一般是这样：

1. 用 pandas 读取 Excel 原始文件。
2. 用 pandas 完成数据合并、匹配合并、分组统计。
3. 把结果 DataFrame 写入 Excel。
4. 用 openpyxl 打开生成文件，调整格式，最后另存。

3.3 Excel 数据类型与缺失值带来的隐性坑

办公表格和数据库表最大的区别在于，Excel 单元格中的格式和值经常“不规范”。使用 pandas 处理时，至少要注意以下三类问题。

第一，空值问题。某一行如果某个单元格是空白的，pandas 通常会用NaN（Not a Number）表示，而不是空字符串。后续做字符串拼接或判断时，如果没处理 NaN，可能出现float与str类型不匹配的报错，也可能出现结果里莫名出现nan字样。

第二，数字与文本混用。很多 Excel 中看起来是数字的列，实际可能左上角有绿色小三角，说明这是文本格式。如果不做类型转换，按销售额字段求和或排序时会报错或得到错误结果。可以用pd.to_numeric处理：

df["销售金额"] = pd.to_numeric(df["销售金额"], errors="coerce")

errors="coerce"的意思是，如果无法转换成数字，则置为 NaN。

第三，日期格式不统一。有的单元格是“2023-01-01”，有的是“2023/1/1”，还有写“2023年1月1日”。提升到 pandas 后不一定都是 datetime 类型。建议读取后用pd.to_datetime统一格式：

df["日期"] = pd.to_datetime(df["日期"], errors="coerce")

3.4 一条可复用的处理流程模板

办公自动化脚本不必每次从零开始，可以抽象出一个流程模板：

遍历原始文件 -> 读取并清洗 -> 合并多表 -> 业务计算 -> 输出结果文件

每一步最好封装成函数，不把所有逻辑堆在一个脚本里。这样后期换需求时，只需要替换其中某一步。

下面用一个更贴近办公场景的完整实战案例，把这套模板落地。

4. 实战场景一：批量合并多个 Excel 文件

4.1 需求描述

假设每个月的销售明细分别保存在多个文件里，文件存放在input/sales文件夹下。比如：

input/sales/2023-01-华东销售.xlsx input/sales/2023-01-华南销售.xlsx input/sales/2023-01-华北销售.xlsx input/sales/2023-01-西南销售.xlsx

每个文件的结构都一样，包含日期、订单号、区域、产品名称、销售数量、销售金额等列。现在需要把这几个文件合并成一张总表output/销售汇总.xlsx，并且在总表中增加一列“来源文件”，方便追溯数据来源于哪个文件。

手动做法是打开每个文件，复制粘贴到同一个 Excel，效率低且容易漏行。用 Python 可以做成一个自动化脚本。

4.2 思路拆解

批量合并文件的基本思路是：

1. 使用glob.glob或pathlib.Path.glob遍历该目录下所有 xlsx 文件。
2. 循环读取每个 Excel 文件。
3. 在读取得到的 DataFrame 中增加一列“来源文件”，值为文件名。
4. 把每个 DataFrame 放到一个 list 中。
5. 用pd.concat把所有 DataFrame 拼接成一个大表。
6. 统一写出到 Excel。

这里推荐用pathlib而不是os.path.join，因为它的路径处理更简单直观。

4.3 完整代码实现

新建一个merge_excel.py文件，放在测试目录下。

# -*- coding: utf-8 -*- """ 功能：批量合并 input/sales 目录下所有 Excel 文件 运行前请确认 input/sales 目录下存在结构一致的 xlsx 文件 """ import pandas as pd from pathlib import Path # 定义输入和输出目录 input_dir = Path("input/sales") output_dir = Path("output") output_dir.mkdir(parents=True, exist_ok=True) # 收集所有 xlsx 文件 xlsx_files = list(input_dir.glob("*.xlsx")) print("找到文件数量：", len(xlsx_files)) # 用一个列表保存每个文件读取出来的 DataFrame df_list = [] for file_path in xlsx_files: # 读取 Excel 文件，默认读取第一个工作表 df = pd.read_excel(file_path) # 增加一列，记录来源文件名 df["来源文件"] = file_path.name df_list.append(df) print("已读取：", file_path.name) # 纵向合并所有 DataFrame result_df = pd.concat(df_list, ignore_index=True) # 查看总行数和列名 print("合并后总行数：", len(result_df)) print("列名：", list(result_df.columns)) # 写入结果文件 output_path = output_dir / "销售汇总.xlsx" result_df.to_excel(output_path, index=False, sheet_name="销售汇总") print("结果已保存：", output_path)

运行该脚本后，终端预期输出大概如下：

找到文件数量： 4 已读取： 2023-01-华东销售.xlsx 已读取： 2023-01-华南销售.xlsx 已读取： 2023-01-华北销售.xlsx 已读取： 2023-01-西南销售.xlsx 合并后总行数： 120 列名： ['日期', '订单号', '区域', '产品名称', '销售数量', '销售金额', '来源文件'] 结果已保存： output/销售汇总.xlsx

4.4 核心逻辑说明

这段代码有几个处理细节值得重点理解。

第一点，glob("*.xlsx")只会匹配当前目录下的 xlsx 文件，不会递归子目录。如果文件存放在多级子目录里，需要改成rglob("*.xlsx")。对大多数办公场景来说，glob就够了。

第二点，pd.concat默认按列名对齐。如果各个文件的列顺序不一致，但列名相同，concat 也能正确对齐；如果列名不同，会出现很多 NaN 列，要特别注意。

第三点，ignore_index=True的作用是让合并后结果的行号重新从 0 开始。如果不加，合并后的 DataFrame 索引会保留原来每个文件的索引，后续df.loc取行可能会比较混乱。

第四点，如果销售金额这一列在原始表格中是文本格式，合并后不同文件的列类型可能不同。可以在读取后立即调用pd.to_numeric做一次强转，避免后面求和时报错。

5. 实战场景二：跨表数据匹配，用 Python 实现“高级 VLOOKUP”

5.1 需求描述

合并完销售明细后，老板可能会要求添加“客户等级”“负责人”等字段。这些字段通常维护在另一张客户信息表里，此时就要根据“客户编号”或“订单号”进行匹配。

举个具体例子：

• 主表订单明细.xlsx，包含字段：订单号、客户编号、产品、金额、下单日期。
• 客户表客户信息.xlsx，包含字段：客户编号、客户名称、客户等级、负责人。

现在要输出一张新表，订单明细每一行都补充上客户名称、客户等级、负责人。这不就是 Excel 里最常用的 VLOOKUP 吗？Excel 公式写起来是：

=VLOOKUP(B2, 客户信息表!A:D, 2, FALSE)

但真要处理几千行、几十万行数据，或者两张表跨文件且文件经常更新时，直接用 pandas 的merge会是更可靠、便于自动化的选择。

5.2 merge 与 VLOOKUP 的对应关系

pandas 的merge与 Excel VLOOKUP 有很强的对应关系。VLOOKUP 的核心逻辑是“根据当前表的某一个键去另一张表查找数据，并把另一张表的指定列取回来”，这正是merge做的事情。

基本写法：

merged_df = pd.merge( left_df, # 主表 right_df, # 查询表，即要“按哪列去查” how="left", # 左连接，保留主表所有行 left_on="客户编号", right_on="客户编号", )

how="left"表示左连接，意思是以主表为基准，能匹配上的就带上客户信息，匹配不上的保留主表行，但客户信息列显示 NaN。这非常像 VLOOKUP 找不到匹配项时返回 #N/A 的状态。

5.3 代码实现

继续使用上一节的案例。假设销售明细总表已经生成在output/销售汇总.xlsx，另有一份客户信息表input/客户信息.xlsx，现在要把客户表的信息匹配到销售明细后输出。

# -*- coding: utf-8 -*- """ 功能：将订单销售明细与客户信息表匹配，输出完整表 """ import pandas as pd from pathlib import Path # 1. 读取主表和客户表 order_df = pd.read_excel("output/销售汇总.xlsx", sheet_name="销售汇总") customer_df = pd.read_excel("input/客户信息.xlsx", sheet_name="客户信息") print("订单明细列：", list(order_df.columns)) print("客户信息列：", list(customer_df.columns))

执行前需要确认两张表都有“客户编号”字段。之后执行匹配：

# 2. 左连接匹配 merged_df = pd.merge( order_df, customer_df, how="left", left_on="客户编号", right_on="客户编号", ) # 3. 检查匹配率，通常可以统计客户名称是否为 NaN null_count = merged_df["客户名称"].isna().sum() total_count = len(merged_df) if null_count > 0: print(f"警告：有 {null_count} 行客户信息未匹配到，占总行数 {null_count / total_count:.2%}") # 4. 写出结果 output_path = Path("output/订单明细_带客户信息.xlsx") merged_df.to_excel(output_path, index=False, sheet_name="明细") print("匹配结果已保存：", output_path)

运行结果可能显示：

订单明细列： ['日期', '订单号', '客户编号', '产品名称', '销售数量', '销售金额', '来源文件'] 客户信息列： ['客户编号', '客户名称', '客户等级', '负责人'] 警告：有 2 行客户信息未匹配到，占总行数 1.67% 匹配结果已保存： output/订单明细_带客户信息.xlsx

如果出现“有行未匹配到”，通常原因包括：

• 客户编号在订单表里存在空格，例如' A001'而不是'A001'。
• 客户编号在两张表里的类型不一致，一边是字符串，一边是数字。
• 客户表确实缺少某些编号记录。

处理方式是在合并前统一 key 字段的格式，例如：

for col in ["客户编号"]: order_df[col] = order_df[col].astype(str).str.strip() customer_df[col] = customer_df[col].astype(str).str.strip()

这一步可以将空字符串和 NaN 的区别先处理好，再做匹配。如果客户编号存在 NaN，直接 astype(str) 会得到字符串'nan'，后面会出现大量错误匹配，所以应当先填充：

order_df["客户编号"] = order_df["客户编号"].fillna("").astype(str).str.strip() customer_df["客户编号"] = customer_df["客户编号"].fillna("").astype(str).str.strip()

把缺失值填成空字符串再统一成字符串格式，是比较严谨的写法。

6. 实战场景三：按指定列拆分数据并生成多个报表

6.1 需求描述

匹配后的订单表里通常包含“区域”列。现在老板希望按区域拆分成多个工作表，比如“华东销售明细”“华南销售明细”“华北销售明细”，最后目标是把每个区域的数据写入同一个工作簿的不同 sheet，也可以另存为多个文件。

这里需要用到的核心能力是groupby或基于某一列唯一值做遍历，再选择性地把子表写入 Excel。

6.2 思路拆解

拆分思路有两种实现路径。

路径 A：拆到同一个文件的多个工作表。

遍历唯一区域，例如用for region, sub_df in df.groupby("区域")，然后使用ExcelWriter把每个 sub_df 写到 sheet_name 为区域名的工作表。

路径 B：拆成多个独立文件。

每个区域单独保存一个 xlsx，文件名形如销售明细_华东.xlsx。这种方式适合把报表作为附件分发给不同负责人。

两种路径可以结合使用，实际业务中按“不同负责人需要拿走自己那一份”来选择。

6.3 示例代码：拆为多个工作表

# -*- coding: utf-8 -*- """ 将总表按区域拆分为多个 sheet """ import pandas as pd from pathlib import Path # 读取匹配好的总表 df = pd.read_excel("output/订单明细_带客户信息.xlsx", sheet_name="明细") # 需要保证区域列没有空值，否则会生成一个 nan 表 df["区域"] = df["区域"].fillna("未知区域") # 创建 ExcelWriter 对象 output_path = Path("output/按区域拆分.xlsx") with pd.ExcelWriter(output_path, engine="openpyxl") as writer: for region, sub_df in df.groupby("区域"): # sheet 名称不能超过 31 个字符，不能包含 /\?*[] 等字符 sheet_name = f"{region}明细" sub_df.to_excel(writer, sheet_name=sheet_name, index=False) print(f"已写入工作表：{sheet_name}，行数：{len(sub_df)}") print("拆分完成：", output_path)

运行后，输出文件按区域拆分.xlsx中会包含多个工作表，每个工作表对应一个区域。

如果希望拆分后的每个子表既包含数据，又保留“客户名称”“客户等级”等列，这段代码不需要改动，因为sub_df继承了原 DataFrame 的全部列。

6.4 示例代码：拆分为多个独立文件

# -*- coding: utf-8 -*- """ 将总表按区域拆分为多个 Excel 文件 """ import pandas as pd from pathlib import Path df = pd.read_excel("output/订单明细_带客户信息.xlsx", sheet_name="明细") df["区域"] = df["区域"].fillna("未知区域") out_dir = Path("output/区域报表") out_dir.mkdir(parents=True, exist_ok=True) for region, sub_df in df.groupby("区域"): safe_region = str(region).replace("/", "_").replace("\\", "_") file_path = out_dir / f"销售明细_{safe_region}.xlsx" sub_df.to_excel(file_path, sheet_name="明细", index=False) print("已生成：", file_path)

如果区域名称中包含/、\、:、*、?、"、<、>、|这些 Windows 文件名非法字符，生成文件时就会报错。因此上面代码用replace先做一层替换。

6.5 工程中常遇到的“多 Sheet 写入”细节

使用pd.ExcelWriter时需要注意两点。其一，如果想在一个 ExcelWriter 中连续写入多个 sheet，不能反复用pd.ExcelWriter后关闭；建议用with语句包裹，确保文件写完并保存。其二，当目标文件已经存在时，如果使用engine="openpyxl"并且在同一个 writer 中写入新 sheet，由于ExcelWriter默认不会自动覆盖整个文件，可能会产生“覆盖或追加”的困惑。

如果目标是每次运行脚本都生成一个全新文件，最稳妥的办法是：先删除旧文件，或写成带时间戳的文件名，例如销售汇总_20250101.xlsx。这样既保留历史，也避免写入冲突。

删除旧文件可以使用标准库：

from pathlib import Path file_path = Path("output/按区域拆分.xlsx") if file_path.exists(): file_path.unlink()

unlink()会删除文件。不过如果删除的是自动生成的报表文件还好，如果是手工维护的正式资料文件，强烈建议不要随便写删除逻辑，而应当使用带时间戳的备份命名。

7. 实战场景四：用 openpyxl 自动化产出带格式的 Excel 报表

7.1 为什么 pandas 导出后还要调格式

pandas 写出的 Excel 文件是“最朴素的表格”：列宽自动适应不理想，表头没有加粗，也没有底色和边框。发给业务部门或领导时，这些缺失的格式会影响阅读体验。

openpyxl 就是弥补这个问题的工具。它可以从 pandas 生成的 xlsx 文件中读取工作簿，然后精确操作每一个单元格的字体、颜色、对齐方式、边框和列宽。下面用一个实际场景来说明。

假设前面已经生成output/按区域拆分.xlsx，里面有多张工作表，现在要统一美化所有 sheet，达到以下要求：

1. 首行表头加粗、白字、带深蓝色背景。
2. 自动调整列宽。
3. 数据区域加上细边框。
4. 冻结首行。

7.2 完整代码实现

# -*- coding: utf-8 -*- """ 用 openpyxl 对 Excel 所有 sheet 做统一格式美化 """ from openpyxl import load_workbook from openpyxl.styles import Font, PatternFill, Alignment, Border, Side from pathlib import Path file_path = Path("output/按区域拆分.xlsx") if not file_path.exists(): raise FileNotFoundError("请先运行拆分脚本，生成 output/按区域拆分.xlsx") wb = load_workbook(file_path) # 定义样式 header_font = Font(name="微软雅黑", size=11, bold=True, color="FFFFFF") header_fill = PatternFill(start_color="4472C4", end_color="4472C4", fill_type="solid") thin_side = Side(style="thin", color="BFBFBF") data_border = Border(left=thin_side, right=thin_side, top=thin_side, bottom=thin_side) center_alignment = Alignment(horizontal="center", vertical="center") for ws in wb.worksheets: # 1. 调节每一列列宽 # 遍历列时，需要知道每一列的最大内容长度 # openpyxl 中 ws.max_column 可以拿到当前工作表最大列数 for col_idx, col_cells in enumerate(ws.columns, start=1): # col_cells 是该列所有单元格 max_len = 0 for cell in col_cells: # 若单元格为空则跳过 if cell.value is None: continue # 简单取字符串长度，中文按长度计 length = len(str(cell.value)) # 避免某些表头过长导致列宽过宽，限制一个最大值 max_len = max(max_len, min(length, 30)) # 设置列宽，左右留一些余量 ws.column_dimensions[col_cells[0].column_letter].width = max_len + 6 # 2. 设置表头样式 for cell in ws[1]: cell.font = header_font cell.fill = header_fill cell.alignment = center_alignment # 3. 给数据区域添加边框 # 数据区域从第 2 行开始，到最大行、最大列 for row in ws.iter_rows(min_row=1, max_row=ws.max_row, min_col=1, max_col=ws.max_column): for cell in row: cell.border = data_border # 数据行也做垂直居中 cell.alignment = Alignment(vertical="center", wrap_text=True) # 4. 冻结首行 ws.freeze_panes = "A2" # 覆盖保存前先备份原文件 backup_path = file_path.with_suffix(".bak.xlsx") if not backup_path.exists(): wb.save(backup_path) wb.save(file_path) print("格式美化完成：", file_path)

7.3 关键代码解释

PatternFill用于给单元格填充背景色，颜色代码是 RGB 的十六进制写法，例如4472C4是偏深一点的蓝色。Font控制字体名称、字号、粗体和文字颜色。Border和Side负责边框，如果不想给所有单元格加边框，可以只给数据区域加。

遍历列计算列宽时，col_cells[0].column_letter可以获取该列对应的 Excel 列字母，比如A、B。像中文、英文、数字混合的内容，精确换算列宽其实很复杂，简单按len(str(value))来估计不会完美，但能满足绝大多数办公场景。如果要更精准，可以按中文字符占两个单位来估算，例如：

length = sum(2 if ord(ch) > 255 else 1 for ch in str(cell.value))

使用 openpyxl 直接做格式修改后，最好先另存为备份文件，再覆盖原文件。原因是 Excel 文件一旦被过程写坏，工作内容就白费了。办公自动化的第一原则是“别把数据搞丢”，所谓高级技巧也应当建立在稳妥备份的前提下。

8. 常见问题与排查思路

办公自动化脚本在实际落地时，最常见的并不是算法问题，而是环境与数据格式问题。下面按高频程度汇总。

8.1 明明 pip 安装了 pandas，运行时却报 ModuleNotFoundError

这个问题在 vscode 和 PyCharm 中都很常见。

最直接的验证方式是，在运行脚本的同一个 IDE 环境里执行：

import sys print(sys.executable)

然后回到命令行执行pip --version，对比两个路径是否一致。

8.2 Excel 中的订单号变成 3.16015e+17 之类的内容

推荐在读取阶段就处理：

df = pd.read_excel("订单.xlsx", dtype={"订单号": str})

8.3 明明两张表里有相同编号，merge 后却匹配不上

一个重要原则：merge 之前先检查 key 列的数据类型和唯一性，不要盲目 merge。

8.4 文件路径中有中文或空格导致读取失败

建议在脚本开头把工作目录切换到脚本所在目录：

from pathlib import Path import os BASE_DIR = Path(__file__).resolve().parent os.chdir(BASE_DIR)

这样可以避免从不同目录运行脚本导致的文件路径不一致。

8.5 生成的 Excel 打开后列宽不对或内容显示不全

实际上，如果用户最终看到的“内容显示不全”，很多时候是因为没有设置行高或没有开启自动换行。openpyxl 中对单元格设置wrap_text=True后，内容会自动换行，配合自动行高显示效果更好。

8.6 每次脚本运行后旧文件被覆盖，想恢复却无法恢复

工程上推荐写一个带时间戳的输出命名函数：

from datetime import datetime timestamp = datetime.now().strftime("%Y%m%d_%H%M%S") output_path = Path(f"output/销售汇总_{timestamp}.xlsx")

这样每次运行不会相互覆盖，也方便回溯历史版本。

9. 最佳实践与工程建议

9.1 先把业务规则整理成文档，再写代码

办公自动化和纯软件开发的差异在于业务灵活，而且经常出现“老板需求一句话，细节藏在 Excel 里”。写代码前，先列出数据从哪来、经过哪些清洗规则、输出成什么结构、谁是最终使用者。这样即使过两个月再看代码，也能快速看懂逻辑。

比如看到区域列有“华东”“华东大区”两种叫法，如果不在代码里做映射规则，合并出来的表就没意义。正确的做法是提前维护一份区域映射字典：

region_map = { "华东": "华东", "华东大区": "华东", "上海": "华东", }

然后用df["区域"] = df["区域"].map(region_map)完成归一化，再执行后面的拆分和统计。

9.2 函数拆分比从头到尾写一个大脚本更可靠

把每个步骤封装成函数，是办公自动化工程化最核心的习惯。同一个目录下文件数量变化、列名变化时，只调整对应函数即可。

def load_sales_files(input_dir: Path) -> pd.DataFrame: ... def normalize_customer_id(df: pd.DataFrame) -> pd.DataFrame: ... def merge_customer_info(df: pd.DataFrame, customer_path: Path) -> pd.DataFrame: ... def split_by_region(df: pd.DataFrame, output_dir: Path) -> None: ...

每个函数只负责一件事，测试时也能单独验证。虽然办公自动化脚本写起来很快，但也可能长期维护，函数化能显著降低维护成本。

9.3 每一步都打印进度与校验信息

脚本运行时不建议什么提示都不输出。至少要在关键节点打印文件数量、读取行数、合并行数、匹配失败行数、输出文件路径。例如：

print(f"[INFO] 读取文件：{file_path.name}，行数：{len(df)}")

这样做不是为了“日志规范”，而是方便定位问题。数据是上游给的，一旦数据格式变化，脚本往往会静默失败，只有打印行数和关键统计量，才能快速发现异常。

9.4 对原文件永远只读，不直接覆盖

办公场景中，最忌讳的就是脚本运行后把原始 Excel 文件覆盖掉。建议原始数据都放在input/目录，代码生成的结果统一放output/。如果你确实需要修改原文件，也要先把原文件复制一份备份。

如果输出到同一个工作簿，明明只是想修改某个 sheet，却误覆盖了另一个 sheet，这种问题必须重视。openpyxl 可以直接load_workbook再修改，但在写入前仍建议保存备份。

9.5 无法转型的数据用 errors="coerce" 前先做好清点

pd.to_numeric、pd.to_datetime使用errors="coerce"可以把无法转换的值变成 NaN，但这也意味着数据会“静默丢失”。如果原来的错误数据量很多，会影响后续统计。

更稳妥的做法是分两步：先转换，再检查转换失败的数量。

converted = pd.to_numeric(df["销售金额"], errors="coerce") bad_count = converted.isna().sum() print(f"无法转换的销售金额数量：{bad_count}") df["销售金额"] = converted

如果 bad_count 过高，就说明原始数据有问题，应该回头检查 Excel 文件，而不是强行靠代码“包容”。

9.6 善用 ExcelWriter 进行多表输出，但别忽略引擎差异

pandas 的to_excel在写入多 sheet 时依赖openpyxl作为引擎。如果你需要保留原文件格式再追加一个 sheet，建议用load_workbook后转成pd.ExcelWriter使用。但需要注意的是，pandas 1.x 和 2.x 中ExcelWriter的追加行为并不完全相同。建议先测试小文件，再对正式数据运行。

9.7 对办公自动化的“安全边界”要有自觉

办公自动化脚本经常操作的是正式业务数据，例如客户明细、销售记录。使用这些数据时，务必遵守公司数据安全规定，不要在公共脚本中写死数据库账号、网盘密码、个人信息等敏感字段。包含个人信息的 Excel 生成文件，不要随意上传到公开代码仓库。

另外，如果脚本涉及删除文件或覆盖文件，执行前先确认文件路径变量正确，尽量用绝对路径或者固定项目结构下的相对路径，避免把脚本放错目录后误删其他文件。

10. 总结与下一步可以做什么

这篇内容从办公自动化的真实需求出发，给出了四个可以串联起来的实战案例：批量合并多个 Excel、跨表匹配合并、按区域拆分多表、用 openpyxl 美化报表格式。对照实际工作流程，你会发现一个“从原始表到成品报表”的完整链路已经打通了。

如果把这些能力继续延伸，还有几个很适合下一步学习的主题：

• 把最后生成的 Excel 报表作为附件，通过 Python 自动发送邮件。
• 把 pandas 处理逻辑接入数据库，从 MySQL/SQL Server 中直接读取数据生成 Excel。
• 把脚本打包成 exe，交付给不会装 Python 的同事使用。
• 学习用 Python 生成 Word、PDF 报告，与 Excel 报表组成一套更完整的办公自动化方案。

办公自动化是一个持续迭代的过程，别指望第一版脚本就能覆盖所有边界情况。真正有效的做法是：先跑通一个最小可用版本，再根据真实数据和实际报错逐步加固。