# Create excel file using data from two dataframes
**URL:** https://discourse.julialang.org/t/create-excel-file-using-data-from-two-dataframes/110320
**Category:** General Usage
**Tags:** dataframes, xlsx, excel
**Created:** [February 16, 2024, 9:47pm UTC](https://discourse.julialang.org/t/create-excel-file-using-data-from-two-dataframes/110320 "2024-02-16T21:47:01Z")
**Posts on this page:** 6
**Page:** 1
### Author:  [@Sandy45](https://discourse.julialang.org/u/Sandy45)
#### Post date: [February 16, 2024, 9:47pm UTC](https://discourse.julialang.org/t/create-excel-file-using-data-from-two-dataframes/110320/1 "2024-02-16T21:47:01Z")
Hello,
I have two Data Frames which has headers and data in them, and I want to write data from these two Data Frames into one single excel sheet. I know we can write each data frame data into separate sheet. Any advice?
julia\> df
5×2 DataFrame
Row │ A B
│ Int64 String
─────┼───────────────
1 │ 1 M
2 │ 2 F
3 │ 3 F
4 │ 4 M
5 │ 1 0
julia\> df2
2×2 DataFrame
Row │ AA AB
│ String Float64
─────┼─────────────────
1 │ aa 10.1
2 │ bb 10.2
---
### Author:  [@Nathan\_Boyer](https://discourse.julialang.org/u/Nathan_Boyer)
#### Post date: [February 16, 2024, 10:05pm UTC](https://discourse.julialang.org/t/create-excel-file-using-data-from-two-dataframes/110320/2 "2024-02-16T22:05:27Z")
[openxlsx](https://felipenoris.github.io/XLSX.jl/dev/api/#XLSX.openxlsx) the file in write mode (`mode="w"`).
Then [writetable!](https://felipenoris.github.io/XLSX.jl/dev/api/#XLSX.writetable!) the tables to the opened file one at a time with the `anchor_cell` keyword.
---
### Author:  [@Sandy45](https://discourse.julialang.org/u/Sandy45)
#### Post date: [February 16, 2024, 11:05pm UTC](https://discourse.julialang.org/t/create-excel-file-using-data-from-two-dataframes/110320/3 "2024-02-16T23:05:02Z")
@Nathan_Boyer I have tried below and seeing some errors, can you please give an example of how can we achieve this ?
````julia
XLSX.writetable("report.xlsx",
REPORT_A=(DataFrames._columns(df), DataFrames.names(df)))
xlsx = XLSX.openxlsx("report.xlsx", mode="rw")
XLSX.writetable("report.xlsx",
REPORT_A=(DataFrames._columns(df2), DataFrames.names(df2));overwrite=true,anchor_cell=("A7")) ```
````
ERROR: Unsupported datatype Char for writing data to Excel file. Supported data types are Union{Missing, Bool, Float64, Int64, Date, DateTime, Time, String} or XLSX.CellValue.
Stacktrace:
[1] error(s::String)
@ Base .\error.jl:33
[2] setdata!(ws::XLSX.Worksheet, ref::XLSX.CellRef, value::Char)
@ XLSX .julia\packages\XLSX\U2Bcm\src\write.jl:385
[3] setindex!(ws::XLSX.Worksheet, v::Char, ref::XLSX.CellRef)
@ XLSX .julia\packages\XLSX\U2Bcm\src\write.jl:375
[4] writetable!(sheet::XLSX.Worksheet, data::Char, columnnames::Char; anchor\_cell::XLSX.CellRef)
@ XLSX .julia\packages\XLSX\U2Bcm\src\write.jl:497
[5] writetable!(sheet::XLSX.Worksheet, data::Char, columnnames::Char)
@ XLSX .julia\packages\XLSX\U2Bcm\src\write.jl:473
[6] writetable(filename::String; overwrite::Bool, kw::Base.Pairs{Symbol, Any, Tuple{Symbol, Symbol}, NamedTuple{(:REPORT\_A, :anchor\_cell), Tuple{Tuple{Vector{AbstractVector}, Vector{String}}, String}}})
@ XLSX .julia\packages\XLSX\U2Bcm\src\write.jl:729
[7] top-level scope
```julia
```
---
### Author:  [@Dan](https://discourse.julialang.org/u/Dan)
#### Post date: [February 16, 2024, 11:36pm UTC](https://discourse.julialang.org/t/create-excel-file-using-data-from-two-dataframes/110320/4 "2024-02-16T23:36:33Z")
From the error message, it seems you can’t write Char types into cells. It should be easy to convert them into Strings which are supported.
```julia
julia> df = DataFrame(answer = rand(['y','n'], 5))
5×1 DataFrame
Row │ answer
│ Char
─────┼────────
1 │ y
2 │ n
3 │ y
4 │ n
5 │ y
julia> df.answer = "" .* df.answer
5-element Vector{String}:
"y"
"n"
"y"
"n"
"y"
```
---
### Author:  [@Sandy45](https://discourse.julialang.org/u/Sandy45)
#### Post date: [February 17, 2024, 2:03am UTC](https://discourse.julialang.org/t/create-excel-file-using-data-from-two-dataframes/110320/5 "2024-02-17T02:03:13Z")
Okay, Finally I got this working, here is what I did just incase if anyone needs in future.
```julia
XLSX.writetable("report.xlsx", REPORT_A=(DataFrames._columns(df), DataFrames.names(df)))
xlsx = XLSX.openxlsx("report.xlsx", mode="rw")
sheet = xlsx["REPORT_A"]
XLSX.writetable!(sheet, df2; anchor_cell=XLSX.CellRef("A6"))
XLSX.writexlsx("report.xlsx", xlsx, overwrite=true)
```
---
### Author:  [@Nathan\_Boyer](https://discourse.julialang.org/u/Nathan_Boyer)
#### Post date: [February 19, 2024, 3:03pm UTC](https://discourse.julialang.org/t/create-excel-file-using-data-from-two-dataframes/110320/6 "2024-02-19T15:03:45Z")
The `"rw"` mode is a little dangerous, especially if your Excel document contains formulas ([reference](https://github.com/felipenoris/XLSX.jl/issues/159)). Also, the `do` block form is more robust; it will always automatically close the file.
This is how I would do it.
```julia
using DataFrames, XLSX
df1 = DataFrame(
A = [1, 2, 3, 4, 1],
B= ["M", "F", "F", "M", "O"],
)
df2 = DataFrame(
AA = ["aa", "bb"],
AB = [10.1, 10.2],
)
filepath = "./xlsxtest.xlsx"
startrow = nrow(df1) + 3
startcolumn = 1
XLSX.openxlsx(filepath, mode="w") do file
sheet = file[1]
XLSX.writetable!(sheet, df1)
XLSX.writetable!(sheet, df2, anchor_cell=XLSX.CellRef(startrow, startcolumn)) # or XLSX.CellRef("A8")
end
```
Additional options are shown in the documentation [here](https://felipenoris.github.io/XLSX.jl/dev/tutorial/#Writing-Excel-Files).