How to create an excel file with multiple sheets using Julia?

user_16450670 0
Viewed 311

I'm programing in Julia and trying to create an excel file with multiple sheets for saving some data. The problem is that the function XLSX.openxlsx() only creates excels with one sheet. I'm trying to run the following code.

XLSX.openxlsx("test_file.xlsx", mode="w") do xf
    sheet = xf[1]
    XLSX.rename!("first")
    sheet["A1"] = "A"
    sheet = xf[2]
    XLSX.rename!("second")
    sheet["A1"] = "B"
    sheet = xf[3]
    XLSX.rename!("third")
    sheet["A1"] = "C"
end

Does anyone know how to create excel files with multiple sheets? or maybe create new sheets in an existing file?

julia xlsx excel
2 Answers

XLSX.addsheet! is what you're looking for.

XLSX.openxlsx("test_file.xlsx", mode="w") do xf
    XLSX.rename!(xf[1], "first")

    for sheetname in ["second", "third", "fourth"]
      XLSX.addsheet!(xf, sheetname)
    end

    xf[1]["A1"] = "A"
    xf[2]["A1"] = "B"
    xf[3]["A1"] = "C"

    # ...
end
sundar_81270

writetable accepts several worksheets as keyword arguments. Hence, I usually use a one-liner to export all data frames to an Excel file. I also definitely recommend to create a DataFrame, manipulate it and finally save to Excel as the DataFrames API is obviously more convenient than XLSX API for data manipulation.

See the code below:

using DataFrames, XLSX
df1 = DataFrame(a=1:5, b=string.("Hello",1:5), c=6.5:10.5)
df2 = DataFrame(a=1:5, d=[11,22,33,missing,missing] )

XLSX.writetable("dfs.xlsx", overwrite=true, 
    WORKSHEET_A=(collect(DataFrames.eachcol(df1)), DataFrames.names(df1)),
    WORKSHEET_B=(collect(DataFrames.eachcol(df2)), DataFrames.names(df2)),
    #add more data frames when needed   
)

enter image description here

user_99577100
Related