// Sheet Formulas ribbon, adapted from the behavior and grouping recorded in // `AutoWiki/waves/wave55/planning/excel-online-audit.md`. This is an original Loopable // expression: it reuses the ribbon primitives and closed action vocabulary, never external // source, assets, or private class names. // // ⭐ W56-T13: Insert Function and More Functions now read the server-owned catalogue lazily and // write through the Sheet's existing cell door; AutoSum and Calculate Now keep their established // actions. Name manager is absent because neither evaluator supports named ranges. Show formulas // is absent because the canvas renderer, not ribbon-local state or CSS, owns painted cell text. // Calculation options is absent because a truthful Manual mode must change SheetGrid's automatic // recalculation door. A command that cannot work is removed instead of rendered as a no-op. // // W56-T18 adds literal top-level shortcuts for the five Excel categories our evaluator serves. // Statistical and Information remain in More Functions, and Financial remains absent because the // evaluator has no financial functions. Literal control labels also keep the parity source scrape // auditable without teaching that asset a TypeScript evaluator. import { useEffect, useId, useMemo, useState } from "react"; import type { FormEvent, KeyboardEvent as ReactKeyboardEvent } from "react"; import { CREDENTIALS } from "../apiContract"; import type { CatalogueFunction } from "../customer-grid/FormulaEditor"; import { AnchoredOverlay } from "../customer-grid/OverlaySurface"; import type { RibbonInsertDoor, RibbonTabContext } from "./Ribbon"; import { colToIndex, parseRef } from "./a1"; import { FUNCTIONS } from "./functionCatalog"; import { IconAutoSum } from "./ribbonIcons"; import { addressOf, cellToValue, commitTyped } from "./sheetState"; import "../styles/wave56/ribbon-formulas.css"; export const FUNCTION_LIBRARY = [ "formula-insert", "formula-autosum", "formula-logical", "formula-text", "formula-date", "formula-lookup", "formula-math", "formula-more", ] as const; const CALCULATION = ["formula-refresh"] as const; export const FORMULAS_KEYS = [...FUNCTION_LIBRARY, ...CALCULATION] as const; const SHEET_CONTEXT = "sheet"; const GRID_CONTEXT = "grid"; const CATEGORY_ORDER = [ "Math", "Statistical", "Logical", "Text", "Date", "Lookup", "Information", ] as const; type PickerView = "categories" | "functions" | "arguments"; interface ParsedArgument { name: string; optional: boolean; } interface ParsedSignature { args: ParsedArgument[]; repeats: boolean; } interface LoadedCatalogue { functions: CatalogueFunction[]; fallback: boolean; } let cachedSheetCatalogue: LoadedCatalogue | null = null; let sheetCatalogueInflight: Promise | null = null; function IconFunction() { return ( ); } function IconMoreFunctions() { return ( ); } function IconCalculate() { return ( ); } /** * Parse the server's display signature into the argument fields the second picker step needs. * Brackets may wrap more than one comma-separated argument, so this walks the text instead of * splitting first and guessing optionality afterwards. */ function parseSignature(signature: string): ParsedSignature { const start = signature.indexOf("("); const end = signature.lastIndexOf(")"); if (start < 0 || end < start) return { args: [], repeats: false }; const body = signature.slice(start + 1, end); const args: ParsedArgument[] = []; let token = ""; let optionalDepth = 0; let tokenOptional = false; let repeats = false; const push = () => { const name = token.trim(); token = ""; if (!name) return; if (name === "...") { repeats = true; return; } args.push({ name, optional: tokenOptional }); }; for (const char of body) { if (char === "[") { optionalDepth += 1; tokenOptional = true; continue; } if (char === "]") { optionalDepth = Math.max(0, optionalDepth - 1); continue; } if (char === ",") { push(); tokenOptional = optionalDepth > 0; continue; } token += char; } push(); return { args, repeats }; } function repeatedName(name: string, offset: number): string { const numbered = /^(.*?)(\d+)$/.exec(name); if (numbered) return `${numbered[1]}${Number(numbered[2]) + offset}`; return `${name}_${offset + 1}`; } function argumentFields(fn: CatalogueFunction): ParsedArgument[] { const parsed = parseSignature(fn.signature); if (!parsed.repeats || parsed.args.length === 0) return parsed.args; const last = parsed.args[parsed.args.length - 1]; const room = fn.maxArgs === null ? 2 : Math.max(0, Math.min(2, fn.maxArgs - parsed.args.length)); return [ ...parsed.args, ...Array.from({ length: room }, (_, index) => ({ name: repeatedName(last.name, index + 1), optional: true, })), ]; } /** The local autocomplete list is the honest, category-compatible offline fallback. */ function fallbackCatalogue(): LoadedCatalogue { return { fallback: true, functions: FUNCTIONS.map((fn) => { const parsed = parseSignature(fn.signature); return { name: fn.name, category: fn.category, signature: fn.signature, description: fn.summary, minArgs: parsed.args.filter((arg) => !arg.optional).length, maxArgs: parsed.repeats ? null : parsed.args.length, contexts: [SHEET_CONTEXT], aliases: [], }; }), }; } function textList(value: unknown): string[] { return Array.isArray(value) ? value.filter((item): item is string => typeof item === "string") : []; } /** * FormulaEditor's normaliser deliberately filters for its grid context. The Sheet still routes * every raw server row through that tested normaliser, temporarily adding the grid context only * so Sheet-only rows survive its filter, then restores the server's original contexts before the * Sheet filter runs. The parser's catalogue remains the authority for names, arity and copy. */ function sheetFunctionsFromResponse( raw: unknown, normalise: (value: unknown) => { functions: CatalogueFunction[] }, ): CatalogueFunction[] { if (!raw || typeof raw !== "object" || Array.isArray(raw)) return []; const body = raw as Record; const rows = Array.isArray(body.functions) ? body.functions : []; const contextsByName = new Map(); const adapted: Record[] = []; for (const row of rows) { if (!row || typeof row !== "object" || Array.isArray(row)) continue; const item = row as Record; const name = typeof item.name === "string" ? item.name.trim() : ""; const contexts = textList(item.contexts); if (!name || !contexts.includes(SHEET_CONTEXT)) continue; contextsByName.set(name, contexts); adapted.push({ ...item, contexts: contexts.includes(GRID_CONTEXT) ? contexts : [...contexts, GRID_CONTEXT], }); } return normalise({ ...body, functions: adapted }).functions .map((fn) => ({ ...fn, contexts: contextsByName.get(fn.name) ?? fn.contexts })) .filter((fn) => fn.contexts.includes(SHEET_CONTEXT)); } /** * Lazy by construction: the grid editor and its React dependencies stay out of the ribbon chunk * until a person opens either catalogue control. The shared loader owns reachability, caching and * failure semantics. A second explicit GET supplies the Sheet-only rows its grid-scoped return * value correctly omits, and this module caches the complete successful result for later opens. */ function loadSheetCatalogue(): Promise { if (cachedSheetCatalogue) return Promise.resolve(cachedSheetCatalogue); if (!sheetCatalogueInflight) { sheetCatalogueInflight = (async () => { try { const mod = await import("../customer-grid/FormulaEditor"); const reachable = await mod.loadFormulaCatalogue(); if (reachable.functions.length === 0) return fallbackCatalogue(); const response = await fetch(mod.FORMULA_CATALOGUE_ROUTE, { credentials: CREDENTIALS }); if (!response.ok) return fallbackCatalogue(); const functions = sheetFunctionsFromResponse( await response.json(), mod.catalogueFromResponse, ); return functions.length ? { functions, fallback: false } : fallbackCatalogue(); } catch { return fallbackCatalogue(); } })().then((result) => { sheetCatalogueInflight = null; if (!result.fallback) cachedSheetCatalogue = result; return result; }); } return sheetCatalogueInflight; } function selectedRange(selection: RibbonInsertDoor["selection"]): string { if (!selection) return ""; const col0 = Math.min(selection.col0, selection.col1); const row0 = Math.min(selection.row0, selection.row1); const col1 = Math.max(selection.col0, selection.col1); const row1 = Math.max(selection.row0, selection.row1); if (col0 === col1 && row0 === row1) return ""; return `${addressOf(col0, row0)}:${addressOf(col1, row1)}`; } function focusWithin( event: ReactKeyboardEvent, containerRole: "listbox" | "menu", itemRole: "option" | "menuitem", ) { let edge: "first" | "last" | null = null; let offset = 0; if (event.key === "ArrowDown") offset = 1; else if (event.key === "ArrowUp") offset = -1; else if (event.key === "Home") edge = "first"; else if (event.key === "End") edge = "last"; else return; event.preventDefault(); const container = event.currentTarget.closest(`[role="${containerRole}"]`); const items = container ? Array.from(container.querySelectorAll(`button[role="${itemRole}"]`)) : []; if (!items.length) return; if (edge) { items[edge === "first" ? 0 : items.length - 1].focus(); return; } const current = items.indexOf(event.currentTarget); items[(current + offset + items.length) % items.length].focus(); } interface FormulaPickerProps { anchor: HTMLElement | null; door: RibbonInsertDoor; initialView: "categories" | "functions"; initialCategory?: (typeof CATEGORY_ORDER)[number]; onDismiss: () => void; } function FormulaPicker({ anchor, door, initialView, initialCategory, onDismiss, }: FormulaPickerProps) { const listId = useId(); const argId = useId(); const [catalogue, setCatalogue] = useState(null); const [view, setView] = useState(initialView); const [category, setCategory] = useState(initialCategory ?? null); const [query, setQuery] = useState(""); const [chosen, setChosen] = useState(null); const [values, setValues] = useState([]); useEffect(() => { let live = true; void loadSheetCatalogue().then((next) => { if (live) setCatalogue(next); }); return () => { live = false; }; }, []); const categories = useMemo(() => { if (!catalogue) return []; const counts = new Map(); for (const fn of catalogue.functions) counts.set(fn.category, (counts.get(fn.category) ?? 0) + 1); const known = CATEGORY_ORDER.filter((name) => counts.has(name)); const other = [...counts.keys()] .filter((name) => !CATEGORY_ORDER.includes(name as (typeof CATEGORY_ORDER)[number])) .sort((a, b) => a.localeCompare(b)); return [...known, ...other].map((name) => ({ name, count: counts.get(name) ?? 0 })); }, [catalogue]); const visibleFunctions = useMemo(() => { if (!catalogue) return []; const needle = query.trim().toLocaleLowerCase(); return catalogue.functions.filter((fn) => { if (category && fn.category !== category) return false; if (!needle) return true; return fn.name.toLocaleLowerCase().includes(needle) || fn.description.toLocaleLowerCase().includes(needle); }); }, [catalogue, category, query]); const fields = useMemo(() => chosen ? argumentFields(chosen) : [], [chosen]); const usedThrough = values.reduce((last, value, index) => value.trim() ? index : last, -1); const missingRequired = fields.some((field, index) => !field.optional && !values[index]?.trim()); const interiorGap = values.slice(0, usedThrough + 1).some((value) => !value.trim()); const canInsert = !!chosen && !missingRequired && !interiorGap; const chooseFunction = (fn: CatalogueFunction) => { const nextFields = argumentFields(fn); const nextValues = nextFields.map(() => ""); if (nextValues.length) nextValues[0] = selectedRange(door.selection); setChosen(fn); setValues(nextValues); setView("arguments"); }; const insertFormula = (event: FormEvent) => { event.preventDefault(); if (!chosen || !canInsert || !door.selection) return; const args = values.slice(0, usedThrough + 1).map((value) => value.trim()); const formula = `=${chosen.name}(${args.join(", ")})`; const col = Math.min(door.selection.col0, door.selection.col1); const row = Math.min(door.selection.row0, door.selection.row1); const address = addressOf(col, row); const get = (refText: string) => { const ref = parseRef(refText); if (!ref || ref.sheet) return cellToValue(null); return cellToValue(door.cellAt(colToIndex(ref.col) - 1, ref.row - 1)); }; door.onCells({ [address]: commitTyped(door.cellAt(col, row), formula, get) }); onDismiss(); }; const title = view === "categories" ? "More Functions" : view === "arguments" ? "Function Arguments" : category ?? "Insert Function"; return (
{view === "arguments" && ( )} {view === "functions" && initialView === "categories" && ( )}

