Formula Excel

Source code

TypeScript ts

// src/components/formula/Formula.ts

import { defineCustomElements } from '@revolist/revogrid/loader';
defineCustomElements();

import {
  ColumnDropdown,
  ColumnStretchPlugin,
  FormulaPlugin,
  FormulaBarPlugin,
  NamedRangesPlugin,
  ExportExcelPlugin,
  RowOddPlugin,
  type ExportExcelEvent,
  createFormulaConditionalCellProperties,
  createNamedRangeDropdown,
  defineFormulaNameManager,
} from '@revolist/revogrid-pro';
import { currentTheme, useRandomData } from '../composables/useRandomData';
import { createExampleHelpTooltip } from '../shared/example-help-tooltip';
import './formula.css';

const { createRandomData } = useRandomData();
const { isDark } = currentTheme();
const fileExcelSvg = '<svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 384 512"><path fill="currentColor" d="M0 64C0 28.7 28.7 0 64 0L213.5 0c17 0 33.3 6.7 45.3 18.7L365.3 125.3c12 12 18.7 28.3 18.7 45.3L384 448c0 35.3-28.7 64-64 64L64 512c-35.3 0-64-28.7-64-64L0 64zm208-5.5l0 93.5c0 13.3 10.7 24 24 24L325.5 176 208 58.5zM164 266.7c-7.4-11-22.3-14-33.3-6.7s-14 22.3-6.7 33.3L163.2 352 124 410.7c-7.4 11-4.4 25.9 6.7 33.3s25.9 4.4 33.3-6.7l28-42 28 42c7.4 11 22.3 14 33.3 6.7s14-22.3 6.7-33.3L220.8 352 260 293.3c7.4-11 4.4-25.9-6.7-33.3s-25.9-4.4-33.3 6.7l-28 42-28-42z"/></svg>';

export function load(parentSelector: string) {
  const parent = document.querySelector(parentSelector);
  if (!parent) return;

  const shell = document.createElement('div');
  shell.className = 'formula-example';

  const toolbar = document.createElement('div');
  toolbar.className = 'formula-toolbar';

  const help = (label: string) => createExampleHelpTooltip(label);

  const namesButton = document.createElement('button');
  namesButton.className = 'rv-btn';
  const insertButton = document.createElement('button');
  insertButton.className = 'rv-btn';
  const managerButton = document.createElement('button');
  managerButton.className = 'rv-btn';
  const targetInput = document.createElement('input');
  targetInput.type = 'number';
  targetInput.min = '0';
  targetInput.step = '50';
  targetInput.setAttribute('aria-label', 'Target price');
  targetInput.className = 'formula-target-input';
  const formulaBarInput = document.createElement('input');
  formulaBarInput.type = 'text';
  formulaBarInput.placeholder = 'Formula or value';
  formulaBarInput.setAttribute('aria-label', 'Formula bar');
  formulaBarInput.className = 'formula-bar-input';
  const formulaBarBadge = document.createElement('span');
  formulaBarBadge.className = 'formula-bar-badge';
  formulaBarBadge.hidden = true;
  const formulaBarControl = document.createElement('div');
  formulaBarControl.className = 'formula-bar-control';
  formulaBarControl.append(formulaBarBadge, formulaBarInput);
  const exportButton = document.createElement('button');
  exportButton.className = 'rv-btn';
  exportButton.type = 'button';
  exportButton.title = 'Export to Excel';
  exportButton.setAttribute('aria-label', 'Export to Excel');
  const exportIcon = document.createElement('span');
  exportIcon.className = 'formula-toolbar-icon';
  exportIcon.innerHTML = fileExcelSvg;
  exportButton.append(exportIcon);
  const exportConfig: ExportExcelEvent = { sheetName: 'RevoGrid Formula', workbookName: 'formula-example.xlsx' };
  const exportToExcel = async () => {
    const plugins = await grid.getPlugins();
    const exportPlugin = plugins.find((plugin) => plugin instanceof ExportExcelPlugin) as ExportExcelPlugin | undefined;
    exportPlugin?.export(exportConfig);
  };

  toolbar.append(
    namesButton,
    help('Toggle between named formulas and direct A1 references.'),
    insertButton,
    help('Insert a row inside the named range to show ref-update behavior.'),
    managerButton,
    help('Open or hide the Formula Name Manager panel.'),
    targetInput,
    help('Change the TargetPrice constant used by formulas and conditional formatting.'),
    formulaBarControl,
    help('Show and edit the focused cell raw value or formula.'),
    exportButton,
  );
  shell.append(toolbar);

  const grid = document.createElement('revo-grid');
  grid.rowHeaders = true;
  grid.stretch = 'all';
  grid.hideAttribution = true;
  grid.theme = isDark() ? 'darkMaterial' : 'material';
  grid.className = 'formula-grid';

  const managerPanel = document.createElement('div');
  managerPanel.className = 'formula-manager';
  managerPanel.hidden = true;

  shell.append(grid, managerPanel);
  parent.appendChild(shell);

  let namesEnabled = true;
  let targetPrice = 500;
  let rowId = 100;
  let managerMounted = false;
  const source = createRandomData(100).map((row, index) => ({
    id: index,
    price: row.price,
    category: index % 2 === 0 ? 'Hardware' : 'Software',
  }));

  const buildFormulaNames = () => ({
    rowIdProp: 'id',
    names: namesEnabled
      ? [
        { name: 'PriceList', scope: 'workbook' as const, kind: 'range' as const, ref: `A1:A${source.length}` },
        { name: 'TargetPrice', scope: 'workbook' as const, kind: 'constant' as const, value: targetPrice },
        { name: 'CategoryList', scope: 'workbook' as const, kind: 'range' as const, ref: 'B1:B2' },
      ]
      : [],
  });
  const readTargetPrice = (names: Array<{ name: string; kind?: string; value?: unknown }>) => {
    const targetName = names.find(name => name.name === 'TargetPrice' && name.kind === 'constant');
    const nextTarget = Number(targetName?.value);
    return Number.isFinite(nextTarget) ? nextTarget : undefined;
  };
  const categoryOptions = ['Hardware', 'Software'];
  const targetFormulaRef = () => namesEnabled ? 'TargetPrice' : String(targetPrice);
  const priceListRef = () => namesEnabled ? 'PriceList' : `A1:A${source.length}`;
  const summaryCategoryLabel = () => namesEnabled ? 'Total (named range)' : 'Total (direct refs)';
  const categoryDropdown = (columns: any[], formulaNames: ReturnType<typeof buildFormulaNames>) => {
    const dropdown = namesEnabled
      ? createNamedRangeDropdown('CategoryList', { allSources: source, columns, names: formulaNames.names })
      : { source: categoryOptions.map(value => ({ value, label: value })) };
    const summaryLabel = summaryCategoryLabel();
    const extraOptions = [...categoryOptions, summaryLabel].filter(
      value => !dropdown.source.some(option => option.value === value),
    );
    return {
      ...dropdown,
      source: [
        ...dropdown.source,
        ...extraOptions.map(value => ({ value, label: value })),
      ],
    };
  };

  const buildGridSource = () => source.map((row, index) => ({
    ...row,
    forecast: `=A${index + 1}+${targetFormulaRef()}`,
  }));

  const buildColumns = (formulaNames: ReturnType<typeof buildFormulaNames>) => {
    const columns: any[] = [
      {
        name: 'Price',
        prop: 'price',
        cellTemplate: (_, { value }) => parseFloat(value).toFixed(2),
      },
      {
        name: 'Category',
        prop: 'category',
        columnType: 'categoryDropdown',
        cellProperties({ type }) {
          return type === 'rowPinEnd' ? { class: 'formula-category-pinned' } : undefined;
        },
      },
      {
        name: 'Formula',
        prop: 'forecast',
        cellProperties: () => ({ class: 'formula-cell' }),
      },
    ];
    const priceConditional = createFormulaConditionalCellProperties(
      `=cellvalue>${targetFormulaRef()}`,
      { class: 'formula-above-target' },
      { allSources: source, columns, names: formulaNames.names },
    );
    columns[0].cellProperties = (props) => {
      if (props.type === 'rowPinEnd') {
        return { class: { 'formula-cell': true, 'formula-cell-pinned': true } };
      }
      return priceConditional(props);
    };
    columns[1].dropdown = categoryDropdown(columns, formulaNames);
    return columns;
  };

  const applyConfig = () => {
    const formulaNames = buildFormulaNames();
    namesButton.textContent = namesEnabled ? 'Names on' : 'Names off';
    namesButton.className = namesEnabled ? 'rv-btn-primary' : 'rv-btn';
    insertButton.textContent = 'Insert row';
    managerButton.textContent = managerPanel.hidden ? 'Name manager' : 'Hide manager';
    targetInput.value = String(targetPrice);

    grid.formulaNames = formulaNames;
    grid.formulaBar = { el: formulaBarInput, badgeEl: formulaBarBadge, showCellBadge: true };
    grid.additionalData = { formulaNames };
    grid.columns = buildColumns(formulaNames);
    grid.columnTypes = { categoryDropdown: ColumnDropdown };
    grid.pinnedBottomSource = [{
      price: `=SUM(${priceListRef()})`,
      category: summaryCategoryLabel(),
      forecast: `=SUM(${priceListRef()})+${targetFormulaRef()}*${source.length}`,
    }];
    grid.plugins = [
      FormulaBarPlugin,
      NamedRangesPlugin,
      FormulaPlugin,
      RowOddPlugin,
      ColumnStretchPlugin,
      ExportExcelPlugin,
    ];
    grid.source = buildGridSource();
  };

  namesButton.addEventListener('click', () => {
    namesEnabled = !namesEnabled;
    applyConfig();
  });
  targetInput.addEventListener('input', () => {
    targetPrice = Number(targetInput.value) || 0;
    applyConfig();
  });
  insertButton.addEventListener('click', () => {
    source.splice(1, 0, {
      id: rowId++,
      price: targetPrice + 75,
      category: 'Hardware',
    });
    applyConfig();
  });
  managerButton.addEventListener('click', () => {
    managerPanel.hidden = !managerPanel.hidden;
    if (!managerMounted) {
      defineFormulaNameManager(managerPanel, grid, { pageSize: 8 });
      managerMounted = true;
    }
    applyConfig();
  });
  exportButton.addEventListener('click', exportToExcel);
  const syncToolbarTarget = (event: Event) => {
    const registry = (event as CustomEvent<{ names?: Array<{ name: string; kind?: string; value?: unknown }> }>).detail;
    const nextTarget = readTargetPrice(registry.names ?? []);
    if (typeof nextTarget !== 'number' || nextTarget === targetPrice) {
      return;
    }
    targetPrice = nextTarget;
    applyConfig();
  };
  grid.addEventListener('formulanameschange', syncToolbarTarget);

  applyConfig();

  return () => {
    grid.removeEventListener('formulanameschange', syncToolbarTarget);
    shell.remove();
  };
}

