使用openpyxl更新保存Excel后，读取公式列返回None值问题

问题描述 #

修改Excel单元格A1的值为8，单元格B1包含公式=A1*5，调用openpyxl的save方法后，使用data_only=True读取B1的值返回None，但预期结果是40。相关代码如下：

import openpyxl

# data_only=False   to update excel file
def write_cell(data_only):
    wb_obj = openpyxl.load_workbook("mydata.xlsx", data_only=data_only)
    sheet_obj = wb_obj["Sheet1"]
    sheet_obj = wb_obj.active
    sheet_obj.cell(row = 1, column = 1).value = 8
    wb_obj.save(filename="mydata.xlsx")

# data_only=True   to read excel file
def read_cell(data_only):
    wb_obj = openpyxl.load_workbook("mydata.xlsx", data_only=data_only)
    sheet = wb_obj["Sheet1"]
    # Formula at column 2 : =A1*5
    val = sheet.cell(row = 1, column = 2).value
    return val

write_cell(False)
print(read_cell(True))

原因分析 #

• openpyxl不具备公式计算能力，它只能读取或写入公式本身，无法自动计算并存储公式结果。
• data_only=True参数的作用是读取Excel文件中已经存储的公式计算结果（这个结果是由Excel应用程序计算后写入文件的），而不是实时计算公式。当你修改A1并保存后，文件中仅更新了A1的值，B1的公式未变，但公式的计算结果并没有被写入文件，因此读取时返回None。

解决方案 #

方案1：借助Excel应用程序触发计算 #

保存文件后，手动用Excel打开该文件并重新保存，Excel会自动计算所有公式并将结果写入文件，之后再用data_only=True读取即可得到正确的40。

方案2：代码中手动实现公式计算 #

如果公式逻辑简单，可以直接在代码中读取依赖单元格的值，手动计算结果：

import openpyxl

def write_cell(data_only):
    wb_obj = openpyxl.load_workbook("mydata.xlsx", data_only=data_only)
    sheet_obj = wb_obj.active
    sheet_obj.cell(row=1, column=1).value = 8
    wb_obj.save(filename="mydata.xlsx")

def read_cell_manual_calc():
    wb_obj = openpyxl.load_workbook("mydata.xlsx", data_only=False)
    sheet = wb_obj["Sheet1"]
    a1_val = sheet.cell(row=1, column=1).value
    # 对应B1的公式=A1*5
    b1_val = a1_val * 5
    return b1_val

write_cell(False)
print(read_cell_manual_calc())  # 输出40

方案3：使用支持公式计算的第三方库 #

使用pycel（纯Python的Excel公式计算库）来编译并计算Excel中的公式，无需依赖Excel应用程序：

1. 先安装pycel：

pip install pycel

2. 修改代码：

import openpyxl
from pycel import ExcelCompiler

def write_cell(data_only):
    wb_obj = openpyxl.load_workbook("mydata.xlsx", data_only=data_only)
    sheet_obj = wb_obj.active
    sheet_obj.cell(row=1, column=1).value = 8
    wb_obj.save(filename="mydata.xlsx")

def read_cell_with_calc():
    # 编译Excel文件并计算指定单元格的值
    compiler = ExcelCompiler(filename="mydata.xlsx")
    val = compiler.evaluate('Sheet1!B1')
    return val

write_cell(False)
print(read_cell_with_calc())  # 输出40

额外优化：清理冗余代码 #

原代码中write_cell函数里先赋值sheet_obj = wb_obj["Sheet1"]，随后又赋值sheet_obj = wb_obj.active，属于冗余操作。如果Sheet1是当前活动表，直接使用wb_obj.active即可；如果需要明确指定Sheet1，直接用wb_obj["Sheet1"]即可，无需重复赋值。

内容的提问来源于stack exchange，提问作者Arun Yadav