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

package

#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!


#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.


#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.