Class SheetEngine

constructor

• new SheetEngine(source, options?): SheetEngine

• Parameters

  • source: SheetDataSourceIf

    the data

  • Optional options: Partial<CellFormulaOptionsIf> & {
        now?: (() => Date);
        random?: (() => number);
    } = {}

    own functions and limits; now/random for tests

  Returns SheetEngine

Readonly options

adjustForStructureChange

• adjustForStructureChange(edit): {
      col: number;
      input: string;
      row: number;
  }[]

• The new formula texts after rows or columns were inserted or deleted (or cells moved) – the caller writes them (the engine only reads the data). Cells are given at their position after the edit.

  Parameters

  • edit: SheetStructureEditIf

    what changed

  Returns {
      col: number;
      input: string;
      row: number;
  }[]

  changed formula texts

evaluate

• evaluate(source, at?): unknown

• Evaluates a formula once (not stored), e.g. for a formula bar preview.

  Parameters

  • source: string

    the formula ("=SUM(D2:D4)", the = is optional)

  • Optional at: readonly [number, number]

    the cell it is evaluated in (for [field])

  Returns unknown

  the result

  Example

  engine.evaluate("=SUM(D2:D4)");   // 3895
  Copy

getDependents

• getDependents(row, col): [number, number][]

• The formula cells that read a cell directly.

  Parameters

  • row: number

    physical row

  • col: number

    column

  Returns [number, number][]

  row and column of each dependent

getFormulaCount

• getFormulaCount(): number

• Returns number

  the number of formula cells

getPrecedents

• getPrecedents(row, col): CellRange[]

• The cells and ranges a formula reads.

  Parameters

  • row: number

    physical row

  • col: number

    column

  Returns CellRange[]

  its precedents (empty for a value cell)

getValue

• getValue(row, col): unknown

• The value of a cell: the result of its formula or its data value (Date for dates, a FormulaError for an error, '=… without the quote).

  Parameters

  • row: number

    physical row (0-based)

  • col: number

    column (0-based)

  Returns unknown

  the value

inputsChanged

• inputsChanged(cells): SheetRecalcResultIf

• The inputs of cells changed (written by the table): formulas are compiled again, the dependent cells recalculated.

  Parameters

  • cells: readonly (readonly [number, number])[]

    physical row and column of each changed cell

  Returns SheetRecalcResultIf

  what was recalculated

isFormula

• isFormula(row, col): boolean

• True if the cell holds a formula.

  Parameters

  • row: number

    physical row

  • col: number

    column

  Returns boolean

  true for a formula cell

rebuild

• rebuild(): SheetRecalcResultIf

• Reads all inputs again and calculates every formula (start, setRows, other columns).

  Returns SheetRecalcResultIf

  what was calculated

recalculate

• recalculate(): SheetRecalcResultIf

• Recalculates the volatile formulas (TODAY, NOW, RAND) and their dependents.

  Returns SheetRecalcResultIf

  what was recalculated

Static fromArrays

• fromArrays(data, options?): SheetEngine

• An engine over a 2-D array (rows of cells) – without a table.

  Parameters

  • data: unknown[][]

    the cells; formula texts start with =

  • Optional options: Partial<CellFormulaOptionsIf>

    own functions and limits

  Returns SheetEngine

  the engine, already calculated

  Example

  SheetEngine.fromArrays([[1, 2, "=A1+B1"]]).getValue(0, 2);   // 3
  Copy