Documentation

Excel cell or cell range reference (e.g. E3), possibly absolute. See 18.18.62 ST_Ref (p. 2482)

Note: The ST_Ref type can point to another sheet (supported) or a sheet in another workbook (separate .xlsx file, not implemented).

A helper type for coordinates to carry the intent of them being relative or absolute (preceded by $):

singleCellRefRaw' (RowRel 5, ColumnAbs 1) == "$A5"

Helper function to apply the same transformation to both members of a tuple

mapBoth Abs (1, 2) == (Abs 1, Abs 2s)

Render position in (row, col) format to an Excel reference.

singleCellRef (RowIndex 2, ColumnIndex 4) == CellRef "D2"

Allow specifying whether a coordinate parameter is relative or absolute.

singleCellRef' (Rel 5, Abs 1) == CellRef "$A5"

Converse function to singleCellRef Ignores a potential foreign sheet prefix.

Converse function to 'singleCellRef'' Ignores a potential foreign sheet prefix.

Converse function to singleCellRef expecting valid reference and failig with a standard error message like "Bad cell reference XXX"

Frame and escape the referenced sheet name in single quotes (apostrophe).

Sheet name in ST_Ref can be single-quoted when it contains non-alphanum class, non-ASCII range characters. Intermediate squote characters are escaped in a doubled fashion: "My ' Sheet" -> 'My '' Sheet'

Unframe and unescape the referenced sheet name.

Render a single cell existing in another worksheet. This function always renders the sheet name single-quoted regardless the presence of spaces. A sheet name shouldn't contain "[]*:?/" chars and apostrophe "'" should not happen at extremities.

mkForeignRange "MyOtherSheet" (Rel 2, Rel 4) (Abs 6, Abs 8) == "'MyOtherSheet'!D2:$H$6"

Converse function to mkForeignSingleCellRef. The provided CellRef must be a foreign range.

Excel range (e.g. D13:H14), actually store as as CellRef in xlsx

Render range

mkRange (RowIndex 2, ColumnIndex 4) (RowIndex 6, ColumnIndex 8) == CellRef "D2:H6"

Render range with possibly absolute coordinates

mkRange' (Abs 2, Abs 4) (6, 8) == CellRef "$D$2:H6"

Render a cell range existing in another worksheet. This function always renders the sheet name single-quoted regardless the presence of spaces. A sheet name shouldn't contain "[]*:?/" chars and apostrophe "'" should not happen at extremities.

mkForeignRange "MyOtherSheet" (Rel 2, Rel 4) (Abs 6, Abs 8) == "'MyOtherSheet'!D2:$H$6"

Converse function to mkRange ignoring absolute coordinates. Ignores a potential foreign sheet prefix.

Converse function to 'mkRange'' to handle possibly absolute coordinates. Ignores a potential foreign sheet prefix.

Converse function to mkForeignRange. The provided Range must be a foreign range.

A sequence of cell references

See 18.18.76 "ST_Sqref (Reference Sequence)" (p.2488)

Common type containing either simple string or rich formatted text. Used in si, comment and is elements

E.g. si spec says: "If the string is just a simple string with formatting applied at the cell level, then the String Item (si) should contain a single text element used to express the string. However, if the string in the cell is more complex - i.e., has formatting applied at the character level - then the string item shall consist of multiple rich text runs which collectively are used to express the string.". So we have either a single Text field, or else a list of RichTextRuns, each of which is some Text with layout properties.

TODO: Currently we do not support phoneticPr (Phonetic Properties, 18.4.3, p. 1723) or rPh (Phonetic Run, 18.4.6, p. 1725).

Section 18.4.8, "si (String Item)" (p. 1725)

See CT_Rst, p. 3903

A formula

See 18.18.35 "ST_Formula (Formula)" (p. 2457)

Cell values include text, numbers and booleans, standard includes date format also but actually dates are represented by numbers with a date format assigned to a cell containing it Specification (ECMA-376): - 18.3.1.4 c (Cell) - 18.18.11 ST_CellType (Cell Type)

The evaluation of an expression can result in an error having one of a number of error values.

See Annex L, L.2.16.8 "Error values" (p. 4764)

Specifies date base used for conversion of serial values to and from datetime values

See Annex L, L.2.16.9.1 "Date Conversion for Serial Values" (p. 4765)

Converts serial value into datetime according to the specified date base. Because Excel treats 1900 as a leap year even though it isn't, this function converts any numbers that represent some time in 1900-02-29 in Excel to UTCTime 1900-03-01 00:00. See https://docs.microsoft.com/en-gb/office/troubleshoot/excel/wrongly-assumes-1900-is-leap-year for details.

show (dateFromNumber DateBase1900 42929.75) == "2017-07-13 18:00:00 UTC"
show (dateFromNumber DateBase1900 60) == "1900-03-01 00:00:00 UTC"
show (dateFromNumber DateBase1900 61) == "1900-03-01 00:00:00 UTC"

Converts datetime into serial value. Because Excel treats 1900 as a leap year even though it isn't, the numbers that represent times in 1900-02-29 in Excel, in the range [60, 61[, are never generated by this function for DateBase1900. This means that under those conditions this is not an inverse of dateFromNumber. See https://docs.microsoft.com/en-gb/office/troubleshoot/excel/wrongly-assumes-1900-is-leap-year for details.

convert column number (starting from 1) to its textual form (e.g. 3 -> "C")

reverse of columnIndexToText