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 so its cells accept = expressions and use the built-in formula editor:

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

Setting and reading formulas

Method	Description
setFormula(row, columnKey, formula)	Set a cell's formula source (e.g. '=qty*price')
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(row, 'total', '=qty * price');
const src = grid.getFormula(row, 'total');   // '=qty * price'

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

grid.setFormula(row, 'margin', '=MARGIN(revenue, cost)');

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.

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' },
      { key: 'price', headerText: 'Price' },
      { key: 'total', headerText: 'Total', allowFormula: true },
    ]
    grid.data = rows
    grid.registerFormulaFunction('MARGIN', ([rev, cost]: number[]) => (rev - cost) / rev)
    rows.forEach((row: any) => grid.setFormula(row, 'total', '=qty * price'))
  }, [])

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