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