React tsx

// src/components/formula/Formula.tsx

import React, { useEffect, useMemo, useRef, useState } from 'react';
import { RevoGrid, type DataType } from '@revolist/react-datagrid';
import {
  ColumnDropdown,
  ColumnStretchPlugin,
  FormulaBarPlugin,
  FormulaPlugin,
  NamedRangesPlugin,
  ExportExcelPlugin,
  RowOddPlugin,
  type ExportExcelEvent,
  createFormulaConditionalCellProperties,
  createNamedRangeDropdown,
  defineFormulaNameManager,
  type FormulaNameRegistry,
} from '@revolist/revogrid-pro';
import { currentTheme, useRandomData } from '../composables/useRandomData';
import {
  EXAMPLE_HELP_TOOLTIP_TAG,
  defineExampleHelpTooltipElement,
} from '../shared/example-help-tooltip';
import fileExcelSvg from '@fortawesome/fontawesome-free/svgs/solid/file-excel.svg?raw';
import './formula.css';

defineExampleHelpTooltipElement();

const Help = ({ text }: { text: string }) => React.createElement(EXAMPLE_HELP_TOOLTIP_TAG, {
  description: text,
});

function readTargetPrice(names: FormulaNameRegistry['names']) {
  const targetName = names.find(name => name.name === 'TargetPrice' && name.kind === 'constant');
  const nextTarget = Number(targetName?.value);
  return Number.isFinite(nextTarget) ? nextTarget : undefined;
}

function Formula() {
  const { isDark } = currentTheme();
  const { createRandomData } = useRandomData();
  const darkMaterial = isDark();
  const gridRef = useRef<HTMLRevoGridElement>(null);
  const formulaBarRef = useRef<HTMLInputElement>(null);
  const formulaBarBadgeRef = useRef<HTMLSpanElement>(null);
  const managerRef = useRef<HTMLDivElement>(null);
  const managerMounted = useRef(false);
  const [namesEnabled, setNamesEnabled] = useState(true);
  const [targetPrice, setTargetPrice] = useState(500);
  const [showManager, setShowManager] = useState(false);
  const [source, setSource] = useState<DataType[]>(() => createRandomData(100).map((row, index) => ({
    id: index,
    price: row.price,
    category: index % 2 === 0 ? 'Hardware' : 'Software',
  })));
  const nextRowId = useRef(100);

  const formulaNames = useMemo(() => ({
    rowIdProp: 'id',
    names: namesEnabled
      ? [
        { name: 'PriceList', scope: 'workbook' as const, kind: 'range' as const, ref: `A1:A${source.length}` },
        { name: 'TargetPrice', scope: 'workbook' as const, kind: 'constant' as const, value: targetPrice },
        { name: 'CategoryList', scope: 'workbook' as const, kind: 'range' as const, ref: 'B1:B2' },
      ]
      : [],
  }), [namesEnabled, source.length, targetPrice]);
  const categoryOptions = useMemo(() => ['Hardware', 'Software'], []);
  const targetFormulaRef = namesEnabled ? 'TargetPrice' : String(targetPrice);
  const priceListRef = namesEnabled ? 'PriceList' : `A1:A${source.length}`;
  const summaryCategoryLabel = namesEnabled ? 'Total (named range)' : 'Total (direct refs)';

  const gridSource = useMemo(() => source.map((row, index) => ({
    ...row,
    forecast: `=A${index + 1}+${targetFormulaRef}`,
  })), [source, targetFormulaRef]);

  const columns = useMemo(
    () => {
      const nextColumns: any[] = [
        {
          name: 'Price',
          prop: 'price',
          cellTemplate: (_, { value }) => parseFloat(value).toFixed(2),
        },
        {
          name: 'Category',
          prop: 'category',
          columnType: 'categoryDropdown',
          cellProperties: ({ type }) => type === 'rowPinEnd' ? { class: 'formula-category-pinned' } : undefined,
        },
        {
          name: 'Formula',
          prop: 'forecast',
          cellProperties: () => ({ class: 'formula-cell' }),
        },
      ];
      const priceConditional = createFormulaConditionalCellProperties(
        `=cellvalue>${targetFormulaRef}`,
        { class: 'formula-above-target' },
        { allSources: source, columns: nextColumns, names: formulaNames.names },
      );
      nextColumns[0].cellProperties = (props) => {
        if (props.type === 'rowPinEnd') {
          return { class: { 'formula-cell': true, 'formula-cell-pinned': true } };
        }
        return priceConditional(props);
      };
      const categoryDropdown = namesEnabled
        ? createNamedRangeDropdown(
          'CategoryList',
          { allSources: source, columns: nextColumns, names: formulaNames.names },
        )
        : { source: categoryOptions.map(value => ({ value, label: value })) };
      nextColumns[1].dropdown = {
        ...categoryDropdown,
        source: [
          ...categoryDropdown.source,
          ...[...categoryOptions, summaryCategoryLabel]
            .filter(value => !categoryDropdown.source.some(option => option.value === value))
            .map(value => ({ value, label: value })),
        ],
      };
      return nextColumns;
    },
    [categoryOptions, formulaNames, namesEnabled, source, summaryCategoryLabel, targetFormulaRef],
  );

  const pinnedBottomSource = useMemo(() => [
    {
      price: `=SUM(${priceListRef})`,
      category: summaryCategoryLabel,
      forecast: `=SUM(${priceListRef})+${targetFormulaRef}*${source.length}`,
    },
  ], [priceListRef, source.length, summaryCategoryLabel, targetFormulaRef]);

  const plugins = useMemo(() => [
    FormulaBarPlugin,
    NamedRangesPlugin,
    FormulaPlugin,
    RowOddPlugin,
    ColumnStretchPlugin,
    ExportExcelPlugin,
  ], []);
  const columnTypes = useMemo(() => ({ categoryDropdown: ColumnDropdown }), []);
  const additionalData = useMemo(() => ({ formulaNames }), [formulaNames]);
  const exportConfig: ExportExcelEvent = { sheetName: 'RevoGrid Formula', workbookName: 'formula-example.xlsx' };
  const exportToExcel = async () => {
    const grid = gridRef.current;
    if (!grid) {
      return;
    }
    const plugins = await grid.getPlugins();
    const exportPlugin = plugins.find((plugin) => plugin instanceof ExportExcelPlugin) as ExportExcelPlugin;
    exportPlugin?.export(exportConfig);
  };

  useEffect(() => {
    if (gridRef.current) {
      gridRef.current.formulaNames = formulaNames;
    }
  }, [formulaNames]);

  useEffect(() => {
    const grid = gridRef.current;
    if (!formulaBarRef.current || !grid) {
      return;
    }
    grid.formulaBar = {
      el: formulaBarRef.current,
      badgeEl: formulaBarBadgeRef.current,
      showCellBadge: true,
    };
    return () => {
      grid.formulaBar = null;
    };
  }, []);

  useEffect(() => {
    if (!showManager || managerMounted.current || !managerRef.current || !gridRef.current) {
      return;
    }
    defineFormulaNameManager(managerRef.current, gridRef.current, { pageSize: 8 });
    managerMounted.current = true;
  }, [showManager]);

  useEffect(() => {
    const grid = gridRef.current;
    if (!grid) {
      return;
    }
    const syncToolbarTarget = (event: Event) => {
      const registry = (event as CustomEvent<FormulaNameRegistry>).detail;
      const nextTarget = readTargetPrice(registry.names);
      if (typeof nextTarget !== 'number') {
        return;
      }
      setTargetPrice(current => current === nextTarget ? current : nextTarget);
    };
    grid.addEventListener('formulanameschange', syncToolbarTarget);
    return () => grid.removeEventListener('formulanameschange', syncToolbarTarget);
  }, []);

  const insertRow = () => {
    setSource((rows) => {
      const nextRows = [...rows];
      nextRows.splice(1, 0, {
        id: nextRowId.current++,
        price: targetPrice + 75,
        category: 'Hardware',
      });
      return nextRows;
    });
  };

  return (
    <div className="formula-example">
      <div className="formula-toolbar">
        <button
          className={namesEnabled ? 'rv-btn-primary' : 'rv-btn'}
          type="button"
          onClick={() => setNamesEnabled(value => !value)}
        >
          {namesEnabled ? 'Names on' : 'Names off'}
        </button>
        <Help text="Toggle between named formulas and direct A1 references." />
        <button className="rv-btn" type="button" onClick={insertRow}>Insert row</button>
        <Help text="Insert a row inside the named range to show ref-update behavior." />
        <button className="rv-btn" type="button" onClick={() => setShowManager(value => !value)}>
          {showManager ? 'Hide manager' : 'Name manager'}
        </button>
        <Help text="Open or hide the Formula Name Manager panel." />
        <button className="rv-btn" type="button" title="Export to Excel" aria-label="Export to Excel" onClick={exportToExcel}>
          <span className="formula-toolbar-icon" dangerouslySetInnerHTML={{ __html: fileExcelSvg }} />
        </button>
        <input
          aria-label="Target price"
          type="number"
          min={0}
          step={50}
          value={targetPrice}
          onChange={(event) => setTargetPrice(Number(event.currentTarget.value) || 0)}
          className="formula-target-input"
        />
        <Help text="Change the TargetPrice constant used by formulas and conditional formatting." />
        <div className="formula-bar-control">
          <span ref={formulaBarBadgeRef} className="formula-bar-badge" hidden />
          <input
            ref={formulaBarRef}
            aria-label="Formula bar"
            type="text"
            placeholder="Formula or value"
            className="formula-bar-input"
          />
        </div>
        <Help text="Show and edit the focused cell raw value or formula." />
      </div>
      <RevoGrid
        ref={gridRef}
        rowHeaders={true}
        columns={columns}
        source={gridSource}
        pinnedBottomSource={pinnedBottomSource}
        columnTypes={columnTypes}
        additionalData={additionalData}
        stretch="all"
        hide-attribution
        theme={darkMaterial ? 'darkMaterial' : 'material'}
        plugins={plugins}
        className="formula-grid"
      />
      <div ref={managerRef} className="formula-manager" hidden={!showManager} />
    </div>
  );
}

