openpyxl写入Excel公式时上一行数值被篡改如何解决

问题根因 #

你编写的公式逻辑存在行号引用错误：公式里使用了{row + 1}引用当前写入行的下一行，首次运行时该下一行无数据，公式会按空值计算得到临时结果。第二次运行时你会给原本的下一行写入实际数据，上一行的公式因为引用了该新值触发自动重算，就会出现数值「被篡改」的现象，本质是公式逻辑不符合你的预期。

解决方案 #

场景1：你需要计算的是当前行与上一行的差值（绝大多数行差统计的需求）

直接修改公式的行号引用逻辑即可，同时可以加判断避免第一行数据引用无效行报错，修改对应代码段为：

# 第一行数据没有上一行，差值默认填0或者根据需求调整占位值
if row == 2:
    sheet.cell(row=row, column=6, value=0)
    sheet.cell(row=row, column=9, value=0)
    sheet.cell(row=row, column=11, value=0)
else:
    sheet.cell(row=row, column=6, value=f'=SUM(D{row}-D{row-1})')
    sheet.cell(row=row, column=9, value=f'=SUM(G{row}-G{row-1})')
    sheet.cell(row=row, column=11, value=f'=SUM(J{row}-J{row-1})')
sheet.cell(row=row, column=12, value=f'=SUM(I{row}/K{row})')

修改后公式只引用已经写入完成的上一行数据，后续新增行不会影响已有行的计算结果。

场景2：你确实需要引用下一行的值做计算

直接在Python中完成计算后写入静态数值即可，不要写入动态公式，避免后续单元格内容变更触发重算。逻辑调整为：

1. 每次写入新行前，先获取上一行的行号prev_row = sheet.max_row
2. 用即将写入新行的对应列数值，减去上一行的同列数值，计算得到差值，直接写入上一行对应的公式列
3. 新行的对应公式列可以先填空值，等下次写入下一行时再计算填充

示例代码片段：

row = sheet.max_row + 1
# 先处理上一行的差值计算
if row > 2:
    prev_row = row -1
    # 用上一行的值和当前即将写入的新值计算差值
    f_val = RTValidResponse - sheet.cell(prev_row, 4).value
    i_val = RTCLOSE - sheet.cell(prev_row,7).value
    k_val = RTValidResponse - sheet.cell(prev_row,10).value
    # 写入静态值到上一行
    sheet.cell(row=prev_row, column=6, value=f_val)
    sheet.cell(row=prev_row, column=9, value=i_val)
    sheet.cell(row=prev_row, column=11, value=k_val)
    sheet.cell(row=prev_row, column=12, value=i_val/k_val if k_val !=0 else 0)

# 再写入当前行的基础数据
sheet.cell(row=row, column=1, value=Date)
sheet.cell(row=row, column=2, value=Time)
sheet.cell(row=row, column=3, value=PCTCLOSE)
sheet.cell(row=row, column=4, value=RTValidResponse)
sheet.cell(row=row, column=7, value=RTCLOSE)
sheet.cell(row=row, column=10, value=RTValidResponse)

额外注意 #

openpyxl本身不执行Excel公式计算，仅负责保存公式，所有公式的计算结果都是打开Excel文件时由Office程序完成的。如果需要彻底避免公式自动重算带来的数值变动，所有计算逻辑都建议在Python中完成后直接写入静态数值。

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