需求很简单:客户给一份 Excel 模板,程序按规则往里填数据,填完交还。
模板是真实业务文件,建筑行业的会议记录表,55 列 103 行,180 个合并单元格,还有几个表单控件和排版好的打印设置。
我用 openpyxl 写了填充逻辑,跑通,打开一看:数据位置全对,合并单元格在,公式在,格式也在。
交付了。
那是个残件,我当时完全没看出来。
先看数据
后来我造了个样本模板专门测这件事。它包含图形对象、数据验证、条件格式、打印设置、冻结窗格,然后用 openpyxl 打开、改一个单元格、保存:
零件数 原始 12 → openpyxl 9
文件大小 原始 7398 → openpyxl 5708
丢失:
xl/drawings/drawing1.xml 图形对象
xl/worksheets/_rels/sheet1.xml.rels 图形的关联关系
xl/sharedStrings.xml
那个黄色文本框「注意事項」,保存之后彻底不存在了。
但请注意它没丢什么:数据验证还在,条件格式还在,页眉页脚还在,冻结窗格也在。
所以你打开文件一眼看去毫无异常。它丢的恰好是你不会去检查的那部分。
回到开头那份真实模板,情况更糟:23 个零件只剩 10 个,30,615 字节掉到 16,438,表单控件和打印设置一起没了。
我为什么没发现
因为测试样本用错了。
最早验证的时候,我自己造了个 4KB 的空模板,几个单元格、一点格式,跑完对比一切正常。
当然正常。那个文件里本来就没什么可丢的。
这是整件事里最值钱的一条教训:
测文件保真度,样本必须内容丰富。 你造得出来的简化样本,openpyxl 也认得,所以它一个都不会丢。
真实业务文件里有什么?打印区域、页眉页脚、表单控件、数据验证、条件格式、图形、批注、自定义属性。这些才是会丢的,而它们恰恰是你不会想到要在测试样本里造的。
xlsx 到底是什么
它就是个 zip。 把后缀改成 .zip 解开:
template.xlsx
├── [Content_Types].xml
├── _rels/.rels
├── docProps/
│ ├── app.xml
│ └── core.xml
└── xl/
├── workbook.xml
├── worksheets/
│ ├── sheet1.xml ← 单元格数据只在这里
│ └── _rels/
│ └── sheet1.xml.rels ← 这张表引用了哪些外部零件
├── sharedStrings.xml ← 字符串池
├── styles.xml
├── theme/theme1.xml
├── drawings/drawing1.xml ← 图形
├── ctrlProps/ ← 表单控件
└── printerSettings/ ← 打印设置
我要改的只是单元格的值,也就是 sheet1.xml 里的一小段。其余十几个文件根本不需要碰。
两条路径
问题就出在这里。
openpyxl 的路径
┌──────────┐ 解析 ┌────────────┐ 序列化 ┌──────────┐
│ 原始 xlsx │ ──────▶ │ 对象模型 │ ────────▶ │ 新的 xlsx │
└──────────┘ └────────────┘ └──────────┘
▲
└─ 模型覆盖不到的零件,到这一步就没了
drawings / ctrlProps / printerSettings ...
当 zip 处理的路径
┌──────────┐
│ 原始 xlsx │
└────┬─────┘
├─ sheet1.xml ──── 改单元格的值 ────▶ 写入新包
├─ drawings/ ──── 字节级原样复制 ──▶ 写入新包
├─ ctrlProps/ ──── 字节级原样复制 ──▶ 写入新包
├─ styles.xml ──── 字节级原样复制 ──▶ 写入新包
└─ ... ──── 字节级原样复制 ──▶ 写入新包
openpyxl 保存 xlsx 的本质,是读进来、按自己的对象模型重新生成一遍。它的模型覆盖不到的零件,重新生成时就不存在了。
而如果只需要改一个文件,那就只改那一个:
import zipfile
def write_cells(src, dst, cells):
with zipfile.ZipFile(src) as zin:
sheet = "xl/worksheets/sheet1.xml"
new_xml = apply_cells(zin.read(sheet), cells) # 只改单元格的值
with zipfile.ZipFile(dst, "w", zipfile.ZIP_DEFLATED) as zout:
for item in zin.infolist():
data = new_xml if item.filename == sheet else zin.read(item.filename)
zout.writestr(item, data) # 其余原样搬运
核心是最后两行。这个方案的可靠性来自一个很朴素的道理:
它根本不理解那些控件和图形,所以它不可能弄丢。
同一份样本再测:12 个零件,一个不少。
细节一:绕开 sharedStrings
xlsx 里的字符串默认存在共享池 sharedStrings.xml,单元格通过索引引用:
<c r="B3" t="s"><v>42</v></c> <!-- 值是共享池里第 42 项 -->
顺着这条路写,每加一个字符串都要查重、追加、更新计数、修正索引,任何一步错了都会破坏其他单元格的引用。
xlsx 规范还允许另一种写法,inline string,值直接写在单元格里:
<c r="B3" t="inlineStr"><is><t>值</t></is></c>
不碰共享池,不改索引,自然也不会破坏原有引用。代价是文件略大,对业务数据量可以忽略。
细节二:一个把我坑了的 falsy
写插入逻辑的时候我这么写:
row = find_row(sheet_data, rownum) or insert_row(sheet_data, rownum)
看着很自然,但它有个 bug。
ElementTree 里,没有子元素的 Element 是 falsy 的。
模板里空行很常见。一个存在但没有单元格的 <row>,会被 or 判成假,于是代码又插入一个同号的行。同一个行号出现两次,Excel 会直接报文件损坏。
正确写法只能是显式判空:
row = find_row(sheet_data, rownum)
if row is None:
row = insert_row(sheet_data, rownum)
发现它靠的是 pytest 打出来的 DeprecationWarning:
DeprecationWarning: Testing an element's truth value will raise an exception
in future versions. Use specific 'len(elem)' or 'elem is not None' test instead.
这个警告值得当错误看。 凡是对 Element 做真值判断的地方,基本都藏着这个问题。
顺带一个架构上的选择
如果要填的内容来自大模型,比如让 agent 读一份会议记录、提炼出各字段该填什么,很自然会想到让 agent 直接生成整个文件。
别这么做。更好的分工是:
agent → 只输出 {单元格: 值} 的映射
代码 → 负责把映射写进文件
好处是结果不对的时候,你能一眼分清是填错了(模型判断有问题)还是写坏了(文件操作有问题)。这两类问题的排查方向完全不同,混在一起会非常难查。
把不确定的部分和确定的部分分开,是和 LLM 打交道时的通用做法。
小结
- openpyxl 不是原地修改,是重新生成,它不认识的零件会静默丢失
- 要保真,把 xlsx 当 zip,只替换需要改的那个 XML,其余原样搬运
- 写字符串用 inline string,绕开共享池的索引维护
- 测保真度的样本必须内容丰富,空模板会骗过你
ElementTree的空 Element 是 falsy,别用or做存在性判断- 让 agent 输出数据,让代码写文件
最后说句公道话:openpyxl 是个好库,做数据分析、从零建表、画图表都很称手。它只是不适合"客户给的模板必须原样返回"这一类需求。工具没错,错的是用在了它不擅长的地方。
代码整理成了一个小库,零依赖,只用标准库:
👉 github.com/alex-712/xlsx-faithful
上面那组数字可以自己复现:
python tests/fixtures/make_template.py # 生成样本
pytest -k compare_against_openpyxl -s # 跑对比