Create excel file using data from two dataframes

General Usage
, ,
1

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

2

openxlsx the file in write mode (mode="w").

Then writetable! the tables to the opened file one at a time with the anchor_cell keyword.

3

@Nathan_Boyer I have tried below and seeing some errors, can you please give an example of how can we achieve this ?

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

4

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> 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"
5

Okay, Finally I got this working, here is what I did just incase if anyone needs in future.

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)
6

The "rw" mode is a little dangerous, especially if your Excel document contains formulas (reference). Also, the do block form is more robust; it will always automatically close the file.

This is how I would do it.

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.

2 Likes