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"})