Exporting Excel data to an already existing .xlsx file

General Usage
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
6 Likes
3

@felipenoris thank you so much, this worked! :slight_smile:

For the sake of completeness, let me just add how to create a new worksheet (if not already existing in the workbook, like it was in my case):

XLSX.openxlsx(file_name, mode="rw") do xf
    XLSX.addsheet!(xf, "new sheet name")
    sheet = xf[2]     # EDIT: this works if there was only 1 sheet before. 
                      # If there were already 2 or more sheets: see comments below.
    XLSX.writetable!(sheet, data, columnnames; anchor_cell=XLSX.CellRef("A1"))
end
5 Likes
4

@felipenoris I have a new problem :frowning:

only works if before there was just one sheet in the Excel file.
That is, if there were e.g. two sheets already, I should write:

sheet = xf[3]

Now I wonder: aiming to keep the code more general, is there a way to count the number of sheets contained in an Excel file?

If that was possible, let’s denote that value by numb_of_sheets, the issue would be solved by something like:

XLSX.openxlsx(file_name, mode="rw") do xf
    XLSX.addsheet!(xf, "new sheet name")
    sheet = xf[numb_of_sheets+1]
    XLSX.writetable!(sheet, data, columnnames; anchor_cell=XLSX.CellRef("A1"))
end

Thanks in advance!

5

You can use XLSX.sheetcount method.

"""
Counts the number of sheets in the Workbook.
"""
@inline sheetcount(wb::Workbook) = length(wb.sheets)
@inline sheetcount(xl::XLSXFile) = sheetcount(xl.workbook)
2 Likes
6

@felipenoris thank you very much for your prompt reply, this works!

1 Like
7

If you can use ods, there is also OdsIO that allows export of Array or DF to a specific place in the destination spreadsheet:

julia> ods_write(“TestSpreadsheet.ods”,Dict((“TestSheet”,3,2)=>[[1,2,3,4,5] [6,7,8,9,10]]))

8

@felipenoris I would have another question related to the topic.
As you suggested above, the following works:

XLSX.openxlsx(file_name, mode="rw") do xf
    numb_of_sheets = XLSX.sheetcount(xf)
    XLSX.addsheet!(xf, "new sheet name")
    sheet = xf[numb_of_sheets+1]
    XLSX.writetable!(sheet, data, columnnames; anchor_cell=XLSX.CellRef("A1"))
end

I’m now wondering if it is also possible to overwrite a certain existing sheet in a given Excel file. I would be interested in deleting the content of such a sheet, and replace with new data.

I tried adding overwrite = true in different part of the previous expression and made different attempts in changing/removing some lines, but still unsuccessfully. Would you have any suggestion?

Thank you in advance!

9

@ale, if you just assign data to an existing cell, the data will be overwritten.
You should probably open an issue asking for a method for deleting sheet data, if you want to wipe out everything.

1 Like
10

Awesome, this whole post is super useful!