// Sheet Data ribbon, behaviorally adapted from the Wave 55 public Excel audit. The commands below // stay within Sheet's existing executable actions; unavailable audit commands remain visible and // honest rather than becoming no-op affordances. import { useState } from "react"; import { AnchoredOverlay } from "../customer-grid/OverlaySurface"; import type { RibbonInsertDoor, RibbonTabContext } from "./Ribbon"; import { usedRange } from "./sheetExport"; import { addressOf, setLiteral } from "./sheetState"; import { asNumber, MAX_SHEET_COLS } from "./types"; import type { Cell, CellBinding } from "./types"; import { listText, parseList, validationOf } from "./validation"; const DATA = ["data-refresh", "data-queries-connections", "data-sort-asc", "data-sort-desc", "data-filter", "data-validation", "data-text-to-columns", "data-flash-fill", "data-remove-duplicates"] as const; const OUTLINE = ["data-hide-rows", "data-hide-columns", "data-unhide"] as const; export const DATA_KEYS = [...DATA, ...OUTLINE] as const; /** Canvas view state carried by the existing insert door, without a document action. */ export interface SheetFilterDoor { filterActive: boolean; toggleFilter: () => void; } type DataRows = (Cell | null)[][]; type DataKey = number | string | null; const hasDataRange = (selection: RibbonInsertDoor["selection"] | undefined): boolean => !!selection && (selection.col0 !== selection.col1 || selection.row0 !== selection.row1); /** * Snapshot before writing, so moving a row cannot overwrite a later row's source. The door is * already in sheet coordinates, including hidden rows and columns; no view-index conversion here. * Excel would offer to widen the selection. Sort exactly the selected rectangle: silently widening * it is the dangerous half of that dialog, because it moves cells the person did not select. */ function transformSelectionRows( door: RibbonInsertDoor | undefined, transform: (rows: DataRows) => DataRows, ): { before: number; after: number } | null { const selection = door?.selection; if (!door || !selection || !hasDataRange(selection)) return null; const col0 = Math.min(selection.col0, selection.col1); const row0 = Math.min(selection.row0, selection.row1); const width = Math.abs(selection.col1 - selection.col0) + 1; const height = Math.abs(selection.row1 - selection.row0) + 1; const rows: DataRows = Array.from({ length: height }, (_, row) => Array.from({ length: width }, (_, col) => door.cellAt(col0 + col, row0 + row))); const next = transform(rows); const edits: Record = {}; for (let row = 0; row < height; row += 1) { for (let col = 0; col < width; col += 1) { // Include every address, especially the shortened tail: omitted edits leave stale cells. edits[addressOf(col0 + col, row0 + row)] = next[row]?.[col] ?? null; } } // The same batch door as paste makes the whole action one history step and one save. door.onCells(edits); return { before: height, after: next.length }; } /** Sort and dedupe agree on values, never formula source, formatting, or binding definitions. */ function dataKey(cell: Cell | null): DataKey { if (!cell || cell.kind === "empty") return null; // Bound cells use their last computed result, even when stale, then their displayed text. // A missing value is not zero; an empty computed string also wins over old source text. const value = cell.value ?? (cell.kind === "bound" ? cell.display ?? cell.text : cell.text ?? cell.display) ?? ""; if (value === "") return null; // Typed numbers are stored as strings in Sheet, so use the same numeric reader as its cells. const number = asNumber(value); return number === null ? String(value).toLowerCase() : number; } /** dataKey normalizes comparisons; text operations need the same value precedence verbatim. */ function dataText(cell: Cell | null): string { if (!cell || cell.kind === "empty") return ""; const value = cell.value ?? (cell.kind === "bound" ? cell.display ?? cell.text : cell.text ?? cell.display) ?? ""; return String(value); } function compareDataKeys(left: DataKey, right: DataKey, direction: 1 | -1): number { // Blanks stay last in BOTH directions; only the nonblank ordering may be reversed. if (left === null) return right === null ? 0 : 1; if (right === null) return -1; const order = typeof left === "number" ? typeof right === "number" ? left - right : -1 : typeof right === "number" ? 1 : left.localeCompare(right); return direction * order; } function sortRows(rows: DataRows, direction: 1 | -1): DataRows { return rows.map((cells, index) => ({ cells, index, key: dataKey(cells[0] ?? null) })) // Original indices break ties outside the direction multiplier, preserving equal-key order. .sort((left, right) => compareDataKeys(left.key, right.key, direction) || left.index - right.index) .map(({ cells }) => cells); } function uniqueRows(rows: DataRows): DataRows { const seen = new Set(); return rows.filter((cells) => { // JSON escapes every embedded NUL, so this separator cannot occur in an encoded cell key. // It also keeps numeric and text keys distinct without mistaking column boundaries for data. const key = cells.map((cell) => JSON.stringify(dataKey(cell))).join("\u0000"); if (seen.has(key)) return false; seen.add(key); // Filtering in source order retains the first entire cell row, including its styles/bindings. return true; }); } function splitTextToColumns(door: RibbonInsertDoor | undefined, delimiter: string): string { const selection = door?.selection; if (!door || !selection || !hasDataRange(selection)) return "Nothing was split; select a range of cells."; const col0 = Math.min(selection.col0, selection.col1); const row0 = Math.min(selection.row0, selection.row1); const row1 = Math.max(selection.row0, selection.row1); const splitRows = Array.from({ length: row1 - row0 + 1 }, (_, row) => { const cell = door.cellAt(col0, row0 + row); if (!cell || dataKey(cell) === null) return null; const pieces = dataText(cell).split(delimiter); return pieces.length > 1 ? pieces : null; }); const count = splitRows.filter((pieces) => pieces !== null).length; if (count === 0) return "0 rows were split because no values contained the chosen delimiter."; const width = splitRows.reduce((widest, pieces) => Math.max(widest, pieces?.length ?? 1), 1); if (col0 + width > MAX_SHEET_COLS) return "Nothing was split because the result would exceed the sheet's column limit."; // Check the entire output rectangle before any write, including cells outside the selection. for (let row = row0; row <= row1; row += 1) { for (let col = col0 + 1; col < col0 + width; col += 1) { const cell = door.cellAt(col, row); // A blank formula or binding still owns content, even when its current result is empty. if (cell && (dataKey(cell) !== null || cell.kind === "formula" || cell.kind === "bound" || cell.sparkline)) { return `Nothing was split because ${addressOf(col, row)} already contains data.`; } } } // Reuse the existing snapshot and batch writer with the checked output range in sheet coordinates. transformSelectionRows({ ...door, selection: { col0, row0, col1: col0 + width - 1, row1 } }, (rows) => rows.map((cells, row) => { const pieces = splitRows[row]; if (!pieces) return cells; return cells.map((cell, col) => { const piece = pieces[col]; return piece === undefined ? cell : setLiteral(cell, piece); }); })); return `${count} ${count === 1 ? "row was" : "rows were"} split into columns.`; } type FlashFillExample = { sources: readonly string[]; target: string }; type FlashFillTransform = (sources: readonly string[]) => string | null; /** Include overlapping occurrences and empty text, but stop at the end even for an empty match. */ function* textPositions(text: string, value: string, from = 0): Generator { let position = text.indexOf(value, from); while (position !== -1 && position <= text.length) { yield position; if (position === text.length) return; position = text.indexOf(value, position + 1); } } function fitFlashFillAffixes( examples: readonly FlashFillExample[], read: FlashFillTransform, ): FlashFillTransform | null { const first = examples[0]; if (!first) return null; const value = read(first.sources); if (value === null) return null; // A repeated source can occur in an affix too; try every placement before refusing this rule. for (const position of textPositions(first.target, value)) { const prefix = first.target.slice(0, position); const suffix = first.target.slice(position + value.length); const transform: FlashFillTransform = (sources) => { const part = read(sources); return part === null ? null : prefix + part + suffix; }; if (examples.every((example) => transform(example.sources) === example.target)) return transform; } return null; } function inferFlashFill(examples: readonly FlashFillExample[]): FlashFillTransform | null { const first = examples[0]; if (!first) return null; const width = first.sources.length; // Family order is intentional: take the first rule that explains EVERY example exactly. for (let col = 0; col < width; col += 1) { const transform = fitFlashFillAffixes(examples, (sources) => sources[col] ?? null); if (transform) return transform; } const delimiters = [" ", ",", ";", "\t", "-", "@", "/", "."] as const; for (let col = 0; col < width; col += 1) { for (const delimiter of delimiters) { const pieces = first.sources[col]?.split(delimiter) ?? []; for (let piece = 0; piece < pieces.length; piece += 1) { const transform = fitFlashFillAffixes(examples, (sources) => sources[col]?.split(delimiter)[piece] ?? null); if (transform) return transform; } } } // Ordered pairs also permit a person to type the right source before the left source. for (let left = 0; left < width; left += 1) { const leftValue = first.sources[left]; if (leftValue === undefined) continue; for (let right = 0; right < width; right += 1) { const rightValue = first.sources[right]; if (left === right || rightValue === undefined) continue; for (const start of textPositions(first.target, leftValue)) { const end = start + leftValue.length; for (const next of textPositions(first.target, rightValue, end)) { const prefix = first.target.slice(0, start); const separator = first.target.slice(end, next); const suffix = first.target.slice(next + rightValue.length); const transform: FlashFillTransform = (sources) => { const leftPart = sources[left]; const rightPart = sources[right]; return leftPart === undefined || rightPart === undefined ? null : prefix + leftPart + separator + rightPart + suffix; }; if (examples.every((example) => transform(example.sources) === example.target)) return transform; } } } } return null; } function flashFillSelection(door: RibbonInsertDoor | undefined): string { const selection = door?.selection; if (!door || !selection || !hasDataRange(selection)) return "Nothing was filled; select a range of cells."; const col0 = Math.min(selection.col0, selection.col1); const row0 = Math.min(selection.row0, selection.row1); const row1 = Math.max(selection.row0, selection.row1); if (col0 === 0) return "Nothing was filled because there is no source column to the left; select a target column to the right of your source data."; // Source columns can be outside the selection; snapshot them before inferring or writing. const rows = Array.from({ length: row1 - row0 + 1 }, (_, index) => { const cell = door.cellAt(col0, row0 + index); return { cell, target: dataText(cell), sources: Array.from({ length: col0 }, (_, col) => dataText(door.cellAt(col, row0 + index))), }; }); const examples = rows.filter((row) => row.target !== ""); if (examples.length === 0) return "Nothing was filled because there is no example; type an example in the target column within your selection."; const pending = rows.filter((row) => row.target === ""); if (pending.length === 0) return "Nothing was filled because there are no empty target cells left; include empty rows in your selection."; // As with Text to Columns, a blank computed result does not make a formula or binding disposable. if (pending.some(({ cell }) => cell && (cell.kind === "formula" || cell.kind === "bound" || cell.sparkline))) { return "Nothing was filled because an empty-looking target contains a formula, binding or sparkline; select empty target cells or clear their contents first."; } const transform = inferFlashFill(examples); if (!transform) return "The examples do not agree on one rule, so nothing was changed; correct the examples or select a smaller range with a consistent pattern."; // Precompute ALL outputs: a missing piece in even one fill row must never cause a partial write. const fills = new Map(); for (const [index, row] of rows.entries()) { if (row.target !== "") continue; const value = transform(row.sources); if (value === null || value === "") { return "Nothing was filled because some rows lack the source text needed by the examples; complete those source cells or select a smaller range."; } fills.set(index, value); } // Only the target column is written; the shared batch helper preserves one history/save step. transformSelectionRows({ ...door, selection: { col0, row0, col1: col0, row1 } }, (cells) => cells.map((row, index) => { const value = fills.get(index); return value === undefined ? row : [setLiteral(row[0], value)]; })); return `${fills.size} ${fills.size === 1 ? "row was" : "rows were"} filled.`; } function IconRefresh() { return ( ); } function IconBand({ column = false }: { column?: boolean }) { return ( ); } function IconSort({ descending = false }: { descending?: boolean }) { return ( ); } function IconRemoveDuplicates() { return ( ); } function IconTextToColumns() { return ( ); } function IconFlashFill() { return ( ); } function IconDataValidation() { return ( ); } function IconQueriesConnections() { return ( ); } function DataValidationControl({ ctx }: { ctx: RibbonTabContext }) { const { insert, control, word, open, anchorEl, openPop, closePop } = ctx; const [text, setText] = useState(""); const [result, setResult] = useState(null); const submit = () => { const selection = insert?.selection; if (!insert || !selection) { setResult("Select a cell to change its validation list."); return; } const parsed = parseList(text); if (parsed.refusal) { setResult(parsed.refusal); return; } const col0 = Math.min(selection.col0, selection.col1); const col1 = Math.max(selection.col0, selection.col1); const row0 = Math.min(selection.row0, selection.row1); const row1 = Math.max(selection.row0, selection.row1); const edits: Record = {}; let boundSkipped = 0; for (let row = row0; row <= row1; row += 1) { for (let col = col0; col <= col1; col += 1) { const cell = insert.cellAt(col, row); const addr = addressOf(col, row); // Mirror validation.ts::validationEdits, including its bound-cell skip and empty-cell // rules; change this with it. The ribbon door has no SheetTab to pass to that helper. if (cell?.kind === "bound") { boundSkipped += 1; continue; } if (!cell) { if (parsed.list.length > 0) { edits[addr] = { kind: "empty", state: "fresh", validation: { list: [...parsed.list] } }; } continue; } if (parsed.list.length === 0) { if (cell.kind === "empty" && !cell.style) { edits[addr] = null; continue; } const cleared: Cell = { ...cell }; delete cleared.validation; edits[addr] = cleared; continue; } edits[addr] = { ...cell, validation: { list: [...parsed.list] } }; } } const count = Object.keys(edits).length; if (count > 0) insert.onCells(edits); const skipped = boundSkipped > 0 ? `; ${boundSkipped} bound ${boundSkipped === 1 ? "cell was" : "cells were"} skipped because their values are computed from a database` : ""; setResult(`${parsed.list.length > 0 ? "Validation list applied to" : "Validation cleared from"} ${count} ${count === 1 ? "cell" : "cells"}${skipped}.`); }; return ( {control( "data-validation", "Data Validation", <>{word("Data Validation")}, (event) => { if (open === "data-validation") { closePop(); return; } const selection = insert?.selection; if (!insert || !selection) return; const anchor = insert.cellAt(Math.min(selection.col0, selection.col1), Math.min(selection.row0, selection.row1)); setText(listText(validationOf(anchor)?.list)); setResult(null); openPop("data-validation")(event); }, false, open === "data-validation", "V", "gallery", !insert?.selection, )} {open === "data-validation" && (
{ event.preventDefault(); submit(); }}>