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}]