Using Formulas

XLSX.jl provides two functions allowing direct access to cell formulas, XLSX.getFormula and setFormula.

Using a simple formula

Find the formula in a cell using the XLSX.getFormula function. This returns a string representation of the function used in the specified cell (e.g. "=A1+B1"). For most standard functions, this is the same as the representation of the same formula in the Excel formula bar (but see the section on Newer Functions (below) for exceptions).

Similarly, set a formula in a cell using the setFormula function, entering the function exactly as it would appear in the Excel formula bar.

To set a formula, it must be a valid Excel formula and written in US english with a comma separator. 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 the formula will be accepted as given.

For example:

julia> using XLSX

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

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

julia> s[1:4, 1]=1:4
1:4

julia> setFormula(s, "A5", "=sum(A1:A4)") # function names are case insensitive. Excel converts them all to uppercase.
"=sum(A1:A4)"

julia> XLSX.getFormula(s, "A5")
"=sum(A1:A4)"

Cell A5 showing the result of =SUM(A1:A4) in Excel

Special characters need escaping in the usual way, so that absolute references must be written as, for example, \$A\$1. Similarly, when entering text strings into formulas, quotation marks need to be escaped. For example:

julia> setFormula(s, "B1", "=hyperlink(\"https://www.bbc.co.uk/news\", \"BBC News\")")
"=hyperlink(\"https://www.bbc.co.uk/news\", \"BBC News\")"

julia> XLSX.getFormula(s, "B1")
"=hyperlink(\"https://www.bbc.co.uk/news\", \"BBC News\")"

A cell showing a BBC News link made by the HYPERLINK function

Valid formulae of arbitrary complexity and degree of nesting are supported.

julia> setFormula(s, "B1", "=MID(A1, FIND(\"-\",A1)+1, FIND(\"-\",A1, FIND(\"-\",A1)))")
"=MID(A1, FIND(\"-\",A1)+1, FIND(\"-\",A1, FIND(\"-\",A1)))"

julia> setFormula(s, "B2", "AND(ROUNDDOWN(A1,0)-TODAY()>(7-WEEKDAY(TODAY())),ROUNDDOWN(A1,0)-TODAY()<(15-WEEKDAY(TODAY())))")
"AND(ROUNDDOWN(A1,0)-TODAY()>(7-WEEKDAY(TODAY())),ROUNDDOWN(A1,0)-TODAY()<(15-WEEKDAY(TODAY())))"

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, unless you give it (see Cached values). The saved XLSXFile is set to force Excel to re-calculate on opening.

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.getFormula(s, "B2")
"=A2+B1"

julia> XLSX.getFormula(s, "J10")
"=I10+J9"

Cached values

Excel saves the last value each formula calculated alongside it, and other programs (pandas, file previews, XLSX.jl itself) read that value rather than calculating the formula. If you know a formula's result, pass it as value, and it is saved as Excel would save it, so the cell reads as that value rather than missing:

julia> s = XLSX.newxlsx()[1];

julia> s["A1:A3"] = [10, 20, 30];

julia> XLSX.setFormula(s, "B1", "=SUM(A1:A3)"; value=60)
"=SUM(A1:A3)"

julia> s["B1"]
60

value may be a number, string, Bool, Date, DateTime or Time (formatted as a date or time, as when written to a cell), or an error value such as XLSX.XL_NA. For a range, give one value for every cell, or an array with the range's shape:

julia> XLSX.setFormula(s, "C1:C3", "=A1*2"; value=[20, 40, 60]);

julia> XLSX.setFormula(s, "D1:D3", "=A1/0"; value=XLSX.XL_DIV0);

julia> s["C3"], XLSX.geterror(s, "D2")
(60, "#DIV/0!")

XLSX.jl does not check the value against the formula, which it can't calculate. Excel still recalculates every formula when it opens the file, so a wrong value is shown only until then, and only by other programs. A workbook set to manual calculation keeps that setting, and Excel then recalculates only when asked (F9).

Referenced formulas

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.

The first cell in the range (reference cell) contains the formula and the other cells in the range contain a reference to the reference cell. The formula returned by XLSX.getFormula will be adjusted appropriately to allow for the offset from the reference cell. This will match what is shown in the formula bar for this cell in Excel.

julia> s[1:4, 1]=1:4
1:4

julia> setFormula(s, "B1", "=A1")
"=A1"

julia> setFormula(s, "B2:B4", "=B1+A2") # creates a running total
"=B1+A2"

