保留公式只覆盖数值：ExcelWriter overlay 模式，公式列不被动

一、问题起点：一次 to_excel 之后，公式去哪了

几乎所有做报表自动化的团队都踩过同一个坑：模板文件里精心设计的公式列（比如“单价×数量”“环比增长率”“累计求和”），在脚本跑完一轮之后全部变成了静态数值，或者干脆变成空白。更隐蔽的情况是——公式还在，但显示的是上一次的旧结果，直到用户手动按一次 F9 才刷新。前者是“公式被覆盖”，后者是“缓存值没更新”，两者根因完全不同，却经常被混为一谈。

典型触发路径是这样的：用 pandas 读入模板 → 加工数据 → df.to_excel(模板路径)。pandas 底层默认走 openpyxl 或 xlsxwriter 引擎，而这两个引擎在“写入一个已存在的文件”时，行为并不一致：openpyxl 是“加载整个工作簿到内存、修改、再整体序列化”，xlsxwriter 则是“只能新建、不能打开已有文件”。换句话说，用 xlsxwriter 去写模板，本质上是在新建一个同名文件，原模板的一切都会丢失。

本文评述：把“公式丢失”简单归因于“库不好用”是一种偷懒。真正的问题是写入 API 的语义层级——是“文件级替换”还是“单元格级覆盖”。理解这一点，后面所有方案都是自然推论。

从工程视角看，这个问题的成本被严重低估。一份带公式的预算表、对账表、KPI 看板，公式往往承载了业务规则本身。公式一旦被数值固化，表面上看“数据还在”，实际上业务逻辑已经死了——下次数据更新时，没有人知道那一列到底是该算的还是该填的。这也是为什么在财务、供应链、医药临床数据管理这些强合规领域，Excel 模板的公式保全被视为数据完整性（data integrity）的一部分，而不是单纯的格式问题。

二、分析主线：写入粒度决定保全边界

本文要确立的核心主线只有一句话：写入粒度决定保全边界。你以什么粒度去写，决定了什么会被保留、什么会被抹掉。

把写入行为按粒度从粗到细排开，可以形成一个清晰的谱系：

所谓 overlay 模式，本质就是落在“单元格级覆盖”这一层：只对需要更新的单元格赋值，其余单元格——尤其是公式列——原封不动。它不是一个库的专属功能，而是一种写入策略。任何支持“打开已有工作簿并修改单元格”的库，都可以实现 overlay；反之，只支持新建的库，无论怎么封装都做不到真正的 overlay。

笔者认为：把 overlay 理解成“模式”而非“功能”，是选型时的关键分水岭。很多团队在 xlsxwriter 上反复尝试“保留公式”，方向从一开始就错了。

三、OOXML 视角：公式、缓存值与 calcChain 的真实结构

要真正理解 overlay 为什么能保住公式，得先看清 xlsx 文件内部长什么样。xlsx 本质是一个 ZIP 包，解压后是一组 XML。与公式相关的关键部件有三个：工作表 XML（xl/worksheets/sheetN.xml）、共享公式表、以及计算链 xl/calcChain.xml。

3.1 公式单元格的 XML 形态

一个带公式的单元格，在 sheet XML 里大致长这样：

<c r="D2">
  <f>C2*B2</f>
  <v>1200</v>
</c>

其中 <f> 是公式本体，<v> 是上一次计算得到的缓存值。注意：Excel 打开文件时，优先展示的是 <v>，而不是实时重算。这就是“公式没变但显示旧值”的根源——缓存值过期了。

3.2 共享公式与 calcChain

当一列公式高度相似时，Excel 会把它压缩成共享公式（shared formula），只在首个单元格写完整公式，其余单元格用 t="shared" 加偏移量引用。而 calcChain.xml 记录了“哪些单元格是公式、按什么顺序计算”。如果 overlay 时只改了值没动公式，calcChain 通常无需变更；但如果你删除了某个公式单元格，calcChain 就会与实际不符，Excel 可能报“文件已损坏”或静默重建。

本文评述：calcChain 是一个“可选但敏感”的部件。openpyxl 在保存时会根据实际公式情况重建它，这也是为什么用 openpyxl 做 overlay 相对安全——它替你处理了这层一致性。

3.3 为什么“只写值”就能保住公式

因为公式和值是同一个 <c> 节点下的两个子元素。当你只对“数据列”的单元格赋值时，公式列的 <c> 节点根本没被触碰，<f> 自然原样保留。反过来，如果整表重写，所有 <c> 节点都是新建的，公式自然不复存在。这就是“写入粒度决定保全边界”在文件格式层面的直接体现。

