Workbook sessions and recalculation.

A session makes a run of calls faster and keeps them consistent. Pass it to any function as opts.session. Persistent sessions save changes to the file; non-persistent ones discard them when the session ends, which suits what-if calculations. Excel expires idle sessions after about 5 minutes (persistent) or 7 minutes (non-persistent).

Values

CALCULATION_TYPES

CALCULATION_TYPES: Vec [
  "Recalculate",
  "Full",
  "FullRebuild"
]

Functions

calculate

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

POST /workbook/application/calculate. kind is Recalculate (the default), Full or FullRebuild; an Err for anything else.

Example

::microsoft::excel::workbooks/calculate("/Reports/Q3.xlsx")

// Rebuild every dependency, inside a session
::microsoft::excel::workbooks/calculate("/Reports/Q3.xlsx", "FullRebuild", {session: "s1"})

calculation-body

fn (kind: Str): Map

The calculate body (pure); an Err for a type other than Recalculate, Full or FullRebuild.

Example

::microsoft::excel::workbooks/calculation-body("Full")  // {calculationType: "Full"}
is-err(::microsoft::excel::workbooks/calculation-body("Everything"))  // true

close-session

fn (item: Any, session: Any): Map
fn (item: Any, session: Any, opts: Map): Map

POST /workbook/closeSession. session is an id or a session Map.

Example

session ::microsoft::excel::workbooks/create-session("/Reports/Q3.xlsx")
::microsoft::excel::workbooks/close-session("/Reports/Q3.xlsx", session)

create-session

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

POST /workbook/createSession. Returns {id, persistChanges}. Changes are saved unless opts.persist is false.

Example

session ::microsoft::excel::workbooks/create-session("/Reports/Q3.xlsx")
::microsoft::excel/read-values("/Reports/Q3.xlsx", "Data", "A1:B2", {session: session.id})
::microsoft::excel::workbooks/close-session("/Reports/Q3.xlsx", session)

// A what-if session: changes are discarded when it ends
::microsoft::excel::workbooks/create-session("/Reports/Q3.xlsx", {persist: false})

refresh-session

fn (item: Any, session: Any): Map
fn (item: Any, session: Any, opts: Map): Map

POST /workbook/refreshSession: keep an idle session alive. session is an id or a session Map.

Example

session ::microsoft::excel::workbooks/create-session("/Reports/Q3.xlsx")
::microsoft::excel::workbooks/refresh-session("/Reports/Q3.xlsx", session)

with-session

fn (item: Any, f: Fn): Any
fn (item: Any, opts: Map, f: Fn): Any

Open a session, call f with opts carrying it, close the session, and return what f returned, an Err included. A failure to close is ignored, since Excel expires the session on its own.

::excel ::microsoft::excel

::excel/with-session("/Reports/Q3.xlsx", (s) {
  ::excel/write-values("/Reports/Q3.xlsx", "Data", "A1", [["a", 1]], s)
  ::excel/read-values("/Reports/Q3.xlsx", "Data", "A1:B1", s)
})