runloopable / web /src /sheet /ribbonFormulas.tsx
fsanyoto's picture
Deploy AIOS web (React glide grid + FastAPI slice)
c1fc133 verified
Raw History Blame Contribute Delete
26.1 kB
// 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<LoadedCatalogue> | null = null;
function IconFunction() {
return (
<svg viewBox="0 0 16 16" aria-hidden="true" className="sheet-ribbon-icon">
<path d="M3 4.5h4M5 2.5v9a2 2 0 0 1-2 2" />
<path d="M9 6l4 5M13 6l-4 5" />
</svg>
);
}
function IconMoreFunctions() {
return (
<svg viewBox="0 0 16 16" aria-hidden="true" className="sheet-ribbon-icon">
<path d="M2.5 4.5h11M2.5 8h7M2.5 11.5h9" />
</svg>
);
}
function IconCalculate() {
return (
<svg viewBox="0 0 16 16" aria-hidden="true" className="sheet-ribbon-icon">
<path d="M12.8 7.8a4.8 4.8 0 1 1-1.5-3.5" />
<path d="M12.9 2.6v3.2H9.7" />
</svg>
);
}
/**
* 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<string, unknown>;
const rows = Array.isArray(body.functions) ? body.functions : [];
const contextsByName = new Map<string, string[]>();
const adapted: Record<string, unknown>[] = [];
for (const row of rows) {
if (!row || typeof row !== "object" || Array.isArray(row)) continue;
const item = row as Record<string, unknown>;
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<LoadedCatalogue> {
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<HTMLButtonElement>,
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<HTMLElement>(`[role="${containerRole}"]`);
const items = container
? Array.from(container.querySelectorAll<HTMLButtonElement>(`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<LoadedCatalogue | null>(null);
const [view, setView] = useState<PickerView>(initialView);
const [category, setCategory] = useState<string | null>(initialCategory ?? null);
const [query, setQuery] = useState("");
const [chosen, setChosen] = useState<CatalogueFunction | null>(null);
const [values, setValues] = useState<string[]>([]);
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<string, number>();
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<HTMLFormElement>) => {
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 (
<AnchoredOverlay
anchor={anchor}
className="sheet-ribbon-pop sheet-ribbon-pop-portal sheet-formula-picker"
onDismiss={onDismiss}
role="dialog"
ariaLabel={title}
dataKind="sheet-formula-picker"
>
<div className="sheet-formula-picker-head">
{view === "arguments" && (
<button
type="button"
className="sheet-formula-back"
onClick={() => setView("functions")}
>
Back
</button>
)}
{view === "functions" && initialView === "categories" && (
<button
type="button"
className="sheet-formula-back"
onClick={() => { setCategory(null); setQuery(""); setView("categories"); }}
>
Categories
</button>
)}
<h3 className="sheet-formula-title">{title}</h3>
</div>
{!catalogue && (
<p className="sheet-formula-status" role="status">Loading functions</p>
)}
{catalogue?.fallback && (
<p className="sheet-formula-fallback" role="status">
Showing the built in list. The server catalogue could not be reached.
</p>
)}
{catalogue && view === "categories" && (
<div className="sheet-formula-categories" role="menu" aria-label="Function categories">
{categories.map((entry, index) => (
<button
key={entry.name}
type="button"
role="menuitem"
className="sheet-formula-category"
autoFocus={index === 0}
onKeyDown={(event) => focusWithin(event, "menu", "menuitem")}
onClick={() => { setCategory(entry.name); setView("functions"); }}
>
<span>{entry.name}</span>
<span className="sheet-formula-count">{entry.count}</span>
</button>
))}
</div>
)}
{catalogue && view === "functions" && (
<>
<input
className="sheet-formula-search"
type="search"
value={query}
placeholder="Search functions"
aria-label="Search functions"
aria-controls={listId}
autoFocus
onChange={(event) => setQuery(event.target.value)}
onKeyDown={(event) => {
if (event.key !== "ArrowDown") return;
const first = document.getElementById(listId)
?.querySelector<HTMLButtonElement>('button[role="option"]');
if (first) {
event.preventDefault();
first.focus();
}
}}
/>
<div
id={listId}
className="sheet-formula-list"
role="listbox"
aria-label={category ? `${category} functions` : "Functions"}
>
{visibleFunctions.map((fn) => (
<button
key={fn.name}
type="button"
role="option"
aria-selected={chosen?.name === fn.name}
className="sheet-formula-option"
onKeyDown={(event) => focusWithin(event, "listbox", "option")}
onClick={() => chooseFunction(fn)}
>
<span className="sheet-formula-option-line">
<strong>{fn.name}</strong>
<span>{fn.signature}</span>
</span>
<span className="sheet-formula-description">{fn.description}</span>
</button>
))}
{visibleFunctions.length === 0 && (
<p className="sheet-formula-empty">No functions match this search.</p>
)}
</div>
</>
)}
{catalogue && view === "arguments" && chosen && (
<form className="sheet-formula-arguments" onSubmit={insertFormula}>
<p className="sheet-formula-signature">{chosen.signature}</p>
{fields.map((field, index) => (
<label key={`${field.name}-${index}`} htmlFor={`${argId}-${index}`}>
<span className="sheet-formula-argument-label">
<span>{field.name}</span>
<span>{field.optional ? "Optional" : "Required"}</span>
</span>
<input
id={`${argId}-${index}`}
className="sheet-formula-argument"
value={values[index] ?? ""}
required={!field.optional}
autoFocus={index === 0}
onChange={(event) => setValues((current) => current.map((value, at) => (
at === index ? event.target.value : value
)))}
/>
</label>
))}
<button
type="submit"
className="sheet-formula-submit"
disabled={!canInsert}
autoFocus={fields.length === 0}
>
Insert
</button>
</form>
)}
</AnchoredOverlay>
);
}
// R19: distinct category glyphs keep the small buttons readable with names in tooltips.
function IconLogical() {
return <svg viewBox="0 0 16 16" aria-hidden="true" className="sheet-ribbon-icon"><path d="m8 1 4 4-4 4-4-4Zm0 8v3M3 12h10M3 12v2m10-2v2" /></svg>;
}
function IconTextFunction() {
return <svg viewBox="0 0 16 16" aria-hidden="true" className="sheet-ribbon-icon"><path d="M3 3h10v2M3 5V3m5 0v10m-3 0h6" /></svg>;
}
function IconDateFunction() {
return <svg viewBox="0 0 16 16" aria-hidden="true" className="sheet-ribbon-icon"><rect x="2" y="3" width="12" height="11" rx="1" /><path d="M5 1v4m6-4v4M2 7h12m-9 3h2m2 0h2" /></svg>;
}
function IconLookupFunction() {
return <svg viewBox="0 0 16 16" aria-hidden="true" className="sheet-ribbon-icon"><path d="M3 3h10M3 6h5M3 9h3" /><circle cx="10" cy="10" r="3" /><path d="m12 12 2 2" /></svg>;
}
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(<>
<span className="sheet-ribbon-slot">
<fieldset className="sheet-formula-trigger-fence" disabled={!canInsert}>
{control(
"formula-insert", "Insert Function", <><IconFunction />{word("Insert Function")}</>,
openPop("formula-insert"), false, open === "formula-insert", "FI", "gallery",
)}
</fieldset>
{open === "formula-insert" && insert?.selection && (
<FormulaPicker
anchor={anchorEl}
door={insert}
initialView="functions"
onDismiss={closePop}
/>
)}
</span>
</>)}
{large(<>
{control(
"formula-autosum", "AutoSum", <><IconAutoSum />{word("AutoSum")}</>,
() => act("autosum"), false, undefined, "U",
)}
</>)}
{stack(
<>
<span className="sheet-ribbon-slot">
<fieldset className="sheet-formula-trigger-fence" disabled={!canInsert}>
{control(
"formula-logical", "Logical", <IconLogical />, openPop("formula-logical"),
false, open === "formula-logical", "L", "gallery",
)}
</fieldset>
{open === "formula-logical" && insert?.selection && (
<FormulaPicker
anchor={anchorEl}
door={insert}
initialView="functions"
initialCategory="Logical"
onDismiss={closePop}
/>
)}
</span>
</>,
<>
<span className="sheet-ribbon-slot">
<fieldset className="sheet-formula-trigger-fence" disabled={!canInsert}>
{control(
"formula-text", "Text", <IconTextFunction />, openPop("formula-text"),
false, open === "formula-text", "T", "gallery",
)}
</fieldset>
{open === "formula-text" && insert?.selection && (
<FormulaPicker
anchor={anchorEl}
door={insert}
initialView="functions"
initialCategory="Text"
onDismiss={closePop}
/>
)}
</span>
</>,
<>
<span className="sheet-ribbon-slot">
<fieldset className="sheet-formula-trigger-fence" disabled={!canInsert}>
{control(
"formula-date", "Date", <IconDateFunction />, openPop("formula-date"),
false, open === "formula-date", "D", "gallery",
)}
</fieldset>
{open === "formula-date" && insert?.selection && (
<FormulaPicker
anchor={anchorEl}
door={insert}
initialView="functions"
initialCategory="Date"
onDismiss={closePop}
/>
)}
</span>
</>
)}
{stack(
<>
<span className="sheet-ribbon-slot">
<fieldset className="sheet-formula-trigger-fence" disabled={!canInsert}>
{control(
"formula-lookup", "Lookup", <IconLookupFunction />, openPop("formula-lookup"),
false, open === "formula-lookup", "K", "gallery",
)}
</fieldset>
{open === "formula-lookup" && insert?.selection && (
<FormulaPicker
anchor={anchorEl}
door={insert}
initialView="functions"
initialCategory="Lookup"
onDismiss={closePop}
/>
)}
</span>
</>,
<>
<span className="sheet-ribbon-slot">
<fieldset className="sheet-formula-trigger-fence" disabled={!canInsert}>
{control(
"formula-math", "Math", <IconAutoSum />, openPop("formula-math"),
false, open === "formula-math", "MH", "gallery",
)}
</fieldset>
{open === "formula-math" && insert?.selection && (
<FormulaPicker
anchor={anchorEl}
door={insert}
initialView="functions"
initialCategory="Math"
onDismiss={closePop}
/>
)}
</span>
</>,
<>
<span className="sheet-ribbon-slot">
<fieldset className="sheet-formula-trigger-fence" disabled={!canInsert}>
{control(
"formula-more", "More Functions", <IconMoreFunctions />,
openPop("formula-more"), false, open === "formula-more", "FM", "gallery",
)}
</fieldset>
{open === "formula-more" && insert?.selection && (
<FormulaPicker
anchor={anchorEl}
door={insert}
initialView="categories"
onDismiss={closePop}
/>
)}
</span>
</>
)}
</>)}
{group("Calculation", large(control(
"formula-refresh", "Calculate Now", <><IconCalculate />{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;