Write to XLSX file while maintaining formatting

General Usage
, ,
1

Hi,

I’m trying to write data to a .xlsx file that has already been formatted (cell size, colors, etc.). Is there anyway to write data to a .xlsx file while maintaining the existing formatting? I’m currently using the XLSX.jl function writetable!() shown in the code below. However, this does not maintain the formatting of the original file.

    XLSX.openxlsx(filename, mode="rw") do xf
        sheet = xf[1]
        XLSX.writetable!(sheet, collect(DataFrames.eachcol(df)), DataFrames.names(df))
    end
2

Linking related thread with an alternative solution.

3

This works now in the main branch of XLSX.jl and will be released in the next release. An incomplete fix was made in v0.11 (issue #281). This was completed in response to issue #454 and will be in v0.12.4.

To illustrate:

using XLSX

# Create a new, empty xlsx file with hundred empty cells 
# and then format the empty cells
openxlsx("formatted.xlsx"; mode="w") do xf
    sh = xf[1]
    sh[1:10, 1:10] = ""
    setAlignment(sh, "A1:J1"; wrapText=true, horizontal="center")
    setRowHeight(sh, 1, 1:10; height=32)
    setFormat(sh, 2:10, [1,5,9]; format="#,##0.000_0")
    setFormat(sh, 2:10, [2,6,10]; format="_0£* #,##0_0")
    setFormat(sh, 2:10, [3,4,7,8]; format="0.0_0")
end

data = [10000 .* rand(9) for _ in 1:10] # generate some random data
titles = ["Title for Column $(i)" for i in 1:10] # and column names

# Add the data to the preformatted worksheet
xf = openxlsx("formatted.xlsx", mode="rw")
sh=xf[1]
writetable!(sh, data, titles)
writexlsx("filled_and_formatted.xlsx", xf; overwrite=true)

The formatting is preserved through the writetable!:

image
image923×273 10.9 KB

It has worked unconditionally for cell font, fill, borders and alignment since v0.11