Overview

Source code

// src/components/formula/Formula.ts

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

import {
  AutoFillPlugin,
  AutoFillPreviewPlugin,
  ColumnDropdown,
  ColumnStretchPlugin,
  FormulaPlugin,
  FormulaBarPlugin,
  FormulaDependencyHighlightPlugin,
  NamedRangesPlugin,
  RangeSelectionLimitPlugin,
  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.scss';
import {
  EXTERNAL_SHEET_LOAD_DELAY_MS,
  SERVICE_LOOKUP_FORMULA,
  registerServiceLookup,
} from './formula-async-service';

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>';
const lookupFormula = "=INDEX('Product List'!$B$1:$B$2,MATCH(B1,'Product List'!$A$1:$A$2,0))";

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 catalogButton = document.createElement('button');
  catalogButton.className = 'rv-btn';
  catalogButton.type = 'button';
  const serviceButton = document.createElement('button');
  serviceButton.className = 'rv-btn';
  serviceButton.type = 'button';
  serviceButton.textContent = 'Reload service lookup';
  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.'),
    catalogButton,
    help('Load the missing Product List dataset, then update it without mounting another grid.'),
    serviceButton,
    help('Invalidate the async VLOOKUP override so it requests fresh service data.'),
    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,
  );
  const workbookMap = document.createElement('section');
  workbookMap.className = 'formula-workbook-map';
  workbookMap.setAttribute('aria-label', 'Cross-sheet lookup setup');
  workbookMap.innerHTML = `
    <div class="formula-workbook-flow">
      <span class="formula-sheet-pill"><strong>Orders</strong><small>mounted RevoGrid</small></span>
      <span class="formula-workbook-arrow" aria-hidden="true">looks up →</span>
      <span class="formula-sheet-pill formula-sheet-pill-external"><strong>Product List</strong><small data-testid="formula-external-sheet-status">not loaded · no grid mounted</small></span>
    </div>
    <div class="formula-workbook-reference">
      <span>Formula stored in Orders!D1</span>
      <code data-testid="formula-cross-sheet-formula">${lookupFormula}</code>
    </div>
    <div class="formula-external-sheet" data-testid="formula-external-sheet">
      <span class="formula-external-sheet-title">Product List is not loaded yet</span>
      <span class="formula-external-sheet-pending" data-testid="formula-external-sheet-pending">Waiting for backend data…</span>
    </div>
  `;
  const externalSheetStatus = workbookMap.querySelector<HTMLElement>('[data-testid="formula-external-sheet-status"]');
  const externalSheet = workbookMap.querySelector<HTMLElement>('[data-testid="formula-external-sheet"]');
  shell.append(toolbar, workbookMap);

  const grid = document.createElement('revo-grid');
  grid.rowHeaders = true;
  grid.range = true;
  grid.stretch = 'last';
  grid.hideAttribution = true;
  grid.theme = isDark() ? 'darkCompact' : 'compact';
  grid.className = 'formula-grid';
  grid.plugins = [
    AutoFillPlugin,
    AutoFillPreviewPlugin,
    RangeSelectionLimitPlugin,
    FormulaBarPlugin,
    FormulaDependencyHighlightPlugin,
    NamedRangesPlugin,
    FormulaPlugin,
    RowOddPlugin,
    ColumnStretchPlugin,
    ExportExcelPlugin,
  ];
  grid.rangeSelectionLimit = { mode: 'column' };

  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 catalogHardwarePrice = 650;
  let catalogLoaded = false;
  let rowId = 100;
  let managerMounted = false;
  let disposeServiceLookup: (() => void) | undefined;
  let externalSheetLoadTimer: number | undefined;
  let disposed = false;
  const formulaDependencyHighlight = {
    dependencyClass: 'formula-dependency-cell',
    formulaCellClass: 'formula-dependency-active-cell',
    dependencyColors: ['#2563eb', '#dc2626', '#16a34a', '#9333ea', '#ea580c', '#0891b2'],
    includeNamedRanges: true,
  };
  const source = createRandomData(100).map((row, index) => ({
    id: index,
    price: row.price,
    category: index % 2 === 0 ? 'Hardware' : 'Software',
  }));
  const productSheet = {
    id: 'Product List',
    rowIdProp: 'id',
    columns: [{ prop: 'category' }, { prop: 'price' }],
    rows: [
      { id: 'product-hardware', category: 'Hardware', price: 650 },
      { id: 'product-software', category: 'Software', price: 450 },
    ],
  };
  const formulaWorkbook = {
    sheetId: 'Orders',
    rowIdProp: 'id',
    externalSheets: [],
  };

  const buildFormulaNames = () => ({
    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 extraOptions = categoryOptions.filter(
      value => !dropdown.source.some(option => option.value === value),
    );
    return {
      ...dropdown,
      syncCellTemplate: true,
      source: [
        ...dropdown.source,
        ...extraOptions.map(value => ({ value, label: value })),
      ],
    };
  };

  const buildGridSource = () => source.map((row, index) => ({
    ...row,
    forecast: `=A${index + 1}+${targetFormulaRef()}`,
    catalogPrice: `=INDEX('Product List'!$B$1:$B$2,MATCH(B${index + 1},'Product List'!$A$1:$A$2,0))`,
    servicePrice: index === 0 ? SERVICE_LOOKUP_FORMULA : '',
  }));

  const buildColumns = (formulaNames: ReturnType<typeof buildFormulaNames>) => {
    const columns: any[] = [
      {
        name: 'Price',
        prop: 'price',
        cellTemplate: (_, { value }) => parseFloat(value).toFixed(2),
      },
      {
        name: 'Category',
        prop: 'category',
        size: 200,
        columnType: 'categoryDropdown',
        readonly: ({ type }) => type === 'rowPinEnd',
        cellProperties({ type }) {
          return type === 'rowPinEnd' ? { class: 'formula-category-pinned' } : undefined;
        },
      },
      {
        name: 'Formula',
        prop: 'forecast',
        cellProperties: () => ({ class: 'formula-cell' }),
      },
      {
        name: 'Catalog (INDEX/MATCH)',
        prop: 'catalogPrice',
        cellProperties: () => ({ class: 'formula-cell' }),
      },
      {
        name: 'Service (async VLOOKUP)',
        prop: 'servicePrice',
        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';
    catalogButton.textContent = catalogLoaded
      ? catalogHardwarePrice === 650 ? 'Raise catalog price' : 'Reset catalog price'
      : 'Load Product List';
    targetInput.value = String(targetPrice);

    grid.formulaNames = formulaNames;
    if (!grid.formulaWorkbook) {
      grid.formulaWorkbook = formulaWorkbook;
    }
    grid.formulaDependencyHighlight = formulaDependencyHighlight;
    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}`,
      catalogPrice: `=SUM(D1:D${source.length})`,
      servicePrice: '',
    }];
    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();
  });
  catalogButton.addEventListener('click', async () => {
    const nextPrice = catalogHardwarePrice === 650 ? 725 : 650;
    const plugins = await grid.getPlugins();
    const formulaPlugin = plugins.find((plugin) => plugin instanceof FormulaPlugin) as FormulaPlugin | undefined;
    if (!catalogLoaded) {
      formulaPlugin?.upsertFormulaSheet(productSheet);
      if (!formulaPlugin) {
        return;
      }
      catalogLoaded = true;
      catalogButton.textContent = 'Raise catalog price';
      if (externalSheetStatus) {
        externalSheetStatus.textContent = 'externalSheets · no grid mounted';
      }
      if (externalSheet) {
        externalSheet.innerHTML = `
          <span class="formula-external-sheet-title">Product List registered after Orders mounted</span>
          <span>A1&nbsp; Hardware</span><strong data-testid="formula-external-hardware-price">B1&nbsp; ${catalogHardwarePrice}</strong>
          <span>A2&nbsp; Software</span><strong>B2&nbsp; 450</strong>
        `;
      }
      return;
    }
    formulaPlugin?.updateFormulaSheetCell('Product List', 'product-hardware', 'price', nextPrice);
    catalogHardwarePrice = nextPrice;
    catalogButton.textContent = catalogHardwarePrice === 650 ? 'Raise catalog price' : 'Reset catalog price';
    const externalHardwarePrice = externalSheet?.querySelector<HTMLElement>('[data-testid="formula-external-hardware-price"]');
    if (externalHardwarePrice) externalHardwarePrice.textContent = `B1  ${catalogHardwarePrice}`;
  });
  serviceButton.addEventListener('click', async () => {
    const plugins = await grid.getPlugins();
    const formulaPlugin = plugins.find((plugin) => plugin instanceof FormulaPlugin) as FormulaPlugin | undefined;
    formulaPlugin?.invalidateAsyncFormulaFunction('VLOOKUP');
  });
  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();
  externalSheetLoadTimer = window.setTimeout(() => {
    if (!catalogLoaded) catalogButton.click();
  }, EXTERNAL_SHEET_LOAD_DELAY_MS);
  void customElements.whenDefined('revo-grid').then(() => grid.getPlugins()).then((plugins) => {
    const formulaPlugin = plugins.find((plugin) => plugin instanceof FormulaPlugin) as FormulaPlugin | undefined;
    if (!formulaPlugin) return;
    disposeServiceLookup = registerServiceLookup(formulaPlugin);
    if (disposed) disposeServiceLookup();
  });

  return () => {
    disposed = true;
    if (externalSheetLoadTimer !== undefined) window.clearTimeout(externalSheetLoadTimer);
    disposeServiceLookup?.();
    grid.removeEventListener('formulanameschange', syncToolbarTarget);
    shell.remove();
  };
}

// src/components/formula/Formula.tsx

import React, { useEffect, useMemo, useRef, useState } from 'react';
import { RevoGrid, type DataType } from '@revolist/react-datagrid';
import {
  AutoFillPlugin,
  AutoFillPreviewPlugin,
  ColumnDropdown,
  ColumnStretchPlugin,
  FormulaBarPlugin,
  FormulaDependencyHighlightPlugin,
  FormulaPlugin,
  NamedRangesPlugin,
  RangeSelectionLimitPlugin,
  ExportExcelPlugin,
  RowOddPlugin,
  type ExportExcelEvent,
  createFormulaConditionalCellProperties,
  createNamedRangeDropdown,
  defineFormulaNameManager,
  type FormulaNameRegistry,
  type FormulaGridWorkbookConfig,
} 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.scss';
import {
  EXTERNAL_SHEET_LOAD_DELAY_MS,
  SERVICE_LOOKUP_FORMULA,
  registerServiceLookup,
} from './formula-async-service';

defineExampleHelpTooltipElement();
const lookupFormula = "=INDEX('Product List'!$B$1:$B$2,MATCH(B1,'Product List'!$A$1:$A$2,0))";
const productSheet = {
  id: 'Product List',
  rowIdProp: 'id',
  columns: [{ prop: 'category' }, { prop: 'price' }],
  rows: [
    { id: 'product-hardware', category: 'Hardware', price: 650 },
    { id: 'product-software', category: 'Software', price: 450 },
  ],
};

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 isDarkTheme = 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 catalogLoadedRef = useRef(false);
  const [namesEnabled, setNamesEnabled] = useState(true);
  const [targetPrice, setTargetPrice] = useState(500);
  const [catalogHardwarePrice, setCatalogHardwarePrice] = useState(650);
  const [catalogLoaded, setCatalogLoaded] = useState(false);
  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(() => ({
    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 formulaDependencyHighlight = useMemo(() => ({
    dependencyClass: 'formula-dependency-cell',
    formulaCellClass: 'formula-dependency-active-cell',
    dependencyColors: ['#2563eb', '#dc2626', '#16a34a', '#9333ea', '#ea580c', '#0891b2'],
    includeNamedRanges: true,
  }), []);
  const formulaWorkbook = useMemo<FormulaGridWorkbookConfig>(() => ({
    sheetId: 'Orders',
    rowIdProp: 'id',
    externalSheets: [],
  }), []);

  const gridSource = useMemo(() => source.map((row, index) => ({
    ...row,
    forecast: `=A${index + 1}+${targetFormulaRef}`,
    catalogPrice: `=INDEX('Product List'!$B$1:$B$2,MATCH(B${index + 1},'Product List'!$A$1:$A$2,0))`,
    servicePrice: index === 0 ? SERVICE_LOOKUP_FORMULA : '',
  })), [source, targetFormulaRef]);

  const columns = useMemo(
    () => {
      const nextColumns: any[] = [
        {
          name: 'Price',
          prop: 'price',
          cellTemplate: (_, { value }) => parseFloat(value).toFixed(2),
        },
        {
          name: 'Category',
          prop: 'category',
          columnType: 'categoryDropdown',
          readonly: ({ type }) => type === 'rowPinEnd',
          cellProperties: ({ type }) => type === 'rowPinEnd' ? { class: 'formula-category-pinned' } : undefined,
        },
        {
          name: 'Formula',
          prop: 'forecast',
          cellProperties: () => ({ class: 'formula-cell' }),
        },
        {
          name: 'Catalog (INDEX/MATCH)',
          prop: 'catalogPrice',
          cellProperties: () => ({ class: 'formula-cell' }),
        },
        {
          name: 'Service (async VLOOKUP)',
          prop: 'servicePrice',
          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,
        syncCellTemplate: true,
        source: [
          ...categoryDropdown.source,
          ...categoryOptions
            .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}`,
      catalogPrice: `=SUM(D1:D${source.length})`,
      servicePrice: '',
    },
  ], [priceListRef, source.length, summaryCategoryLabel, targetFormulaRef]);

  const plugins = useMemo(() => [
    AutoFillPlugin,
    AutoFillPreviewPlugin,
    RangeSelectionLimitPlugin,
    FormulaBarPlugin,
    FormulaDependencyHighlightPlugin,
    NamedRangesPlugin,
    FormulaPlugin,
    RowOddPlugin,
    ColumnStretchPlugin,
    ExportExcelPlugin,
  ], []);
  const rangeSelectionLimit = useMemo(() => ({ mode: 'column' as const }), []);
  const columnTypes = useMemo(() => ({ categoryDropdown: ColumnDropdown }), []);
  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(() => {
    const grid = gridRef.current;
    if (!formulaBarRef.current || !grid) {
      return;
    }
    grid.formulaBar = {
      el: formulaBarRef.current,
      badgeEl: formulaBarBadgeRef.current,
      showCellBadge: true,
    };
    return () => {
      grid.formulaBar = null;
    };
  }, []);

  useEffect(() => {
    const timer = window.setTimeout(() => {
      const grid = gridRef.current;
      if (!grid || catalogLoadedRef.current) return;
      void grid.getPlugins().then((gridPlugins) => {
        if (catalogLoadedRef.current) return;
        const formulaPlugin = gridPlugins.find(
          plugin => plugin instanceof FormulaPlugin,
        ) as FormulaPlugin | undefined;
        formulaPlugin?.upsertFormulaSheet(productSheet);
        catalogLoadedRef.current = Boolean(formulaPlugin);
        setCatalogLoaded(Boolean(formulaPlugin));
      });
    }, EXTERNAL_SHEET_LOAD_DELAY_MS);
    return () => window.clearTimeout(timer);
  }, []);

  useEffect(() => {
    const grid = gridRef.current;
    if (!grid) return;
    let disposed = false;
    let unregister: (() => void) | undefined;
    void grid.getPlugins().then((gridPlugins) => {
      const formulaPlugin = gridPlugins.find(
        plugin => plugin instanceof FormulaPlugin,
      ) as FormulaPlugin | undefined;
      if (!formulaPlugin) return;
      unregister = registerServiceLookup(formulaPlugin);
      if (disposed) unregister();
    });
    return () => {
      disposed = true;
      unregister?.();
    };
  }, []);

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

  const updateCatalogPrice = async () => {
    const grid = gridRef.current;
    if (!grid) {
      return;
    }
    const nextPrice = catalogHardwarePrice === 650 ? 725 : 650;
    const gridPlugins = await grid.getPlugins();
    const formulaPlugin = gridPlugins.find(
      plugin => plugin instanceof FormulaPlugin,
    ) as FormulaPlugin | undefined;
    if (!catalogLoaded) {
      formulaPlugin?.upsertFormulaSheet(productSheet);
      catalogLoadedRef.current = Boolean(formulaPlugin);
      setCatalogLoaded(Boolean(formulaPlugin));
      return;
    }
    formulaPlugin?.updateFormulaSheetCell('Product List', 'product-hardware', 'price', nextPrice);
    setCatalogHardwarePrice(nextPrice);
  };

  const reloadServiceLookup = async () => {
    const grid = gridRef.current;
    if (!grid) return;
    const gridPlugins = await grid.getPlugins();
    const formulaPlugin = gridPlugins.find(
      plugin => plugin instanceof FormulaPlugin,
    ) as FormulaPlugin | undefined;
    formulaPlugin?.invalidateAsyncFormulaFunction('VLOOKUP');
  };

  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" onClick={updateCatalogPrice}>
          {!catalogLoaded ? 'Load Product List' : catalogHardwarePrice === 650 ? 'Raise catalog price' : 'Reset catalog price'}
        </button>
        <Help text="Load the missing Product List dataset, then update it without mounting another grid." />
        <button className="rv-btn" type="button" onClick={reloadServiceLookup}>Reload service lookup</button>
        <Help text="Invalidate the async VLOOKUP override so it requests fresh service data." />
        <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>
      <section className="formula-workbook-map" aria-label="Cross-sheet lookup setup">
        <div className="formula-workbook-flow">
          <span className="formula-sheet-pill">
            <strong>Orders</strong>
            <small>mounted RevoGrid</small>
          </span>
          <span className="formula-workbook-arrow" aria-hidden="true">looks up →</span>
          <span className="formula-sheet-pill formula-sheet-pill-external">
            <strong>Product List</strong>
            <small>{catalogLoaded ? 'externalSheets · no grid mounted' : 'not loaded · no grid mounted'}</small>
          </span>
        </div>
        <div className="formula-workbook-reference">
          <span>Formula stored in Orders!D1</span>
          <code data-testid="formula-cross-sheet-formula">{lookupFormula}</code>
        </div>
        <div className="formula-external-sheet" data-testid="formula-external-sheet">
          <span className="formula-external-sheet-title">
            {catalogLoaded ? 'Product List registered after Orders mounted' : 'Product List is not loaded yet'}
          </span>
          {catalogLoaded ? <>
            <span>A1&nbsp; Hardware</span>
            <strong data-testid="formula-external-hardware-price">B1&nbsp; {catalogHardwarePrice}</strong>
            <span>A2&nbsp; Software</span>
            <strong>B2&nbsp; 450</strong>
          </> : (
            <span className="formula-external-sheet-pending" data-testid="formula-external-sheet-pending">
              Waiting for backend data…
            </span>
          )}
        </div>
      </section>
      <RevoGrid
        ref={gridRef}
        rowHeaders={true}
        columns={columns}
        source={gridSource}
        pinnedBottomSource={pinnedBottomSource}
        columnTypes={columnTypes}
        formulaNames={formulaNames}
        formulaWorkbook={formulaWorkbook}
        formulaDependencyHighlight={formulaDependencyHighlight}
        rangeSelectionLimit={rangeSelectionLimit}
        stretch="all"
        range={true}
        hide-attribution
        theme={isDarkTheme ? 'darkCompact' : 'compact'}
        plugins={plugins}
        className="formula-grid"
      />
      <div ref={managerRef} className="formula-manager" hidden={!showManager} />
    </div>
  );
}

