Excel tables: list, create, update and delete, columns and rows, and rows as records. A table is addressed by name or id. Tables are the reliable way to append: Excel finds the end for you.
Functions
add-rows
fn (item: Any, table: Str, rows: Vec): Map
fn (item: Any, table: Str, rows: Vec, opts: Map): Map
POST /workbook/tables/{table}/rows/add: add rows at the end, or at
opts.index. Send many rows in one call; adding them one at a time is
slow. Not idempotent, so a blind retry can duplicate rows.
Example
::microsoft::excel::tables/add-rows("/Reports/Q3.xlsx", "Leads", [["Ada", "demo"], ["Grace", "trial"]])
// At the top of the table
::microsoft::excel::tables/add-rows("/Reports/Q3.xlsx", "Leads", [["Linus", "new"]], {index: 0})
add-rows-body
fn (rows: Vec, opts: Map): Map
The rows/add body (pure): values, plus index from opts.index
when set.
Example
::microsoft::excel::tables/add-rows-body([["Ada", 9]], {}) // {values: [["Ada", 9]]}
::microsoft::excel::tables/add-rows-body([["Ada", 9]], {index: 0}) // {values: [["Ada", 9]], index: 0}
append-records
fn (item: Any, table: Str, records: Vec): Map
fn (item: Any, table: Str, records: Vec, columns: Vec?): Map
fn (item: Any, table: Str, records: Vec, columns: Vec?, opts: Map): Map
Add records as table rows. columns sets the cell order; by default
the table's own columns are fetched. Missing values are empty cells.
An empty Vec makes no call and returns {}.
::microsoft::excel::tables/append-records("/Reports/Q3.xlsx", "Leads", [{name: "Ada", stage: "demo"}])
column-names
fn (item: Any, table: Str): Vec
fn (item: Any, table: Str, opts: Map): Vec
A table's column names, in order.
Example
names ::microsoft::excel::tables/column-names("/Reports/Q3.xlsx", "Leads")
length(names)
convert-to-range
fn (item: Any, table: Str): Map
fn (item: Any, table: Str, opts: Map): Map
POST /workbook/tables/{table}/convertToRange: remove the table and keep its cells.
Example
::microsoft::excel::tables/convert-to-range("/Reports/Q3.xlsx", "Leads")
create-table
fn (item: Any, sheet: Str, address: Str): Map
fn (item: Any, sheet: Str, address: Str, opts: Map): Map
POST /worksheets/{sheet}/tables/add: turn a range into a table. The
first row is the header unless opts.has-headers is false. Returns the
table; use rename-table to name it.
Example
table ::microsoft::excel::tables/create-table("/Reports/Q3.xlsx", "Pipeline", "A1:B2")
::microsoft::excel::tables/rename-table("/Reports/Q3.xlsx", table.id, "PipelineTable")
// A range with no header row
::microsoft::excel::tables/create-table("/Reports/Q3.xlsx", "Pipeline", "D1:E9", {has-headers: false})
create-table-body
fn (sheet: Str, address: Str, opts: Map): Map
The tables/add body (pure): the address qualified with its sheet, and
hasHeaders from opts.has-headers (default true).
Example
::microsoft::excel::tables/create-table-body("Q3 Plan", "A1:B5", {}) // {address: "'Q3 Plan'!A1:B5", hasHeaders: true}
::microsoft::excel::tables/create-table-body("Data", "A1:B5", {has-headers: false}) // {address: "Data!A1:B5", hasHeaders: false}
delete-row
fn (item: Any, table: Str, index: Int): Map
fn (item: Any, table: Str, index: Int, opts: Map): Map
DELETE a table row by its 0-based index. Later rows move up, so delete
from the highest index first. Returns {}.
Example
::microsoft::excel::tables/delete-row("/Reports/Q3.xlsx", "Leads", 3)
delete-table
fn (item: Any, table: Str): Map
fn (item: Any, table: Str, opts: Map): Map
DELETE /workbook/tables/{table}: delete the table and its data. Returns
{}.
Example
::microsoft::excel::tables/delete-table("/Reports/Q3.xlsx", "Leads")
get-table
fn (item: Any, table: Str): Map
fn (item: Any, table: Str, opts: Map): Map
GET /workbook/tables/{table}.
Example
table ::microsoft::excel::tables/get-table("/Reports/Q3.xlsx", "Leads")
table.showTotals
list-rows
fn (item: Any, table: Str): Vec
fn (item: Any, table: Str, opts: Map): Vec
GET /workbook/tables/{table}/rows. Returns a Vec of {index, values},
with values one row inside a grid. Pass top and skip to page.
Example
page ::microsoft::excel::tables/list-rows("/Reports/Q3.xlsx", "Leads", {top: 50})
first(first(page).values)
// The next page
::microsoft::excel::tables/list-rows("/Reports/Q3.xlsx", "Leads", {top: 50, skip: 50})
list-tables
fn (item: Any): Vec
fn (item: Any, opts: Map): Vec
GET /workbook/tables, or a worksheet's tables with opts.sheet. Returns
a Vec of tables.
Example
tables ::microsoft::excel::tables/list-tables("/Reports/Q3.xlsx")
map(tables, %.name)
// One worksheet's tables
::microsoft::excel::tables/list-tables("/Reports/Q3.xlsx", {sheet: "Pipeline"})
read-table-records
fn (item: Any, table: Str): Vec
fn (item: Any, table: Str, opts: Map): Vec
A table's data as records keyed by its column names. Blank cells are
null and fully blank rows are skipped, so an empty table gives [].
It always reads the whole table; use list-rows to page.
Example
leads ::microsoft::excel::tables/read-table-records("/Reports/Q3.xlsx", "Leads")
map(leads, %.name)
// A workbook in another drive
::microsoft::excel::tables/read-table-records("01ABC", "Leads", {drive: "b1"})
rename-table
fn (item: Any, table: Str, name: Str): Map
fn (item: Any, table: Str, name: Str, opts: Map): Map
Rename a table.
Example
::microsoft::excel::tables/rename-table("/Reports/Q3.xlsx", "Table1", "Leads")
row-path
fn (item: Any, table: Str, index: Int, opts: Map): Str
The Graph path of a table row by its 0-based index (pure).
Example
::microsoft::excel::tables/row-path("01ABC", "Leads", 3, {drive: "b1"}) // "/drives/b1/items/01ABC/workbook/tables/Leads/rows/3"
update-table
fn (item: Any, table: Str, changes: Map): Map
fn (item: Any, table: Str, changes: Map, opts: Map): Map
PATCH /workbook/tables/{table}. changes holds name, showHeaders,
showTotals, style, ...
Example
::microsoft::excel::tables/update-table("/Reports/Q3.xlsx", "Leads", {showTotals: true, style: "TableStyleMedium2"})