# Create a new sheet in an excel file

**URL:** <https://discourse.julialang.org/t/create-a-new-sheet-in-an-excel-file/88432>\
**Category:** General Usage\
**Tags:** xlsx\
**Created:** [October 8, 2022, 8:23am UTC](https://discourse.julialang.org/t/create-a-new-sheet-in-an-excel-file/88432 "2022-10-08T08:23:27Z")\
**Posts on this page:** 9\
**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:** [October 8, 2022, 8:23am UTC](https://discourse.julialang.org/t/create-a-new-sheet-in-an-excel-file/88432/1 "2022-10-08T08:23:27Z")

</div>

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?

```julia
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](https://global.discourse-cdn.com/julialang/original/3X/7/a/7a4a5121ee54a016cc0d52f56a4c154a8402e062.png)

Thank you in advance for your answers

---

<div class="post-metadata">

**Author:** ![skleinbo](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/skleinbo/32/36080_2.png) [@skleinbo](https://discourse.julialang.org/u/skleinbo)\
**Post date:** [October 8, 2022, 8:57am UTC](https://discourse.julialang.org/t/create-a-new-sheet-in-an-excel-file/88432/2 "2022-10-08T08:57:25Z")

</div>

Using `mode=w` opens a blank file and overwrites any existing file. This is just normal behavior when opening any file with the `w`rite flag. You want `rw`. To quote from the [documentation](https://felipenoris.github.io/XLSX.jl/stable/tutorial/#Writing-Excel-Files)

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

---

<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:** [October 8, 2022, 9:46am UTC](https://discourse.julialang.org/t/create-a-new-sheet-in-an-excel-file/88432/3 "2022-10-08T09:46:09Z")

</div>

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

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

```

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

```

---

<div class="post-metadata">

**Author:** ![skleinbo](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/skleinbo/32/36080_2.png) [@skleinbo](https://discourse.julialang.org/u/skleinbo)\
**Post date:** [October 8, 2022, 10:08am UTC](https://discourse.julialang.org/t/create-a-new-sheet-in-an-excel-file/88432/4 "2022-10-08T10:08:59Z")

</div>

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

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

---

<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:** [October 8, 2022, 12:23pm UTC](https://discourse.julialang.org/t/create-a-new-sheet-in-an-excel-file/88432/5 "2022-10-08T12:23:01Z")

</div>

I wrote this:

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

```julia
LoadError: syntax: incomplete: "do" 

```

---

<div class="post-metadata">

**Author:** ![skleinbo](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/skleinbo/32/36080_2.png) [@skleinbo](https://discourse.julialang.org/u/skleinbo)\
**Post date:** [October 8, 2022, 12:56pm UTC](https://discourse.julialang.org/t/create-a-new-sheet-in-an-excel-file/88432/6 "2022-10-08T12:56:44Z")

</div>

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.

---

<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:** [October 8, 2022, 1:13pm UTC](https://discourse.julialang.org/t/create-a-new-sheet-in-an-excel-file/88432/7 "2022-10-08T13:13:15Z")

</div>

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

I have now a new problem… :

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

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

```

---

<div class="post-metadata">

**Author:** ![skleinbo](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/skleinbo/32/36080_2.png) [@skleinbo](https://discourse.julialang.org/u/skleinbo)\
**Post date:** [October 8, 2022, 1:43pm UTC](https://discourse.julialang.org/t/create-a-new-sheet-in-an-excel-file/88432/8 "2022-10-08T13:43:58Z")

</div>

> [@Zizilabg](#):
>
> `LoadError: AssertionError: ATTRIBUTION is not a valid CellRef.`

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 🙂

---

<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:** [October 9, 2022, 9:43am UTC](https://discourse.julialang.org/t/create-a-new-sheet-in-an-excel-file/88432/10 "2022-10-09T09:43:48Z")

</div>

Thank you very much!