export default Formula;

Vue vue

<template>
  <div class="formula-example">
    <div class="formula-toolbar">
      <button
        :class="namesEnabled ? 'rv-btn-primary' : 'rv-btn'"
        type="button"
        @click="namesEnabled = !namesEnabled"
      >
        {{ namesEnabled ? 'Names on' : 'Names off' }}
      </button>
      <example-help-tooltip description="Toggle between named formulas and direct A1 references." />
      <button class="rv-btn" type="button" @click="insertRow">Insert row</button>
      <example-help-tooltip description="Insert a row inside the named range to show ref-update behavior." />
      <button class="rv-btn" type="button" @click="toggleManager">
        {{ showManager ? 'Hide manager' : 'Name manager' }}
      </button>
      <example-help-tooltip description="Open or hide the Formula Name Manager panel." />
      <button class="rv-btn" type="button" title="Export to Excel" aria-label="Export to Excel" @click="exportToExcel">
        <span class="formula-toolbar-icon" v-html="fileExcelSvg" />
      </button>
      <input
        v-model.number="targetPrice"
        aria-label="Target price"
        type="number"
        min="0"
        step="50"
        class="formula-target-input"
      />
      <example-help-tooltip description="Change the TargetPrice constant used by formulas and conditional formatting." />
      <div class="formula-bar-control">
        <span ref="formulaBarBadgeRef" class="formula-bar-badge" hidden />
        <input
          ref="formulaBarRef"
          aria-label="Formula bar"
          type="text"
          placeholder="Formula or value"
          class="formula-bar-input"
        />
      </div>
      <example-help-tooltip description="Show and edit the focused cell raw value or formula." />
    </div>
    <VGrid
      ref="gridRef"
      :theme="isDark ? 'darkMaterial' : 'material'"
      :columns="columns"
      :source="gridRows"
      :pinned-bottom-source="pinnedBottomSource"
      :column-types="columnTypes"
      :additional-data="additionalData"
      :plugins="plugins"
      stretch="all"
      row-headers
      hide-attribution
      class="formula-grid overflow-hidden cell-border"
    />
    <div ref="managerRef" class="formula-manager" :hidden="!showManager" />
  </div>
</template>

<script setup lang="ts">
import { computed, nextTick, onBeforeUnmount, onMounted, ref, watch } from 'vue'
import { VGrid, type ColumnRegular } from '@revolist/vue3-datagrid'
import {
  ColumnDropdown,
  ColumnStretchPlugin,
  FormulaBarPlugin,
  FormulaPlugin,
  NamedRangesPlugin,
  ExportExcelPlugin,
  RowOddPlugin,
  type ExportExcelEvent,
  createFormulaConditionalCellProperties,
  createNamedRangeDropdown,
  defineFormulaNameManager,
  type FormulaNameRegistry,
} from '@revolist/revogrid-pro';
import { currentThemeVue, useRandomData } from '../composables/useRandomData'
import { defineExampleHelpTooltipElement } from '../shared/example-help-tooltip'
import fileExcelSvg from '@fortawesome/fontawesome-free/svgs/solid/file-excel.svg?raw'
import './formula.css'

defineExampleHelpTooltipElement()

const { isDark } = currentThemeVue();
const { createRandomData } = useRandomData()

const namesEnabled = ref(true)
const targetPrice = ref(500)
const showManager = ref(false)
const nextRowId = ref(100)
const managerMounted = ref(false)
const gridRef = ref<{ $el?: Element | null } | Element | null>(null)
const formulaBarRef = ref<HTMLInputElement | null>(null)
const formulaBarBadgeRef = ref<HTMLSpanElement | null>(null)
const managerRef = ref<HTMLDivElement | null>(null)
let boundFormulaGrid: HTMLRevoGridElement | null = null

const formulaNames = computed(() => ({
  rowIdProp: 'id',
  names: namesEnabled.value
    ? [
      { name: 'PriceList', scope: 'workbook' as const, kind: 'range' as const, ref: `A1:A${rows.value.length}` },
      { name: 'TargetPrice', scope: 'workbook' as const, kind: 'constant' as const, value: targetPrice.value },
      { name: 'CategoryList', scope: 'workbook' as const, kind: 'range' as const, ref: 'B1:B2' },
    ]
    : [],
}))
function readTargetPrice(names: FormulaNameRegistry['names']) {
  const targetName = names.find(name => name.name === 'TargetPrice' && name.kind === 'constant')
  const nextTarget = Number(targetName?.value)
  return Number.isFinite(nextTarget) ? nextTarget : undefined
}
const categoryOptions = ['Hardware', 'Software']
const targetFormulaRef = computed(() => namesEnabled.value ? 'TargetPrice' : String(targetPrice.value))
const priceListRef = computed(() => namesEnabled.value ? 'PriceList' : `A1:A${rows.value.length}`)
const summaryCategoryLabel = computed(() => namesEnabled.value ? 'Total (named range)' : 'Total (direct refs)')

const additionalData = computed(() => ({ formulaNames: formulaNames.value }))

const plugins = computed(() => [
  FormulaBarPlugin,
  NamedRangesPlugin,
  FormulaPlugin,
  RowOddPlugin,
  ColumnStretchPlugin,
  ExportExcelPlugin,
])
const columnTypes = { categoryDropdown: ColumnDropdown }
const exportConfig: ExportExcelEvent = { sheetName: 'RevoGrid Formula', workbookName: 'formula-example.xlsx' }

const rows = ref(createRandomData(100).map((row, index) => ({
  id: index,
  price: row.price,
  category: index % 2 === 0 ? 'Hardware' : 'Software',
})))

const gridRows = computed(() => rows.value.map((row, index) => ({
  ...row,
  forecast: `=A${index + 1}+${targetFormulaRef.value}`,
})))

const columns = computed<ColumnRegular[]>(() => {
  const nextColumns: ColumnRegular[] = [
    {
      name: 'Price',
      prop: 'price',
      cellTemplate: (_, { value }) => parseFloat(value).toFixed(2),
    },
    {
      name: 'Category',
      prop: 'category',
      columnType: 'categoryDropdown',
      cellProperties: ({ type }: { type: string }) => {
        if (type === 'rowPinEnd') {
          return { class: 'formula-category-pinned' }
        }
        return {}
      },
    },
    {
      name: 'Formula',
      prop: 'forecast',
      cellProperties: () => ({ class: 'formula-cell' }),
    },
  ]

  const priceConditional = createFormulaConditionalCellProperties(
    `=cellvalue>${targetFormulaRef.value}`,
    { class: 'formula-above-target' },
    { allSources: rows.value, columns: nextColumns, names: formulaNames.value.names },
  )
  nextColumns[0].cellProperties = (props: any) => {
    if (props.type === 'rowPinEnd') {
      return { class: { 'formula-cell': true, 'formula-cell-pinned': true } }
    }
    return priceConditional(props)
  }
  const categoryDropdown = namesEnabled.value
    ? createNamedRangeDropdown(
      'CategoryList',
      { allSources: rows.value, columns: nextColumns, names: formulaNames.value.names },
    )
    : { source: categoryOptions.map(value => ({ value, label: value })) }
  ;(nextColumns[1] as any).dropdown = {
    ...categoryDropdown,
    source: [
      ...categoryDropdown.source,
      ...[...categoryOptions, summaryCategoryLabel.value]
        .filter(value => !categoryDropdown.source.some(option => option.value === value))
        .map(value => ({ value, label: value })),
    ],
  }
  return nextColumns
})

const pinnedBottomSource = computed(() => [
  {
    price: `=SUM(${priceListRef.value})`,
    category: summaryCategoryLabel.value,
    forecast: `=SUM(${priceListRef.value})+${targetFormulaRef.value}*${rows.value.length}`,
  },
])

function getGridEl(): HTMLRevoGridElement | null {
  const value = gridRef.value
  if (!value) {
    return null
  }
  const element = '$el' in value ? value.$el : value
  if (!element) {
    return null
  }
  if (element.tagName?.toLowerCase() === 'revo-grid') {
    return element as HTMLRevoGridElement
  }
  return element.querySelector?.('revo-grid') as HTMLRevoGridElement | null
}

function bindFormulaBar() {
  const grid = getGridEl()
  if (!grid || !formulaBarRef.value) {
    return
  }
  if (boundFormulaGrid && boundFormulaGrid !== grid) {
    boundFormulaGrid.removeEventListener('formulanameschange', syncToolbarTarget)
    boundFormulaGrid.formulaBar = null
  }
  boundFormulaGrid = grid
  grid.addEventListener('formulanameschange', syncToolbarTarget)
  grid.formulaNames = formulaNames.value
  grid.formulaBar = {
    el: formulaBarRef.value,
    badgeEl: formulaBarBadgeRef.value,
    showCellBadge: true,
  }
}

