[ANN] Write Excel files with XLSX.jl v0.2.0

#1

This is a major upgrage for package XLSX.jl . You can now export your DataFrames to Excel files.

XLSX.writetable("df.xlsx", DataFrames.columns(df), DataFrames.names(df)) # df is a DataFrame

You can even export multiple dataframes to a single Excel file. Each DataFrame will be put into a separate worksheet.

XLSX.writetable("report.xlsx", REPORT_A=( DataFrames.columns(df1), DataFrames.names(df1) ), REPORT_B=( DataFrames.columns(df2), DataFrames.names(df2) ))

Streaming and Caching are also built into the package.

Enjoy!

16 Likes
#2

Can you write data to a worksheet without nuking figures that are elsewhere on the sheet, or even replace the data the figure is based on?

#3

I haven´t thought about that, but it turns out it just works. As long as your data cells has no formatting applied to them. I would say this gets unstable, so might not work in a complicated workbook.

import XLSX
f = XLSX.openxlsxtemplate("fig_template.xlsx") # opens existing excel file with figure as template
s = f[1] # gets the first worksheet

using DataFrames
df = DataFrame(X=[1,2,3,4,5,6], Y=[0.2, 0.3, 0.4, 0.5, 0.1, 1])

XLSX.writetable!(s, DataFrames.columns(df), DataFrames.names(df)) # write table to worksheet
XLSX.writexlsx("out.xlsx", f) # saves as new excel file

I guess I should improve the package to leave unedited worksheets untouched when saving the Excel file. So you would be able to export data to a worksheet with no formats using Julia, and write a pretty report on another worksheet.

3 Likes
#4

Im trying to export a Dataframe to an excel file, but I get this error:

ERROR: LoadError: MethodError: no method matching setdata!(::XLSX.Worksheet, ::XLSX.CellRef, ::Symbol)

Closest candidates are:
setdata!(::XLSX.Worksheet, ::XLSX.CellRef, ::DateTime) at /Users/hannaekfalth/.julia/v0.6/XLSX/src/write.jl:248
setdata!(::XLSX.Worksheet, ::XLSX.CellRef, ::Base.Dates.Time) at /Users/hannaekfalth/.julia/v0.6/XLSX/src/write.jl:235
setdata!(::XLSX.Worksheet, ::XLSX.CellRef, ::Date) at /Users/hannaekfalth/.julia/v0.6/XLSX/src/write.jl:222

Do you have an idea on where this error comes from?

Thanks in advance!

#5

Yes!

Data types supported for exporting are these:

String, Missings.Missing, Float64, Int, Bool, Dates.Date, Dates.Time, Dates.DateTime.

You´re exporting a Symbol.
If you convert that to one of data types supported, it should work.

1 Like
#6

Hi… i am having some troubles with this:

B_PCH= rand(TruncatedNormal(245.39, 2.79, 0, 280),15)
B_BIO= rand(TruncatedNormal(225.27, 3.22, 0, 280),15)
B_EOLR= rand(TruncatedNormal(271.38, 0.73, 0, 300),30)
B=vcat(B_PCH, B_BIO, B_EOLR)

XLSX.openxlsx(“Pasta1.xlsx”, mode=“rw”) do xf
sheet = xf[“Sheet1”]
sheet[“C2:C61”] = value.(B)
end
ERROR: LoadError: MethodError: no method matching value(::Float64)

Thanks!

#7

Hi there!

It looks like this problem is not with XLSX but with this line

sheet["C2:C61"] = value.(B)

specifically the function value not being known as the ERROR message suggests: "no method matching value(::Float64)". What was the function there for?

For me sheet["C2:C61"] wouldn’t work (although it did in an example) but replacing the above line with this:

sheet["C2", dim = 1] = B

seemed to work the way you intended it.

Finally, I think it could benefit your post if you read this suggestions to improve posts for help. In this case, for example, quoting your code, making sure it runs if someone want’s to try it (I happened to know I need to import Distributions but maybe that’s not the case for everyone), or creating a separate thread for your question.

Best of luck with your code!

2 Likes
#8

Thank you very much for your help, It was very useful. I will consider your tips for upcoming questions. It was my first time participating in a forum.

Best regards!

Laura

#9

You’re welcome!

It was my first time participating in a forum.

I figured, that’s why I linked you the page – how would you know otherwise.

I hope you enjoy your time here and don’t hesitate to ask if you need help with anything!

1 Like