export default Formula;

<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" @click="updateCatalogPrice">
        {{ !catalogLoaded ? 'Load Product List' : catalogHardwarePrice === 650 ? 'Raise catalog price' : 'Reset catalog price' }}
      </button>
      <example-help-tooltip description="Load the missing Product List dataset, then update it without mounting another grid." />
      <button class="rv-btn" type="button" @click="reloadServiceLookup">Reload service lookup</button>
      <example-help-tooltip description="Invalidate the async VLOOKUP override so it requests fresh service data." />
      <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>
    <section class="formula-workbook-map" aria-label="Cross-sheet lookup setup">
      <div class="formula-workbook-flow">
        <span class="formula-sheet-pill">
          <strong>Orders</strong>
          <small>mounted RevoGrid</small>
        </span>
        <span class="formula-workbook-arrow" aria-hidden="true">looks up →</span>
        <span class="formula-sheet-pill formula-sheet-pill-external">
          <strong>Product List</strong>
          <small>{{ catalogLoaded ? 'externalSheets · no grid mounted' : 'not loaded · no grid mounted' }}</small>
        </span>
      </div>
      <div class="formula-workbook-reference">
        <span>Formula stored in Orders!D1</span>
        <code data-testid="formula-cross-sheet-formula">{{ lookupFormula }}</code>
      </div>
      <div class="formula-external-sheet" data-testid="formula-external-sheet">
        <span class="formula-external-sheet-title">
          {{ catalogLoaded ? 'Product List registered after Orders mounted' : 'Product List is not loaded yet' }}
        </span>
        <template v-if="catalogLoaded">
          <span>A1&nbsp; Hardware</span>
          <strong data-testid="formula-external-hardware-price">B1&nbsp; {{ catalogHardwarePrice }}</strong>
          <span>A2&nbsp; Software</span>
          <strong>B2&nbsp; 450</strong>
        </template>
        <span v-else class="formula-external-sheet-pending" data-testid="formula-external-sheet-pending">
          Waiting for backend data…
        </span>
      </div>
    </section>
    <VGrid
      ref="gridRef"
      :theme="isDark ? 'darkCompact' : 'compact'"
      :columns="columns"
      :source="gridRows"
      :pinned-bottom-source="pinnedBottomSource"
      :column-types="columnTypes"
      :plugins="plugins"
      :formula-names.prop="formulaNames"
      :formula-workbook.prop="formulaWorkbook"
      :formula-dependency-highlight.prop="formulaDependencyHighlight"
      :range-selection-limit.prop="rangeSelectionLimit"
      stretch="all"
      range
      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 {
  AutoFillPlugin,
  AutoFillPreviewPlugin,
  ColumnDropdown,
  ColumnStretchPlugin,
  FormulaBarPlugin,
  FormulaDependencyHighlightPlugin,
  FormulaPlugin,
  NamedRangesPlugin,
  RangeSelectionLimitPlugin,
  ExportExcelPlugin,
  RowOddPlugin,
  type ExportExcelEvent,
  createFormulaConditionalCellProperties,
  createNamedRangeDropdown,
  defineFormulaNameManager,
  type FormulaNameRegistry,
  type FormulaGridWorkbookConfig,
} 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.scss'
import {
  EXTERNAL_SHEET_LOAD_DELAY_MS,
  SERVICE_LOOKUP_FORMULA,
  registerServiceLookup,
} from './formula-async-service'

