py-excel-solver    

A wrapper that uses Scipy's linprog() function to emulate the UI of Excel's Solver. It's supposed to be extremely easy to use. If you've set up a simple optimization problem in Excel, just copy and paste the values into the function below and get the same output.

Source code

Install

pip install excel_solver

Example 1

Solved in Excel:

Solved in Python:

Code:

import solver
solver.solve(
    problem_type = "min",
    objective_function = [
        4, 5, 3, 7, 6
    ],
    constraints_left = [
        [10,  20,  10,  30,  20],
        [5,   7,   4,   9,   2],
        [1,   4,   10,  2,   1],
        [500, 450, 160, 300, 500],
    ],
    constraints_right = [
        16,
        10,
        15,
        600,
    ],
    constraints_signs = [
        ">=",
        ">=",
        ">=",
        ">=",
    ],
    minimum_for_all=0.1, # replaces lines 15-19 in the excel image above
)

Output:

------------------------------------------------------
MINIMIZE: z = 4a + 5b + 3c + 7d + 6e
------------------------------------------------------
OPTIMAL VALUE:  8.04
------------------------------------------------------
QUANTITIES:
a:  0.44415
b:  0.18091
c:  1.35322
d:  0.1
e:  0.1
------------------------------------------------------
Optimization terminated successfully. (HiGHS Status 7: Optimal)

Example 2

Solved in Excel:

Solved in Python:

Code

import solver
solver.solve(
    problem_type = "max",
    objective_function = [
        16, 20.5, 14
    ],
    constraints_left = [
        [4,  6,  2],
        [3,  8,  6],
        [9,  6,  4],
        [30, 40, 25],
    ],
    constraints_right = [
        2000,
        2000,
        1440,
        9600,
    ],
    constraints_signs = [
        "<=",
        "<=",
        "<=",
        "<=",
    ],
)

Output

------------------------------------------------------
MAXIMIZE: z = 16a + 20.5b + 14c
------------------------------------------------------
OPTIMAL VALUE:  4960
------------------------------------------------------
QUANTITIES:
a:  0
b:  160
c:  120
------------------------------------------------------
Optimization terminated successfully. (HiGHS Status 7: Optimal)

Now, let's try switching the 2nd constraint in the previous problem from '<= 2000' to '= 1984'. If you were using Scipy, this wouldn't be possible without making two new separate arrays to store this constraint.

NOTE: This is NOT necessary, but I've re-ordered the constraints so the equality is on the bottom. You can have them in any order you like.

Code

import solver
solver.solve(
    problem_type = "max",
    objective_function = [
        16, 20.5, 14
    ],
    constraints_left = [
        [4,  6,  2],
        [9,  6,  4],
        [30, 40, 25],
        [3,  8,  6],
    ],
    constraints_right = [
        2000,
        1440,
        9600,
        1984,
    ],
    constraints_signs = [
        "<=",
        "<=",
        "<=",
        "=",
    ],
)

Output

------------------------------------------------------
MAXIMIZE: z = 16a + 20.5b + 14c
------------------------------------------------------
OPTIMAL VALUE:  4952
------------------------------------------------------
QUANTITIES:
a:  0
b:  176
c:  96
------------------------------------------------------
Optimization terminated successfully. (HiGHS Status 7: Optimal)