Exporting Excel data to an already existing .xlsx file


#1

Hi,

I am trying to export different arrays to different sheets of an already existing Excel file. The format of this file is .xlsx.
Since I need to have several sheets in the same file, I can’t use “CSV”.
Secondly, it seems to me that “ExcelReaders” works just for reading files, which is great and easy to use, but doesn’t help now in the exporting phase.
Thirdly, I have tried “XLSX” (https://felipenoris.github.io/XLSX.jl/stable/tutorial.html#Writing-Excel-Files-1). It seems to me that if I try to export data in DataFrame format, I am forced to export them to a new Excel file all the times.
However, exporting them as explained in the section “Edit Existing Files” at the same link seems extremely time consuming and would require many lines of code to simply export different arrays to different sheets in the same .xslx file.

Does anyone have any suggestions?

P.S. I am aware that “Taro” exists, however I don’t have Java on my computer and I would like to avoid installing it if possible!


#2

I guess you can try to use:

XLSX.writetable!(sheet::Worksheet, data, columnnames; anchor_cell::CellRef=CellRef("A1"))

where sheet is a reference to the target worksheet, data is a vector of columns, and columnnames is a vector of column names.

From a df::DataFrame, you can set:

data = DataFrames.columns(df)
columnnames = DataFrames.names(df)

You must first open workbook in read-write mode:

XLSX.openxlsx("file.xlsx", mode="rw") do xf
    sheet = xf[1]
    XLSX.writetable!(sheet, data, columnnames)
end