Writing dataframes or arrays from Julia to excel file

[ssrrii]

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?

[pdeffebach]

Looking quickly at the docs, maybe you want something like this?

[rocco_sprmnt21]

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

[ssrrii]

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!

[Nathan_Boyer]

I answered a similar question here:

Create excel file using data from two dataframes

The "rw" mode is a little dangerous, especially if your Excel document contains formulas (reference). 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.

[rocco_sprmnt21]

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

[ssrrii]

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.

[rocco_sprmnt21]

@ssrrii

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

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

[Nathan_Boyer]

@ssrrii

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

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

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.

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

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 …

[ssrrii]

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

[ssrrii]

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.

[rocco_sprmnt21]

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

[ssrrii]

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.