openpyxl 批量处理 Excel 实战：合并多表 / 公式填充 / 样式自动化

一、为什么 Excel 自动化是 80% 办公族的隐藏 KPI

上周和一位在跨境电商做财务总监的朋友吃饭，他吐槽每月 1 号到 5 号团队要把 30多个站点的对账 Excel 合并到一张总表，4 个人每天加班到凌晨。我看了一眼流程：人工打开30 个 xlsx、复制 sheet、贴到总表、公式下拉、调整样式，循环 4 天。这个场景在国内财务、运营、HR 圈子里每家公司都在上演。

我曾帮一家 SaaS 公司把 HR 月报流程从 6 小时压缩到 90 秒，关键就是 openpyxl。它不像 pandas 那样适合做复杂数据分析，但在「保留原格式 + 写公式 + 调样式」这件事上是王者。今天我用 3 个真实业务场景（多工作簿合并、跨表公式下拉、千行报表样式）演示一遍。

二、openpyxl 基础：Workbook / Worksheet / Cell 三层模型

openpyxl 的对象模型非常直观，整个 Excel 文件就是一个 Workbook，工作表是 Worksheet，单元格是 Cell。三者关系：

• Workbook：相当于一个 .xlsx 文件，包含若干 Worksheet
• Worksheet：每个 sheet，常见操作是读写 cell- Cell：单元格，包含 value、font、fill、border、number_format 等属性

# 安装：pip install openpyxl==3.1.2
from openpyxl import Workbook, load_workbook

# 创建新文件
wb = Workbook()
ws = wb.active
ws["A1"] = "Hello"
ws["B1"] = "World"
ws.append(["row1_col1", "row1_col2"])  # 追加一行
wb.save("demo.xlsx")

# 读取已有文件
wb = load_workbook("demo.xlsx", data_only=False)  # data_only=False 保留公式
print(wb.sheetnames)        # 所有 sheet 名
ws = wb["Sheet"]
print(ws["A1"].value)       # 读单元格值
print(ws.max_row, ws.max_column)  # 数据范围

注意 data_only 参数：设为 True 时打开文件后 .value 返回的是公式计算后的结果（要求 Excel 之前打开保存过）；设为 False 返回公式字符串本身。批量处理时建议 False，避免误读缓存值。

三、实战 1：多工作簿合并

假设每个分站点的对账文件命名是 site_001.xlsx、site_002.xlsx，每个文件只有一个 sheet，结构一致。我们要把它们全部合并到 summary.xlsx 的不同 sheet 里：

# merge_workbooks.py
# 用法：python merge_workbooks.py ./sites ./summary.xlsx
import sys, glob, os
from openpyxl import load_workbook, Workbook

src_dir = sys.argv[1]
dst_path = sys.argv[2]

# 1. 创建目标 workbook
out_wb = Workbook()
out_wb.remove(out_wb.active)  # 删掉默认 Sheet

# 2. 遍历源文件
files = sorted(glob.glob(os.path.join(src_dir, "site_*.xlsx")))
for fp in files:
    src_wb = load_workbook(fp, data_only=False)
    src_ws = src_wb.active
    site_name = os.path.splitext(os.path.basename(fp))[0]

    # 3. 复制 sheet 到目标文件
    new_ws = out_wb.create_sheet(title=site_name[:31])  # sheet 名最长 31 字符
    for row in src_ws.iter_rows(values_only=False):
        for cell in row:
            new_ws[cell.coordinate].value = cell.value

out_wb.save(dst_path)
print(f"merged {len(files)} files → {dst_path}")

代码使用 iter_rows 而不是逐个 cell(row, col) 访问，性能提升3-5 倍。sheet 名最长 31 字符是 Excel 硬限制，超过了会抛 InvalidWorksheetName。

四、实战 2：公式批量下拉

合并完数据，下一步通常是计算”小计”、”合计”、”环比”。如果硬编码数值，下次源数据变了公式就失效。正确做法是写公式：

# fill_formulas.py
from openpyxl import load_workbook
from openpyxl.utils import get_column_letter

wb = load_workbook("summary.xlsx")
for ws in wb.worksheets:
    last_row = ws.max_row
    if last_row < 2:
        continue
    # 在末列写小计公式（SUM 当前行 B 到 H）
    sub_col = ws.max_column + 1
    col_letter = get_column_letter(sub_col)
    ws.cell(row=1, column=sub_col, value="小计")
    for r in range(2, last_row + 1):
        # 公式：B 到 H 列求和
        ws.cell(row=r, column=sub_col,
                value=f"=SUM(B{r}:H{r})")

    # 在最末行写合计（SUM 所有小计）
    total_row = last_row + 1
    ws.cell(row=total_row, column=1, value="合计")
    ws.cell(row=total_row, column=sub_col,
            value=f"=SUM({col_letter}2:{col_letter}{last_row})")
wb.save("summary_with_formula.xlsx")

公式用字符串赋值即可，openpyxl 会原样写入，Excel 打开时会自动计算。注意：data_only 参数会影响后续读取结果，但不影响写入。

