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

Question

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?

Sundar R · Accepted Answer · 2021-11-30 16:19:31Z

2

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

edited Nov 30, 2021 at 16:19

answered Nov 30, 2021 at 16:01

Sundar R

14.8k7 gold badges56 silver badges80 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Przemyslaw Szufel · Accepted Answer · 2021-11-30 20:09:15Z

2

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   
)

answered Nov 30, 2021 at 20:09

Przemyslaw Szufel

42.5k3 gold badges42 silver badges73 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related