julia> XLSX.getFormula(s, "B2")
"=B1+A2"

julia> XLSX.getFormula(s, "B4") # returned formula is adjusted for offset
"=B3+A4"

Unlike with conventional functions, dynamic array functions (e.g. SORT or UNIQUE) should not be put into a ReferencedFormula 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.

External references

An existing formula may contain references to cells in external workbooks, in the form [index]SheetName!A1 where index is an integer providing an internal Excel reference to the external workbook. XLSX.getFormula can be used to obtain the file name of the external reference using the keyword option get_external_refs=true to replace the index with the actual workbook path (as stored in the workbook's externalReferences). By default, get_external_refs=false and the formula is returned unchanged.

julia> XLSX.getFormula(s, "B1")
"[1]Sheet1!\$A\$1"

julia> XLSX.getFormula(s, "B1"; get_external_refs=true)
"[https://d.docs.live.net/.../Documents/Julia/XLSX/linked-2.xlsx]Sheet1!\$A\$1"

It is not currently possible to use setFormula to create a formula that references a worksheet in an external file.

Newer functions

Microsoft continues to add new functions to Excel with increasing complexity. Some of these are not represented straightforwardly in the internal xml files using the representation shown in the formula bar like "traditional" formulas are. Instead, these need function prefixes when written to file. For example:

julia> s[1:5, 1]=rand(5)
5-element Vector{Float64}:
 0.2781977934853824
 0.3063671990614626
 0.44046282567557327
 0.38515484505427067
 0.37629936008012144

julia> setFormula(s, "A6", "sum(A1:A5)") # traditional function
"sum(A1:A5)"

julia> setFormula(s, "B1", "sort(A1:A5)") # needs a prefix. `setFormula` adds this transparently to the user.
"_xlfn.SORT(A1:A5)"

julia> setBorder(s, "A6";top=["style"=>"double"])
1

julia> writexlsx("mytest.xlsx", f, overwrite=true)
"C:\\Users\\...\\Julia\\XLSX\\mytest.xlsx"

The prefix is not shown by Excel in the formula bar, but it is needed in the internal xml file. setFormula adds this prefix automatically. A dummy ref xml attribute is also created by setFormula and will be properly calculated on opening when Excel determines the spill range.

Cell B1 holding =SORT(A1:A5), its result spilling down to B5

<c r="B1" cm="1">
  <f t="array" ref="B1:B1">_xlfn.SORT(A1:A5)</f> # prefix `_xlfn.` and `t` and `ref` attributes required by Excel
</c>

<c r="A6" s="1">
  <f>sum(A1:A5)</f> # simple representation as shown in the formula bar
</c>

The functions GROUPBY and PIVOTBY need an aggregation function as their 3rd/4th argument respectively. Currently only simple aggregator functions are supported (e.g. SUM, AVERAGE, COUNT, COUNTA, MIN, MAX, MEDIAN, PRODUCT, STDEV, STDEVP, VAR and VARP) by default (i.e. with raw=false).

More complex versions of theses functions and some of the other newer Excel functions are not easy to parse. For example, Excel's LAMBDA function is not well supported (in GROUPBY/PIVOTBY or otherwise). Moreover, as Microsoft adds more new functions in future that require a prefix or that generate a spill range (or both), setFormula may not recognise them automatically.

For these cases, a keyword option, raw, is provided (default=false), allowing the internal xml representation of a function (which may differ significantly from its representation in the formula bar) to be entered directly. This allows any arbitrary future function to be added using setFormula. An additional keyword, spill (default=nothing), can be used to tell setFormula that the raw function will create a spill range (or to force it not to). The simplest way to determine the raw representation of any arbitrary function is to create it in Excel and then to inspect the resulting xml file. For example:

setFormula(f[1], "H21", "_xlfn.GROUPBY(E1:E151,A1:D151,_xlfn.LAMBDA(_xlpm.x,AVERAGE(_xlpm.x)),3,1)"; raw=true)
# setFormula recognises the `GROUPBY` function so, in this case, it is not necessary to specify `spill=true`

Spill ranges

Some functions, such as SORT or UNIQUE, will return multiple values and Excel will "spill" these into a spill range the extent of which depends on the data on which the function is operating. Generally, setFormula will handle this transparently and create a cell formula that spills when needed by adding an attribute t="array" to the cell in the internal xml file. For example

julia> s[1:5, 1]=rand(5)
5-element Vector{Float64}:
 0.1826020207094764
 0.8336742268414907
 0.773953482649498
 0.9739450126819114
 0.6627007761343171

julia> s[1:5, 2]="" # make sure these aren't EmptyCells so spill range can be formatted
""

julia> setFormula(s, "B1", "=sort(A1:A5)") # cell B1 contains the formula but the result spills down to B5
"=_xlfn.SORT(A1:A5)"

julia> setFormat(s, "A1:B5";format="Number")
-1

A SORT formula in B1 spilling sorted values down to B5

Even simple formulas can be made to spill in recent versions of Excel. For example, any cell given the formula "=A1:A5" would produce a 5-element result and would spill to fill 5 cells.

You may include a spill range in a formula using the base cell name with a # suffix, and this is treated as a reference to the full spill range. For example, to add an extra column to the above table, use setFormula(s, "C1", "=2*B1#") just as in Excel. The result will spill to fill cells C1:C5.

If a function Excel expects to return a spill range does not have attribute t="array" set, Excel will automatically create an "implicit intersection" when it opens the file by adding an @ prefix to the function name. The @ symbol tells Excel to return a single value from a range or array, based on the context of the formula. For example, if a formula references a column of values but is entered in a single cell, Excel uses @ to pick the value from the same row as the formula.

For most formulas, setFormula will determine the correct attribute value for t. In cases where it doesn't (usually when using raw=true), it is possible to force the formula to indicate (to Excel) that it spills using the spill=true keyword option as described above. Similarly, in the unlikely event that setFormula might choose to create a spill function incorrectly, this can be prevented with spill=false.

Only rarely in complex, nested formulae involving cell range references, aggregator functions and/or dynamic array functions, and for any function that spills as a direct result of using a named cell range, should it be necessary to use the raw keyword option described above.

Note

Excel is often very fussy about the internal structure of an xlsx file but the resulting error messages (when Excel tries to open a file it considers malformed) may be somewhat cryptic. If there is an error in the formula you enter, it may not be clear what it is from the error Excel produces (as shown below). A safe fall back may be to test the formula in Excel itself and copy/paste it into julia. Alternatively, copy the formula directly from the xml representation of a working Excel file and use the raw=true keyword option (and spill=true if the function used creates a spill range).

For example:


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

julia> f[1][1:3, 1]=collect(1:3)
3-element Vector{Int64}:
1
2
3

julia> setFormula(f[1], "B1", "=max(A1:A3") # missing closing parenthesis in the formula
"=max(A1:A3"

julia> XLSX.getFormula(f[1], "B1")
"=max(A1:A3"


julia> writexlsx("mytest.xlsx", f, overwrite=true)
"C:\\Users\\...\\Julia\\XLSX\\mytest.xlsx"

Excel's message that it found a problem with some content in the file Excel's report of the repairs it made to the file

Ignoring error-checking warnings

Excel marks cells it suspects with a green triangle: a number stored as text, a formula unlike its neighbours, a formula that leaves out cells beside its range, and so on. XLSX.setIgnoredErrors turns these warnings off for some cells, as Excel's Ignore Error does, which is useful when, for example, a column of codes is deliberately kept as text:

Two columns of the codes 007, 042, 100 and 315 kept as text. In the first, each cell has Excel's green error triangle in its corner; in the second, where numberStoredAsText is ignored, there are none

julia> s = XLSX.newxlsx()[1];

julia> s["A1:A3"] = ["007", "042", "100"];

julia> XLSX.setIgnoredErrors(s, "A1:A3"; numberStoredAsText=true)

julia> XLSX.setIgnoredErrors(s, "A2"; evalError=true)

julia> XLSX.getIgnoredErrors(s)
2-element Vector{Pair{String, Vector{Symbol}}}:
 "A1,A3" => [:numberStoredAsText]
    "A2" => [:evalError, :numberStoredAsText]

The warnings are evalError, twoDigitTextYear, numberStoredAsText, formula, formulaRange, unlockedFormula, emptyCellReference, listDataValidation and calculatedColumn. Giving one as false shows it again. As Excel does, each cell is listed once, with all the warnings ignored in it, so ranges are split where the warnings of their cells differ. Whole columns ("B:B") and rows reach the edges of the sheet. XLSX.getIgnoredErrors also gives one cell's warnings, and XLSX.removeIgnoredErrors shows all the warnings again, in a range or the whole sheet. The ranges move as rows and columns are inserted and deleted.

See also