Write from julia in excel

General Usage
1

Hello,

Here is my code:

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.

2

Just to start somewhere, have you already read: Tutorial · XLSX.jl

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

3

Thanks.

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?

4

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.

5

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

6

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:

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
7

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.

8

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