我的知识记录

Python Excel写入公式并读取计算结果方法详解

用openpyxl向Excel写入公式后，openpyxl本身不计算结果，本篇讲清怎么写入SUM/AVERAGE等公式、为什么data_only读出来是空、以及用LibreOffice无头重算后读取真实结果的完整流程。

场景痛点

很多人用Python生成Excel报表时，想让单元格带上=SUM(A2:A10)这种公式，让打开文件的人能看到联动计算。但写进去之后，再用openpyxl读回来，却发现公式单元格读到的是None或者公式字符串本身，拿不到算出来的数字。问题不在代码写错，而在于openpyxl只负责读写文本，不负责执行公式计算。本篇把写入公式、读取计算值这两件事一次讲清楚。

用到的库

pip install openpyxl pandas

完整代码

# -*- coding: utf-8 -*-
"""
Python 写入 Excel 公式，并读取计算结果
方案：openpyxl 写公式 -> LibreOffice 无头重算 -> data_only=True 读结果
"""
import subprocess
from pathlib import Path

from openpyxl import Workbook, load_workbook


def write_formulas(path: str):
"""向 Excel 写入成绩数据和汇总公式"""
wb = Workbook()
ws = wb.active
ws.title = "成绩"

# 写表头
ws.append(["姓名", "语文", "数学", "总分", "平均分"])

# 写 5 行成绩
rows = [
["张三", 88, 92],
["李四", 76, 85],
["王五", 95, 100],
["赵六", 60, 72],
["钱七", 82, 79],
]
for name, yw, sx in rows:
ws.append([name, yw, sx, None, None])

# 给第 2~6 行写公式：总分=B+C，平均分=总分/2
for r in range(2, 7):
ws.cell(row=r, column=4).value = f"=B{r}+C{r}"          # 总分
ws.cell(row=r, column=5).value = f"=ROUND(D{r}/2, 1)"   # 平均分

# 底部再写一行合计
ws.cell(row=7, column=1).value = "班级总分"
ws.cell(row=7, column=4).value = "=SUM(D2:D6)"

wb.save(path)
print("公式已写入：", path)


def recalc_with_libreoffice(path: str):
"""
用 LibreOffice 无头模式打开并另存，触发公式重算。
注意：此步需要本机已安装 libreoffice（soffice 命令可用）。
"""
out_dir = str(Path(path).parent)
cmd = [
"soffice", "--headless",
"--convert-to", "xlsx",
"--outdir", out_dir,
path,
]
subprocess.run(cmd, check=True, capture_output=True)
print("已通过 LibreOffice 重算：", path)


def read_calc_result(path: str):
"""用 data_only=True 读取公式计算后的缓存值"""
wb = load_workbook(path, data_only=True)
ws = wb["成绩"]

print("\n读取计算结果：")
for r in range(2, 8):
name = ws.cell(row=r, column=1).value
total = ws.cell(row=r, column=4).value
avg = ws.cell(row=r, column=5).value
print(f"{name}\t总分={total}\t平均分={avg}")


def main():
file_path = "students.xlsx"
write_formulas(file_path)

# 直接用 data_only 读，此时公式从未被 Excel/LibreOffice 计算过，缓存为空
wb = load_workbook(file_path, data_only=True)
print("未重算时 D2 =", wb["成绩"]["D2"].value)  # 大概率是 None

# 触发重算后再读
recalc_with_libreoffice(file_path)
read_calc_result(file_path)


if __name__ == "__main__":
main()

代码讲解

  • write_formulas：往单元格写字符串形式的公式，必须以等号=开头，openpyxl 会自动识别为公式。f"=B{r}+C{r}" 用行号拼接，批量生成每行的公式。
  • 关键认知：openpyxl 是个"文件读写库"，不是"Excel 计算引擎"。它写入公式后并不会马上算出结果，单元格里存的只是公式文本。
  • 直接 load_workbook(..., data_only=True) 时，openpyxl 会去读文件里缓存的"上次计算值"。这个缓存只有 Excel、WPS 或 LibreOffice 真正打开过并保存后才会产生。从没被打开过的新文件，缓存就是空，所以读到 None。
  • recalc_with_libreoffice：调用本机 soffice 无头转换，让 LibreOffice 打开文件、执行全部公式、再存回 xlsx。这一步把计算结果写进缓存。
  • read_calc_result：重算之后再用 data_only=True 打开，D2、E2 这些公式单元格就能读到真实数字。
  • 如果不重算就想拿结果，也可以在写公式前用 Python 自己算好数值直接填进去，公式只给用户在 Excel 里看效果用。

运行结果

运行后当前目录生成 students.xlsx。控制台先打印"未重算时 D2 = None"，接着打印每个人的总分和平均分，例如"王五 总分=195 平均分=97.5"。用 Excel/WPS 打开文件，能看到 D、E 列是活公式，改 B 列语文分，结果会跟着变。

注意事项

  • data_only=True 只对"曾经被 Excel/LibreOffice 保存过"的文件有效；纯 openpyxl 新建的文件第一次读一定是 None，这不是 bug。
  • 服务器/CI 环境没装 LibreOffice 时，重算这步会失败。替代方案：用 formulas 或 pycel 这类纯 Python 公式引擎在内存里算，或者干脆在 Python 里算好数值直接写入。
  • Windows 上若装了 Microsoft Excel，也可以用 win32com 调用 Excel 打开保存来重算，但这强依赖 Windows + Office，跨平台不推荐。
  • 写公式时注意区域设置：函数名用英文（SUM、ROUND），参数分隔符用逗号，不要用中文全角符号。
  • 公式里引用中文 sheet 名需要加单引号，例如 ='成绩'!A1；本例 sheet 名是英文"成绩"也建议稳妥加引号。

Python Excel写入公式并读取计算结果方法详解

标签：

更新时间：2026-09-15 09:02:50

上一篇：Python合并多个Excel表格到一张表

下一篇：Python Excel多表关联 Vlookup用pandas merge实现

转载请注明原文链接：https://www.muzicopy.com/suibi/49049.html

关注热点