Convert between Excel value grids ([[...], ...], rows of cells) and
records (one Map per row, keyed by the header row), plus A1 notation
helpers. All functions are pure.
Values
LETTERS
LETTERS: Str "ABCDEFGHIJKLMNOPQRSTUVWXYZ"
Functions
a1
fn (sheet: Str, address: Str): Str
An A1 range on a sheet: a1("Q3 Plan", "A1:C10") is 'Q3 Plan'!A1:C10.
Example
::microsoft::excel::records/a1("Data", "A1:C10") // "Data!A1:C10"
::microsoft::excel::records/a1("Q3 Plan", "A1:C10") // "'Q3 Plan'!A1:C10"
cell-value
fn (value: Any): Any
A record value as a cell: Str, Int, Dec and Bool pass through; null
becomes "" (an empty cell); Maps and Vecs become JSON text.
Example
::microsoft::excel::records/cell-value(9) // 9
::microsoft::excel::records/cell-value(null) // ""
::microsoft::excel::records/cell-value(["a"]) // "[\"a\"]"
column-index
fn (letters: Str): Int
The 0-based column index for A1 letters, in either case: A is 0, AA
is 26.
Example
::microsoft::excel::records/column-index("A") // 0
::microsoft::excel::records/column-index("aa") // 26
column-letter
fn (index: Int): Str
The A1 letters for a 0-based column index: 0 is A, 25 is Z, 26 is
AA, 701 is ZZ.
Example
::microsoft::excel::records/column-letter(0) // "A"
::microsoft::excel::records/column-letter(26) // "AA"
expand-address
fn (address: Str, rows: Int, columns: Int): Str
A single top-left cell expanded to rows by columns cells:
expand-address("B2", 2, 3) is B2:D3. Anything else, such as a
full range or a named range, is returned unchanged.
Example
::microsoft::excel::records/expand-address("B2", 2, 3) // "B2:D3"
::microsoft::excel::records/expand-address("A1:C3", 9, 9) // "A1:C3"
header-names
fn (header: Vec): Vec
The header row as column names: each cell as a trimmed Str; blank headers become null (their column is skipped).
Example
::microsoft::excel::records/header-names(["name", " score ", ""]) // ["name", "score", null]
is-cell
fn (address: Str): Bool
True for a single A1 cell such as B2.
Example
::microsoft::excel::records/is-cell("B2") // true
::microsoft::excel::records/is-cell("A1:C3") // false
quote-sheet
fn (name: Str): Str
A sheet name quoted for A1 notation: Sheet1 stays as is; "Q3 Plan"
becomes 'Q3 Plan' and a ' inside is doubled.
Example
::microsoft::excel::records/quote-sheet("Sheet1") // "Sheet1"
::microsoft::excel::records/quote-sheet("Q3 Plan") // "'Q3 Plan'"
::microsoft::excel::records/quote-sheet("Bob's") // "'Bob''s'"
record-columns
fn (records: Vec): Vec
The column names for records: every key, in order of first appearance.
Example
::microsoft::excel::records/record-columns([{name: "Ada"}, {name: "Bob", stage: "demo"}]) // ["name", "stage"]
records-to-grid
fn (records: Vec): Vec
fn (records: Vec, columns: Vec): Vec
Records as a full grid: the header row (columns, default every key)
followed by records-to-rows.
Example
::microsoft::excel::records/records-to-grid([{name: "Ada", score: 9}]) // [["name", "score"], ["Ada", 9]]
::microsoft::excel::records/records-to-grid([{name: "Ada", score: 9}], ["score"]) // [["score"], [9]]
records-to-rows
fn (records: Vec): Vec
fn (records: Vec, columns: Vec): Vec
Records as value rows ordered by columns (default: every key in order
of first appearance), without a header. Missing values become "".
Example
::microsoft::excel::records/records-to-rows([{name: "Ada", score: 9}, {name: "Bob"}]) // [["Ada", 9], ["Bob", ""]]
::microsoft::excel::records/records-to-rows([{name: "Ada", score: 9}], ["score", "name"]) // [[9, "Ada"]]
rectangular
fn (grid: Vec): Vec
A grid with every row padded with null to the longest row's length, as Excel requires.
Example
::microsoft::excel::records/rectangular([[1, 2], [3]]) // [[1, 2], [3, null]]
row-to-record
fn (columns: Vec, row: Vec): Map
One row as a Map keyed by columns (from header-names); blank and
missing cells are null and null-named columns are skipped.
Example
::microsoft::excel::records/row-to-record(["name", null, "score"], ["Ada", "x", ""]) // {name: "Ada", score: null}
rows-to-records
fn (values: Vec?): Vec
A value grid whose first row is a header, as a Vec of Maps. Fully
blank rows are skipped. A missing or empty grid gives [].
::microsoft::excel::records/rows-to-records([["name", "score"], ["Ada", 9], ["Bob", ""]])
// [{name: "Ada", score: 9}, {name: "Bob", score: null}]