Python 批量处理 Excel 实战（openpyxl + pandas）

Python 批量处理 Excel 实战

适合谁

• 每天要从后台导出十几张表，手工复制粘贴到一张总表的人
• 做跨境生意，需要把平台订单表清洗成统一格式再发给仓库的人
• 每月要做营收、投放、库存三类报表，流程固定但耗时两小时以上的人

如果你只是想快速处理一次表格，用 Excel 自带功能就够了。真正值得上 Python 的信号是：这件事你每个月、每周甚至每天都会重复做一遍。

两个库怎么选

| 场景 | 推荐 | 理由 | | --- | --- | --- | | 读取 / 聚合 / 计算几十万行 | pandas | 一行代码完成分组统计，速度快 | | 保留原有样式、合并单元格、批注 | openpyxl | pandas 会丢样式，openpyxl 精确到单元格 | | 生成带边框配色的正式报表 | openpyxl | 可以单独控制字体、填充、列宽 | | 只是做数据中间处理，输出新文件 | pandas | 写完 to_excel 一步到位 |

一句话记法：pandas 管数据，openpyxl 管排版。实际项目里经常两个一起用。

实战一：把文件夹里所有订单表合成一张

导出目录里躺着三十多个平台的订单文件，格式相同，逐个打开复制是最浪费时间的做法。

import pandas as pd
from pathlib import Path

SRC = Path("./orders")
# 只取 xlsx，跳过 Excel 打开的临时文件（~$ 开头）
files = [f for f in SRC.glob("*.xlsx") if not f.name.startswith("~$")]

frames = []
for f in files:
    df = pd.read_excel(f, dtype={"订单号": str})  # 订单号强制转字符串，防止科学计数法
    df["来源文件"] = f.name
    frames.append(df)

merged = pd.concat(frames, ignore_index=True)
merged.to_excel("订单汇总.xlsx", index=False)
print(f"合并完成：{len(files)} 个文件，共 {len(merged)} 行")

三个新手必踩的坑，这里一次性避开：

1. 订单号变成科学计数法 —— 读表时用 dtype={"订单号": str} 锁死类型 2. Excel 临时文件混进来 —— 过滤 ~$ 前缀文件 3. 合并后行数对不上 —— 用 ignore_index=True，否则索引重复会导致后续筛选错乱

实战二：清洗脏数据

平台导出的表通常带着空行、重复单号、金额带货币符号。

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

# 1. 去掉全空行
df = df.dropna(how="all")

# 2. 同一订单号只保留最后一条更新记录
df = df.drop_duplicates(subset=["订单号"], keep="last")

# 3. 金额字段可能含 ¥、逗号，先转字符串清洗再转数值
df["金额"] = (
    df["金额"].astype(str)
    .str.replace(r"[¥￥,$ ]", "", regex=True)
    .astype(float)
)

# 4. 日期字段统一格式
df["下单时间"] = pd.to_datetime(df["下单时间"], errors="coerce")

# 5. 剔除金额为负或缺失的关键记录
df = df[df["金额"].notna() & (df["金额"] > 0)]

print(df.describe())

errors="coerce" 是关键：遇到解析不了的日期不会报错中断，而是转成 NaT，之后再统一过滤。

实战三：用 openpyxl 生成能直接发出去的报表

pandas 输出的表没有美观可言。要交给老板或客户的报表，用 openpyxl 收拾一遍。

from openpyxl import load_workbook
from openpyxl.styles import Font, PatternFill, Alignment
from openpyxl.utils import get_column_letter

wb = load_workbook("订单汇总.xlsx")
ws = wb.active

header_fill = PatternFill("solid", fgColor="4F46E5")
header_font = Font(color="FFFFFF", bold=True)

for cell in ws[1]:
    cell.fill = header_fill
    cell.font = header_font
    cell.alignment = Alignment(horizontal="center", vertical="center")

# 按内容宽度自适应列宽
for col in ws.columns:
    letter = get_column_letter(col[0].column)
    width = max((len(str(c.value)) for c in col if c.value), default=10)
    ws.column_dimensions[letter].width = min(width + 4, 40)

# 冻结首行，滚动时表头不跑
ws.freeze_panes = "A2"

wb.save("月度订单报表.xlsx")

每周自动跑一次的写法

脚本稳定之后，就不需要手动点了。

• macOS / Linux：crontab -e 加一行 0 9 * * 1 /usr/bin/python3 /path/to/report.py
• Windows：任务计划程序里建基本任务，触发器选每周一，操作指向 pythonw.exe（用 pythonw 不会有黑窗口闪一下）

跑批脚本建议加三步保险：

1. 先把上次的产出重命名备份，再写新文件 2. 关键步骤用 logging 落日志，别只用 print 3. 异常时用 try / except 包住并把错误写进日志，方便第二天查

常见问题

装 pandas 时报错或很慢？ 用国内源：pip install pandas openpyxl -i https://pypi.tuna.tsinghua.edu.cn/simple。

中文写进 Excel 乱码？ pandas / openpyxl 本身 UTF-8 没问题，乱码通常是源文件编码问题。读取 CSV 时显式指定 encoding="utf-8-sig"（处理带 BOM 的表很有效）。

文件被占用报错 PermissionError？ 说明这张表正开着。Excel 打开文件时会上锁，脚本跑之前先关掉。

下一步

• 想把图表也自动化：看 matplotlib 或直接用 Excel 原生图表（openpyxl 支持 BarChart）
• 数据量上到百万行：换 polars 或先落 SQLite 再做聚合
• 想做成同事也能用的工具：套一层 Streamlit，十行代码就是个网页小工具