Cell ranges: read and write values and formulas by A1 address, used ranges, clearing, inserting and deleting cells, and rows as records.

Excel returns blank cells as "" and dates as serial numbers (use read-formatted for what the cell shows). When writing, "" empties a cell, null leaves it unchanged, and a string starting with = is a formula.

Functions

clear-body

fn (opts: Map): Map

The clear body (pure): opts.apply-to is All (the default), Formats or Contents.

Example

::microsoft::excel::ranges/clear-body({})  // {applyTo: "All"}
::microsoft::excel::ranges/clear-body({apply-to: "Contents"})  // {applyTo: "Contents"}

clear-range

fn (item: Any, sheet: Str, address: Str): Map
fn (item: Any, sheet: Str, address: Str, opts: Map): Map

POST /range(...)/clear. Clears values and formatting unless opts.apply-to is Formats or Contents.

Example

::microsoft::excel::ranges/clear-range("/Reports/Q3.xlsx", "Data", "A2:C100")

// Keep the formatting
::microsoft::excel::ranges/clear-range("/Reports/Q3.xlsx", "Data", "A2:C100", {apply-to: "Contents"})

delete-cells

fn (item: Any, sheet: Str, address: Str, shift: Str): Map
fn (item: Any, sheet: Str, address: Str, shift: Str, opts: Map): Map

POST /range(...)/delete. Delete the cells at address, moving the rest Up or Left.

Example

// Remove three cells and close the gap
::microsoft::excel::ranges/delete-cells("/Reports/Q3.xlsx", "Data", "A2:C2", "Up")

get-range

fn (item: Any, sheet: Str, address: Str): Map
fn (item: Any, sheet: Str, address: Str, opts: Map): Map

GET /worksheets/{sheet}/range(address='...'). Returns the range: address, values, formulas, text, numberFormat, rowCount, columnCount, ... Pass {select: "values,address"} to trim it.

Example

cells ::microsoft::excel::ranges/get-range("/Reports/Q3.xlsx", "Data", "A1:C10")
cells.rowCount

// Only the fields you need
::microsoft::excel::ranges/get-range("/Reports/Q3.xlsx", "Data", "A1:C10", {select: "values,address"})

insert-cells

fn (item: Any, sheet: Str, address: Str, shift: Str): Map
fn (item: Any, sheet: Str, address: Str, shift: Str, opts: Map): Map

POST /range(...)/insert. Insert blank cells over address, moving existing cells Down or Right.

Example

// A blank row of three cells above the data
::microsoft::excel::ranges/insert-cells("/Reports/Q3.xlsx", "Data", "A2:C2", "Down")

read-formatted

fn (item: Any, sheet: Str, address: Str): Vec
fn (item: Any, sheet: Str, address: Str, opts: Map): Vec

A range's cells as the text they display, as a grid of Strs.

Example

// Dates as the cell shows them, not as serial numbers
shown ::microsoft::excel::ranges/read-formatted("/Reports/Q3.xlsx", "Data", "A2:A5")
first(shown)

read-formulas

fn (item: Any, sheet: Str, address: Str): Vec
fn (item: Any, sheet: Str, address: Str, opts: Map): Vec

A range's formulas as a grid; cells without a formula hold their value.

Example

formulas ::microsoft::excel::ranges/read-formulas("/Reports/Q3.xlsx", "Summary", "B2:B5")
first(formulas)

read-records

fn (item: Any, sheet: Str): Vec
fn (item: Any, sheet: Str, opts: Map): Vec

A worksheet as records, one Map per row keyed by the first row. Reads the used range unless opts.address names a range. Blank cells are null and fully blank rows are skipped.

leads ::microsoft::excel::ranges/read-records("/Reports/Q3.xlsx", "Leads")

read-values

fn (item: Any, sheet: Str, address: Str): Vec
fn (item: Any, sheet: Str, address: Str, opts: Map): Vec

A range's values as a grid (a Vec of rows).

Example