四、主流写入栈的 overlay 能力对照

选型之前，先把各栈的“能不能 overlay”这张表看清楚。下表基于各库官方文档与笔者实际验证整理，能力判定以“能否打开已有 xlsx 并只改部分单元格”为准。

笔者认为：选型时“能不能打开已有文件”应当作为硬性门槛。凡是不能打开的，无论文档写得多漂亮，都不适合 overlay 场景。

五、openpyxl 实战：单元格级覆盖的完整骨架

下面给出一套可直接复用的 overlay 骨架。核心思路：加载模板 → 定位数据区 → 逐单元格赋值 → 保存。全程不触碰公式列。

5.1 最小可用版本

from openpyxl import load_workbook

def overlay_write(template_path, out_path, rows):
    # data_only=False 是关键：保留公式本体
    wb = load_workbook(template_path, data_only=False)
    ws = wb["Sheet1"]

    start_row = 2          # 数据从第 2 行开始
    data_cols = ["A", "B"] # 只覆盖这两列，C 列是公式列

    for i, row in enumerate(rows):
        r = start_row + i
        for col, val in zip(data_cols, row):
            ws[f"{col}{r}"] = val   # 单元格级赋值

    wb.save(out_path)

这段代码里有两个容易踩的点。第一，data_only=False 必须显式写出，否则 openpyxl 会把公式读成缓存值，再保存时公式就没了。第二，ws[f"{col}{r}"] = val 是单元格级赋值，不会影响同行的公式列。

5.2 处理“数据行数动态变化”

真实场景里，数据行数往往不固定。如果模板里公式列只预置了 100 行，而这次要写 150 行，多出来的 50 行公式列就是空的。此时需要“向下填充公式”，而不是让公式列空着。

from openpyxl.utils import get_column_letter

def fill_formula_down(ws, col_letter, first_row, last_row):
    src = ws[f"{col_letter}{first_row}"].value
    if not (isinstance(src, str) and src.startswith("=")):
        return
    for r in range(first_row + 1, last_row + 1):
        ws[f"{col_letter}{r}"] = src  # openpyxl 会自动平移相对引用

这里依赖 openpyxl 的相对引用平移能力：当你把 =C2*B2 写到第 3 行时，它会自动变成 =C3*B3。本文评述：这个特性在多数情况下符合直觉，但对含绝对引用（$）或跨表引用的公式，务必人工验证平移结果，不要盲信。

5.3 与 pandas 协作的正确姿势

很多团队舍不得 pandas 的数据加工能力，那就让它只负责“算”，不负责“写”。

import pandas as pd
from openpyxl import load_workbook

df = pd.read_sql(sql, conn)          # pandas 负责取数与加工
wb = load_workbook("template.xlsx")  # openpyxl 负责落盘
ws = wb.active

for i, row in enumerate(df.itertuples(index=False), start=2):
    ws[f"A{i}"] = row.订单号
    ws[f"B{i}"] = row.数量
    # C 列公式不动

wb.save("output.xlsx")

本文评述：把 pandas 限定在“计算层”、把 openpyxl 限定在“写入层”，是一种职责分离。它牺牲了一点代码简洁度，换来的是公式、样式、数据验证的完整保全，这笔账在长期维护中非常划算。

六、公式列保护：三种策略与适用边界

overlay 解决了“脚本不破坏公式”，但还有一个问题：如何防止“人”破坏公式。这需要工作表保护层面的设计。以下三种策略各有取舍。

6.1 策略一：整表保护 + 数据区解锁

默认所有单元格 locked=True，把数据录入区设为 locked=False，再开启 sheet protection。这样用户只能改数据区，公式列被锁死。

from openpyxl.styles import Protection

for r in range(2, 1000):
    ws[f"A{r}"].protection = Protection(locked=False)
    ws[f"B{r}"].protection = Protection(locked=False)

ws.protection.sheet = True
ws.protection.password = "optional"

适用边界：适合交付给业务人员填写的模板。注意保护密码只是“防误改”，不是安全机制，Excel 的保护很容易被绕过。

6.2 策略二：公式列隐藏 + 列宽归零

把辅助公式列（比如中间计算步骤）隐藏起来，只展示结果列。这不能阻止有心人取消隐藏，但能显著降低误操作概率。

6.3 策略三：写入前校验 + 写入后回读