defineExampleHelpTooltipElement()

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

const namesEnabled = ref(true)
const targetPrice = ref(500)
const catalogHardwarePrice = ref(650)
const catalogLoaded = ref(false)
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
let disposeServiceLookup: (() => void) | undefined
let externalSheetLoadTimer: number | undefined

const formulaNames = computed(() => ({
  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 lookupFormula = "=INDEX('Product List'!$B$1:$B$2,MATCH(B1,'Product List'!$A$1:$A$2,0))"

const formulaDependencyHighlight = {
  dependencyClass: 'formula-dependency-cell',
  formulaCellClass: 'formula-dependency-active-cell',
  dependencyColors: ['#2563eb', '#dc2626', '#16a34a', '#9333ea', '#ea580c', '#0891b2'],
  includeNamedRanges: true,
}

const plugins = [
  AutoFillPlugin,
  AutoFillPreviewPlugin,
  RangeSelectionLimitPlugin,
  FormulaBarPlugin,
  FormulaDependencyHighlightPlugin,
  NamedRangesPlugin,
  FormulaPlugin,
  RowOddPlugin,
  ColumnStretchPlugin,
  ExportExcelPlugin,
]
const rangeSelectionLimit = { mode: 'column' as const }
const columnTypes = { categoryDropdown: ColumnDropdown }
const exportConfig: ExportExcelEvent = { sheetName: 'RevoGrid Formula', workbookName: 'formula-example.xlsx' }
const productSheet = {
  id: 'Product List',
  rowIdProp: 'id',
  columns: [{ prop: 'category' }, { prop: 'price' }],
  rows: [
    { id: 'product-hardware', category: 'Hardware', price: 650 },
    { id: 'product-software', category: 'Software', price: 450 },
  ],
}
const formulaWorkbook: FormulaGridWorkbookConfig = {
  sheetId: 'Orders',
  rowIdProp: 'id',
  externalSheets: [],
}

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}`,
  catalogPrice: `=INDEX('Product List'!$B$1:$B$2,MATCH(B${index + 1},'Product List'!$A$1:$A$2,0))`,
  servicePrice: index === 0 ? SERVICE_LOOKUP_FORMULA : '',
})))

const columns = computed<ColumnRegular[]>(() => {
  const nextColumns: ColumnRegular[] = [
    {
      name: 'Price',
      prop: 'price',
      cellTemplate: (_, { value }) => parseFloat(value).toFixed(2),
    },
    {
      name: 'Category',
      prop: 'category',
      columnType: 'categoryDropdown',
      readonly: ({ type }: { type: string }) => type === 'rowPinEnd',
      cellProperties: ({ type }: { type: string }) => {
        if (type === 'rowPinEnd') {
          return { class: 'formula-category-pinned' }
        }
        return {}
      },
    },
    {
      name: 'Formula',
      prop: 'forecast',
      cellProperties: () => ({ class: 'formula-cell' }),
    },
    {
      name: 'Catalog (INDEX/MATCH)',
      prop: 'catalogPrice',
      cellProperties: () => ({ class: 'formula-cell' }),
    },
    {
      name: 'Service (async VLOOKUP)',
      prop: 'servicePrice',
      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,
    syncCellTemplate: true,
    source: [
      ...categoryDropdown.source,
      ...categoryOptions
        .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}`,
    catalogPrice: `=SUM(D1:D${rows.value.length})`,
    servicePrice: '',
  },
])

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

