Single objective multiple integer variable optimisation of an Excel model

[jt269]

Hello everybody,

I am trying to optimise a large excel model using Pymoo, I have set up the problem to communicate with Excel via pyWin32.

During debugging in VSCode I get the following exception:

Exception
('Problem Error: F can not be set, expected shape (100, 1) but provided (1, 1)', ValueError('cannot reshape array of size 1 into shape (100,1)'))
ValueError: cannot reshape array of size 1 into shape (100,1)

During handling of the above exception, another exception occurred:

  File "C:\Database\Python\RSG\RSG Opt.py", line 71, in <module>
    res = minimize(
          ^^^^^^^^^
Exception: ('Problem Error: F can not be set, expected shape (100, 1) but provided (1, 1)', ValueError('cannot reshape array of size 1 into shape (100,1)'))

I am trying to optimise a single objective so I do not know why it is expecting an array with 100 values in it. At no point do I set the variable of 100. Does Pymoo want an array into which it can save all 100 evaluations of the first population of 100?

Fully realise there could be other issues with my code which might be incorrectly setting the array size so full code is here:

import win32com.client as win32
import numpy as np
from pymoo.core.problem import Problem
from pymoo.algorithms.soo.nonconvex.ga import GA
from pymoo.optimize import minimize

# Connect to Excel
excel = win32.gencache.EnsureDispatch('Excel.Application')
excel.Visible = True  # Keep Excel hidden

# Reference the active workbook (assuming it's already open)
workbook = excel.ActiveWorkbook

# Prompt for the sheet name and range
sheet_name = input("Enter the sheet name: ")
range_address = input("Enter the variable range address (e.g., A1:B10): ")
target = input("Enter the objective cell address (e.g., A1:B10): ")

# Reference the specified sheet and range
try:
    worksheet = workbook.Sheets(sheet_name)
    variable_range = worksheet.Range(range_address)
    objective_cell = worksheet.Range(target)

except Exception as e:
    print(f"Error: {e}")
    excel.Quit()
    quit()

# Read the number of variables based on the number of rows in the range
num_vars = variable_range.Rows.Count
Vars=np.array(variable_range.Value)
trans_vars = np.transpose(Vars)

# Read the lower bounds from Excel (assuming they are in column -2)
lower_bounds_range = variable_range.GetOffset(0,-2)
lower_bounds = [int(cell.Value) for cell in lower_bounds_range]

# Read the upper bounds from Excel (assuming they are in column -1)
upper_bounds_range = variable_range.GetOffset(0, -1)
upper_bounds = [int(cell.Value) for cell in upper_bounds_range]


# Define the Optimization Problem
class ExcelOptimizationProblem(Problem):
    def __init__(self, num_vars, lower_bounds, upper_bounds):
        super().__init__(n_var=num_vars, n_obj=1, n_constr=1, xl=lower_bounds, xu=upper_bounds, type_var=int)

    def _evaluate(self, x, out, *args, **kwargs):
        # Set the decision variable values in Excel
        for i, val in enumerate(x):
            variable_range.Cells(1,1).GetOffset(i, 0).Value = val

        excel.Calculate()

        objective_value = float(objective_cell.Value)

        constraint_value = 0

        out["F"] = [-objective_value]
        out["G"] = [-constraint_value]

problem = ExcelOptimizationProblem(
    num_vars, lower_bounds, upper_bounds
    )

algorithm = GA(
    pop_size=100,
    eliminate_duplicates=True)

res = minimize(
    problem, algorithm, ('n_gen', 10), verbose=True
    )

print("Best solution found: \nX = %s\nF = %s" % (res.X, res.F))

workbook.Close()
excel.Quit()

Can anyone give me some guidance?

Thanks in advance.