How to add a file to the package artifacts?

[TimG1964]

IIUC, artifacts seem to be created from here: "https://www.dropbox.com/s/9rgm3pnd1dmzmy9/xlsx_artifacts.tar.gz?dl=1"

I need to add a new artifact so that XLSX.jl can understand dynamic array functions like SORT or UNIQUE. This is an xl/metadata.xml file that needs to be added to a workbook if one of these formulae is added. If a workbook is read that already contains one of these functions, this metadata.xml file is present and properly managed. However, if a user of XLSX.jl wants to add such a function and there isn't already one somewhere else, then this file needs to be added.

It needs to go into the relocatable_data location, but I don't see how to get it in there. Is there an easy way?

The file isn't large. I think the following will suffice:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<metadata xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:xda="http://schemas.microsoft.com/office/spreadsheetml/2017/dynamicarray">
  <metadataTypes count="1">
    <metadataType name="XLDAPR" minSupportedVersion="120000" copy="1" pasteAll="1" pasteValues="1" merge="1" splitFirst="1" rowColShift="1" clearFormats="1" clearComments="1" assign="1" coerce="1" cellMeta="1"/>
    </metadataTypes>
  <futureMetadata name="XLDAPR" count="1">
    <bk>
      <extLst>
        <ext uri="{bdbb8cdc-fa1e-496e-a857-3c3f30c029c3}">
          <xda:dynamicArrayProperties fDynamic="1" fCollapsed="0"/>
        </ext>
      </extLst>
    </bk>
  </futureMetadata>
  <cellMetadata count="1">
    <bk>
      <rc t="1" v="0"/>
    </bk>
  </cellMetadata>
</metadata>

[TimG1964]

I have a PR to make that will add a setFormula() function to XLSX.jl but I cannot do it at present because it needs this metadata.xml file in the artifacts and I don't know how to put it there. If I make the PR, I think it will say file not found.

This is the relevant code:

        if !haskey(xf.files, "xl/metadata.xml") # add metadata.xml on first use of a dynamicArray formula
            xf.data["xl/metadata.xml"] = XML.Node(XML.Raw(read(joinpath(_relocatable_data_path(), "metadata.xml"))))
            xf.files["xl/metadata.xml"] = true # set file as read
            add_override!(xf, "/xl/metadata.xml", "application/vnd.openxmlformats-officedocument.spreadsheetml.sheetMetadata+xml")
            rId = add_relationship!(get_workbook(ws), "metadata.xml", "http://schemas.openxmlformats.org/officeDocument/2006/relationships/sheetMetadata")
        end

Here are the docs for the function to give an idea if what I'm trying to add:

setFormula(ws::Worksheet, RefOrRange::AbstractString, formula::String)
setFormula(xf::XLSXFile,  RefOrRange::AbstractString, formula::String)

setFormula(sh::Worksheet, row, col, formula::String)

Set the Excel formula to be used in the given cell or cell range.

Formulae must be valid Excel formulae and written in US english with comma
separators. Cell references may be absolute or relative references in either
the row or the column or both (e.g. \$A\$2). No validation of the specified
formula is made by XLSX.jl and formulae are stored verbatim, as given.

If a contiguous range is specfied, setFormula will usually create a
referencedFormula. This is the same as Excel would use if using drag fill to
copy a formula into a range of cells.

Non-contiguous ranges are not supported by setFormula. Set the formula in
each cell or contiguous range separately.

Since XLSX.jl does not and cannot replicate all the functions built in to Excel,
setting a formula in a cell does not permit the cell's value to be re-calculated
within XLSX.jl. Instead, although the formula is properly added to the cell, the
value is set to missing so that Excel will re-calculate it on opening.

Unlike with conventional functions, dynamic array functions (e.g. SORT or UNIQUE)
should not be put into ReferencedFormulae and neither should functions that refer to
cells in other sheets. When functions of this type are identified, they will instead be
duplicated individually in each cell in the given range (but with relative cell references
appropriately adjusted). This is an internal process that should be transparent
to the user.

Note that dynamic array functions will return values into a spill range the extent of
which depends on the data on which the function is operating. If any of the cells in the
spill range already contains a value, Excel will show an @SPILL error.

Examples:

julia> using XLSX

julia> f=newxlsx("setting formulas")
XLSXFile("blank.xlsx") containing 1 Worksheet
            sheetname size          range        
