Create a new sheet in an excel file

General Usage
1

Hello,

I would like to create a new sheet in an excel file that already has two sheets. I wrote this but it erased all my data… Can someone explain me how to do it?

XLSX.openxlsx("choix élèves julia.xlsx", mode="w") do xf
    sheet = xf["ATTRIBUTION"]
    sheet["ATTRIBUTION", dim=2] = collect(1:4)
end

My program consists of assigning classes to students. I would like to post the following:
(column 1) Name of the course
(column 2) list of assigned students.
(column 3) Penalty
In this form:
image

Thank you in advance for your answers

2

Using mode=w opens a blank file and overwrites any existing file. This is just normal behavior when opening any file with the write flag. You want rw. To quote from the documentation

Opening a file in write mode with XLSX.openxlsx will open a new (blank) Excel file for editing.

and

Opening a file in read-write mode with XLSX.openxlsx will open an existing Excel file for editing. This will preserve existing data in the original file.

3

Thank you for your answer. When I put “rw”, I get the following error message:

LoadError: ATTRIBUTION is not a valid sheetname or cell/range reference.


XLSX.openxlsx("choix élèves julia.xlsx", mode="rw") do xf
    sheet = xf["ATTRIBUTION"]
    sheet["ATTRIBUTION", dim=2] = collect(1:4)
end
4

That’s because the sheet is not automatically created when you access it. You can use

!XLSX.hassheet(xf, "ATTRIBUTION") && XLSX.addsheet!(xf, "ATTRIBUTION")

to check if it exists, and create it if not.

Be mindful though, that editing files with XSLS.jl can be dangerous if your sheets contain formula. See the warning message in the docs.

5

I wrote this:

XLSX.openxlsx("choix élèves julia.xlsx", mode="rw") do xf
    !XLSX.hassheet(xf, "ATTRIBUTION") && XLSX.addsheet!(xf, "ATTRIBUTION")
    sheet = xf["ATTRIBUTION"]
    sheet["ATTRIBUTION", dim=2] = collect(1:4)

    for j in 1:s
            sheet[i+1,1] = "Sujet ",j,":"
            for i in 1:e
                if JuMP.value.(x[i,j])==1
                    sheet[i+1,2] = i
                    sheet[i+1,3] = i
                end
            end
        end
end

And got this error message:

LoadError: syntax: incomplete: "do"
6

That usually means you are missing an end. Are you sure the final one was included in the file you loaded? Otherwise I can see nothing wrong.

Except that you want
sheet["A1", dim=2] = collect(1:4) instead of "ATTRIBUTION" to reference a cell.

7

Thank you. you were right, i missed an end.

I have now a new problem… :

LoadError: AssertionError: ATTRIBUTION is not a valid CellRef.

Also, I didn’t really understand the line "sheet["ATTRIBUTION", dim=2] = collect(1:4) "
If my j is 37 and i is 169, is what I wrote correct? Because for the moment nothing is displayed in the new sheet.

XLSX.openxlsx("choix élèves julia.xlsx", mode="rw") do xf
    !XLSX.hassheet(xf, "ATTRIBUTION") && XLSX.addsheet!(xf, "ATTRIBUTION")
    sheet = xf["ATTRIBUTION"]
    sheet["ATTRIBUTION", dim=2] = collect(1:4)

    for j in 1:s
            sheet[i+1,1] = "Sujet ",j,":"
            for i in 1:e
                if JuMP.value.(x[i,j])==1
                    sheet[i+1,2] = i
                    sheet[i+1,3] = i
                end
            end
        end
    end
end
8

That’s what I was trying to say in the second paragraph. "ATTRIBUTION" is not a cell identifier. "A1" is for example. So if you want column headers 1:4 in cells A1:D1, you would do sheet["A1", dim=2] = collect(1:4)

You also probably want sheet[j+1,1] = string("Sujet ",j,":"). i is not defined outside of the inner loop, and the right hand side is a tuple without string.

Anyway, to produce a table similar to what you showed in the opening post, you most certainly want to loop over students in the inner loop and subjects in the outer loop, because students seem to repeat for every subject.

Because for the moment nothing is displayed in the new sheet.

That’s because the function fails with an error :slight_smile:

10

Thank you very much!