{title}

{!catalogue && (

Loading functions

)} {catalogue?.fallback && (

Showing the built in list. The server catalogue could not be reached.

)} {catalogue && view === "categories" && (
{categories.map((entry, index) => ( ))}
)} {catalogue && view === "functions" && ( <> setQuery(event.target.value)} onKeyDown={(event) => { if (event.key !== "ArrowDown") return; const first = document.getElementById(listId) ?.querySelector('button[role="option"]'); if (first) { event.preventDefault(); first.focus(); } }} />
{visibleFunctions.map((fn) => ( ))} {visibleFunctions.length === 0 && (

No functions match this search.

)}
)} {catalogue && view === "arguments" && chosen && (

{chosen.signature}

{fields.map((field, index) => ( ))}
)}
); } // R19: distinct category glyphs keep the small buttons readable with names in tooltips. function IconLogical() { return ; } function IconTextFunction() { return ; } function IconDateFunction() { return ; } function IconLookupFunction() { return ; } export const formulasGroups = (ctx: RibbonTabContext) => { const { act, anchorEl, closePop, control, group, large, stack, insert, open, openPop, word } = ctx; const canInsert = !!insert?.selection; return ( <> {group("Function Library", <> {large(<>
{control( "formula-insert", "Insert Function", <>{word("Insert Function")}, openPop("formula-insert"), false, open === "formula-insert", "FI", "gallery", )}
{open === "formula-insert" && insert?.selection && ( )}
)} {large(<> {control( "formula-autosum", "AutoSum", <>{word("AutoSum")}, () => act("autosum"), false, undefined, "U", )} )} {stack( <>
{control( "formula-logical", "Logical", , openPop("formula-logical"), false, open === "formula-logical", "L", "gallery", )}
{open === "formula-logical" && insert?.selection && ( )}
, <>
{control( "formula-text", "Text", , openPop("formula-text"), false, open === "formula-text", "T", "gallery", )}
{open === "formula-text" && insert?.selection && ( )}
, <>
{control( "formula-date", "Date", , openPop("formula-date"), false, open === "formula-date", "D", "gallery", )}
{open === "formula-date" && insert?.selection && ( )}
)} {stack( <>
{control( "formula-lookup", "Lookup", , openPop("formula-lookup"), false, open === "formula-lookup", "K", "gallery", )}
{open === "formula-lookup" && insert?.selection && ( )}
, <>
{control( "formula-math", "Math", , openPop("formula-math"), false, open === "formula-math", "MH", "gallery", )}
{open === "formula-math" && insert?.selection && ( )}
, <>
{control( "formula-more", "More Functions", , openPop("formula-more"), false, open === "formula-more", "FM", "gallery", )}
{open === "formula-more" && insert?.selection && ( )}
)} )} {group("Calculation", large(control( "formula-refresh", "Calculate Now", <>{word("Calculate Now")}, () => act("refresh"), false, undefined, "C", )))} ); }; export const FORMULAS_TAB = { id: "formulas", key: "tab-formulas", kt: "M", label: "Formulas", bodyClass: "sheet-ribbon-formulas", toolbarLabel: "Formulas", keys: FORMULAS_KEYS, groups: formulasGroups, } as const;