如何用OpenPyXL同时存储Excel公式与计算值并离线读取？

我希望在Python中使用OpenPyXL创建包含公式的Excel工作簿，后续解析该工作簿时无需先在Excel中打开以计算公式结果。例如，已知某公式的计算结果，想要通过OpenPyXL同时存储该公式及其运算值。

我想要生成如下工作表：

我尝试编写的代码如下：

import math
from openpyxl import Workbook, load_workbook

wb = Workbook()
ws = wb.active

ws.cell(1, 1).value = 'Value'
ws.cell(1, 2).value = 'Log(10)'

for row, i in enumerate([1, 10], start = 2):
    ws.cell(row, 1).value = i
    formulaCell = ws.cell(row, 2)
    # What do I have to assign here in order to be able to read both the formula and value later?
    formulaCell.value = math.log10(i)
    formulaCell.formula = f'=LOG10(A{row})'

wbName = 'test.xlsx'
wb.save(wbName)

# Read the value and formula back in
wb_data     = load_workbook(wbName, data_only = True)
wb_formulas = load_workbook(wbName, data_only = False)

# The following is expected to print:
# 0
# =LOG10(A2)
print(wb_data['B2'].value)
print(wb_formulas['B2'].value)

请问如何修改代码，才能后续同时读取单元格的计算值和公式？

解决方案 #

问题出在代码的赋值顺序：先设置value再设置formula会导致公式覆盖手动设置的计算值，最终Excel文件仅保存公式，没有预存的计算结果。

正确的做法是先设置公式，再手动将预计算结果设为单元格的缓存值，这样OpenPyXL会同时保存公式和预计算值。修改后的代码如下：

import math
from openpyxl import Workbook, load_workbook

wb = Workbook()
ws = wb.active

ws.cell(1, 1).value = 'Value'
ws.cell(1, 2).value = 'Log(10)'

for row, i in enumerate([1, 10], start=2):
    ws.cell(row, 1).value = i
    formulaCell = ws.cell(row, 2)
    # 先设置公式
    formulaCell.formula = f'=LOG10(A{row})'
    # 再设置预计算结果作为缓存值
    formulaCell.value = math.log10(i)

wbName = 'test.xlsx'
wb.save(wbName)

# 读取验证
wb_data = load_workbook(wbName, data_only=True)
wb_formulas = load_workbook(wbName, data_only=False)

# 预期输出：
# 0.0
# =LOG10(A2)
print(wb_data['B2'].value)
print(wb_formulas['B2'].value)

原理说明 #

• 先设置formula，OpenPyXL会将单元格标记为公式类型。
• 随后设置value，该值会被作为公式的预计算缓存值写入Excel文件。
• 后续读取时：
  • 用data_only=True加载，会直接读取预存的缓存值，无需Excel打开计算；
  • 用data_only=False加载，会读取单元格的公式内容。

内容的提问来源于stack exchange，提问作者Jeff G