Streamlit更新Excel数据后自动保存及公式加载异常问题

问题背景 #

• 基于Streamlit开发财务评估工具，用户输入数据会更新Excel文件对应单元格
• 已成功更新单元格，但重新加载工作簿时，公式计算的单元格为空，仅手动保存Excel后才会显示计算结果
• 部署环境为Ubuntu（Streamlit Cloud），xlwings因系统限制无法使用
• 技术栈：Python 3.12.3、openpyxl 3.1.5、Streamlit 1.39.0、Pandas 2.2.3

相关代码片段（main.py） #

from pathlib import Path
from openpyxl import load_workbook
import streamlit as st
import pandas as pd

def load_financial_model(file_path, sheet_name, header=None):
    return pd.read_excel(file_path, sheet_name=sheet_name, header=header)

file_path = "your_excel_file.xlsx"  # 替换为实际文件路径
file_extension = Path(file_path).suffix.lower()[1:]
if file_extension in ['xlsx', 'xls']:
    workbook = load_workbook(file_path)
    sheet = workbook['Inp_C']

    if st.button("Save Changes"):
        sheet.cell(row=11, column=11, value=st.session_state.fcp2)
        sheet.cell(row=14, column=11, value=st.session_state.field35)
        sheet.cell(row=15, column=11, value=st.session_state.field36)  
        sheet.cell(row=20, column=11, value=st.session_state.field38) 
        sheet.cell(row=25, column=11, value=st.session_state.drtp2) 
        sheet.cell(row=30, column=11, value=st.session_state.cepsp2) 
        sheet.cell(row=31, column=11, value=st.session_state.ces2  / 100) 
        sheet.cell(row=37, column=11, value=st.session_state.field40 / 100) 
        sheet.cell(row=41, column=11, value=st.session_state.upfp2 / 100) 
        sheet.cell(row=44, column=11, value=st.session_state.cfp2 / 100) 
        sheet.cell(row=47, column=11, value=st.session_state.field42 / 100) 
        sheet.cell(row=48, column=11, value=st.session_state.field43 / 100) 
        sheet.cell(row=62, column=11, value=st.session_state.cgp2) 
        sheet.cell(row=63, column=11, value=st.session_state.cfltp2)   
        sheet.cell(row=64, column=11, value=st.session_state.ogsgp2) 
        sheet.cell(row=65, column=11, value=st.session_state.ofo2) 
        sheet.cell(row=68, column=11, value=st.session_state.field53) 
        sheet.cell(row=69, column=11, value=st.session_state.field62) 
        sheet.cell(row=70, column=11, value=st.session_state.field58) 
        sheet.cell(row=71, column=11, value=st.session_state.field59) 
        sheet.cell(row=78, column=11, value=st.session_state.field45 / 100)
        sheet.cell(row=79, column=11, value=st.session_state.field46 / 100)
        sheet.cell(row=84, column=11, value=st.session_state.ofwaccp2  / 100)
        sheet.cell(row=86, column=11, value=st.session_state.drcitrp2 / 100)

        workbook.save(file_path)
        workbook.close()
    else:
        st.write("No changes have been made.")

    # 此处加载更新后的模型时，公式单元格为空
    df = load_financial_model(file_path,sheet_name='Output',header=None)
    st.write(df)

解决方案 #

方法1：使用LibreOffice Headless触发公式计算 #

Linux环境下可利用LibreOffice的无头模式模拟Excel的公式计算，步骤如下：

1. 安装LibreOffice无头组件：

sudo apt-get update && sudo apt-get install libreoffice-headless libreoffice-calc

2. 在代码中添加函数调用LibreOffice处理Excel文件，触发公式计算并保存：

import subprocess

def trigger_excel_calculation(file_path):
    # 调用LibreOffice打开并保存文件，触发公式计算
    result = subprocess.run(
        [
            "libreoffice",
            "--headless",
            "--invisible",
            "--norestore",
            "--calc",
            f"--save-to={file_path}",
            file_path
        ],
        capture_output=True,
        text=True
    )
    # 可选：打印调试信息
    if result.returncode != 0:
        st.error(f"公式计算触发失败: {result.stderr}")

3. 修改原代码逻辑，在保存Excel后调用该函数，再重新加载数据：

if st.button("Save Changes"):
    # 省略更新单元格代码...
    
    workbook.save(file_path)
    workbook.close()

    # 触发公式计算
    trigger_excel_calculation(file_path)

    # 重新加载计算后的结果
    df = load_financial_model(file_path, sheet_name='Output', header=None)
    st.write(df)
else:
    st.write("No changes have been made.")
    df = load_financial_model(file_path, sheet_name='Output', header=None)
    st.write(df)

方法2：将Excel公式迁移至Python/Pandas实现 #

如果Excel中的公式逻辑可复现，直接用Python或Pandas实现计算逻辑，完全脱离Excel依赖：

• 读取用户输入数据，用代码实现原Excel的公式运算
• 直接生成输出结果，无需依赖Excel文件的公式计算

方法3：openpyxl配合外部计算工具补充 #

openpyxl本身不执行公式计算，仅能读取已计算的值。若必须保留Excel文件，可结合方法1的LibreOffice处理，确保保存后公式已被计算，再以data_only=True加载工作簿获取计算值：

# 处理后加载工作簿
workbook = load_workbook(file_path, data_only=True)
output_sheet = workbook['Output']
# 读取计算后的值

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