async function updateCatalogPrice() {
  const grid = getGridEl()
  if (!grid) {
    return
  }
  const nextPrice = catalogHardwarePrice.value === 650 ? 725 : 650
  const gridPlugins = await grid.getPlugins()
  const formulaPlugin = gridPlugins.find(
    plugin => plugin instanceof FormulaPlugin,
  ) as FormulaPlugin | undefined
  if (!catalogLoaded.value) {
    await loadProductList()
    return
  }
  formulaPlugin?.updateFormulaSheetCell('Product List', 'product-hardware', 'price', nextPrice)
  catalogHardwarePrice.value = nextPrice
}

async function loadProductList() {
  if (catalogLoaded.value) return
  const grid = getGridEl()
  if (!grid) return
  const gridPlugins = await grid.getPlugins()
  const formulaPlugin = gridPlugins.find(
    plugin => plugin instanceof FormulaPlugin,
  ) as FormulaPlugin | undefined
  formulaPlugin?.upsertFormulaSheet(productSheet)
  catalogLoaded.value = Boolean(formulaPlugin)
}

async function reloadServiceLookup() {
  const grid = getGridEl()
  if (!grid) return
  const gridPlugins = await grid.getPlugins()
  const formulaPlugin = gridPlugins.find(
    plugin => plugin instanceof FormulaPlugin,
  ) as FormulaPlugin | undefined
  formulaPlugin?.invalidateAsyncFormulaFunction('VLOOKUP')
}

