MealPy结合OpenPyXL操作Excel：保存后公式消失问题求解

运行代码并保存Excel工作簿后，所有公式消失，仅保留数据。尝试移除data_only=True参数，但代码无法运行。需要在不影响Excel原有公式的前提下运行代码，保留原始公式以便MealPy计算后得到最终结果。

原因分析 #

• 用data_only=True加载工作簿时，openpyxl仅读取单元格的计算结果，不保留公式；保存时只会写入数值，导致公式丢失。
• 移除data_only=True后，sheet['U9'].value返回的是公式字符串（如=SUM(...)），而非计算后的数值，MealPy的目标函数需要数值类型的适应度值，因此报错。

解决方案 #

核心是保留公式加载工作簿，同时获取公式的实时计算结果。由于openpyxl本身不具备Excel公式计算能力，推荐使用xlwings调用本地Excel引擎实现需求，它能完美保留公式并获取计算值。

步骤1：安装xlwings #

pip install xlwings

步骤2：修改后的代码 #

import xlwings as xw
from mealpy import FloatVar, SHADE

# 后台打开Excel工作簿，with上下文自动处理保存和关闭
with xw.Book('Book2.xlsx') as wb:
    sheet = wb.sheets['Optimization']

    def objective_function(solution):
        # 给目标单元格赋值
        sheet.range('F4').value = solution[0]
        sheet.range('F5').value = solution[1]
        sheet.range('F6').value = solution[2]
        sheet.range('F7').value = solution[3]
        sheet.range('F8').value = solution[4]
        sheet.range('F9').value = solution[5]
        sheet.range('E16').value = solution[6]
        sheet.range('F16').value = solution[7]
        sheet.range('G16').value = solution[8]
        sheet.range('H16').value = solution[9]
        sheet.range('I16').value = solution[10]
        sheet.range('J16').value = solution[11]
        sheet.range('K16').value = solution[12]
        sheet.range('L16').value = solution[13]
        sheet.range('M16').value = solution[14]
        sheet.range('N16').value = solution[15]
        sheet.range('O16').value = solution[16]
        sheet.range('P16').value = solution[17]
        sheet.range('Q16').value = solution[18]
        sheet.range('R16').value = solution[19]
        sheet.range('S16').value = solution[20]

        # 触发Excel实时计算，确保获取最新结果
        wb.app.calculate()
        # 返回U9的计算数值
        return sheet.range('U9').value

    problem = {
        "obj_func": objective_function,
        "bounds": FloatVar(ub=(1.,)*21, lb=(0.,)*21),
        "minmax": "min",
        "log_to": "console",
    }

    # 运行优化算法
    optimizer = SHADE.OriginalSHADE(epoch=100, pop_size=50)
    g_best = optimizer.solve(problem)
    print(f"Best solution: {g_best.solution}, Best fitness: {g_best.target.fitness}")

# with块结束后，工作簿自动保存并关闭Excel

方案说明 #

• 依赖本地安装的Excel软件（Windows/Mac均支持），Linux环境可尝试formulaic库（仅支持基础公式）。
• Excel默认在后台运行，不会弹出界面，计算完成后自动关闭。
• 保存后的工作簿完整保留所有原始公式，仅修改了指定单元格的数值，其他公式可正常计算。

内容的提问来源于stack exchange，提问作者Chong Wen Cong