这是工程上最可靠的一招：写入前记录公式列的公式指纹，写入后重新加载文件，比对公式是否一致。不一致就告警甚至回滚。

def snapshot_formulas(path, sheet, col, rows):
    wb = load_workbook(path, data_only=False)
    ws = wb[sheet]
    return {f"{col}{r}": ws[f"{col}{r}"].value for r in rows}

before = snapshot_formulas("template.xlsx", "Sheet1", "C", range(2, 101))
# ... overlay 写入 ...
after = snapshot_formulas("output.xlsx", "Sheet1", "C", range(2, 101))
assert before == after, "公式列被意外修改！"

笔者认为：三种策略不是互斥的，而是纵深防御。生产环境里，策略一 + 策略三的组合性价比最高。

七、缓存值陷阱：为什么公式没变，打开却显示旧值

这是 overlay 场景里最迷惑人的问题。公式明明保住了，用户打开却看到旧数字。原因在第三章已经埋下伏笔：Excel 优先显示 <v> 缓存值，而不是实时重算。

7.1 三种触发条件

• 脚本改了公式引用的数据列，但没有触发重算；
• 文件由非 Excel 程序生成，从未写入过缓存值；
• 工作簿被设置为手动计算模式（calcPr 中 calcMode="manual"）。

7.2 处理办法

最直接的办法是强制全量重算。openpyxl 提供了工作簿级别的开关：

wb.calculation.fullCalcOnLoad = True
wb.calculation.calcMode = "auto"

设置之后，Excel 打开文件时会自动全量重算，缓存值随即刷新。本文评述：fullCalcOnLoad 是一个被严重低估的开关，它用一次打开时的计算开销，换来了“数据永远新鲜”的确定性，在 overlay 场景里几乎是必选项。

7.3 服务端重算的替代路径

如果下游系统不经过 Excel 直接读取缓存值（比如某些 BI 工具用 openpyxl 的 data_only=True 读），那么 fullCalcOnLoad 帮不上忙——因为它只在 Excel 打开时生效。此时需要真正的计算引擎，比如 LibreOffice 的 headless 模式、或 Python 侧的 formulas、pycel 等库。这类方案能算出缓存值并写回，但覆盖面有限，复杂函数（如 XLOOKUP、动态数组）支持度参差不齐，需要按实际公式集评估。

八、多栈对照：pandas / xlsxwriter / POI / NPOI / EPPlus

不同技术栈的 overlay 写法差异不小，这里给出关键片段，方便跨语言团队对齐。

8.1 Apache POI（Java）

try (FileInputStream fis = new FileInputStream("template.xlsx");
     XSSFWorkbook wb = new XSSFWorkbook(fis)) {
    XSSFSheet ws = wb.getSheetAt(0);
    for (int i = 0; i < rows.size(); i++) {
        Row row = ws.getRow(i + 1);
        row.getCell(0).setCellValue(rows.get(i).getId());
        row.getCell(1).setCellValue(rows.get(i).getQty());
        // 第 3 列公式不动
    }
    try (FileOutputStream fos = new FileOutputStream("output.xlsx")) {
        wb.write(fos);
    }
}

POI 的语义与 openpyxl 一致：打开已有工作簿、改单元格、写回。注意 getCell 可能返回 null，生产代码要判空。

8.2 NPOI（.NET）

using (var fs = new FileStream("template.xlsx", FileMode.Open, FileAccess.Read)) {
    var wb = new XSSFWorkbook(fs);
    var ws = wb.GetSheetAt(0);
    for (int i = 0; i < rows.Count; i++) {
        var row = ws.GetRow(i + 1) ?? ws.CreateRow(i + 1);
        row.GetCell(0)?.SetCellValue(rows[i].Id) ?? row.CreateCell(0).SetCellValue(rows[i].Id);
        row.CreateCell(1).SetCellValue(rows[i].Qty);
    }
    using (var outFs = new FileStream("output.xlsx", FileMode.Create)) {
        wb.Write(outFs);
    }
}

8.3 为什么 pandas 需要绕行

pandas 的 to_excel 设计目标是“把 DataFrame 写成一张表”，它的语义天然是工作表级重写。即便用 ExcelWriter(mode="a") 追加模式，也是新建 sheet 而不是改单元格。所以正确做法是：pandas 只做数据加工，落盘交给 openpyxl / POI。本文评述：这不是 pandas 的缺陷，而是职责边界——让数据框架库去承担单元格级写入，本身就是错配。

