How to export multiple dataframe to multiple excel worksheets (in a loop)?

I’m trying to export the results of my optimization problem to excel. I have tested the function below that works very well but I have to pass all the data frames once a time.

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

However, I have many data frames and I’d like to pass them to excel, to different worksheets inside a loop. I have tried to push data frames into the file I have created but it didn’t work. .

ERROR: LoadError: AssertionError: Output file report.xlsx already exists.

Anyone know if it’s possible ? In python, you could make something like that:
Thanks in advance.

Presumably one of the things you are looking for is a way to convert Dicts or NamedTuples to keyword arguments. This can be done with splatting e.g.

sort(A; (dims=1,)...)  # with named tuple in 0.7
sort(A; Dict(:dims=>1)...)  # with Dict

Now if you want you can create Dicts in a loop and pass them to XLSX.writetable.

1 Like

Thanks for your answer but I cannot see how to apply this splat concept :frowning: Let’s suppose I have a dictionary of dataframes

dict_dt["New York"] =  df1
dict_dt["Los Angeles"] = df2

XLSX.writetable cannot to be called inside a loop and requires that I send all the dataframe once a time.
Are you suggesting I have to create another dictionary that keep the values of the arguments(REPORT_A,REPORT_B) and then I will be able to send this dictionary to XLSX.writetable?

I’m a little hazy on what exactly you are trying to do but I was imagining something like

dict = Dict{Symbol,DataFrame}()
for x ∈ somelist
    dict[getname(x)] = getdf(x)
XLSX.writetable(fname; dict...)

This way you can do essentially anything you want inside the loop and then call writetable when you are ready to create a file. You needn’t worry about performance as very little actual copying is being done when you populate the Dict. I assume you’ve already looked through the XLSX documentation to see if it’s possible to create file objects before you write them, but it might also be look reading the source code for writetable.


Thanks again.

XLSX.writetable(fname; dict…)

The problem I see in this line above is that I cannot just send dict because the arg needs the name of the sheet, the information about names and columns separately :frowning: . Like bellow:

XLSX.writetable("report.xlsx", REPORT_A=( DataFrames.columns(df1), DataFrames.names(df1) ))

so I cannot see how to send it separately once I have a dictionary that contains my whole set of data frames…

I’m a little hazy on what exactly you are trying to do but I was imagining something like
I’m trying to do exactly the same thing in this code, but in Julia :confused:

import pandas as pd
#initialze the excel writer
writer = pd.ExcelWriter('MyFile.xlsx', engine='xlsxwriter')

#store your dataframes in a  dict, where the key is the sheet name you want
frames = {'sheetName_1': dataframe1, 'sheetName_2': dataframe2,
        'sheetName_3', dataframe3}

#now loop thru and put each on a specific sheet
for sheet, frame in  frames.iteritems(): # .use .items for python 3.X
    frame.to_excel(writer, sheet_name = sheet)

#critical last step

Something like this?

dict = Dict(:sheet1=>(df1.columns, names(df1)),
            :sheet2=>(df2.columns, names(df2)),
            :sheet3=>(df3.columns, names(df3)))
XLSX.writetable(filename; dict...)

You can splat any type of Dict you want, if you need the values to be tuples, you can just put tuples in it.


yeahh, exactly that!! But it still doesn’t work :frowning: . I got the following error :

ERROR: LoadError: MethodError: no method matching writetable(::String, ::Pair{String,Tuple{Array{Any,1},Array{Symbol,1}}}, ::Pair{S
tring,Tuple{Array{Any,1},Array{Symbol,1}}}, ::Pair{String,Tuple{Array{Any,1},Array{Symbol,1}}})

I guess it’s because I have to send a following that looks like that:

XLSX.writetable(filename; name_sheet = (df1.columns, names(df1)) ...)

but i can’t find the right syntax :confused:

The syntax is as described above, you need to call the function as

XLSX.writetable(filename; dict...)

Where the dict... expands your dictionary in the function call.
This is actually really handy and I didn’t know this worked, the function definition is here - might be helpful if the XLSX.jl docs included this use case as well!

To expand on the examples in the XLSX readme:

using DataFrames, XLSX

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

# writetable example from XLSX README:
    REPORT_A=(DataFrames.columns(df1), DataFrames.names(df1)), 
    REPORT_B=(DataFrames.columns(df2), DataFrames.names(df2)))

# Using splatting and a dict, this can be written as:
d = Dict(:REPORT_A=>(df1.columns, names(df1)), :REPORT_B=>(df1.columns, names(df1)))    
XLSX.writetable("report2.xlsx"; d...)   

Thank you very much :slight_smile:

Yes, this should be the way to go. Or you can write yourself a function that accepts DataFrames as input and bakes this XLSX.writetable function call.

Although this solution is not so juicy, the idea of this XLSX.writetable implementation is to be a syntax sugar for the same kind of code shown in the python example. In the source code of this function, you can find the same functions provided by the python code referenced by this thread, that you can also use as a solution for this kind of user case.

function writetable(filename::AbstractString, tables::Vector{Tuple{String, Vector{Any}, Vector{T}}}; overwrite::Bool=false, rewrite=nothing) where {T<:Union{String, Symbol}}

    # rewrite is deprecated
    if rewrite != nothing
        error("`rewrite` keyword argument was renamed to `overwrite` in `XLSX.writexlsx`.")

    if !overwrite
        @assert !isfile(filename) "$filename already exists."

    xf = open_empty_template()

    is_first = true

    for (sheetname, data, column_names) in tables
        if is_first
            # first sheet already exists in template file
            sheet = xf[1]
            rename!(sheet, string(sheetname))
            writetable!(sheet, data, column_names)

            is_first = false
            sheet = addsheet!(xf, string(sheetname))
            writetable!(sheet, data, column_names)

    # write output file
    writexlsx(filename, xf, overwrite=overwrite)