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

Method	Description
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.

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.

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.

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 }} />
}