-------------------------------------------------
     setting formulas 1x1           A1:A1        


julia> s=f[1]
1×1 Worksheet: ["setting formulas"](A1:A1) 

julia> s["A2:A10"]=1
1

julia> s["A1:J1"]=1
1

julia> setFormula(s, "B2:J10", "=A2+B1") # adds formulae but cannot update calculated values

julia> s[:]
10×10 Matrix{Any}:
 1  1         1         1         1         1         1         1         1         1
 1   missing   missing   missing   missing   missing   missing   missing   missing   missing
 1   missing   missing   missing   missing   missing   missing   missing   missing   missing
 1   missing   missing   missing   missing   missing   missing   missing   missing   missing
 1   missing   missing   missing   missing   missing   missing   missing   missing   missing
 1   missing   missing   missing   missing   missing   missing   missing   missing   missing
 1   missing   missing   missing   missing   missing   missing   missing   missing   missing
 1   missing   missing   missing   missing   missing   missing   missing   missing   missing
 1   missing   missing   missing   missing   missing   missing   missing   missing   missing
 1   missing   missing   missing   missing   missing   missing   missing   missing   missing

# formulae are there for when the saved file is opened in Excel.
julia> XLSX.getcell(s, "B2")
XLSX.Cell(B2, "", "", "", XLSX.ReferencedFormula("=A2+B1", 0, "B2:J10", nothing))

julia> XLSX.getcell(s, "J10")
XLSX.Cell(J10, "", "", "", XLSX.FormulaReference(0, nothing))

julia> addsheet!(f, "trig functions")
1×1 Worksheet: ["trig functions"](A1:A1) 

julia> f
XLSXFile("mytest.xlsx") containing 2 Worksheets
            sheetname size          range        
-------------------------------------------------
     setting formulas 10x10         A1:J10
       trig functions 1x1           A1:A1


julia> s2=f[2]
1×1 Worksheet: ["trig functions"](A1:A1)

julia> for i=1:100, s2[i, 1] = 2.0*pi*i/100.0; end

julia> setFormula(s2, "B1:B100", "=sin(A1)")

julia> setFormula(s2, "C1:C100", "=cos(A1)")

julia> setFormula(s2, "D1:D100", "=sin(A1)^2 + cos(A1)^2")

julia> f=newxlsx("mysheet")
XLSXFile("blank.xlsx") containing 1 Worksheet
            sheetname size          range
-------------------------------------------------
              mysheet 1x1           A1:A1

julia> s=f[1]
1×1 Worksheet: ["mysheet"](A1:A1)

julia> s["A1"]=["Header1" "Header2" "Header3"; 1 2 3; 4 5 6; 7 8 9; 1 2 3; 4 5 6; 7 8 9]
7×3 Matrix{Any}:
  "Header1"   "Header2"   "Header3"
 1           2           3
 4           5           6
 7           8           9
 1           2           3
 4           5           6
 7           8           9

julia> setFormula(s, "E1:G1", "=sort(unique(A2:A7),,-1)") # using dynamic array functions

[TimG1964]

I've created metadata.xml as a const so I can work with formulas. I don't know if it remains preferable to change it to an artifact but, for now, the use of a constant allows me to make the PR.

[TimG1964]

The build_artifacts.jl file in ./artifacts_scripts refers to this location "https://www.dropbox.com/s/9rgm3pnd1dmzmy9/xlsx_artifacts.tar.gz?dl=1" which is a bit confusing to me!

[felipenoris]

@TimG1964, this packages uses some artifacts to store static data. See https://github.com/JuliaData/XLSX.jl/blob/master/Artifacts.toml. They refer to this Release: https://github.com/JuliaData/XLSX.jl/releases/tag/artifact-v0.8.

All you have to do is to create a new Release pointing to the "artifact" branch (which is just an empty branch). From there, github lets you upload files manually to the release page. You'll see the files under the "Assets" section of the release page. Then, update the link and hash of these files in "Artifacts.toml".

[felipenoris]

The build_artifacts.jl file in ./artifacts_scripts refers to this location "https://www.dropbox.com/s/9rgm3pnd1dmzmy9/xlsx_artifacts.tar.gz?dl=1" which is a bit confusing to me!

I think this is just an "util" script to help update "Artifacts.toml" with file url and hash. This "dropbox" link is not used by the package.