Is it possible to store the JuMP output to an *.xlsx?

Specific Domains Optimization (Mathematical)
, ,
1

Hi,

I have a model that uses CPLEX to find optimal solution. When I call JuMP.value.(x) ir cannot show all elements in the terminal. So, I’ve tried write_to_file(model, "model.xlsx") to store it in excel file to see which comination of x[i,j] takes value. However, I had the error ERROR: Unable to automatically detect format of model.xlsx.

How to have all values stored in an excel fle automatically?

2

Probably not, but XSLX.jl knows about the Tables.jl interface. So if you can coerce your output to a Tables-compatible object, like a Vector of NamedTuples or a DataFrame, saving should be trivial.

2 Likes
3

Is it possible to force the output 1-dimensional DenseAxisArray{Float64,1,...} of solver JuMP/CPLEX in table format??

Or in the documentation, it mentions write_to_file(model, "model.mps"), but what it shoes it’s not values that are taken values.

4

I’m not familiar with the XLSX format and associated packages, but something like this snippet should work to export your solution to a CSV file. It should not be too difficult to adapt it to use XLSX.jl instead.

using JuMP, HiGHS

coeffs = rand(5,3)
model = Model(HiGHS.Optimizer)
@variable(model, x[1:5,1:3] >= 0)
@objective(model, Min, sum(coeffs .* x))
@constraint(model, sum(x) == 1)
optimize!(model)

using CSV, Tables
CSV.write("output.csv", Tables.table(value.(x)))
4 Likes
5

You can use Containers.rowtable for this. See Containers · JuMP.

julia> using JuMP

julia> import CSV

julia> import HiGHS

julia> model = Model(HiGHS.Optimizer);

julia> set_silent(model)

julia> @variable(model, x[i=1:5, j=2:3] >= i + j);

julia> @objective(model, Min, sum(x));

julia> optimize!(model)

julia> CSV.write(
           "output.csv", 
           Containers.rowtable(value, x; header = [:i, :j, :value]),
       )
"output.csv"

shell> cat output.csv
i,j,value
1,2,3.0
2,2,4.0
3,2,5.0
4,2,6.0
5,2,7.0
1,3,4.0
2,3,5.0
3,3,6.0
4,3,7.0
5,3,8.0

julia> import XLSX

julia> import DataFrames

julia> table = Containers.rowtable(value, x; header = [:i, :j, :value])
10-element Vector{NamedTuple{(:i, :j, :value), Tuple{Int64, Int64, Float64}}}:
 (i = 1, j = 2, value = 3.0)
 (i = 2, j = 2, value = 4.0)
 (i = 3, j = 2, value = 5.0)
 (i = 4, j = 2, value = 6.0)
 (i = 5, j = 2, value = 7.0)
 (i = 1, j = 3, value = 4.0)
 (i = 2, j = 3, value = 5.0)
 (i = 3, j = 3, value = 6.0)
 (i = 4, j = 3, value = 7.0)
 (i = 5, j = 3, value = 8.0)

julia> XLSX.writetable("output.xlsx", "output" => DataFrames.DataFrame(table))

image
image1496×1016 162 KB

8 Likes
6

Thank you all! And specially @odow :cowboy_hat_face:

2 Likes
7

I’ve tried a similar thing for another variable z(i,j,t) that has more indiceis but got error ERROR: Invalid number of column names provided: Got 4, expected 2.? Why?

CSV.write(
           "solution.csv", 
           Containers.rowtable(value, z; header = [:i, :j, :t, :value]),
       )
"solution.csv"
8

What is the definition of z? It looks like it has just a single dimension, not 3.

You probably need Containers.rowtable(value, z; header = [:index, :value]).

1 Like
9

Yes, this is it!
z is defined as zidx = [(i,j,t) for (i,j) in edges for t in times] @variable(model, z[zidx] >= 0)

1 Like
10

Just to clarify for future readers, in:

zidx = [(i,j,t) for (i,j) in edges for t in times]
@variable(model, z[zidx] >= 0)

The variable z is a vector with a single indexed dimension, and each element in the index is a tuple (i, j, t). That’s different to

I = unique(first.(edges))
J = unique(last.(edges))
@variable(model, z[i = I, j = J, t = times; (i, j) in edges] >= 0)

where z is a SparseAxisArray with three indexed dimensions.

In the latter case, you could use Containers.rowtable(value, z; header = [:i, :j, :t, :value]).

1 Like
11

Hi,
I’ve tried the proposed solution, but got “rowtable not defined”. JuMP is up to date. What did I miss ?

12

Did you try:

?

Do you have an MWE?

13 
import JuMP
import XLSX, DataFrames
import Gurobi
model = Model(Gurobi.Optimizer);
set_silent(model)
@variable(model, x[i=1:5, j=2:3] >= i + j);
@objective(model, Min, sum(x));
optimize!(model)

table = Containers.rowtable(value, x; header = [:i, :j, :value])
XLSX.writetable("output.xlsx", "output" => DataFrames.DataFrame(table))

The optimization problem is solved, but I get the message
ERROR: UndefVarError: rowtable not defined

14

I changed your import to using, otherwise nothing works:

using JuMP
using XLSX, DataFrames
using HiGHS
model = Model(HiGHS.Optimizer);
set_silent(model)
@variable(model, x[i=1:5, j=2:3] >= i + j);
@objective(model, Min, sum(x));
optimize!(model)

table = Containers.rowtable(value, x; header = [:i, :j, :value])
XLSX.writetable("output.xlsx", "output" => DataFrames.DataFrame(table))

This works smoothly on my side.

Here is my env:

julia> using Pkg; Pkg.status()
  [a93c6f00] DataFrames v1.5.0
  [87dc4568] HiGHS v1.5.0
  [4076af6c] JuMP v1.9.0
  [fdbf4ff8] XLSX v0.9.0
15

I upgraded my JuMP version before my first post, but I still have the version v1.1.1 (which is the source of my issue, I guess) and cannot update due to other depencies. I will export my results in a .xlsx file somehow else…

16

If not all dependencies are required at the same time, you can use another environment with ] activate MyEnv

1 Like