# Write from julia in excel

**URL:** <https://discourse.julialang.org/t/write-from-julia-in-excel/87582>\
**Category:** General Usage\
**Created:** [September 21, 2022, 4:01pm UTC](https://discourse.julialang.org/t/write-from-julia-in-excel/87582 "2022-09-21T16:01:23Z")\
**Posts on this page:** 8\
**Page:** 1

<div class="post-metadata">

**Author:** ![Zizilabg](https://avatars.discourse-cdn.com/v4/letter/z/3da27b/32.png) [@Zizilabg](https://discourse.julialang.org/u/Zizilabg)\
**Post date:** [September 21, 2022, 4:01pm UTC](https://discourse.julialang.org/t/write-from-julia-in-excel/87582/1 "2022-09-21T16:01:23Z")

</div>

Hello,

Here is my code:

```julia
using XLSX
using JuMP
using Gurobi
using DataFrames

# change working directory to the one containing this file
cd(@ __DIR__ )

fichierDonnees=XLSX.readxlsx("choix élèves julia.xlsx")

ws=fichierDonnees["ELEVES"] # sélection de la feuille du fichier Excel (ws pour "Worksheet")

println("Eleves prioritaire")
n=2
while !ismissing(ws[n,2]) # ismissing retourne true si la cellule est vide
    global e=ws[n,1]
    global n=n+1
end

ws2=fichierDonnees["SUJETS"]

println()
println(ws.name)

l=2
while !ismissing(ws2[l,1]) # ismissing retourne true si la cellule est vide
    global s=ws2[l,1]
    global l=l+1
end

q = [0, 5, 20, 100, 1000]

p=zeros(e,s)
for i in 1:e, j in 1:s
   if j==ws[i+1,4]
        p[i,j]=q[1]
    elseif j==ws[i+1,6]
        p[i,j]= q[2]
    elseif j==ws[i+1,8]
        p[i,j]=q[3]
    elseif j==ws[i+1,10]
        p[i,j]= q[4]
    else
       p[i,j]= q[5]
    end
end

TB=Model(optimizer_with_attributes(Gurobi.Optimizer))

@variable(TB,x[1:e, 1:s],Bin)

@objective(TB, Min,sum(sum(x[i,j]*p[i,j] for j in 1:s) for i in 1:e))

@constraint(TB, contraintebase[i=1:e], sum(x[i,j] for j in 1:s ) == 1)

@variable(TB, y1, Bin)
@variable(TB, y2, Bin)
@constraint(TB, contrainte1a, y1+y2 == 1)
@constraint(TB, contrainte1b[j=1:s], sum(x[i,j] for i in 1:e ) >= 3*y1)
@constraint(TB, contrainte1c[j=1:s], sum(x[i,j] for i in 1:e ) <= 1000*(1-y2))

#contrainte 2
for j in 1:s
    if ws2[j+1, 3] == 1
        @constraint(TB, sum(x[i, j] for i in 1:e) <= 8)
    else
        @constraint(TB, sum(x[i, j] for i in 1:e) <= 12)
    end
end

#contrainte 3
#for i in 1:e, j in 1:s
# if (ws[i+1,3]==1)
# @constraint(TB, x[i,j]==1)
# end
#end

JuMP.optimize!(TB)

println("Attribution des séminaires:")
println()

    for i in 1:e, j in 1:s
        if JuMP.value.(x[i,j])==1
            println(ws[i+1,2]," attribué à ",ws2[j+1,2])
        end
    end

println("Pénalités totales: ", objective_value(TB))

```

It’s about assigning students to courses, based on their preferences. The data comes from an excel document.

Could someone tell me how I could display, for each student, the course assigned to them, in an excel document?

Thanks in advance for your help.

---

<div class="post-metadata">

**Author:** ![SteffenPL](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/steffenpl/32/206270_2.png) [@SteffenPL](https://discourse.julialang.org/u/SteffenPL)\
**Post date:** [September 21, 2022, 4:04pm UTC](https://discourse.julialang.org/t/write-from-julia-in-excel/87582/2 "2022-09-21T16:04:58Z")

</div>

Just to start somewhere, have you already read: [Tutorial · XLSX.jl](https://felipenoris.github.io/XLSX.jl/stable/tutorial/#Writing-Excel-Files)

You essentially have two choices, either go via DataFrames, e.g. writing a DataFrame and then saving it via XLSX. Or, you can modify a new or existing XLSX file. Both variants are shown in the docs.  
(But of course, feel free to ask if you need more details on that.)

---

<div class="post-metadata">

**Author:** ![Zizilabg](https://avatars.discourse-cdn.com/v4/letter/z/3da27b/32.png) [@Zizilabg](https://discourse.julialang.org/u/Zizilabg)\
**Post date:** [September 21, 2022, 4:29pm UTC](https://discourse.julialang.org/t/write-from-julia-in-excel/87582/3 "2022-09-21T16:29:49Z")

</div>

Thanks.

```julia
For i in 1:e, j in 1:s
XLSX.openxlsx("choix élèves julia.xlsx", mode="rw") do fichierDonnees
    sheet = fichierDonnees["ELEVES"]
    sheet[i,16] = j
end
end

```

I wrote this but it doesn’t work. Could you tell me why?

---

<div class="post-metadata">

**Author:** ![\_bernhard](https://avatars.discourse-cdn.com/v4/letter/_/bc79bd/32.png) [@\_bernhard](https://discourse.julialang.org/u/_bernhard)\
**Post date:** [September 21, 2022, 5:19pm UTC](https://discourse.julialang.org/t/write-from-julia-in-excel/87582/4 "2022-09-21T17:19:49Z")

</div>

Unfortunately “doesn’t work” is not too useful. Is there an error? What is printed to the terminal?

Maybe the `For` is the problem which should probably be a `for`, but maybe it is a typo.

---

<div class="post-metadata">

**Author:** ![Zizilabg](https://avatars.discourse-cdn.com/v4/letter/z/3da27b/32.png) [@Zizilabg](https://discourse.julialang.org/u/Zizilabg)\
**Post date:** [September 21, 2022, 5:26pm UTC](https://discourse.julialang.org/t/write-from-julia-in-excel/87582/5 "2022-09-21T17:26:26Z")

</div>

I have no error messages. The program runs without giving me any answer …

---

<div class="post-metadata">

**Author:** ![Dan](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/dan/32/42581_2.png) [@Dan](https://discourse.julialang.org/u/Dan)\
**Post date:** [September 21, 2022, 6:04pm UTC](https://discourse.julialang.org/t/write-from-julia-in-excel/87582/6 "2022-09-21T18:04:24Z")

</div>

This code is a bit strange. The assignment `sheet[i,16] = j` overwrites cell `[i,16]` with several values of `j`. The meaning must be different, perhaps: `sheet[i,j] = "something"`.

Additionally, each iteration, the file is opened and closed, which is extremely wastful if not dangerous. The proper way would be to open file outside the `for` loops:

```julia
XLSX.openxlsx("choix élèves julia.xlsx", mode="rw") do fichierDonnees
    sheet = fichierDonnees["ELEVES"]
    for i in 1:e, j in 1:s
        sheet[i,16] = j # fix this line
    end
end

```

---

<div class="post-metadata">

**Author:** ![Zizilabg](https://avatars.discourse-cdn.com/v4/letter/z/3da27b/32.png) [@Zizilabg](https://discourse.julialang.org/u/Zizilabg)\
**Post date:** [September 21, 2022, 6:44pm UTC](https://discourse.julialang.org/t/write-from-julia-in-excel/87582/7 "2022-09-21T18:44:51Z")

</div>

Thanks, now I can display values in my excel file. However, it shows me the same value for all students.  
Would you know how I could make it display the subject assigned to each student? (Display, for each i, the j assigned (JuMP.value.(x[i,j])==1)).

Thanks for your help.

---

<div class="post-metadata">

**Author:** ![Dan](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/dan/32/42581_2.png) [@Dan](https://discourse.julialang.org/u/Dan)\
**Post date:** [September 21, 2022, 7:13pm UTC](https://discourse.julialang.org/t/write-from-julia-in-excel/87582/8 "2022-09-21T19:13:28Z")

</div>

Perhaps, replacing `sheet[i,16] = j` with `if x[i,j]==1 sheet[i,16] = j ; end`