grid ::microsoft::excel::ranges/read-values("/Reports/Q3.xlsx", "Data", "A1:C10")
first(grid)

// A workbook in another drive
::microsoft::excel::ranges/read-values("01ABC", "Data", "A1:C10", {drive: "b1"})

update-range

fn (item: Any, sheet: Str, address: Str, changes: Map): Map
fn (item: Any, sheet: Str, address: Str, changes: Map, opts: Map): Map

PATCH /worksheets/{sheet}/range(address='...'). changes holds values, formulas or numberFormat, each a grid matching the address's size.

Example

::microsoft::excel::ranges/update-range("/Reports/Q3.xlsx", "Data", "A1:B1", {values: [["Name", "Score"]]})

// Each field is a grid the size of the address
::microsoft::excel::ranges/update-range("/Reports/Q3.xlsx", "Data", "B2:B3", {numberFormat: [["0.00"], ["0.00"]]}, {session: "s1"})

used-range

fn (item: Any, sheet: Str): Map
fn (item: Any, sheet: Str, opts: Map): Map

GET /worksheets/{sheet}/usedRange: the smallest range holding every used cell.

Example

used ::microsoft::excel::ranges/used-range("/Reports/Q3.xlsx", "Data")
used.address

// Skip cells that only have formatting, and trim the response
::microsoft::excel::ranges/used-range("/Reports/Q3.xlsx", "Data", {values-only: true, select: "address,values"})

used-range-path

fn (item: Any, sheet: Str, opts: Map): Str

The Graph path of a worksheet's used range (pure). With opts.values-only, cells that only have formatting are left out.

Example

::microsoft::excel::ranges/used-range-path("01ABC", "Data", {drive: "b1"})
// "/drives/b1/items/01ABC/workbook/worksheets/Data/usedRange"

::microsoft::excel::ranges/used-range-path("01ABC", "Data", {drive: "b1", values-only: true})
// "/drives/b1/items/01ABC/workbook/worksheets/Data/usedRange(valuesOnly=true)"

write-formulas

fn (item: Any, sheet: Str, address: Str, grid: Vec): Map
fn (item: Any, sheet: Str, address: Str, grid: Vec, opts: Map): Map

Write a grid of formulas, addressed as in write-values.

Example

::microsoft::excel::ranges/write-formulas("/Reports/Q3.xlsx", "Summary", "B2", [["=SUM(Data!C:C)"], ["=AVERAGE(Data!C:C)"]])

write-records

fn (item: Any, sheet: Str, address: Str, records: Vec): Map
fn (item: Any, sheet: Str, address: Str, records: Vec, columns: Vec?): Map
fn (item: Any, sheet: Str, address: Str, records: Vec, columns: Vec?, opts: Map): Map

Write records as a header row and data rows from a top-left cell. columns sets the order (default, or null: every key in order of first appearance). Missing values empty their cells. No records and no columns make no call.

Example

leads [{name: "Ada", stage: "demo"}, {name: "Grace", stage: "trial"}]
::microsoft::excel::ranges/write-records("/Reports/Q3.xlsx", "Pipeline", "A1", leads)

// Choose the columns and their order
::microsoft::excel::ranges/write-records("/Reports/Q3.xlsx", "Pipeline", "A1", leads, ["stage", "name"])

// With a session, keeping the default columns
::microsoft::excel::ranges/write-records("/Reports/Q3.xlsx", "Pipeline", "A1", leads, null, {session: "s1"})

write-values

fn (item: Any, sheet: Str, address: Str, grid: Vec): Map
fn (item: Any, sheet: Str, address: Str, grid: Vec, opts: Map): Map

Write a grid to a worksheet. address is a full range, or a single top-left cell that is expanded to the grid's size. Short rows are padded with null, which leaves those cells unchanged. An empty grid makes no call and returns {}.

::microsoft::excel::ranges/write-values("/Reports/Q3.xlsx", "Data", "B2", [["a", 1], ["b", 2]])