# How to export multiple dataframe to multiple excel worksheets (in a loop)?
**URL:** https://discourse.julialang.org/t/how-to-export-multiple-dataframe-to-multiple-excel-worksheets-in-a-loop/12126
**Category:** General Usage
**Created:** [July 3, 2018, 1:25pm UTC](https://discourse.julialang.org/t/how-to-export-multiple-dataframe-to-multiple-excel-worksheets-in-a-loop/12126 "2018-07-03T13:25:03Z")
**Posts on this page:** 10
**Page:** 1
### Author:  [@jayce\_ram](https://discourse.julialang.org/u/jayce_ram)
#### Post date: [July 3, 2018, 1:25pm UTC](https://discourse.julialang.org/t/how-to-export-multiple-dataframe-to-multiple-excel-worksheets-in-a-loop/12126/1 "2018-07-03T13:25:03Z")
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.
```julia
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. .
```julia
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](https://stackoverflow.com/questions/35707723/writing-multiple-pandas-dataframes-to-multiple-excel-worksheets)
Thanks in advance.
---
### Author:  [@ExpandingMan](https://discourse.julialang.org/u/ExpandingMan)
#### Post date: [July 3, 2018, 2:02pm UTC](https://discourse.julialang.org/t/how-to-export-multiple-dataframe-to-multiple-excel-worksheets-in-a-loop/12126/2 "2018-07-03T14:02:01Z")
Presumably one of the things you are looking for is a way to convert `Dict`s or `NamedTuple`s to keyword arguments. This can be done with splatting e.g.
```julia
sort(A; (dims=1,)...) # with named tuple in 0.7
sort(A; Dict(:dims=>1)...) # with Dict
```
Now if you want you can create `Dict`s in a loop and pass them to `XLSX.writetable`.
---
### Author:  [@jayce\_ram](https://discourse.julialang.org/u/jayce_ram)
#### Post date: [July 3, 2018, 2:49pm UTC](https://discourse.julialang.org/t/how-to-export-multiple-dataframe-to-multiple-excel-worksheets-in-a-loop/12126/3 "2018-07-03T14:49:11Z")
Thanks for your answer but I cannot see how to apply this splat concept ☹ Let’s suppose I have a dictionary of dataframes
```nohighlight
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?
---
### Author:  [@ExpandingMan](https://discourse.julialang.org/u/ExpandingMan)
#### Post date: [July 3, 2018, 3:20pm UTC](https://discourse.julialang.org/t/how-to-export-multiple-dataframe-to-multiple-excel-worksheets-in-a-loop/12126/4 "2018-07-03T15:20:59Z")
I’m a little hazy on what exactly you are trying to do but I was imagining something like
```julia
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`.
---
### Author:  [@jayce\_ram](https://discourse.julialang.org/u/jayce_ram)
#### Post date: [July 3, 2018, 3:33pm UTC](https://discourse.julialang.org/t/how-to-export-multiple-dataframe-to-multiple-excel-worksheets-in-a-loop/12126/5 "2018-07-03T15:33:03Z")
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:
```julia
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 😕
```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()
```
---
### Author:  [@ExpandingMan](https://discourse.julialang.org/u/ExpandingMan)
#### Post date: [July 3, 2018, 3:38pm UTC](https://discourse.julialang.org/t/how-to-export-multiple-dataframe-to-multiple-excel-worksheets-in-a-loop/12126/6 "2018-07-03T15:38:43Z")
Something like this?
```julia
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.
---
### Author:  [@jayce\_ram](https://discourse.julialang.org/u/jayce_ram)
#### Post date: [July 3, 2018, 3:54pm UTC](https://discourse.julialang.org/t/how-to-export-multiple-dataframe-to-multiple-excel-worksheets-in-a-loop/12126/7 "2018-07-03T15:54:13Z")
yeahh, exactly that!! But it still doesn’t work ☹ . I got the following error :
```julia
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:
```julia
XLSX.writetable(filename; name_sheet = (df1.columns, names(df1)) ...)
```
but i can’t find the right syntax 😕
---
### Author:  [@nilshg](https://discourse.julialang.org/u/nilshg)
#### Post date: [July 3, 2018, 3:56pm UTC](https://discourse.julialang.org/t/how-to-export-multiple-dataframe-to-multiple-excel-worksheets-in-a-loop/12126/8 "2018-07-03T15:56:03Z")
The syntax is as described above, you need to call the function as
```julia
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](https://github.com/felipenoris/XLSX.jl/blob/master/src/helper.jl#L141) - might be helpful if the XLSX.jl docs included this use case as well!
To expand on the examples in the XLSX readme:
```julia
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...)
```
---
### Author:  [@jayce\_ram](https://discourse.julialang.org/u/jayce_ram)
#### Post date: [July 4, 2018, 7:20am UTC](https://discourse.julialang.org/t/how-to-export-multiple-dataframe-to-multiple-excel-worksheets-in-a-loop/12126/9 "2018-07-04T07:20:10Z")
Thank you very much 🙂
---
### Author:  [@felipenoris](https://discourse.julialang.org/u/felipenoris)
#### Post date: [August 28, 2018, 2:03pm UTC](https://discourse.julialang.org/t/how-to-export-multiple-dataframe-to-multiple-excel-worksheets-in-a-loop/12126/10 "2018-08-28T14:03:54Z")
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.
```julia
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
```