async function installServiceLookup() {
  const grid = getGridEl()
  if (!grid) return
  const gridPlugins = await grid.getPlugins()
  const formulaPlugin = gridPlugins.find(
    plugin => plugin instanceof FormulaPlugin,
  ) as FormulaPlugin | undefined
  if (formulaPlugin) disposeServiceLookup = registerServiceLookup(formulaPlugin)
}

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

onMounted(async () => {
  bindFormulaBar()
  await installServiceLookup()
  externalSheetLoadTimer = window.setTimeout(() => {
    void loadProductList()
  }, EXTERNAL_SHEET_LOAD_DELAY_MS)
})

onBeforeUnmount(() => {
  if (externalSheetLoadTimer !== undefined) window.clearTimeout(externalSheetLoadTimer)
  disposeServiceLookup?.()
  if (boundFormulaGrid) {
    boundFormulaGrid.removeEventListener('formulanameschange', syncToolbarTarget)
    boundFormulaGrid.formulaBar = null
    boundFormulaGrid = null
  }
})
</script>

import { CUSTOM_ELEMENTS_SCHEMA, ChangeDetectorRef, Component, ElementRef, ViewChild, ViewEncapsulation, type AfterViewInit, type OnDestroy, type OnInit } from '@angular/core';
import { RevoGrid } from '@revolist/angular-datagrid';
import {
  AutoFillPlugin,
  AutoFillPreviewPlugin,
  ColumnDropdown,
  ColumnStretchPlugin,
  FormulaBarPlugin,
  FormulaDependencyHighlightPlugin,
  FormulaPlugin,
  NamedRangesPlugin,
  RangeSelectionLimitPlugin,
  ExportExcelPlugin,
  RowOddPlugin,
  createFormulaConditionalCellProperties,
  createNamedRangeDropdown,
  defineFormulaNameManager,
  type FormulaBarConfig,
  type FormulaDependencyHighlightConfig,
  type FormulaNameDefinition,
  type FormulaNamesConfig,
  type FormulaNameRegistry,
  type FormulaGridWorkbookConfig,
  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';
import {
  EXTERNAL_SHEET_LOAD_DELAY_MS,
  SERVICE_LOOKUP_FORMULA,
  registerServiceLookup,
} from './formula-async-service';

type FormulaGridElement = HTMLRevoGridElement & {
  formulaNames?: FormulaNamesConfig | FormulaNameDefinition[];
  formulaWorkbook?: FormulaGridWorkbookConfig | null;
  formulaDependencyHighlight?: FormulaDependencyHighlightConfig | boolean | null;
  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" (click)="updateCatalogPrice()">{{ !catalogLoaded ? 'Load Product List' : catalogHardwarePrice === 650 ? 'Raise catalog price' : 'Reset catalog price' }}</button>
        <example-help-tooltip description="Load the missing Product List dataset, then update it without mounting another grid."></example-help-tooltip>
        <button class="rv-btn" type="button" (click)="reloadServiceLookup()">Reload service lookup</button>
        <example-help-tooltip description="Invalidate the async VLOOKUP override so it requests fresh service data."></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>
      <section class="formula-workbook-map" aria-label="Cross-sheet lookup setup">
        <div class="formula-workbook-flow">
          <span class="formula-sheet-pill">
            <strong>Orders</strong>
            <small>mounted RevoGrid</small>
          </span>
          <span class="formula-workbook-arrow" aria-hidden="true">looks up →</span>
          <span class="formula-sheet-pill formula-sheet-pill-external">
            <strong>Product List</strong>
            <small>{{ catalogLoaded ? 'externalSheets · no grid mounted' : 'not loaded · no grid mounted' }}</small>
          </span>
        </div>
        <div class="formula-workbook-reference">
          <span>Formula stored in Orders!D1</span>
          <code data-testid="formula-cross-sheet-formula">{{ lookupFormula }}</code>
        </div>
        <div class="formula-external-sheet" data-testid="formula-external-sheet">
          <span class="formula-external-sheet-title">{{ catalogLoaded ? 'Product List registered after Orders mounted' : 'Product List is not loaded yet' }}</span>
          @if (catalogLoaded) {
            <span>A1&nbsp; Hardware</span>
            <strong data-testid="formula-external-hardware-price">B1&nbsp; {{ catalogHardwarePrice }}</strong>
            <span>A2&nbsp; Software</span>
            <strong>B2&nbsp; 450</strong>
          } @else {
            <span class="formula-external-sheet-pending" data-testid="formula-external-sheet-pending">Waiting for backend data…</span>
          }
        </div>
      </section>
      <revo-grid
        #gridRef
        [rowHeaders]="true"
        [columns]="columns"
        [source]="gridSource"
        [pinnedBottomSource]="pinnedBottomSource"
        [columnTypes]="columnTypes"
        [formulaNames]="formulaNames"
        [formulaWorkbook]="formulaWorkbook"
        [formulaDependencyHighlight]="formulaDependencyHighlight"
        [rangeSelectionLimit]="rangeSelectionLimit"
        stretch="all"
        [range]="true"
        [hideAttribution]="true"
        [theme]="theme"
        [plugins]="plugins"
        class="formula-grid"
      ></revo-grid>
      <div #managerPanel class="formula-manager" [hidden]="!showManager"></div>
    </div>`,
  styleUrls: ['./formula.scss'],
  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 ? 'darkCompact' : 'compact';
  targetPrice = 500;
  catalogHardwarePrice = 650;
  catalogLoaded = false;
  lookupFormula = "=INDEX('Product List'!$B$1:$B$2,MATCH(B1,'Product List'!$A$1:$A$2,0))";
  showManager = false;
  private nextRowId = 100;
  private managerMounted = false;
  private disposeServiceLookup?: () => void;
  private externalSheetLoadTimer?: number;
  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();
  productSheet = {
    id: 'Product List',
    rowIdProp: 'id',
    columns: [{ prop: 'category' }, { prop: 'price' }],
    rows: [
      { id: 'product-hardware', category: 'Hardware', price: 650 },
      { id: 'product-software', category: 'Software', price: 450 },
    ],
  };
  formulaWorkbook: FormulaGridWorkbookConfig = {
    sheetId: 'Orders',
    rowIdProp: 'id',
    externalSheets: [],
  };
  rangeSelectionLimit = { mode: 'column' as const };
  gridSource = this.buildGridSource();
  pinnedBottomSource = this.buildPinnedBottomSource();
  plugins: any[] = [];
  columnTypes = { categoryDropdown: ColumnDropdown };
  columns: any[] = [];
  formulaDependencyHighlight: FormulaDependencyHighlightConfig = {
    dependencyClass: 'formula-dependency-cell',
    formulaCellClass: 'formula-dependency-active-cell',
    dependencyColors: ['#2563eb', '#dc2626', '#16a34a', '#9333ea', '#ea580c', '#0891b2'],
    includeNamedRanges: true,
  };
  exportConfig: ExportExcelEvent = { sheetName: 'RevoGrid Formula', workbookName: 'formula-example.xlsx' };
  fileExcelSvg = fileExcelSvg;

  constructor(private readonly changeDetector: ChangeDetectorRef) {}

  ngOnInit() {
    this.applyFormulaConfig();
  }

  async ngAfterViewInit() {
    this.gridElement.nativeElement.addEventListener('formulanameschange', this.syncToolbarTarget);
    this.gridElement.nativeElement.formulaBar = {
      el: this.formulaBarElement.nativeElement,
      badgeEl: this.formulaBarBadgeElement.nativeElement,
      showCellBadge: true,
    };
    const gridPlugins = await this.gridElement.nativeElement.getPlugins();
    const formulaPlugin = gridPlugins.find(
      plugin => plugin instanceof FormulaPlugin,
    ) as FormulaPlugin | undefined;
    if (formulaPlugin) this.disposeServiceLookup = registerServiceLookup(formulaPlugin);
    this.externalSheetLoadTimer = window.setTimeout(() => {
      void this.loadProductList();
    }, EXTERNAL_SHEET_LOAD_DELAY_MS);
  }

  ngOnDestroy() {
    if (this.externalSheetLoadTimer !== undefined) window.clearTimeout(this.externalSheetLoadTimer);
    this.disposeServiceLookup?.();
    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;
      }
    });
  }

  async updateCatalogPrice() {
    const grid = this.gridElement?.nativeElement;
    if (!grid) {
      return;
    }
    const nextPrice = this.catalogHardwarePrice === 650 ? 725 : 650;
    const gridPlugins = await grid.getPlugins();
    const formulaPlugin = gridPlugins.find(
      plugin => plugin instanceof FormulaPlugin,
    ) as FormulaPlugin | undefined;
    if (!this.catalogLoaded) {
      await this.loadProductList();
      return;
    }
    formulaPlugin?.updateFormulaSheetCell('Product List', 'product-hardware', 'price', nextPrice);
    this.catalogHardwarePrice = nextPrice;
  }

  private async loadProductList() {
    if (this.catalogLoaded) return;
    const gridPlugins = await this.gridElement?.nativeElement?.getPlugins();
    const formulaPlugin = gridPlugins?.find(
      plugin => plugin instanceof FormulaPlugin,
    ) as FormulaPlugin | undefined;
    formulaPlugin?.upsertFormulaSheet(this.productSheet);
    this.catalogLoaded = Boolean(formulaPlugin);
    this.changeDetector.detectChanges();
  }

  async reloadServiceLookup() {
    const gridPlugins = await this.gridElement?.nativeElement?.getPlugins();
    const formulaPlugin = gridPlugins?.find(
      plugin => plugin instanceof FormulaPlugin,
    ) as FormulaPlugin | undefined;
    formulaPlugin?.invalidateAsyncFormulaFunction('VLOOKUP');
  }

  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();
    this.changeDetector.detectChanges();
  };

  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 {
      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.gridSource = this.buildGridSource();
    this.pinnedBottomSource = this.buildPinnedBottomSource();
    this.plugins = [
      AutoFillPlugin,
      AutoFillPreviewPlugin,
      RangeSelectionLimitPlugin,
      FormulaBarPlugin,
      FormulaDependencyHighlightPlugin,
      NamedRangesPlugin,
      FormulaPlugin,
      RowOddPlugin,
      ColumnStretchPlugin,
      ExportExcelPlugin,
    ];
    this.columns = this.buildColumns();
  }

  private buildGridSource() {
    return this.source.map((row, index) => ({
      ...row,
      forecast: `=A${index + 1}+${this.targetFormulaRef()}`,
      catalogPrice: `=INDEX('Product List'!$B$1:$B$2,MATCH(B${index + 1},'Product List'!$A$1:$A$2,0))`,
      servicePrice: index === 0 ? SERVICE_LOOKUP_FORMULA : '',
    }));
  }

  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}`,
      catalogPrice: `=SUM(D1:D${this.source.length})`,
      servicePrice: '',
    }];
  }

  private buildColumns() {
    const columns: any[] = [
      {
        name: 'Price',
        prop: 'price',
        cellTemplate: (_: any, { value }: { value: any }) => parseFloat(value).toFixed(2),
      },
      {
        name: 'Category',
        prop: 'category',
        columnType: 'categoryDropdown',
        readonly: ({ type }: { type: string }) => type === 'rowPinEnd',
        cellProperties: ({ type }: { type: string }) => type === 'rowPinEnd' ? { class: 'formula-category-pinned' } : null,
      },
      {
        name: 'Formula',
        prop: 'forecast',
        cellProperties: () => ({ class: 'formula-cell' }),
      },
      {
        name: 'Catalog (INDEX/MATCH)',
        prop: 'catalogPrice',
        cellProperties: () => ({ class: 'formula-cell' }),
      },
      {
        name: 'Service (async VLOOKUP)',
        prop: 'servicePrice',
        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 extraOptions = this.categoryOptions.filter(
      value => !categoryDropdown.source.some(option => option.value === value),
    );
    columns[1].dropdown = {
      ...categoryDropdown,
      syncCellTemplate: true,
      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);
  }
}

Formula adds spreadsheet-style calculations to RevoGrid. A source value beginning with = is stored as an expression, while the mounted grid renders its derived result. For example, source data can retain =A1*B1 while the cell displays 30.

The contract is deliberately narrower than Excel:

A1 coordinates follow the authored row and column order, not the current sorted, filtered, pinned, or hidden presentation.
Formula recalculates mounted cells when configured source or workbook data changes. It does not replace the raw formula in grid.source with the result.
Invalid syntax, unknown functions, and circular references resolve to #ERROR. Autofill that moves a relative reference before A1 produces #REF!.
Functions come from FormulaJS, but RevoGrid owns the expression parser. Use the documented FormulaJS-backed subset, not assumed full Excel compatibility.

Try the demo

The demo is an Orders grid connected to a Product List dataset. In the workbook legend, Orders is the mounted RevoGrid. Product List is external sheet data available to the formula engine; it is not a second mounted grid. On narrow screens the legend stacks these parts without changing that relationship.

Control	What to observe
Select a formula cell	The Formula Bar shows the raw `=...` expression while the grid keeps showing the calculated value.
Names on/off	Formulas switch between readable names and direct A1 references without changing their results.
Insert row	References below the inserted row update to keep pointing at the same logical data.
Name manager	View and edit range, constant, and formula names used by the workbook.
Raise catalog price	Product List changes through the mounted Formula API and dependent Orders cells recalculate.
Reload service lookup	The async formula is invalidated, briefly shows its loading state, then renders the refreshed service value.

Choose your path

Goal	Start here	Helpful first
Add your first calculated column	Quick Start	RevoGrid columns and source data
Copy formulas or understand A1 identity	A1 References and Autofill	Quick Start
Replace coordinates with readable names	Named Ranges	A1 references
Add a Formula Bar, highlights, lists, or styling	Formula UI and Helpers	Quick Start
Look up values in another dataset	Cross-sheet Formulas	A1 references
Call an application service from a formula	Async Formula Functions	Quick Start
Recalculate and persist without a grid	Backend Calculation and Persistence	Cross-sheet formulas
Check syntax and available functions	Supported Functions	Quick Start

Next steps

Next: Quick Start · Deeper reference: Formula API