Cannot round-trip dynamic array functions

[TimG1964]

This worksheet has the formulas =UNIQUE(D1:D10) in cell F1 and =SORT(C1:C10,,-1) in cell G1. These formulas belong to a family that produce "spill" ranges, and you can see this for the sort function in the above image.

A workbook that is opened and then re-written by XLSX.jl will corrupt formulae like these.

julia> f=XLSX.openxlsx("arrays.xlsx", mode="rw")
XLSXFile("arrays.xlsx") containing 1 Worksheet
            sheetname size          range
-------------------------------------------------
               Sheet1 10x7          A1:G10

julia> s=f[1]
10×7 XLSX.Worksheet: ["Sheet1"](A1:G10)

julia> XLSX.getcellrange(s, "F1:G1")
1×2 Matrix{XLSX.AbstractCell}:
 Cell(F1, "", "", "1", Formula("_xlfn.UNIQUE(D1:D10)"))  Cell(G1, "", "", "30", Formula("_xlfn._xlws.SORT(C1:C10,,-1)"))

julia> XLSX.writexlsx("newtest.xlsx", f, overwrite=true)

The formulas are converted to a compatible form, and the spill values are disconnected from the formula and entered as simple values in each cell.

You can restore the correct function in Excel by deleting the @ from the formula (which then shows a #SPILL! error) and then removing the values from the spill range, freeing them for the spill function to use.

Cells with these kind of functions have both t="array" and ref=spillRange attributes, which are lost by XLSX.jl. Also, the Excel file seems to need an extra xl/metadata.xml internal file to declare the dynamic array properties needed for this class of function.