Single objective multiple integer variable optimisation of an Excel model - Python Pymoo library

64 views Asked by At

I am trying to optimize 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 optimize 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 to 100. Does Pymoo want an array to save all 100 evaluations of the first population of 100?

I fully realize there could be other issues with my code, which might be incorrectly setting the array size, so the complete 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?

1

There are 1 answers

1
Pieter-Jan On

Is your number of variables n_vars consistent with the length of upper and lower bounds? In which underlying line of code is the error produced exactly?