Writing a dataframe to Excel XLSX

Specific Domains Data
,
1

Can we write a dataframe (without headers) to a specified sheet and specified cell in Excel? I have a dataframe that I want to write to a specific sheet in a spreadsheet. Consider this dataframe

df = DataFrame(Name=["aaaa","bbbb","cccc"], Type = [1,2.0,5.5])

I have a spreadsheet “MyXLfile.xlsx” which has a tab called “Required_tab”, and I want to write this dataframe df (without headers) to the cell starting A1 in this tab.

2

Have you seen XLSX.jl? I don’t know all of its features, but it would be a good place to start.

3

Yes, I have gone through documentation of XLSX. The examples given in documentation either write single values to cells or just write a dataframe by creating a new Excel file:
https://felipenoris.github.io/XLSX.jl/stable/tutorial/#Writing-Excel-Files
I have tried the code below but it gives an error message

XLSX.openxlsx("MyXLfile.xlsx", mode="rw") do xf
    sheet = xf["Required_tab"]
    sheet["A1"] = XLSX.writetable(sheet, collect(DataFrames.eachcol(df1)), DataFrames.names(df1))
end

The code below also doesn’t work

XLSX.openxlsx("MyXLfile.xlsx", mode="rw") do xf
    sheet = xf["Required_tab"]
    sheet["A1"] = df1
end

Maybe @felipenoris can point me in the right direction

4

You try something like this

XLSX.openxlsx("my_new_file.xlsx", mode="rw") do xf
    sheet=xf["Required_tab"]
    sheet["A1"]= df.Type
end
5

You can write the data and add a new tab by

using DataFrames
using XLSX
df = DataFrame(Name=["aaaa","bbbb","cccc"], Type = [1,2.0,5.5])
XLSX.openxlsx("MyXLfile.xlsx",mode="rw") do xf
     XLSX.addsheet!(xf,"Required_tab")
     sheet = xf["Required_tab"]
     for r in 1:size(df,1), c in 1:size(df,2)
          sheet[XLSX.CellRef(r , c )] = df[r,c]
     end
end
3 Likes
6

Thank you.