function syncToolbarTarget(event: Event) {
  const registry = (event as CustomEvent<FormulaNameRegistry>).detail
  const nextTarget = readTargetPrice(registry.names)
  if (typeof nextTarget !== 'number' || nextTarget === targetPrice.value) {
    return
  }
  targetPrice.value = nextTarget
}

function insertRow() {
  const nextRows = [...rows.value]
  nextRows.splice(1, 0, {
    id: nextRowId.value++,
    price: targetPrice.value + 75,
    category: 'Hardware',
  })
  rows.value = nextRows
}

async function toggleManager() {
  showManager.value = !showManager.value
  await nextTick()
  const grid = getGridEl()
  if (!managerMounted.value && managerRef.value && grid) {
    defineFormulaNameManager(managerRef.value, grid, { pageSize: 8 })
    managerMounted.value = true
  }
}

async function exportToExcel() {
  const grid = getGridEl()
  if (!grid) {
    return
  }
  const plugins = await grid.getPlugins()
  const exportPlugin = plugins.find(
    (plugin) => plugin instanceof ExportExcelPlugin
  ) as ExportExcelPlugin
  exportPlugin?.export(exportConfig)
}

watch(formulaNames, async value => {
  await nextTick()
  const grid = getGridEl()
  if (grid) {
    grid.formulaNames = value
  }
}, { immediate: true })

watch([gridRef, formulaBarRef, formulaBarBadgeRef], async () => {
  await nextTick()
  bindFormulaBar()
}, { flush: 'post' })

onMounted(() => {
  bindFormulaBar()
})

onBeforeUnmount(() => {
  if (boundFormulaGrid) {
    boundFormulaGrid.removeEventListener('formulanameschange', syncToolbarTarget)
    boundFormulaGrid.formulaBar = null
    boundFormulaGrid = null
  }
})
</script>

Angular ts

import { CUSTOM_ELEMENTS_SCHEMA, Component, ElementRef, ViewChild, ViewEncapsulation, type AfterViewInit, type OnDestroy, type OnInit } from '@angular/core';
import { RevoGrid } from '@revolist/angular-datagrid';
import {
  ColumnDropdown,
  ColumnStretchPlugin,
  FormulaBarPlugin,
  FormulaPlugin,
  NamedRangesPlugin,
  ExportExcelPlugin,
  RowOddPlugin,
  createFormulaConditionalCellProperties,
  createNamedRangeDropdown,
  defineFormulaNameManager,
  type FormulaBarConfig,
  type FormulaNameDefinition,
  type FormulaNamesConfig,
  type FormulaNameRegistry,
  type ExportExcelEvent,
} from '@revolist/revogrid-pro';
import { currentTheme, useRandomData } from '../composables/useRandomData';
import { defineExampleHelpTooltipElement } from '../shared/example-help-tooltip';
import fileExcelSvg from '@fortawesome/fontawesome-free/svgs/solid/file-excel.svg?raw';

type FormulaGridElement = HTMLRevoGridElement & {
  formulaNames?: FormulaNamesConfig | FormulaNameDefinition[];
  formulaBar?: (FormulaBarConfig & {
    badgeEl?: HTMLElement | null;
    showCellBadge?: boolean;
  }) | null;
};

defineExampleHelpTooltipElement();

@Component({
  selector: 'formula-grid',
  standalone: true,
  imports: [RevoGrid],
  schemas: [CUSTOM_ELEMENTS_SCHEMA],
  template: `
    <div class="formula-example">
      <div class="formula-toolbar">
        <button [class]="namesEnabled ? 'rv-btn-primary' : 'rv-btn'" type="button" (click)="toggleNames()">{{ namesEnabled ? 'Names on' : 'Names off' }}</button>
        <example-help-tooltip description="Toggle between named formulas and direct A1 references."></example-help-tooltip>
        <button class="rv-btn" type="button" (click)="insertRow()">Insert row</button>
        <example-help-tooltip description="Insert a row inside the named range to show ref-update behavior."></example-help-tooltip>
        <button class="rv-btn" type="button" (click)="toggleManager()">{{ showManager ? 'Hide manager' : 'Name manager' }}</button>
        <example-help-tooltip description="Open or hide the Formula Name Manager panel."></example-help-tooltip>
        <button class="rv-btn" type="button" title="Export to Excel" aria-label="Export to Excel" (click)="exportExcel()">
          <span class="formula-toolbar-icon" [innerHTML]="fileExcelSvg"></span>
        </button>
        <input
          aria-label="Target price"
          type="number"
          min="0"
          step="50"
          [value]="targetPrice"
          (input)="setTargetPrice($event)"
          class="formula-target-input"
        />
        <example-help-tooltip description="Change the TargetPrice constant used by formulas and conditional formatting."></example-help-tooltip>
        <div class="formula-bar-control">
          <span #formulaBarBadge class="formula-bar-badge" hidden></span>
          <input
            #formulaBar
            aria-label="Formula bar"
            type="text"
            placeholder="Formula or value"
            class="formula-bar-input"
          />
        </div>
        <example-help-tooltip description="Show and edit the focused cell raw value or formula."></example-help-tooltip>
      </div>
      <revo-grid
        #gridRef
        [rowHeaders]="true"
        [columns]="columns"
        [source]="gridSource"
        [pinnedBottomSource]="pinnedBottomSource"
        [columnTypes]="columnTypes"
        [additionalData]="additionalData"
        stretch="all"
        [hideAttribution]="true"
        [theme]="theme"
        [plugins]="plugins"
        class="formula-grid"
      ></revo-grid>
      <div #managerPanel class="formula-manager" [hidden]="!showManager"></div>
    </div>`,
  styleUrls: ['./formula.css'],
  encapsulation: ViewEncapsulation.None,
})
export class FormulaGridComponent implements OnInit, AfterViewInit, OnDestroy {
  @ViewChild('gridRef', { read: ElementRef }) gridElement!: ElementRef<FormulaGridElement>;
  @ViewChild('formulaBar', { read: ElementRef }) formulaBarElement!: ElementRef<HTMLInputElement>;
  @ViewChild('formulaBarBadge', { read: ElementRef }) formulaBarBadgeElement!: ElementRef<HTMLSpanElement>;
  @ViewChild('managerPanel', { read: ElementRef }) managerElement!: ElementRef<HTMLDivElement>;

  namesEnabled = true;
  themeDark = currentTheme().isDark();
  theme = this.themeDark ? 'darkMaterial' : 'material';
  targetPrice = 500;
  showManager = false;
  private nextRowId = 100;
  private managerMounted = false;
  private categoryOptions = ['Hardware', 'Software'];
  source: any[] = useRandomData().createRandomData(100).map((row, index) => ({
    id: index,
    price: row.price,
    category: index % 2 === 0 ? 'Hardware' : 'Software',
  }));

  formulaNames = this.buildFormulaNames();
  additionalData = { formulaNames: this.formulaNames };
  gridSource = this.buildGridSource();
  pinnedBottomSource = this.buildPinnedBottomSource();
  plugins: any[] = [];
  columnTypes = { categoryDropdown: ColumnDropdown };
  columns: any[] = [];
  exportConfig: ExportExcelEvent = { sheetName: 'RevoGrid Formula', workbookName: 'formula-example.xlsx' };
  fileExcelSvg = fileExcelSvg;

  ngOnInit() {
    this.applyFormulaConfig();
  }

  ngAfterViewInit() {
    this.gridElement.nativeElement.addEventListener('formulanameschange', this.syncToolbarTarget);
    this.gridElement.nativeElement.formulaBar = {
      el: this.formulaBarElement.nativeElement,
      badgeEl: this.formulaBarBadgeElement.nativeElement,
      showCellBadge: true,
    };
  }

  ngOnDestroy() {
    const grid = this.gridElement?.nativeElement;
    grid?.removeEventListener('formulanameschange', this.syncToolbarTarget);
    if (grid) {
      grid.formulaBar = null;
    }
  }

  toggleNames() {
    this.namesEnabled = !this.namesEnabled;
    this.applyFormulaConfig();
  }

  setTargetPrice(event: Event) {
    this.targetPrice = Number((event.currentTarget as HTMLInputElement).value) || 0;
    this.applyFormulaConfig();
  }

  insertRow() {
    const nextSource = [...this.source];
    nextSource.splice(1, 0, {
      id: this.nextRowId++,
      price: this.targetPrice + 75,
      category: 'Hardware',
    });
    this.source = nextSource;
    this.applyFormulaConfig();
  }

  toggleManager() {
    this.showManager = !this.showManager;
    window.setTimeout(() => {
      if (!this.managerMounted && this.managerElement?.nativeElement && this.gridElement?.nativeElement) {
        defineFormulaNameManager(this.managerElement.nativeElement, this.gridElement.nativeElement, { pageSize: 8 });
        this.managerMounted = true;
      }
    });
  }

  private syncToolbarTarget = (event: Event) => {
    const registry = (event as CustomEvent<FormulaNameRegistry>).detail;
    const nextTarget = this.readTargetPrice(registry.names);
    if (typeof nextTarget !== 'number' || nextTarget === this.targetPrice) {
      return;
    }
    this.targetPrice = nextTarget;
    this.applyFormulaConfig();
  };

  private readTargetPrice(names: FormulaNameRegistry['names']) {
    const targetName = names.find(name => name.name === 'TargetPrice' && name.kind === 'constant');
    const nextTarget = Number(targetName?.value);
    return Number.isFinite(nextTarget) ? nextTarget : undefined;
  }

