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

**URL:** <https://discourse.julialang.org/t/is-it-possible-to-store-the-jump-output-to-an-xlsx/95887>\
**Category:** Optimization (Mathematical)\
**Tags:** jump, csv, xlsx\
**Created:** [March 10, 2023, 8:51pm UTC](https://discourse.julialang.org/t/is-it-possible-to-store-the-jump-output-to-an-xlsx/95887 "2023-03-10T20:51:14Z")\
**Posts on this page:** 16\
**Page:** 1

<div class="post-metadata">

**Author:** ![Optimization](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/optimization/32/32462_2.png) [@Optimization](https://discourse.julialang.org/u/Optimization)\
**Post date:** [March 10, 2023, 8:51pm UTC](https://discourse.julialang.org/t/is-it-possible-to-store-the-jump-output-to-an-xlsx/95887/1 "2023-03-10T20:51:14Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![pdeffebach](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/pdeffebach/32/10320_2.png) [@pdeffebach](https://discourse.julialang.org/u/pdeffebach)\
**Post date:** [March 10, 2023, 9:04pm UTC](https://discourse.julialang.org/t/is-it-possible-to-store-the-jump-output-to-an-xlsx/95887/2 "2023-03-10T21:04:38Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Optimization](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/optimization/32/32462_2.png) [@Optimization](https://discourse.julialang.org/u/Optimization)\
**Post date:** [March 10, 2023, 9:11pm UTC](https://discourse.julialang.org/t/is-it-possible-to-store-the-jump-output-to-an-xlsx/95887/3 "2023-03-10T21:11:10Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![ffevotte](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/ffevotte/32/6587_2.png) [@ffevotte](https://discourse.julialang.org/u/ffevotte)\
**Post date:** [March 10, 2023, 10:08pm UTC](https://discourse.julialang.org/t/is-it-possible-to-store-the-jump-output-to-an-xlsx/95887/4 "2023-03-10T22:08:26Z")

</div>

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](https://felipenoris.github.io/XLSX.jl/stable/) instead.

```julia
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)))

```

---

<div class="post-metadata">

**Author:** ![odow](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/odow/32/28685_2.png) [@odow](https://discourse.julialang.org/u/odow)\
**Post date:** [March 10, 2023, 11:30pm UTC](https://discourse.julialang.org/t/is-it-possible-to-store-the-jump-output-to-an-xlsx/95887/5 "2023-03-10T23:30:52Z")

</div>

You can use `Containers.rowtable` for this. See [Containers · JuMP](https://jump.dev/JuMP.jl/stable/manual/containers/#Tables-2).

```julia
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

```

```plaintext
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](https://global.discourse-cdn.com/julialang/original/3X/6/2/62e9abeddf0000ebe3be3832c37c37df2de3b59e.jpeg)

---

<div class="post-metadata">

**Author:** ![Optimization](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/optimization/32/32462_2.png) [@Optimization](https://discourse.julialang.org/u/Optimization)\
**Post date:** [March 12, 2023, 9:38pm UTC](https://discourse.julialang.org/t/is-it-possible-to-store-the-jump-output-to-an-xlsx/95887/6 "2023-03-12T21:38:43Z")

</div>

Thank you all! And specially @odow 🤠

---

<div class="post-metadata">

**Author:** ![Optimization](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/optimization/32/32462_2.png) [@Optimization](https://discourse.julialang.org/u/Optimization)\
**Post date:** [March 12, 2023, 9:45pm UTC](https://discourse.julialang.org/t/is-it-possible-to-store-the-jump-output-to-an-xlsx/95887/7 "2023-03-12T21:45:43Z")

</div>

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?

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

```

---

<div class="post-metadata">

**Author:** ![odow](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/odow/32/28685_2.png) [@odow](https://discourse.julialang.org/u/odow)\
**Post date:** [March 12, 2023, 9:53pm UTC](https://discourse.julialang.org/t/is-it-possible-to-store-the-jump-output-to-an-xlsx/95887/8 "2023-03-12T21:53:19Z")

</div>

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])`.

---

<div class="post-metadata">

**Author:** ![Optimization](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/optimization/32/32462_2.png) [@Optimization](https://discourse.julialang.org/u/Optimization)\
**Post date:** [March 13, 2023, 9:29am UTC](https://discourse.julialang.org/t/is-it-possible-to-store-the-jump-output-to-an-xlsx/95887/9 "2023-03-13T09:29:22Z")

</div>

> [@odow](#):
>
> `Containers.rowtable(value, z; header = [:index, :value])`.

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)`

---

<div class="post-metadata">

**Author:** ![odow](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/odow/32/28685_2.png) [@odow](https://discourse.julialang.org/u/odow)\
**Post date:** [March 13, 2023, 7:29pm UTC](https://discourse.julialang.org/t/is-it-possible-to-store-the-jump-output-to-an-xlsx/95887/10 "2023-03-13T19:29:18Z")

</div>

Just to clarify for future readers, in:

```julia
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

```plaintext
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])`.

---

<div class="post-metadata">

**Author:** ![geo1093](https://avatars.discourse-cdn.com/v4/letter/g/5f9b8f/32.png) [@geo1093](https://discourse.julialang.org/u/geo1093)\
**Post date:** [March 20, 2023, 1:51pm UTC](https://discourse.julialang.org/t/is-it-possible-to-store-the-jump-output-to-an-xlsx/95887/11 "2023-03-20T13:51:08Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![joaquimg](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/joaquimg/32/223_2.png) [@joaquimg](https://discourse.julialang.org/u/joaquimg)\
**Post date:** [March 20, 2023, 2:11pm UTC](https://discourse.julialang.org/t/is-it-possible-to-store-the-jump-output-to-an-xlsx/95887/12 "2023-03-20T14:11:00Z")

</div>

Did you try:

> [@odow](#):
>
> Containers.rowtable

?

Do you have an MWE?

---

<div class="post-metadata">

**Author:** ![geo1093](https://avatars.discourse-cdn.com/v4/letter/g/5f9b8f/32.png) [@geo1093](https://discourse.julialang.org/u/geo1093)\
**Post date:** [March 20, 2023, 2:14pm UTC](https://discourse.julialang.org/t/is-it-possible-to-store-the-jump-output-to-an-xlsx/95887/13 "2023-03-20T14:14:28Z")

</div>

```julia
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`

---

<div class="post-metadata">

**Author:** ![joaquimg](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/joaquimg/32/223_2.png) [@joaquimg](https://discourse.julialang.org/u/joaquimg)\
**Post date:** [March 20, 2023, 2:25pm UTC](https://discourse.julialang.org/t/is-it-possible-to-store-the-jump-output-to-an-xlsx/95887/14 "2023-03-20T14:25:49Z")

</div>

I changed your `import` to `using`, otherwise nothing works:

```julia
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
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

```

---

<div class="post-metadata">

**Author:** ![geo1093](https://avatars.discourse-cdn.com/v4/letter/g/5f9b8f/32.png) [@geo1093](https://discourse.julialang.org/u/geo1093)\
**Post date:** [March 20, 2023, 2:43pm UTC](https://discourse.julialang.org/t/is-it-possible-to-store-the-jump-output-to-an-xlsx/95887/15 "2023-03-20T14:43:17Z")

</div>

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…

---

<div class="post-metadata">

**Author:** ![joaquimg](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/joaquimg/32/223_2.png) [@joaquimg](https://discourse.julialang.org/u/joaquimg)\
**Post date:** [March 20, 2023, 3:30pm UTC](https://discourse.julialang.org/t/is-it-possible-to-store-the-jump-output-to-an-xlsx/95887/16 "2023-03-20T15:30:19Z")

</div>

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