SolverStudio & Gurobi

SolverStudio allows you to build models inside Excel using Gurobi’s Python modelling language. SolverStudio manages all the access to data on the spreadsheet, making it easy to build and run models within Excel.

To use Gurobi in a recent version of SolverStudio, you only need to have installed Gurobi; SolverStudio uses the Python provided with Gurobi, and so no other copy of Python is required.

As you’d expect, Gurobi models should have SolverStudio’s Language menu set to Gurobi.

The data items you define on the spreadsheet (using SolverStudio’s Data Items editor) appear as Python lists and dictionaries, allowing to read and write this data on the spreadsheet. To access the data items, you need to use:
from SolverStudio import *

We suggest you look at the Gurobi example on the SolverStudio examples spreadsheet. This sample model (used with permission from Gurobi) is listed below.

#!/usr/bin/python

# Copyright 2011, Gurobi Optimization, Inc.
# Modified by A Mason 2012 for SolverStudio demo
# Used with permission of Gurobi
# See http://www.gurobi.com/doc/46/examples/node114.html

# Solve the classic diet model, showing how to add constraints
# to an existing model.

from gurobipy import *
from SolverStudio import *

# Nutrition guidelines, based on
# USDA Dietary Guidelines for Americans, 2005
# http://www.health.gov/DietaryGuidelines/dga2005/

# Model
m = Model(“diet”)

# Create decision variables for the nutrition information,
# which we limit via bounds
nutrition = {}
for c in categories:
nutrition[c] = m.addVar(lb=minNutrition[c], ub=maxNutrition[c], name=c)

# Create decision variables for the foods to buy
buy = {}
for f in foods:
buy[f] = m.addVar(obj=cost[f], name=f)

# The objective is to minimize the costs
m.modelSense = GRB.MINIMIZE

# Update model to integrate new variables
m.update()

# Nutrition constraints
for c in categories:
m.addConstr(
quicksum(nutritionValues[f,c] * buy[f] for f in foods) == nutrition[c], c)

def printSolution():
if m.status == GRB.status.OPTIMAL:
print ‘\nCost:’, m.objVal
print ‘\nBuy:’
for f in foods:
if buy[f].x > 0.0001:
print f, buy[f].x
print ‘\nNutrition:’
for c in categories:
print c, nutrition[c].x
else:
print ‘No solution’

# Solve
m.optimize()
printSolution()

# Copy the solution onto the spreadsheet
for f in foods:
solution[f] = buy[f].x

#print ‘\nAdding constraint: at most 6 servings of dairy’
#m.addConstr(buy[‘milk’] + buy[‘ice cream’] <= 6, “limit_dairy”)
# Solve
#m.optimize()
#printSolution()

Leave a Reply

Your email address will not be published. Required fields are marked *