  private buildFormulaNames() {
    return {
      rowIdProp: 'id',
      names: this.namesEnabled
        ? [
          { name: 'PriceList', scope: 'workbook' as const, kind: 'range' as const, ref: `A1:A${this.source.length}` },
          { name: 'TargetPrice', scope: 'workbook' as const, kind: 'constant' as const, value: this.targetPrice },
          { name: 'CategoryList', scope: 'workbook' as const, kind: 'range' as const, ref: 'B1:B2' },
        ]
        : [],
    };
  }

  private applyFormulaConfig() {
    this.formulaNames = this.buildFormulaNames();
    this.additionalData = { formulaNames: this.formulaNames };
    this.gridSource = this.buildGridSource();
    this.pinnedBottomSource = this.buildPinnedBottomSource();
    this.plugins = [
      FormulaBarPlugin,
      NamedRangesPlugin,
      FormulaPlugin,
      RowOddPlugin,
      ColumnStretchPlugin,
      ExportExcelPlugin,
    ];
    this.columns = this.buildColumns();
    if (this.gridElement?.nativeElement) {
      this.gridElement.nativeElement.formulaNames = this.formulaNames;
    }
  }

  private buildGridSource() {
    return this.source.map((row, index) => ({
      ...row,
      forecast: `=A${index + 1}+${this.targetFormulaRef()}`,
    }));
  }

  private targetFormulaRef() {
    return this.namesEnabled ? 'TargetPrice' : String(this.targetPrice);
  }

  private priceListRef() {
    return this.namesEnabled ? 'PriceList' : `A1:A${this.source.length}`;
  }

  private summaryCategoryLabel() {
    return this.namesEnabled ? 'Total (named range)' : 'Total (direct refs)';
  }

  private buildPinnedBottomSource() {
    return [{
      price: `=SUM(${this.priceListRef()})`,
      category: this.summaryCategoryLabel(),
      forecast: `=SUM(${this.priceListRef()})+${this.targetFormulaRef()}*${this.source.length}`,
    }];
  }

  private buildColumns() {
    const columns: any[] = [
      {
        name: 'Price',
        prop: 'price',
        cellTemplate: (_: any, { value }: { value: any }) => parseFloat(value).toFixed(2),
      },
      {
        name: 'Category',
        prop: 'category',
        columnType: 'categoryDropdown',
        cellProperties: ({ type }: { type: string }) => type === 'rowPinEnd' ? { class: 'formula-category-pinned' } : null,
      },
      {
        name: 'Formula',
        prop: 'forecast',
        cellProperties: () => ({ class: 'formula-cell' }),
      },
    ];

    const priceConditional = createFormulaConditionalCellProperties(
      `=cellvalue>${this.targetFormulaRef()}`,
      { class: 'formula-above-target' },
      { allSources: this.source, columns, names: this.formulaNames.names },
    );
    columns[0].cellProperties = (props: any) => {
      if (props.type === 'rowPinEnd') {
        return { class: { 'formula-cell': true, 'formula-cell-pinned': true } };
      }
      return priceConditional(props);
    };
    const categoryDropdown = this.namesEnabled
      ? createNamedRangeDropdown(
        'CategoryList',
        { allSources: this.source, columns, names: this.formulaNames.names },
      )
      : { source: this.categoryOptions.map(value => ({ value, label: value })) };
    const summaryLabel = this.summaryCategoryLabel();
    const extraOptions = [...this.categoryOptions, summaryLabel].filter(
      value => !categoryDropdown.source.some(option => option.value === value),
    );
    columns[1].dropdown = {
      ...categoryDropdown,
      source: [
        ...categoryDropdown.source,
        ...extraOptions.map(value => ({ value, label: value })),
      ],
    };
    return columns;
  }

  async exportExcel() {
    const plugins = await this.gridElement?.nativeElement?.getPlugins();
    if (!plugins) {
      return;
    }
    const plugin = plugins.find(
      (p) => p instanceof ExportExcelPlugin,
    ) as ExportExcelPlugin;
    plugin?.export(this.exportConfig);
  }
}

Implement and use complex formulas similar to Excel, allowing for dynamic calculations and data manipulation within your grid cells. The Formula Plugin empowers grid to support Excel-like formulas, enabling users to perform advanced data processing directly within the grid.

With the Formula Plugin, users can enter formulas in grid cells using an equal sign (=) followed by the formula (e.g., =SUM(A1:B2)). The plugin will parse the formula, calculate the result, and display it in the corresponding cell. It also ensures that the results are dynamically updated whenever the referenced cells change, similar to how Excel operates.

This feature makes it easier to handle complex data manipulations and calculations directly within the grid, providing a powerful tool for users who need to process and analyze data on the fly.

Named ranges and formula names

Named ranges are a Pro layer for making formulas readable and reusable. Add NamedRangesPlugin next to FormulaPlugin whenever a grid uses grid.formulaNames, the Formula Name Manager, named constants, named ranges, or named formulas.

FormulaPlugin can evaluate basic A1 formulas by itself. It also keeps a lightweight fallback for read-only grid.formulaNames evaluation, but NamedRangesPlugin owns the runtime registry: validation, mutation APIs, lifecycle events, range jump behavior, and deterministic A1 ref updates after row or column changes. For Excel-style named formulas, install both plugins explicitly.

Names can be:

• Workbook-global: available to every sheet context.
• Sheet-local: tied to a sheetId and resolved before a workbook name with the same display name.
• Range names: A1 references such as B2:B20, usable in functions like SUM(Revenue).
• Constant names: values such as thresholds, rates, or flags.
• Formula names: reusable formulas such as =SUM(Revenue) * TaxRate.

Quick setup

import {
  ColumnDropdown,
  FormulaBarPlugin,
  FormulaPlugin,
  NamedRangesPlugin,
  createFormulaConditionalCellProperties,
  createNamedRangeDropdown,
} from '@revolist/revogrid-pro';

const source = [
  { product: 'Keyboard', price: 120, category: 'Hardware' },
  { product: 'License', price: 900, category: 'Software' },
];

const columns = [
  { prop: 'product', name: 'Product' },
  { prop: 'price', name: 'Price' },
  { prop: 'category', name: 'Category', columnType: 'categoryDropdown' },
];

const formulaNames = {
  activeSheetId: 'Budget',
  names: [
    { name: 'PriceList', scope: 'workbook', kind: 'range', ref: 'B1:B2' },
    { name: 'TargetPrice', scope: 'workbook', kind: 'constant', value: 500 },
    { name: 'CategoryList', scope: 'workbook', kind: 'range', ref: 'C1:C2' },
    { name: 'AboveTargetTotal', scope: 'workbook', kind: 'formula', value: '=SUM(PriceList)' },
  ],
};

columns[1].cellProperties = createFormulaConditionalCellProperties(
  '=cellvalue>TargetPrice',
  { class: 'above-target', style: { fontWeight: '600' } },
  { allSources: source, columns, names: formulaNames.names },
);

columns[2].dropdown = createNamedRangeDropdown(
  'CategoryList',
  { allSources: source, columns, names: formulaNames.names },
);

grid.plugins = [FormulaBarPlugin, NamedRangesPlugin, FormulaPlugin];
grid.columnTypes = { categoryDropdown: ColumnDropdown };
grid.formulaNames = formulaNames;
grid.columns = columns;
grid.source = source;
grid.pinnedBottomSource = [{ product: 'Total', price: '=SUM(PriceList)', category: 'Named range' }];

const formulaBar = document.querySelector<HTMLInputElement>('#formula-bar');
if (formulaBar) {
  grid.formulaBar = { el: formulaBar };
}

Note

additionalData.formulaNames is still supported for existing integrations, but new code should prefer grid.formulaNames.

Configuration

Set names either as a full config object or as a shorthand array.

grid.formulaNames = {
  activeSheetId: 'Q4',
  rowIdProp: 'id',
  autoUpdateRefs: true,
  names: [
    { name: 'Revenue', scope: 'workbook', kind: 'range', ref: 'B2:B50' },
    { name: 'TaxRate', scope: 'workbook', kind: 'constant', value: 0.2 },
    { name: 'NetRevenue', scope: 'sheet', sheetId: 'Q4', kind: 'formula', value: '=SUM(Revenue) * (1 - TaxRate)' },
  ],
};

Field	Description
names	List of formula-name definitions.
activeSheetId	Current sheet context. Defaults to default.
rowIdProp	Stable row identity field used to detect deterministic inserts/deletes across immutable row replacements.
autoUpdateRefs	Enables A1 ref updates after deterministic row/column inserts/deletes. Defaults to true.

Each definition supports:

Field	Description
name	Display name used in formulas. Must not look like a cell address or match a formula function.
scope	workbook or sheet. Defaults to workbook unless sheetId is set.
sheetId	Sheet id for sheet-local names.
kind	range, constant, or formula.
ref	A1 reference for range names.
value	Constant value or named formula string. Formula values must start with =.
comment	Optional description shown by tooling such as the Name Manager.

Scope and resolution

Unqualified names resolve with Excel-style precedence: the active sheet-local name wins, then the workbook-global name is used.

grid.formulaNames = {
  activeSheetId: 'Q4',
  names: [
    { name: 'TaxRate', scope: 'workbook', kind: 'constant', value: 0.18 },
    { name: 'TaxRate', scope: 'sheet', sheetId: 'Q4', kind: 'constant', value: 0.22 },
    { name: 'TaxRate', scope: 'sheet', sheetId: 'Q1', kind: 'constant', value: 0.2 },
  ],
};

// Uses Q4 sheet-local value.
grid.source = [{ total: '=A1*TaxRate' }];

// Force workbook or a specific sheet.
grid.source = [
  { total: '=A1*workbook!TaxRate' },
  { total: '=A1*Q1!TaxRate' },
];

Duplicate names are rejected only within the same scope. The same display name may exist once globally and once per sheet.

Runtime API