延伸阅读：openpyxl 官方文档 “Inserting and deleting rows and columns” 一节对公式平移有详细说明；pandas 官方 “Excel I/O” 文档明确了各引擎的写入语义差异。视频方面，可参考 YouTube 上 “openpyxl tutorial: keep formulas when writing data” 类实操演示，配合本文骨架食用效果更佳。

九、性能与一致性：大文件 overlay 的取舍

openpyxl 的常规模式会把整个工作簿读进内存，几万行的文件尚可，几十万行就会吃紧。此时有几个方向。

9.1 read_only 与 write_only 的局限

openpyxl 提供 read_only=True 和 write_only=True 两种流式模式，能显著降低内存占用。但要注意：read_only 模式下单元格是只读的，无法直接改；write_only 模式只能从头写新文件，无法打开已有文件。所以这两种模式都无法直接用于 overlay。本文评述：这是一个典型的“性能与能力不可兼得”，overlay 场景下只能接受常规模式的内存开销，或者改用 POI 的 SXSSF（同样只支持写新文件）。

9.2 分片与合并

如果单文件确实过大，可以考虑“按业务维度分片成多个模板文件”，每个文件行数可控，overlay 后再由下游汇总。这牺牲了单文件完整性，换来了处理稳定性。

9.3 一致性优先于吞吐

在财务、对账这类场景，宁可慢一点，也要保证公式保全和缓存值正确。建议把“公式指纹校验”作为流水线的强制关卡，而不是可选步骤。

十、工程化封装与自动化校验

把前面的碎片拼成一个可维护的模块，大致需要四层：模板管理层、写入层、校验层、重算层。

• 模板管理层：模板文件版本化，公式列清单化（记录哪些列是公式列）；
• 写入层：只写数据列，公式列跳过；
• 校验层：写入前后比对公式指纹，失败即告警；
• 重算层：设置 fullCalcOnLoad，必要时调用 LibreOffice 重算。

一个实用的技巧是把“公式列清单”写进模板的隐藏配置 sheet，脚本读取配置来决定写哪些列。这样模板改版时，脚本无需改代码。本文评述：把“哪些列是公式列”这一知识从代码里搬到模板里，是降低长期维护成本的关键一步。

# 模板内隐藏 sheet "_meta"
# A列: 列名  B列: 是否公式列(1/0)
meta = wb["_meta"]
formula_cols = {row[0].value for row in meta.iter_rows(min_row=2) if row[1].value == 1}

十一、前沿与预判：从公式保全到语义化表格

把视野拉远一点，overlay 问题其实是“电子表格作为数据容器”这一范式矛盾的缩影。表格里同时住着数据、公式、格式、验证规则，而大多数程序化写入工具只把表格当“二维数组”。

近几年的一个明显趋势是“结构化引用”的普及——Excel 表格（ListObject）用列名而非单元格地址引用，公式天然具备扩展性。如果模板用结构化引用，overlay 时新增行会自动纳入公式范围，公式保全的负担大幅下降。本文评述：结构化引用是电子表格从“坐标网格”走向“语义数据表”的重要一步，值得在模板设计阶段就纳入考量。

另一个方向是“公式即代码”的治理思路：把关键公式纳入版本控制，用测试用例验证其行为，而不是依赖人工检查。这与数据工程里的 dbt、Great Expectations 思路一脉相承。可以预见，未来的报表流水线会把“公式保全校验”作为数据质量检查的一个标准项。

十二、结论与可执行清单

回到主线：写入粒度决定保全边界。想要公式列不被动，就必须把写入粒度压到单元格级，并选择支持“打开已有文件”的库。以下是可直接落地的清单：

1. 选型：确认库支持打开已有 xlsx（openpyxl / POI / NPOI / EPPlus）；
2. 加载：load_workbook(data_only=False)，保住公式本体；
3. 写入：只对数据列做单元格级赋值，公式列跳过；
4. 扩展：数据行超出模板时，向下填充公式并验证引用平移；
5. 重算：设置 fullCalcOnLoad=True，避免旧缓存值；
6. 校验：写入前后比对公式指纹，失败即告警；
7. 保护：整表保护 + 数据区解锁，防人工误改；
8. 治理：公式列清单入模板，公式纳入版本控制。

本文评述：overlay 不是炫技，而是把“表格里的业务逻辑”当作一等公民来对待。当脚本不再粗暴地重写整张表，公式、验证、格式这些沉淀下来的资产才真正被尊重。