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: python - Writing multiple pandas dataframes to multiple excel worksheets - Stack Overflow
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 
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)
end
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.
2 Likes
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 . 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 
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
writer.save()
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.
2 Likes
yeahh, exactly that!! But it still doesn’t work . 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
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:
XLSX.writetable("report.xlsx",
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...)
5 Likes
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`.")
end
if !overwrite
@assert !isfile(filename) "$filename already exists."
end
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
else
sheet = addsheet!(xf, string(sheetname))
writetable!(sheet, data, column_names)
end
end
# write output file
writexlsx(filename, xf, overwrite=overwrite)
end