After the grid is initialized, get the plugin instance through grid.getPlugins() and use the runtime methods.

const plugins = await grid.getPlugins();
const namedRanges = plugins.find((plugin) => 'getFormulaNames' in plugin);

namedRanges.upsertFormulaName({
  name: 'ForecastRange',
  scope: 'workbook',
  kind: 'range',
  ref: 'D2:D20',
});

const validation = namedRanges.validateFormulaNameRef({
  name: 'ForecastRange',
  kind: 'range',
  ref: 'D2:D20',
});

await namedRanges.jumpToFormulaName('ForecastRange');

Available methods:

Method	Description
getFormulaNames()	Returns valid names as a defensive copy.
getFormulaNamesConfig()	Returns the current public config shape.
setFormulaNames(config)	Replaces the full registry.
upsertFormulaName(definition)	Creates or replaces a name in its scope.
deleteFormulaName(name, scope?, sheetId?)	Deletes a name. Pass scope and sheet id to target a sheet-local name.
validateFormulaNameRef(definition)	Validates name syntax, duplicate scope conflicts, formula kind, and range bounds.
jumpToFormulaName(name)	Scrolls and focuses the first cell of a range name. Supports workbook!Name and SheetId!Name.

The plugin emits:

Event	Detail
formulanameschange	Current normalized registry.
formulanamevalidationerror	Validation result for invalid definitions.
formulanamejump	Name, definition, row index, and column index for a successful jump.

Name Manager UI

Use the bundled panel when users need to manage names at runtime.

import { defineFormulaNameManager } from '@revolist/revogrid-pro';

const panel = document.querySelector('#name-manager');
defineFormulaNameManager(panel, grid, { pageSize: 50 });

The manager supports:

• Create, edit, and delete.
• Workbook and sheet-local scope selection.
• Range, constant, and formula names.
• Inline validation status.
• Jump-to for range names.

Formula bar input

Use FormulaBarPlugin when you need an Excel-style input outside the grid. The bar shows the focused cell’s raw source value, so formula cells display =... even while the grid renders the computed result.

import {
  FormulaBarPlugin,
  FormulaPlugin,
} from '@revolist/revogrid-pro';

grid.plugins = [FormulaBarPlugin, FormulaPlugin];

const input = document.querySelector<HTMLInputElement>('#formula-bar');
if (input) {
  grid.formulaBar = { el: input };
}

The input commits on Enter. Blur only synchronizes the bar with the current grid state and does not write to the grid. While the input is active, the grid marks the cell that will receive the formula bar edit; if grid focus changes, that edit target is cleared so the bar cannot accidentally apply an old value to a newly focused cell. Set grid.formulaBar = null when removing the input. The plugin runtime also exposes getFormulaBarState() and setFormulaBarValue(value) through grid.getPlugins() for custom UI controls.

If the same grid also uses named formulas, install the runtime registry plugin as well:

grid.plugins = [FormulaBarPlugin, NamedRangesPlugin, FormulaPlugin];

Dropdown validation lists

createNamedRangeDropdown converts a named range into a dropdown source. This is a lightweight validation-list helper; it does not replace a full validation subsystem.

columns[2] = {
  prop: 'status',
  name: 'Status',
  columnType: 'statusDropdown',
  dropdown: createNamedRangeDropdown('AllowedStatuses', {
    allSources: source,
    columns,
    names: [{ name: 'AllowedStatuses', kind: 'range', ref: 'C1:C3' }],
    label: (value) => String(value).toUpperCase(),
  }),
};

Conditional formatting formulas

createFormulaConditionalCellProperties builds a cellProperties callback. Conditional formulas can use names and the built-in tokens cellvalue, row, and column.

columns[1].cellProperties = createFormulaConditionalCellProperties(
  '=cellvalue >= TargetPrice',
  { class: 'above-target' },
  {
    allSources: source,
    columns,
    names: [{ name: 'TargetPrice', kind: 'constant', value: 500 }],
  },
);

Supported comparison operators are >, >=, <, <=, =, and <>. Numeric and string comparisons are supported.

Ref updates after row and column changes

When autoUpdateRefs !== false, NamedRangesPlugin updates A1 refs in:

• Named range refs.
• Named formula strings.
• Formula strings stored in existing source rows.

Ref updates happen only when the plugin can infer a deterministic row or column insert/delete. For immutable source replacements, configure rowIdProp so existing rows can be matched reliably.

grid.formulaNames = {
  rowIdProp: 'id',
  names: [{ name: 'InputRows', kind: 'range', ref: 'A2:A20' }],
};

If a replacement is arbitrary and stable identity cannot be inferred, the plugin preserves refs and reports validation state instead of guessing.

Framework examples

The Formula demo includes named ranges across all supported framework examples. Each example includes the same interaction surface: formula names on/off, material/darkMaterial theme switching, odd-row styling on/off, row insertion for ref-update behavior, a formula-name manager panel, named dropdown values, conditional formatting, and highlighted formula cells.

• Vanilla TypeScript: examples/components/src/components/formula/Formula.ts
• React: examples/components/src/components/formula/Formula.tsx
• Vue 3: examples/components/src/components/formula/Formula.vue
• Angular: examples/components/src/components/formula/FormulaAngular.ts

Formula List

The Formula Plugin is based on the formulajs engine, which means many features are only limited by system resources. This flexibility allows for extensive formula functionality, making it possible to use the plugin for a wide range of calculations and data operations.

Below is a comprehensive list of supported formulas and their capabilities.

Date Functions

Title	Call	Result
DATE	DATE(2008, 7, 8)	Tue Jul 08 2008 00:00:00 GMT-0700 (PDT)
DATEVALUE	DATEVALUE('8/22/2011')	Mon Aug 22 2011 00:00:00 GMT-0700 (PDT)
DAY	DAY('15-Apr-11')	15
DAYS	DAYS('3/15/11', '2/1/11')	42
DAYS360	DAYS360('1-Jan-11', '31-Dec-11')	360
EDATE	EDATE('1/15/11', -1)	Wed Dec 15 2010 00:00:00 GMT-0800 (PST)
EOMONTH	EOMONTH('1/1/11', -3)	Sun Oct 31 2010 00:00:00 GMT-0700 (PDT)
HOUR	HOUR('7/18/2011 7:45:00 AM')	7
MINUTE	MINUTE('2/1/2011 12:45:00 PM')	45
ISOWEEKNUM	ISOWEEKNUM('3/9/2012')	10
MONTH	MONTH('15-Apr-11')	4
NETWORKDAYS	NETWORKDAYS('10/1/2012', '3/1/2013', ['11/22/2012'])	109
NETWORKDAYSINTL	NETWORKDAYSINTL('1/1/2006', '2/1/2006', 7, ['1/2/2006'])	23
NOW	NOW()	Thu Feb 20 2020 23:02:55 GMT+0100 (Central European Standard Time)
SECOND	SECOND('2/1/2011 4:48:18 PM')	18
TIME	TIME(16, 48, 10)	0.7001157407407408
TIMEVALUE	TIMEVALUE('22-Aug-2011 6:35 AM')	0.2743055555555556
TODAY	TODAY()	Thu Feb 20 2020 23:02:55 GMT+0100 (Central European Standard Time)
WEEKDAY	WEEKDAY('2/14/2008', 3)	3
YEAR	YEAR('7/5/2008')	2008
WEEKNUM	WEEKNUM('3/9/2012', 2)	11
WORKDAY	WORKDAY('10/1/2008', 151, ['11/26/2008', '12/4/2008'])	Mon May 04 2009 00:00:00 GMT-0700 (PDT)
WORKDAYINTL	WORKDAYINTL('1/1/2012', 30, 17)	Sun Feb 05 2012 00:00:00 GMT-0800 (PST)
YEARFRAC	YEARFRAC('1/1/2012', '7/30/2012', 3)	0.5780821917808219

Financial Functions

Title	Call	Result
ACCRINT	ACCRINT('01/01/2011', '02/01/2011', '07/01/2014', 0.1, 1000, 1, 0)	350
CUMIPMT	CUMIPMT(0.1/12, 30*12, 100000, 13, 24, 0)	-9916.77251395708
CUMPRINC	CUMPRINC(0.1/12, 30*12, 100000, 13, 24, 0)	-614.0863271085149
DB	DB(1000000, 100000, 6, 1, 6)	159500
DDB	DDB(1000000, 100000, 6, 1, 1.5)	250000
DOLLARDE	DOLLARDE(1.1, 16)	1.625
DOLLARFR	DOLLARFR(1.625, 16)	1.1
EFFECT	EFFECT(0.1, 4)	0.10381289062499977
FV	FV(0.1/12, 10, -100, -1000, 0)	2124.874409194097
FVSCHEDULE	FVSCHEDULE(100, [0.09,0.1,0.11])	133.08900000000003
IPMT	IPMT(0.1/12, 6, 2*12, 100000, 1000000, 0)	928.8235718400465
IRR	IRR([-75000,12000,15000,18000,21000,24000], 0.075)	0.05715142887178447
ISPMT	ISPMT(0.1/12, 6, 2*12, 100000)	-625
MIRR	MIRR([-75000,12000,15000,18000,21000,24000], 0.1, 0.12)	0.07971710360838036
NOMINAL	NOMINAL(0.1, 4)	0.09645475633778045
NPER	NPER(0.1/12, -100, -1000, 10000, 0)	63.39385422740764
NPV	NPV(0.1, -10000, 2000, 4000, 8000)	1031.3503176012546
PDURATION	PDURATION(0.1, 1000, 2000)	7.272540897341714
PMT	PMT(0.1/12, 2*12, 100000, 1000000, 0)	-42426.08563793503
PPMT	PPMT(0.1/12, 6, 2*12, 100000, 1000000, 0)	-43354.909209775076
PV	PV(0.1/12, 2*12, 1000, 10000, 0)	-29864.950264779152
RATE	RATE(2*12, -1000, -10000, 100000, 0, 0.1)	0.06517891177181533

