Using XLSX.jl to save DataFrames to multiple sheets

General Usage
, ,
1

I’ve been using XLSX.jl to save dataframes to an Excel workbook. I would like to save each DataFrame to a different sheet in the workbook. The documentation gives an example of such a case that works:

df1 = DataFrames.DataFrame(COL1=[10,20,30], COL2=["Fist", "Sec", "Third"])
df2 = DataFrames.DataFrame(AA=["aa", "bb"], AB=[10.1, 10.2])

XLSX.writetable("report.xlsx", REPORT_A=( collect(DataFrames.eachcol(df1)), DataFrames.names(df1) ), REPORT_B=( collect(DataFrames.eachcol(df2)), DataFrames.names(df2) ))

However, in my case the number of dataframes is variable and I would like to find a way to handle any number of dataframes instead of hardcoding them into the writetable call. For example, if I had an array or collection of dataframes, I would ideally be able to format that correctly into the writetable call or iterate over the workbook and add each dataframe to a new sheet. Does anybody know if something like this is doable in XLSX.jl?

2

I didn’t read the XLSX.jl docs but if all you need to do is pass a list of keyword arguments containing the sheet names and dataframes, you can create a named tuple with something like:

sheets = (; zip(names, dataframes)...)

and then splat the named tuple in the function call:

XLSX.writetable("report.xlsx", sheets...)
3

You don’t have to hardcode keyword arguments. You can make a namedtuple or a dict with symbols as keys and then splat that into the XLSX call as variable length keyword args.

d = Dict(
    :sheet1 => df1,
    :sheet2 => df2,
)

XLSX.writetable("name.xlsx"; d...)

The semicolon before d is important because otherwise the iterable is splatted as a number of positional arguments, not keywords.

4

This worked! Thanks!

5

I would like to (re)open the discussion because the proposed solution no longer seems to work. I have the error
AbstractDataFrame is not iterable. Use eachrow(df) to get a row iterator or eachcol(df) to get a column iterator

Here is an alternative solution which comes directly from the XLSX.jl documentation (API Reference · XLSX.jl):
XLSX.writetable("name.xlsx", "sheet1" => df1, "sheet2" => df2)

fdekerm