五、实战 3：样式自动化

HR 月报最难的是样式：表头蓝色背景白字加粗、数字列千分位、负数红色、整行边框。我用 NamedStyle 统一管理：

# style_report.py
from openpyxl import load_workbook
from openpyxl.styles import (
    Font, PatternFill, Border, Side, Alignment, NamedStyle
)

wb = load_workbook("summary_with_formula.xlsx")

# 1. 定义 4 个 NamedStyle
header_font = Font(name="微软雅黑", size=11, bold=True, color="FFFFFF")
header_fill = PatternFill("solid", fgColor="305496")
header_style = NamedStyle(name="header")
header_style.font = header_font
header_style.fill = header_fill
header_style.alignment = Alignment(horizontal="center", vertical="center")

money_style = NamedStyle(name="money", number_format="#,##0.00;[Red]-#,##0.00")
thin = Side(border_style="thin", color="BFBFBF")
border_style = NamedStyle(name="border", border=Border(left=thin, right=thin, top=thin, bottom=thin))

wb.add_named_style(header_style)
wb.add_named_style(money_style)
wb.add_named_style(border_style)

# 2. 应用到每个 sheet
for ws in wb.worksheets:
    # 表头样式
    for cell in ws[1]:
        cell.style = "header"
    ws.row_dimensions[1].height = 24

    # 数据行样式    for row in ws.iter_rows(min_row=2, max_row=ws.max_row):
        for cell in row:
            cell.style = "border"
            # 数字列应用金额格式
            if isinstance(cell.value, (int, float)):
                cell.style = "money"

    # 列宽自适应
    for col_idx in range(1, ws.max_column + 1):
        ws.column_dimensions[chr(64 + col_idx)].width = 14

wb.save("summary_styled.xlsx")
print("done")

NamedStyle 的好处是样式可复用，修改一处全局生效。如果用普通 cell.font = ... 写法，每个单元格都是独立 Style 对象，后续修改非常麻烦。

六、性能优化：read_only / write_only 模式对比

如果只是读取数据做分析，不需要写公式/样式，可以用 read_only=True 模式，它流式读取不加载整个树到内存。如果是从头生成大文件，用 write_only=True：

# perf_test.py
import time, os
from openpyxl import Workbook, load_workbook

# 生成 10 万行测试数据
src = Workbook()
ws = src.active
for r in range(1, 100_001):
    ws.append([f"name_{r}", r, r * 1.5])
src.save("big.xlsx")
print(f"file size: {os.path.getsize('big.xlsx')/1024/1024:.1f} MB")

# 普通模式复制
t0 = time.time()
src = load_workbook("big.xlsx")
dst = Workbook()
ws2 = dst.active
for row in src.active.iter_rows(values_only=True):
    ws2.append(row)
dst.save("big_copy_normal.xlsx")
print(f"normal mode: {time.time()-t0:.2f}s")

# read_only + write_only 模式
t0 = time.time()
src = load_workbook("big.xlsx", read_only=True)
dst = Workbook(write_only=True)
ws3 = dst.create_sheet()
for row in src.active.iter_rows(values_only=True):
    ws3.append(row)
dst.save("big_copy_fast.xlsx")
print(f"fast mode: {time.time()-t0:.2f}s")

实测 10 万行 × 3 列，普通模式约 18 秒，read_only + write_only 模式约 4 秒。原理是普通模式构建完整单元格对象树（包含样式、坐标、超链接等元数据），而流式模式只保留数据。

七、踩坑记录

踩坑 1：合并单元格后索引错位

ws.merge_cells("A1:D1") 后，访问 B1、C1、D1 会得到 None。要遍历合并区域需要先 ws.merged_cells.ranges 拿到所有 range，再判断坐标是否落在其中。

踩坑 2：数字 vs 字符串

从 CSV 读到的 “123” 写入 Excel 是字符串，求和公式会忽略。可以用 cell.value = int(value) 或写入时统一 cell.data_type = 'n'。

踩坑 3：大文件内存爆掉

加载 100MB xlsx 用普通模式可能占 2GB 内存。处理超大文件必须用 read_only=True，且循环结束前不要访问 src.sheetnames 之外的对象。

八、小结：openpyxl vs xlsxwriter vs pandas.ExcelWriter 选型矩阵

场景	推荐库	理由
读取数据 + 简单处理	pandas	API 简洁，read_excel 够用
保留公式 + 写样式	openpyxl	唯一同时支持读写公式和样式的成熟库
从零生成大文件	openpyxl write_only / xlsxwriter	都很快，xlsxwriter 样式 API 更友好但不支持读
需要图表 / 数据透视表	openpyxl	支持基础图表，复杂图表建议用 xlsxwriter

总结：如果你要做「读 → 改 → 写」并保留原 Excel 公式与样式，openpyxl 是唯一选择；纯生成大报表用 write_only 模式；纯数据分析用 pandas。掌握这套组合拳，下个月1 号你就能准时下班。