Engineering Functions

Title	Call	Result
BIN2DEC	BIN2DEC(101010)	42
BIN2HEX	BIN2HEX(101010)	2a
BIN2OCT	BIN2OCT(101010)	52
BITAND	BITAND(42, 24)	8
BITLSHIFT	BITLSHIFT(42, 24)	704643072
BITOR	BITOR(42, 24)	58
BITRSHIFT	BITRSHIFT(42, 2)	10
BITXOR	BITXOR(42, 24)	50
COMPLEX	COMPLEX(3, 4)	3+4i
CONVERT	CONVERT(64, 'kibyte', 'bit')	524288
DEC2BIN	DEC2BIN(42)	101010
DEC2HEX	DEC2HEX(42)	2a
DEC2OCT	DEC2OCT(42)	52
DELTA	DELTA(42, 42)	1
ERF	ERF(1)	0.8427007929497149
ERFC	ERFC(1)	0.1572992070502851
GESTEP	GESTEP(42, 24)	1
HEX2BIN	HEX2BIN('2a')	101010
HEX2DEC	HEX2DEC('2a')	42
HEX2OCT	HEX2OCT('2a')	52
IMABS	IMABS('3+4i')	5
IMAGINARY	IMAGINARY('3+4i')	4
IMARGUMENT	IMARGUMENT('3+4i')	0.9272952180016122
IMCONJUGATE	IMCONJUGATE('3+4i')	3-4i
IMCOS	IMCOS('1+i')	0.8337300251311491-0.9888977057628651i
IMCOSH	IMCOSH('1+i')	0.8337300251311491+0.9888977057628651i
IMCOT	IMCOT('1+i')	0.21762156185440265-0.8680141428959249i
IMCSC	IMCSC('1+i')	0.6215180171704283-0.3039310016284264i
IMCSCH	IMCSCH('1+i')	0.3039310016284264-0.6215180171704283i
IMDIV	IMDIV('1+2i', '3+4i')	0.44+0.08i
IMEXP	IMEXP('1+i')	1.4686939399158851+2.2873552871788423i
IMLN	IMLN('1+i')	0.3465735902799727+0.7853981633974483i
IMLOG10	IMLOG10('1+i')	0.1505149978319906+0.3410940884604603i
IMLOG2	IMLOG2('1+i')	0.5000000000000001+1.1330900354567985i
IMPOWER	IMPOWER('1+i', 2)	1.2246063538223775e-16+2.0000000000000004i
IMPRODUCT	IMPRODUCT('1+2i', '3+4i', '5+6i')	-85+20i
IMREAL	IMREAL('3+4i')	3
IMSEC	IMSEC('1+i')	0.4983370305551868+0.591083841721045i
IMSECH	IMSECH('1+i')	0.4983370305551868-0.591083841721045i
IMSIN	IMSIN('1+i')	1.2984575814159773+0.6349639147847361i
IMSINH	IMSINH('1+i')	0.6349639147847361+1.2984575814159773i
IMSQRT	IMSQRT('1+i')	1.0986841134678098+0.45508986056222733i
IMSUB	IMSUB('3+4i', '1+2i')	2+2i
IMSUM	IMSUM('1+2i', '3+4i', '5+6i')	9+12i
IMTAN	IMTAN('1+i')	0.2717525853195117+1.0839233273386946i
OCT2BIN	OCT2BIN('52')	101010
OCT2DEC	OCT2DEC('52')	42
OCT2HEX	OCT2HEX('52')	2a

Logical Functions

Title	Call	Result
AND	AND(true, false, true)	false
FALSE	FALSE()	false
IF	IF(true, 'Hello!', 'Goodbye!')	Hello!
IFS	IFS(false, 'Hello!', true, 'Goodbye!')	Goodbye!
IFERROR	IFERROR('#DIV/0!', 'Error')	Error
IFNA	IFNA('#N/A', 'Error')	Error
NOT	NOT(true)	false
OR	OR(true, false, true)	true
SWITCH	SWITCH(7, 9, 'Nine', 7, 'Seven')	Seven
TRUE	TRUE()	true
XOR	XOR(true, false, true)	false

Math Functions

Title	Call	Result
ABS	ABS(-4)	4
ACOS	ACOS(-0.5)	2.0943951023931957
ACOSH	ACOSH(10)	2.993222846126381
ACOT	ACOT(2)	0.46364760900080615
ACOTH	ACOTH(6)	0.16823611831060645
AGGREGATE	AGGREGATE(9, 4, [-5,15], [32,'Hello World!'])	10,32
ARABIC	ARABIC('MCMXII')	1912
ASIN	ASIN(-0.5)	-0.5235987755982988
ASINH	ASINH(-2.5)	-1.6472311463710965
ATAN	ATAN(1)	0.7853981633974483
ATAN2	ATAN2(-1, -1)	-2.356194490192345
ATANH	ATANH(-0.1)	-0.10033534773107562
BASE	BASE(15, 2, 10)	0000001111
CEILING	CEILING(-5.5, 2, -1)	-6
CEILINGMATH	CEILINGMATH(-5.5, 2, -1)	-6
CEILINGPRECISE	CEILINGPRECISE(-4.1, -2)	-4
COMBIN	COMBIN(8, 2)	28
COMBINA	COMBINA(4, 3)	20
COS	COS(1)	0.5403023058681398
COSH	COSH(1)	1.5430806348152437
COT	COT(30)	-0.15611995216165922
COTH	COTH(2)	1.0373147207275482
CSC	CSC(15)	1.5377805615408537
CSCH	CSCH(1.5)	0.46964244059522464
DECIMAL	DECIMAL('FF', 16)	255
ERF	ERF(1)	0.8427007929497149
ERFC	ERFC(1)	0.1572992070502851
EVEN	EVEN(-1)	-2
EXP	EXP(1)	2.718281828459045
FACT	FACT(5)	120
FACTDOUBLE	FACTDOUBLE(7)	105
FLOOR	FLOOR(-3.1)	-4
FLOORMATH	FLOORMATH(-4.1, -2, -1)	-4
FLOORPRECISE	FLOORPRECISE(-3.1, -2)	-4
GCD	GCD(24, 36, 48)	12
INT	INT(-8.9)	-9
ISEVEN	ISEVEN(-2.5)	true
ISOCEILING	ISOCEILING(-4.1, -2)	-4
ISODD	ISODD(-2.5)	false
LCM	LCM(24, 36, 48)	144
LN	LN(86)	4.454347296253507
LOG	LOG(8, 2)	3
LOG10	LOG10(100000)	5
MOD	MOD(3, -2)	-1
MROUND	MROUND(-10, -3)	-9
MULTINOMIAL	MULTINOMIAL(2, 3, 4)	1260
ODD	ODD(-1.5)	-3
POWER	POWER(5, 2)	25
PRODUCT	PRODUCT(5, 15, 30)	2250
QUOTIENT	QUOTIENT(-10, 3)	-3
RADIANS	RADIANS(180)	3.141592653589793
RAND	RAND()	[Random real number greater between 0 and 1]
RANDBETWEEN	RANDBETWEEN(-1, 1)	[Random integer between bottom and top]
ROUND	ROUND(626.3, -3)	1000
ROUNDDOWN	ROUNDDOWN(-3.14159, 2)	-3.14
ROUNDUP	ROUNDUP(-3.14159, 2)	-3.15
SEC	SEC(45)	1.9035944074044246
SECH	SECH(45)	5.725037161098787e-20
SIGN	SIGN(-0.00001)	-1
SIN	SIN(1)	0.8414709848078965
SINH	SINH(1)	1.1752011936438014
SQRT	SQRT(16)	4
SQRTPI	SQRTPI(2)	2.5066282746310002
SUBTOTAL	SUBTOTAL(9, [-5,15], [32,'Hello World!'])	10,32
SUM	SUM(-5, 15, 32, 'Hello World!')	42
SUMIF	SUMIF([2,4,8,16], '>5')	24
SUMIFS	SUMIFS([2,4,8,16], [1,2,3,4], '>=2', [1,2,4,8], '<=4')	12
SUMPRODUCT	SUMPRODUCT([[1,2],[3,4]], [[1,0],[0,1]])	5
SUMSQ	SUMSQ(3, 4)	25
SUMX2MY2	SUMX2MY2([1,2], [3,4])	-20
SUMX2PY2	SUMX2PY2([1,2], [3,4])	30
SUMXMY2	SUMXMY2([1,2], [3,4])	8
TAN	TAN(1)	1.5574077246549023
TANH	TANH(-2)	-0.9640275800758168
TRUNC	TRUNC(-8.9)	-8

Statistical Functions

