Home > ApexGrid Docs > Docs > Formula Engine (Enterprise)

Formula Engine (Enterprise)

apex-grid-enterprise includes a spreadsheet-style formula engine: cells can hold = expressions that reference other cells and ranges, recalculate on data changes, and export as either their computed value or their source.

Enabling formulas on a column

Mark a column allowFormula (and editable) so its cells accept = expressions and use the built-in formula editor:

grid.columns = [
  { key: 'qty', headerText: 'Qty', type: 'number', editable: true },
  { key: 'price', headerText: 'Price', type: 'currency', editable: true },
  { key: 'total', headerText: 'Total', type: 'currency', editable: true, allowFormula: true },
];

References are A1-style and positional: columns map to letters by configuration order (A is the first column, including hidden ones) and rows are 1-based over the source data. With the columns above, qty is A, price is B, and total is C, so the Total cell in the first row is =A1*B1. Because the computed result stays canonical in row[key], sorting, filtering, aggregation, export, and charts keep working on the value.

Setting and reading formulas

MethodDescription
setFormula(row, columnKey, formula)Set a cell's formula source (e.g. '=A1*B1')
getFormula(row, columnKey)Return a cell's formula source, or undefined
clearFormula(row, columnKey)Remove a cell's formula
recalculateFormulas()Force a full recalculation
grid.setFormula(grid.data[0], 'total', '=A1*B1');
const src = grid.getFormula(grid.data[0], 'total');   // '=A1*B1'

Authoring formulas by mouse

While editing a formula cell (after typing =), you can build references without hand-typing them:

  • Autocomplete: typing a function name shows a suggestion list; Up/Down to move, Enter/Tab or click to accept, Escape to dismiss.
  • Click-to-insert: click any grid cell to insert its reference at the caret; Shift-click inserts an absolute $A$1.
  • Drag-to-insert a range: press on a cell and drag across the grid to insert a live A1:C3 range reference, with a dashed marching-ants marquee tracking the cells.
  • Re-pick: clicking again replaces the just-picked reference (type an operator to add a second one); Escape backs out of a just-picked reference without leaving the edit.
  • F4 absolute/relative: with the caret on a reference, press F4 to cycle its $ markers: A1 → $A$1 → A$1 → $A1 → A1.

Point-and-click reference entry is mouse-driven: while editing, the arrow keys always move the text caret and never start referencing cells. Every referenced cell also lights up in its own color while the editor is open (a data-formula-ref decoration you can theme).

Show-formulas view

Toggle a spreadsheet-style "show formulas" view that displays each allowFormula cell's source instead of its computed value. Computed values are untouched, so turning it off restores the normal display:

grid.showFormulas = true;
// or in markup: <apex-grid-enterprise show-formulas>

A user-provided cellTemplate is always respected and never overridden.

Custom functions

Register your own formula functions by name:

grid.registerFormulaFunction('MARGIN', (args) => {
  const [revenue, cost] = args;
  return (revenue - cost) / revenue;
});

// A = revenue column, B = cost column
grid.setFormula(grid.data[0], 'margin', '=MARGIN(A1, B1)');

The engine ships a set of built-in functions (exported as BUILTIN_FUNCTION_NAMES). Custom functions registered by name extend that set. From 0.8 a name may contain digits, underscores and dots (LOG10, VAT_RATE, STDEV.S), and registerFormulaFunction throws a TypeError for a name no formula could call, including IF, TRUE and FALSE. A custom function that throws shows #VALUE! in its cell instead of stopping recalculation.

References

Formulas reference other cells and ranges. The engine exposes A1-style helpers and a parser/evaluator for advanced use:

import { parseFormula, evaluate } from 'apex-grid-enterprise';

const ast = parseFormula('=SUM(A1:A10)');

Errors surface as a typed FormulaError with a FormulaErrorCode (name, ref, value, div/0, cycle) so you can present spreadsheet-style error cells. Error values are first-class: #REF!, #NAME?, #DIV/0!, #VALUE!, and #CYCLE! render as their code (even in typed number / currency columns) and are excluded from numeric aggregates, and an error operand propagates. IF short-circuits, so =IF(B1=0, 0, A1/B1) is safe.

Results that changed in 0.8

Formulas now follow spreadsheet rules more closely, so a few results differ from 0.7:

  • A Date cell reads as its epoch milliseconds (=A1+86400000 is one day later); any other object reads as #VALUE!.
  • A blank cell equals "", 0 and FALSE, so =A1="" and =A1=0 are TRUE for an empty A1.
  • % takes the sign of the divisor, like MOD: =-7%3 is 2 (was -1).
  • A result that is not a finite number is #VALUE!, so =POWER(0,-1) no longer writes Infinity.
  • A range of more than one cell passed to a fixed-argument function such as ROUND, ABS or LEFT is #VALUE!.
  • ROUND, ROUNDUP and ROUNDDOWN round in decimal: =ROUND(1.005,2) is 1.01.
  • Numbers turned into text keep 15 significant digits, and text reads as a number only when it is a plain decimal, so 0x10 is #VALUE!. Exponent notation such as 2E5 is accepted.
  • Filling, pasting or moving a formula past row 1 or column A writes #REF! for the references that leave the sheet.

Formulas also stay on their rows: reordering rows with applyToData, deleting rows, or reloading data with a rowId moves each formula with its row. getState and setState key formulas by rowId and restore only into allowFormula columns, reporting skipped entries in SetStateResult.warnings. A formula entered in the editor runs the column validators and the cancellable cellValueChanging, and is undoable like any edit.

Formula-aware export

CSV and XLSX export can emit each formula cell's =... source instead of its computed value with the formulas option:

grid.exportToXLSX({ filename: 'model', formulas: true });   // exports sources
grid.exportToCSV({ formulas: true });

Without formulas: true, cells export their computed values. From 0.8 a CSV with formulas: true writes references that point at the cells of the file, below the header and in the exported order, so =A1*B1 on the first data row is written =A2*B2. A formula that reads a cell the export leaves out or reorders, or calls a custom function, exports its value instead. XLSX writes real formula cells with their computed values.

React example

import { useEffect, useRef } from 'react'
import 'apex-grid-enterprise/define'

export default function FormulaGrid() {
  const ref = useRef<any>(null)

  useEffect(() => {
    const grid = ref.current
    grid.columns = [
      { key: 'qty', headerText: 'Qty', type: 'number', editable: true },
      { key: 'price', headerText: 'Price', type: 'currency', editable: true },
      { key: 'total', headerText: 'Total', type: 'currency', editable: true, allowFormula: true },
    ]
    grid.data = rows
    // qty = A, price = B; each row's total is that row's qty × price
    rows.forEach((row: any, i: number) => grid.setFormula(row, 'total', `=A${i + 1}*B${i + 1}`))
  }, [])

  return <apex-grid-enterprise ref={ref} style={{ height: 480 }} />
}