# Writing dataframes or arrays from Julia to excel file

**URL:** <https://discourse.julialang.org/t/writing-dataframes-or-arrays-from-julia-to-excel-file/112096>\
**Category:** New to Julia\
**Tags:** question\
**Created:** [March 25, 2024, 4:11pm UTC](https://discourse.julialang.org/t/writing-dataframes-or-arrays-from-julia-to-excel-file/112096 "2024-03-25T16:11:04Z")\
**Posts on this page:** 13\
**Page:** 1

<div class="post-metadata">

**Author:** ![ssrrii](https://avatars.discourse-cdn.com/v4/letter/s/838e76/32.png) [@ssrrii](https://discourse.julialang.org/u/ssrrii)\
**Post date:** [March 25, 2024, 4:11pm UTC](https://discourse.julialang.org/t/writing-dataframes-or-arrays-from-julia-to-excel-file/112096/1 "2024-03-25T16:11:05Z")

</div>

I have four data frames (D1, …, D4) usually having fixed number of columns, but varying numer of rows. I would like to write them next to each other to a single new sheet in an existing xlsx file. I use the packages XLSX and DataFrames. The excel-file is in the working directory, like my program.

I use the following function in the function:

function export\_to\_xlsx\_sheet(XF::AbstractString, sheet\_name::AbstractString, D1::DataFrame, D2::DataFrame, D3::DataFrame, D4::DataFrame)  
# Load existing Excel file  
xf = XLSX.readxlsx(XF)  
# Create a new worksheet for the dataframes to file XF  
XLSX.addsheet!(xf, sheet\_name)  
# Write dataframes to the new sheet  
XLSX.writetable!(sheet\_name, D1, anchor=“A1”)  
XLSX.writetable!(sheet\_name, D2, anchor=size(D1, 1) + 2)  
XLSX.writetable!(sheet\_name, D3, anchor=size(D1, 1) + size(D2, 1) + 4)  
XLSX.writetable!(sheet\_name, D4, anchor=size(D1, 1) + size(D2, 1) + size(D3, 1) + 6)  
# Save changes to the Excel file  
XLSX.writexlsx(XF, xf)  
end

I am probably doing something wrong. I get the message: “XLSXFile instance is not writable”. Can anyone help to get the dataframes to the single new sheet in the exisitng file?

---

<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 25, 2024, 5:10pm UTC](https://discourse.julialang.org/t/writing-dataframes-or-arrays-from-julia-to-excel-file/112096/2 "2024-03-25T17:10:29Z")

</div>

Looking quickly at the docs, maybe you want something like [this](https://felipenoris.github.io/XLSX.jl/stable/tutorial/#Writing-Excel-Files)?

---

<div class="post-metadata">

**Author:** ![rocco\_sprmnt21](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/rocco_sprmnt21/32/20127_2.png) [@rocco\_sprmnt21](https://discourse.julialang.org/u/rocco_sprmnt21)\
**Post date:** [March 25, 2024, 5:18pm UTC](https://discourse.julialang.org/t/writing-dataframes-or-arrays-from-julia-to-excel-file/112096/3 "2024-03-25T17:18:32Z")

</div>

```julia
sheet_name="f3"
    XLSX.openxlsx(XF, mode="rw") do xf
        newsheet=XLSX.addsheet!(xf, sheet_name)
        XLSX.writetable!(newsheet, D1, anchor_cell=XLSX.CellRef("A1"))
        row_n=size(D1, 1) + 2
        XLSX.writetable!(newsheet, D2, anchor_cell=XLSX.CellRef("A$row_n"))
    end

```

---

<div class="post-metadata">

**Author:** ![ssrrii](https://avatars.discourse-cdn.com/v4/letter/s/838e76/32.png) [@ssrrii](https://discourse.julialang.org/u/ssrrii)\
**Post date:** [March 25, 2024, 5:31pm UTC](https://discourse.julialang.org/t/writing-dataframes-or-arrays-from-julia-to-excel-file/112096/4 "2024-03-25T17:31:53Z")

</div>

Hi rocco,  
This certainly is a move in the right direction. A sheet is added now to the output file. The data frames are now vertically stacked, whereas I would like them horizontally added, but that is probably rearranging the cell references. I’ll try to elaborate that!

---

<div class="post-metadata">

**Author:** ![Nathan\_Boyer](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/nathan_boyer/32/14825_2.png) [@Nathan\_Boyer](https://discourse.julialang.org/u/Nathan_Boyer)\
**Post date:** [March 25, 2024, 5:36pm UTC](https://discourse.julialang.org/t/writing-dataframes-or-arrays-from-julia-to-excel-file/112096/5 "2024-03-25T17:36:16Z")

</div>

I answered a similar question here:

> [@Create excel file using data from two dataframes](https://discourse.julialang.org/t/create-excel-file-using-data-from-two-dataframes/110320/6):
>
> The "rw" mode is a little dangerous, especially if your Excel document contains formulas ([reference](https://github.com/felipenoris/XLSX.jl/issues/159)). Also, the do block form is more robust; it will always automatically close the file. This is how I would do it. using DataFrames, XLSX df1 = DataFrame( A = [1, 2, 3, 4, 1], B= ["M", "F", "F", "M", "O"], ) df2 = DataFrame( AA = ["aa", "bb"], AB = [10.1, 10.2], ) filepath = "./xlsxtest.xlsx" startrow = nrow(df1) + 3 startcolumn = 1 XLSX.openxlsx(filepath, mode="w") do file s…

The `anchor_cell` keyword argument must be passed a `XLSX.CellRef`. Rocco showed how to use a cell name for your anchor cell, but you can also use row and column indices as I did in the link.

---

<div class="post-metadata">

**Author:** ![rocco\_sprmnt21](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/rocco_sprmnt21/32/20127_2.png) [@rocco\_sprmnt21](https://discourse.julialang.org/u/rocco_sprmnt21)\
**Post date:** [March 25, 2024, 5:46pm UTC](https://discourse.julialang.org/t/writing-dataframes-or-arrays-from-julia-to-excel-file/112096/6 "2024-03-25T17:46:26Z")

</div>

```julia
col_n=('A'+size(D1, 2))*'1'

```

---

<div class="post-metadata">

**Author:** ![ssrrii](https://avatars.discourse-cdn.com/v4/letter/s/838e76/32.png) [@ssrrii](https://discourse.julialang.org/u/ssrrii)\
**Post date:** [March 25, 2024, 5:46pm UTC](https://discourse.julialang.org/t/writing-dataframes-or-arrays-from-julia-to-excel-file/112096/7 "2024-03-25T17:46:37Z")

</div>

Hi rocco,  
Thanks this works well. Since the number of columns per data frame is fixed, I could do the following:  
XLSX.openxlsx(XF, mode=“rw”) do xf  
newsheet=XLSX.addsheet!(xf, sheet\_name)  
XLSX.writetable!(newsheet, D1, anchor\_cell=XLSX.CellRef(“A1”))  
XLSX.writetable!(newsheet, D2, anchor\_cell=XLSX.CellRef(“B1”))  
XLSX.writetable!(newsheet, D3, anchor\_cell=XLSX.CellRef(“C1”))  
XLSX.writetable!(newsheet, D4, anchor\_cell=XLSX.CellRef(“F1”))  
end

This looks simple. I am still a bit puzzled why it was not writable. But I am fairly new to Julia, so I guess it is probably part of the learning curve. Thanks a lot !

Now, I will also dive into the idea of Nathan.

---

<div class="post-metadata">

**Author:** ![rocco\_sprmnt21](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/rocco_sprmnt21/32/20127_2.png) [@rocco\_sprmnt21](https://discourse.julialang.org/u/rocco_sprmnt21)\
**Post date:** [March 25, 2024, 5:51pm UTC](https://discourse.julialang.org/t/writing-dataframes-or-arrays-from-julia-to-excel-file/112096/8 "2024-03-25T17:51:31Z")

</div>

> [@ssrrii](#):
>
> Now, I will also dive into the idea of Nathan.

I think it’s a more flexible and easy to use way

---

<div class="post-metadata">

**Author:** ![Nathan\_Boyer](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/nathan_boyer/32/14825_2.png) [@Nathan\_Boyer](https://discourse.julialang.org/u/Nathan_Boyer)\
**Post date:** [March 25, 2024, 6:18pm UTC](https://discourse.julialang.org/t/writing-dataframes-or-arrays-from-julia-to-excel-file/112096/9 "2024-03-25T18:18:32Z")

</div>

> [@ssrrii](#):
>
> Now, I will also dive into the idea of Nathan.

What you wrote is correct and fine. I am just pointing out that …

```julia
XLSX.CellRef("F1") == XLSX.CellRef(1,6)

```

and you may find that alternate form more convenient if you need to do math to get your `anchor_cell` locations.

* * *

> [@ssrrii](#):
>
> I am still a bit puzzled why it was not writable.

 ![image](https://global.discourse-cdn.com/julialang/original/3X/d/7/d7d05ca12548428bc67840d1a20cf7efd4974376.png)

The Excel file needs to be open for writing when you go to `addsheet!` or `writetable!`.

* * *

Also, for posts on this forum please read through the link below, especially #2. Your code is difficult to read without formatting.

> [@Please read: make it easier to help you](https://discourse.julialang.org/t/please-read-make-it-easier-to-help-you/14757):
>
> Welcome to the Julia Discourse! We are enthusiastic about helping Julia programmers, both beginner and experienced. This public service announcement (PSA) outlines best practices when asking for help. Following these points makes it easier for us to help you and more likely you’ll get a prompt, useful answer. Keywords are highlighted to make it easier to refer to specific points. Choose a descriptive title that captures the key part of your question, eg “plots with multiple axes” instead of …

---

<div class="post-metadata">

**Author:** ![ssrrii](https://avatars.discourse-cdn.com/v4/letter/s/838e76/32.png) [@ssrrii](https://discourse.julialang.org/u/ssrrii)\
**Post date:** [March 26, 2024, 12:33pm UTC](https://discourse.julialang.org/t/writing-dataframes-or-arrays-from-julia-to-excel-file/112096/10 "2024-03-26T12:33:13Z")

</div>

Dear rocco,  
why does the ‘1’ appear at the end? What does it mean/do?

---

<div class="post-metadata">

**Author:** ![ssrrii](https://avatars.discourse-cdn.com/v4/letter/s/838e76/32.png) [@ssrrii](https://discourse.julialang.org/u/ssrrii)\
**Post date:** [March 26, 2024, 12:41pm UTC](https://discourse.julialang.org/t/writing-dataframes-or-arrays-from-julia-to-excel-file/112096/11 "2024-03-26T12:41:41Z")

</div>

Thanks, Nathan and Rocc, I get your point. Calculations to determine cell refs are possible. Great. Now, I also understand why the file was not writeable. Thanks for adding to my understanding.

---

<div class="post-metadata">

**Author:** ![rocco\_sprmnt21](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/rocco_sprmnt21/32/20127_2.png) [@rocco\_sprmnt21](https://discourse.julialang.org/u/rocco_sprmnt21)\
**Post date:** [March 26, 2024, 4:00pm UTC](https://discourse.julialang.org/t/writing-dataframes-or-arrays-from-julia-to-excel-file/112096/12 "2024-03-26T16:00:42Z")

</div>

Wanting to write the second table next to the first and not below (as you specified), it was necessary to calculate the anchor\_cell.  
To make it clearer, in the example the first dataframe, placed at “A1”, has 4 columns, so we want to paste the second starting from the fifth column i.e. from cell “E1”.  
To do this, in general terms, I use the formula `col_n=('A'+size(D1, 2))*'1'`  
Below I break it down into the various pieces to make the content clearer.  
know :

1. that in Julia the function is defined that adds an integer to a character to obtain the character that is n positions later in the asci table.
2. the function \*(str1,str2) is also defined or in the infixed form str1 \* str2 which concatenates multiple strings and/or characters.

So `col_n=('A'+size(D1, 2))*'1'` simply attaches the ‘1’ character to the end of the string.

```julia
julia> D1=DataFrame(rand(10,4),:auto)
10×4 DataFrame
 Row │ x1 x2 x3 x4        
     │ Float64 Float64 Float64 Float64
─────┼───────────────────────────────────────────
   1 │ 0.827833 0.444002 0.794025 0.14214
   2 │ 0.0702885 0.967284 0.12464 0.318974
   3 │ 0.779892 0.731767 0.523908 0.597223
   4 │ 0.639172 0.0926431 0.923284 0.177832
   5 │ 0.801687 0.709386 0.666152 0.971309
   6 │ 0.925279 0.367764 0.35316 0.97212
   7 │ 0.561104 0.535708 0.168824 0.515559
   8 │ 0.73952 0.344785 0.666316 0.0681426
   9 │ 0.291793 0.407933 0.827863 0.477402
  10 │ 0.103099 0.762747 0.615376 0.294676

julia> col_n=('A'+size(D1, 2))*'1'
"E1"

julia> size(D1, 2)
4

julia> 'A'
'A': ASCII/Unicode U+0041 (category Lu: Letter, uppercase)

julia> 'A'+4
'E': ASCII/Unicode U+0045 (category Lu: Letter, uppercase)

julia> Char(0x0041)
'A': ASCII/Unicode U+0041 (category Lu: Letter, uppercase)

julia> Char(0x0045)
'E': ASCII/Unicode U+0045 (category Lu: Letter, uppercase)

julia> string("abcd","efg")
"abcdefg"

julia> "abcd"*"efg"
"abcdefg"

julia> 'A'*'1'
"A1"

julia> ('A'+4)*'1'
"E1"

```

This way you can do everything you can do in the form with row and column numbers indicated by @Nathan_Boyer, but it’s a little more intricate

---

<div class="post-metadata">

**Author:** ![ssrrii](https://avatars.discourse-cdn.com/v4/letter/s/838e76/32.png) [@ssrrii](https://discourse.julialang.org/u/ssrrii)\
**Post date:** [March 26, 2024, 5:30pm UTC](https://discourse.julialang.org/t/writing-dataframes-or-arrays-from-julia-to-excel-file/112096/13 "2024-03-26T17:30:59Z")

</div>

Wow, I wasn’t aware yet, that an integer can be added to a character, i.e. its ASCII/Unicode. But this makes manipulation of characters very convenient.I see the \*‘1’ concatenates the column character and the row number to make the reference cell. Thanks for the help.