Title	Call	Result
AVEDEV	AVEDEV([2,4], [8,16])	4.5
AVERAGE	AVERAGE([2,4], [8,16])	7.5
AVERAGEA	AVERAGEA([2,4], [8,16])	7.5
AVERAGEIF	AVERAGEIF([2,4,8,16], '>5', [1, 2, 3, 4])	3.5
AVERAGEIFS	AVERAGEIFS([2,4,8,16], [1,2,3,4], '>=2', [1,2,4,8], '<=4')	6
BETADIST	BETADIST(2, 8, 10, true, 1, 3)	0.6854705810117458
BETAINV	BETAINV(0.6854705810117458, 8, 10, 1, 3)	1.9999999999999998
BINOMDIST	BINOMDIST(6, 10, 0.5, false)	0.205078125
CORREL	CORREL([3,2,4,5,6], [9,7,12,15,17])	0.9970544855015815
COUNT	COUNT([1,2], [3,4])	4
COUNTA	COUNTA([1, null, 3, 'a', '', 'c'])	4
COUNTBLANK	COUNTBLANK([1, null, 3, 'a', '', 'c'])	2
COUNTIF	COUNTIF(['Caen', 'Melbourne', 'Palo Alto', 'Singapore'], 'a')	3
COUNTIFS	COUNTIFS([2,4,8,16], [1,2,3,4], '>=2', [1,2,4,8], '<=4')	2
COUNTUNIQUE	COUNTUNIQUE([1,1,2,2,3,3])	3
COVARIANCEP	COVARIANCEP([3,2,4,5,6], [9,7,12,15,17])	5.2
COVARIANCES	COVARIANCES([2,4,8], [5,11,12])	9.666666666666668
DEVSQ	DEVSQ([2,4,8,16])	115
EXPONDIST	EXPONDIST(0.2, 10, true)	0.8646647167633873
FDIST	FDIST(15.2069, 6, 4, false)	0.0012237917087831735
FINV	FINV(0.01, 6, 4)	0.10930991412457851
FISHER	FISHER(0.75)	0.9729550745276566
FISHERINV	FISHERINV(0.9729550745276566)	0.75
FORECAST	FORECAST(30, [6,7,9,15,21], [20,28,31,38,40])	10.607253086419755
FREQUENCY	FREQUENCY([79,85,78,85,50,81,95,88,97], [70,79,89])	1,2,4,2
GAMMA	GAMMA(2.5)	1.3293403919101043
GAMMALN	GAMMALN(10)	12.801827480081961
GAUSS	GAUSS(2)	0.4772498680518208
GEOMEAN	GEOMEAN([2,4], [8,16])	5.656854249492381
GROWTH	GROWTH([2,4,8,16], [1,2,3,4], [5])	32.00000000000003
HARMEAN	HARMEAN([2,4], [8,16])	4.266666666666667
HYPGEOMDIST	HYPGEOMDIST(1, 4, 8, 20, false)	0.3632610939112487
INTERCEPT	INTERCEPT([2,3,9,1,8], [6,5,11,7,5])	0.04838709677419217
KURT	KURT([3,4,5,2,3,4,5,6,4,7])	-0.15179963720841627
LARGE	LARGE([3,5,3,5,4,4,2,4,6,7], 3)	5
LINEST	LINEST([1,9,5,7], [0,4,2,3], true, true)	2,1
LOGNORMDIST	LOGNORMDIST(4, 3.5, 1.2, true)	0.0390835557068005
LOGNORMINV	LOGNORMINV(0.0390835557068005, 3.5, 1.2, true)	4.000000000000001
MAX	MAX([0.1,0.2], [0.4,0.8], [true, false])	0.8
MAXA	MAXA([0.1,0.2], [0.4,0.8], [true, false])	1
MEDIAN	MEDIAN([1,2,3], [4,5,6])	3.5
MIN	MIN([0.1,0.2], [0.4,0.8], [true, false])	0.1
MINA	MINA([0.1,0.2], [0.4,0.8], [true, false])	0
MODEMULT	MODEMULT([1,2,3,4,3,2,1,2,3])	2,3
MODESNGL	MODESNGL([1,2,3,4,3,2,1,2,3])	2
NORMDIST	NORMDIST(42, 40, 1.5, true)	0.9087887802741321
NORMINV	NORMINV(0.9087887802741321, 40, 1.5)	42
NORMSDIST	NORMSDIST(1, true)	0.8413447460685429
NORMSINV	NORMSINV(0.8413447460685429)	1.0000000000000002
PEARSON	PEARSON([9,7,5,3,1], [10,6,1,5,3])	0.6993786061802354
PERCENTILEEXC	PERCENTILEEXC([1,2,3,4], 0.3)	1.5
PERCENTILEINC	PERCENTILEINC([1,2,3,4], 0.3)	1.9
PERCENTRANKEXC	PERCENTRANKEXC([1,2,3,4], 2, 2)	0.4
PERCENTRANKINC	PERCENTRANKINC([1,2,3,4], 2, 2)	0.33
PERMUT	PERMUT(100, 3)	970200
PERMUTATIONA	PERMUTATIONA(4, 3)	64
PHI	PHI(0.75)	0.30113743215480443
POISSONDIST	POISSONDIST(2, 5, true)	0.12465201948308113
PROB	PROB([1,2,3,4], [0.1,0.2,0.2,0.1], 2, 3)	0.4
QUARTILEEXC	QUARTILEEXC([1,2,3,4], 1)	1.25
QUARTILEINC	QUARTILEINC([1,2,3,4], 1)	1.75
RANKAVG	RANKAVG(4, [2,4,4,8,8,16], false)	4.5
RANKEQ	RANKEQ(4, [2,4,4,8,8,16], false)	4
RSQ	RSQ([9,7,5,3,1], [10,6,1,5,3])	0.4891304347826088
SKEW	SKEW([3,4,5,2,3,4,5,6,4,7])	0.3595430714067974
SKEWP	SKEWP([3,4,5,2,3,4,5,6,4,7])	0.303193339354144
SLOPE	SLOPE([1,9,5,7], [0,4,2,3])	2
SMALL	SMALL([3,5,3,5,4,4,2,4,6,7], 3)	3
STANDARDIZE	STANDARDIZE(42, 40, 1.5)	1.3333333333333333
STDEVA	STDEVA([2,4], [8,16], [true, false])	6.013872850889572
STDEVP	STDEVP([2,4], [8,16], [true, false])	5.361902647381804
STDEVPA	STDEVPA([2,4], [8,16], [true, false])	5.489889697333535
STDEVS	STDEVS([2,4], [8,16], [true, false])	6.191391873668904
STEYX	STEYX([2,3,9,1,8,7,5], [6,5,11,7,5,4,4])	3.305718950210041
TDIST	TDIST(60, 1, true)	0.9946953263673741
TINV	TINV(0.9946953263673741, 1)	59.99999999996535
TRIMMEAN	TRIMMEAN([4,5,6,7,2,3,4,5,1,2,3], 0.2)	3.7777777777777777
VARA	VARA([2,4], [8,16], [true, false])	36.16666666666667
VARP	VARP([2,4], [8,16], [true, false])	28.75
VARPA	VARPA([2,4], [8,16], [true, false])	30.13888888888889
VARS	VARS([2,4], [8,16], [true, false])	38.333333333333336
WEIBULLDIST	WEIBULLDIST(105, 20, 100, true)	0.9295813900692769
ZTEST	ZTEST([3,6,7,8,6,5,4,2,1,9], 4)	0.09057419685136381

Text Functions

Title	Call	Result
CHAR	CHAR(65)	A
CLEAN	CLEAN('Monthly report')	Monthly report
CODE	CODE('A')	65
CONCATENATE	CONCATENATE('Andreas', ' ', 'Hauser')	Andreas Hauser
EXACT	EXACT('Word', 'word')	false
FIND	FIND('M', 'Miriam McGovern', 3)	8
LEFT	LEFT('Sale Price', 4)	Sale
LEN	LEN('Phoenix, AZ')	11
LOWER	LOWER('E. E. Cummings')	e. e. cummings
MID	MID('Fluid Flow', 7, 20)	Flow
NUMBERVALUE	NUMBERVALUE('2.500,27', ',', '.')	2500.27
PROPER	PROPER('this is a TITLE')	This Is A Title
REGEXEXTRACT	REGEXEXTRACT('Palo Alto', 'Alto')	Alto
REGEXMATCH	REGEXMATCH('Palo Alto', 'Alto')	true
REGEXREPLACE	REGEXREPLACE('Sutoiku', 'utoiku', 'TOIC')	STOIC
REPLACE	REPLACE('abcdefghijk', 6, 5, '*')	abcde*k
REPT	REPT('*-', 3)	*-*-*-
RIGHT	RIGHT('Sale Price', 5)	Price
ROMAN	ROMAN(499)	CDXCIX
SEARCH	SEARCH('margin', 'Profit Margin')	8
SPLIT	SPLIT('A,B,C', ',')	A,B,C
SUBSTITUTE	SUBSTITUTE('Quarter 1, 2011', '1', '2', 3)	Quarter 1, 2012
T	T('Rainfall')	Rainfall
TRIM	TRIM(' First Quarter Earnings ')	First Quarter Earnings
UNICHAR	UNICHAR(66)	B
UNICODE	UNICODE('B')	66
UPPER	UPPER('total')	TOTAL

The performance and limits of the Formula Plugin are largely dependent on the system resources available. Factors such as available memory and CPU power can influence the plugin’s performance, especially when working with large datasets or complex calculations. Always consider the environment in which the plugin will be used to optimize its performance.

Saving Resolved (Calculated) Values

The Formula Plugin evaluates formulas at render time only. The underlying data source always stores the raw formula string (e.g., =SUM(B1:B6)). This mirrors the way Excel works internally — the formula itself is the source of truth, and the computed display value is derived from it.

This means that reading grid.source (or listening to afteredit) will return the formula string, not the numeric result. If you need to persist the computed value to a database you must resolve it yourself before saving.

Resolving formulas before saving

Use the evaluateRawValuesFormula utility (exported from @revolist/revogrid-pro) together with grid.getSource() and grid.getColumns():

import { evaluateRawValuesFormula, isFormula } from '@revolist/revogrid-pro';

async function saveToDatabase(grid: HTMLRevoGridElement) {
  const source  = await grid.getSource();   // raw rows — formulas intact
  const columns = await grid.getColumns();  // ColumnRegular[]

  const resolvedRows = source.map(row => {
    const resolved = { ...row };
    for (const col of columns) {
      const val = row[col.prop];
      if (isFormula(val)) {
        resolved[col.prop] = evaluateRawValuesFormula(val, source, columns);
      }
    }
    return resolved;
  });

  // persist resolvedRows to your database
}

Note

evaluateRawValuesFormula expects flat ColumnRegular[] (no grouping columns). The column order must match the column array you pass to the grid, as the formula engine resolves cell addresses (e.g., B3) by column index within that array. This is the same approach used internally by the Excel export plugin.