Spaces:
Running on CPU Upgrade
Running on CPU Upgrade
Download web/src/sheet/ribbonData.tsx from fsanyoto/runloopable: direct link, hf CLI and curl.
- Browser
- Download file 31 kB
-
https://huggingface.co/spaces/fsanyoto/runloopable/resolve/main/web/src/sheet/ribbonData.tsx
- Command line
-
hf download hf://spaces/fsanyoto/runloopable/web/src/sheet/ribbonData.tsx
-
curl -L -o ribbonData.tsx https://huggingface.co/spaces/fsanyoto/runloopable/resolve/main/web/src/sheet/ribbonData.tsx
31 kB
| // 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<string, Cell | null> = {}; | |
| 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<string>(); | |
| 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<number> { | |
| 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<number, string>(); | |
| 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 ( | |
| <svg viewBox="0 0 16 16" aria-hidden="true" className="sheet-ribbon-icon"> | |
| <path d="M13 8a5 5 0 1 1-1.6-3.7" /> | |
| <path d="M13.2 2.6v3.2h-3.2" /> | |
| </svg> | |
| ); | |
| } | |
| function IconBand({ column = false }: { column?: boolean }) { | |
| return ( | |
| <svg viewBox="0 0 16 16" aria-hidden="true" className="sheet-ribbon-icon"> | |
| <rect x="2.5" y="2.5" width="11" height="11" rx="1" /> | |
| {column ? <path d="M6.2 2.5v11M9.8 2.5v11" /> : <path d="M2.5 6.2h11M2.5 9.8h11" />} | |
| </svg> | |
| ); | |
| } | |
| function IconSort({ descending = false }: { descending?: boolean }) { | |
| return ( | |
| <svg viewBox="0 0 16 16" stroke="currentColor" fill="none" aria-hidden="true" className="sheet-ribbon-icon"> | |
| <path d={descending ? "M3.5 2.5v11m-2-2 2 2 2-2" : "M3.5 13.5v-11m-2 2 2-2 2 2"} /> | |
| <path d="M8 3.5h2M8 8h4M8 12.5h6" /> | |
| </svg> | |
| ); | |
| } | |
| function IconRemoveDuplicates() { | |
| return ( | |
| <svg viewBox="0 0 16 16" stroke="currentColor" fill="none" aria-hidden="true" className="sheet-ribbon-icon"> | |
| <rect x="2" y="2" width="8" height="8" rx="1" /> | |
| <path d="M6 10v3a1 1 0 0 0 1 1h6a1 1 0 0 0 1-1V7a1 1 0 0 0-1-1h-3M8.5 10h3" /> | |
| </svg> | |
| ); | |
| } | |
| function IconTextToColumns() { | |
| return ( | |
| <svg viewBox="0 0 16 16" stroke="currentColor" fill="none" aria-hidden="true" className="sheet-ribbon-icon"> | |
| <path d="M2 3h12M2 6h4M2 9h4M8 5v9M10 6h4M10 9h4M2 12h4M10 12h4" /> | |
| </svg> | |
| ); | |
| } | |
| function IconFlashFill() { | |
| return ( | |
| <svg viewBox="0 0 16 16" stroke="currentColor" fill="none" aria-hidden="true" className="sheet-ribbon-icon"> | |
| <path d="M2 3h4M2 8h3M2 13h4M11 1 7 8h3l-1 7 5-9h-4l1-5Z" /> | |
| </svg> | |
| ); | |
| } | |
| function IconDataValidation() { | |
| return ( | |
| <svg viewBox="0 0 16 16" stroke="currentColor" fill="none" aria-hidden="true" className="sheet-ribbon-icon"> | |
| <rect x="1.5" y="2.5" width="13" height="11" rx="1" /> | |
| <path d="m3.5 8 2 2 3.5-4M11 6h1M11 9h1" /> | |
| </svg> | |
| ); | |
| } | |
| function IconQueriesConnections() { | |
| return ( | |
| <svg viewBox="0 0 16 16" stroke="currentColor" fill="none" aria-hidden="true" className="sheet-ribbon-icon"> | |
| <ellipse cx="6" cy="3.5" rx="4" ry="2" /> | |
| <path d="M2 3.5v7c0 1.1 1.8 2 4 2M10 3.5v3M2 7c0 1.1 1.8 2 4 2M8 11h6m-2-2 2 2-2 2" /> | |
| </svg> | |
| ); | |
| } | |
| function DataValidationControl({ ctx }: { ctx: RibbonTabContext }) { | |
| const { insert, control, word, open, anchorEl, openPop, closePop } = ctx; | |
| const [text, setText] = useState(""); | |
| const [result, setResult] = useState<string | null>(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<string, Cell | null> = {}; | |
| 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 ( | |
| <span className="sheet-ribbon-slot"> | |
| {control( | |
| "data-validation", "Data Validation", <><IconDataValidation />{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" && ( | |
| <AnchoredOverlay | |
| anchor={anchorEl} | |
| className="sheet-ribbon-pop sheet-ribbon-pop-portal sheet-ribbon-insert-form" | |
| onDismiss={closePop} | |
| role="dialog" | |
| ariaLabel="Data Validation" | |
| > | |
| <form className="sheet-ribbon-insert-fields" onSubmit={(event) => { event.preventDefault(); submit(); }}> | |
| <label className="sheet-ribbon-insert-caption" htmlFor="sheet-data-validation-list"> | |
| Allowed values, one per line | |
| </label> | |
| <textarea | |
| id="sheet-data-validation-list" | |
| name="validation" | |
| className="sheet-ribbon-insert-area" | |
| rows={6} | |
| value={text} | |
| onChange={(event) => { setText(event.target.value); setResult(null); }} | |
| /> | |
| <p className="sheet-ribbon-insert-hint">Leave empty to clear validation.</p> | |
| <button type="submit" className="sheet-ribbon-item" disabled={!insert?.selection}>Apply</button> | |
| {result !== null && <p className="sheet-ribbon-insert-hint" role="status">{result}</p>} | |
| </form> | |
| </AnchoredOverlay> | |
| )} | |
| </span> | |
| ); | |
| } | |
| function connectionDescription(binding: CellBinding): string { | |
| // CellBinding is a Record<string, unknown>. Narrow the fields BindingEditor actually writes. | |
| const parts = [typeof binding.database === "string" ? binding.database.trim() : ""]; | |
| const aggregate = binding.aggregate; | |
| if (aggregate && typeof aggregate === "object" && !Array.isArray(aggregate)) { | |
| const fn = "fn" in aggregate && typeof aggregate.fn === "string" ? aggregate.fn.trim() : ""; | |
| const field = "field" in aggregate && typeof aggregate.field === "string" ? aggregate.field.trim() : ""; | |
| parts.push([fn, field].filter(Boolean).join(" ")); | |
| } | |
| const scope = binding.scope; | |
| if (scope && typeof scope === "object" && !Array.isArray(scope)) { | |
| const name = "view_name" in scope && typeof scope.view_name === "string" ? scope.view_name.trim() : ""; | |
| const id = "view_id" in scope && typeof scope.view_id === "string" ? scope.view_id.trim() : ""; | |
| parts.push(name || id); | |
| } | |
| return parts.filter(Boolean).join(", "); | |
| } | |
| function QueriesConnectionsControl({ ctx }: { ctx: RibbonTabContext }) { | |
| const { insert, control, word, act, open, anchorEl, openPop, closePop } = ctx; | |
| const [connections, setConnections] = useState<{ address: string; description: string }[]>([]); | |
| return ( | |
| <span className="sheet-ribbon-slot"> | |
| {control( | |
| "data-queries-connections", "Queries & Connections", <><IconQueriesConnections />{word("Queries & Connections")}</>, | |
| (event) => { | |
| if (open === "data-queries-connections") { closePop(); return; } | |
| const found: { address: string; description: string }[] = []; | |
| // Inspect on open, not on every Ribbon render. The existing usedRange owns the scan. | |
| const range = insert ? usedRange(insert.cellAt) : null; | |
| if (insert && range) { | |
| for (let row = range.row0; row <= range.row1; row += 1) { | |
| for (let col = range.col0; col <= range.col1; col += 1) { | |
| const cell = insert.cellAt(col, row); | |
| if (cell?.kind !== "bound" || !cell.binding) continue; | |
| found.push({ address: addressOf(col, row), description: connectionDescription(cell.binding) }); | |
| } | |
| } | |
| } | |
| setConnections(found); | |
| openPop("data-queries-connections")(event); | |
| }, | |
| false, open === "data-queries-connections", "Q", "gallery", false, | |
| )} | |
| {open === "data-queries-connections" && ( | |
| <AnchoredOverlay | |
| anchor={anchorEl} | |
| className="sheet-ribbon-pop sheet-ribbon-pop-portal" | |
| onDismiss={closePop} | |
| role="dialog" | |
| ariaLabel="Queries & Connections" | |
| > | |
| {connections.length === 0 ? ( | |
| <p className="sheet-ribbon-insert-hint">This workbook has no connected cells.</p> | |
| ) : ( | |
| <ul aria-label="Connected cells"> | |
| {connections.map(({ address, description }) => ( | |
| <li key={address} className="sheet-ribbon-insert-hint"> | |
| {address}{description ? `: ${description}` : ""} | |
| </li> | |
| ))} | |
| </ul> | |
| )} | |
| <button type="button" className="sheet-ribbon-item" onClick={() => act("refresh")}>Refresh All</button> | |
| </AnchoredOverlay> | |
| )} | |
| </span> | |
| ); | |
| } | |
| function TextToColumnsControl({ ctx }: { ctx: RibbonTabContext }) { | |
| const { insert, control, word, item, open, anchorEl, openPop, closePop } = ctx; | |
| const [result, setResult] = useState<string | null>(null); | |
| const split = (delimiter: string) => setResult(splitTextToColumns(insert, delimiter)); | |
| return ( | |
| <span className="sheet-ribbon-slot"> | |
| {control( | |
| "data-text-to-columns", "Text to Columns", <><IconTextToColumns />{word("Text to Columns")}</>, | |
| (event) => { setResult(null); openPop("data-text-to-columns")(event); }, | |
| false, open === "data-text-to-columns", "T", "gallery", !hasDataRange(insert?.selection), | |
| )} | |
| {open === "data-text-to-columns" && ( | |
| <AnchoredOverlay | |
| anchor={anchorEl} | |
| className="sheet-ribbon-pop sheet-ribbon-pop-portal" | |
| onDismiss={closePop} | |
| role="menu" | |
| ariaLabel="Text to Columns delimiter" | |
| > | |
| {item("Comma", () => split(","), false, "menuitem", "C")} | |
| {item("Semicolon", () => split(";"), false, "menuitem", "M")} | |
| {item("Tab", () => split("\t"), false, "menuitem", "T")} | |
| {item("Space", () => split(" "), false, "menuitem", "S")} | |
| {result !== null && <p className="sheet-ribbon-insert-hint" role="status">{result}</p>} | |
| </AnchoredOverlay> | |
| )} | |
| </span> | |
| ); | |
| } | |
| /** | |
| * groups(ctx) is called inside Ribbon's render, so hooks there would change order on a tab switch. | |
| * A real child component owns this result because the tab context has no notice callback. | |
| */ | |
| function RemoveDuplicatesControl({ ctx }: { ctx: RibbonTabContext }) { | |
| const { insert, control, open, anchorEl, openPop, closePop } = ctx; | |
| const [result, setResult] = useState<string | null>(null); | |
| return ( | |
| <span className="sheet-ribbon-slot"> | |
| {control( | |
| "data-remove-duplicates", "Remove Duplicates", <IconRemoveDuplicates />, | |
| (event) => { | |
| // Pressing an open result closes it without deduping the already-cleared tail again. | |
| if (open === "data-remove-duplicates") { closePop(); return; } | |
| const count = transformSelectionRows(insert, uniqueRows); | |
| if (!count) return; | |
| setResult(`${count.before - count.after} duplicate values found and removed; ${count.after} unique values remain.`); | |
| openPop("data-remove-duplicates")(event); | |
| }, | |
| // This is a completed action with status feedback, so it has no menu/gallery semantics. | |
| false, undefined, "D", undefined, !hasDataRange(insert?.selection), | |
| )} | |
| {open === "data-remove-duplicates" && result !== null && ( | |
| <AnchoredOverlay | |
| anchor={anchorEl} | |
| className="sheet-ribbon-pop sheet-ribbon-pop-portal" | |
| onDismiss={closePop} | |
| role="status" | |
| ariaLabel="Remove Duplicates result" | |
| initialFocus="none" | |
| > | |
| <p className="sheet-ribbon-insert-hint">{result}</p> | |
| </AnchoredOverlay> | |
| )} | |
| </span> | |
| ); | |
| } | |
| function FlashFillControl({ ctx }: { ctx: RibbonTabContext }) { | |
| const { insert, control, open, anchorEl, openPop, closePop } = ctx; | |
| const [result, setResult] = useState<string | null>(null); | |
| return ( | |
| <span className="sheet-ribbon-slot"> | |
| {control( | |
| "data-flash-fill", "Flash Fill", <IconFlashFill />, | |
| (event) => { | |
| if (open === "data-flash-fill") { closePop(); return; } | |
| setResult(flashFillSelection(insert)); | |
| openPop("data-flash-fill")(event); | |
| }, | |
| false, undefined, "F", undefined, !hasDataRange(insert?.selection), | |
| )} | |
| {open === "data-flash-fill" && result !== null && ( | |
| <AnchoredOverlay | |
| anchor={anchorEl} | |
| className="sheet-ribbon-pop sheet-ribbon-pop-portal" | |
| onDismiss={closePop} | |
| role="status" | |
| ariaLabel="Flash Fill result" | |
| initialFocus="none" | |
| > | |
| <p className="sheet-ribbon-insert-hint">{result}</p> | |
| </AnchoredOverlay> | |
| )} | |
| </span> | |
| ); | |
| } | |
| function IconFilter() { | |
| return <svg viewBox="0 0 16 16" aria-hidden="true" className="sheet-ribbon-icon"><path d="M2 3h12L9.5 8v4l-3 1V8Z" /></svg>; | |
| } | |
| function IconOutlineUnhide() { | |
| return <svg viewBox="0 0 16 16" aria-hidden="true" className="sheet-ribbon-icon"><rect x="2" y="2" width="12" height="12" rx="1" /><path d="M2 6h12M6 2v12m3-5 2 2m0-2-2 2" /></svg>; | |
| } | |
| export const dataGroups = (ctx: RibbonTabContext) => { | |
| const { act, control, group, large, stack, word, insert, closePop } = ctx; | |
| const filterDoor = insert as (RibbonInsertDoor & Partial<SheetFilterDoor>) | undefined; | |
| const dataDisabled = !hasDataRange(insert?.selection); | |
| return ( | |
| <> | |
| {group("Queries & Connections", <> | |
| {large(<> | |
| {control( | |
| "data-refresh", "Refresh All", <><IconRefresh />{word("Refresh All")}</>, | |
| () => act("refresh"), false, undefined, "R", | |
| )} | |
| </>)} | |
| {large(<> | |
| <QueriesConnectionsControl ctx={ctx} /> | |
| </>)} | |
| </>)} | |
| {group("Sort", stack( | |
| <> | |
| {control( | |
| "data-sort-asc", "Sort A to Z", <IconSort />, | |
| () => { transformSelectionRows(insert, (rows) => sortRows(rows, 1)); closePop(); }, | |
| false, undefined, "SA", undefined, dataDisabled, | |
| )} | |
| </>, | |
| <> | |
| {control( | |
| "data-sort-desc", "Sort Z to A", <IconSort descending />, | |
| () => { transformSelectionRows(insert, (rows) => sortRows(rows, -1)); closePop(); }, | |
| false, undefined, "SD", undefined, dataDisabled, | |
| )} | |
| </> | |
| ))} | |
| {group("Filter", large(control( | |
| "data-filter", "Filter", <><IconFilter />{word("Filter")}</>, | |
| () => { filterDoor?.toggleFilter?.(); closePop(); }, | |
| filterDoor?.filterActive ?? false, undefined, "F", | |
| )))} | |
| {group("Data Validation", large(<DataValidationControl ctx={ctx} />))} | |
| {group("Data Tools", <> | |
| {large(<> | |
| <TextToColumnsControl ctx={ctx} /> | |
| </>)} | |
| {stack( | |
| <> | |
| <FlashFillControl ctx={ctx} /> | |
| </>, | |
| <> | |
| <RemoveDuplicatesControl ctx={ctx} /> | |
| </> | |
| )} | |
| </>)} | |
| {group("Outline", stack( | |
| <> | |
| {control( | |
| "data-hide-rows", "Hide Rows", <IconBand />, | |
| () => act("hide-rows"), false, undefined, "HR", | |
| )} | |
| </>, | |
| <> | |
| {control( | |
| "data-hide-columns", "Hide Columns", <IconBand column />, | |
| () => act("hide-columns"), false, undefined, "HC", | |
| )} | |
| </>, | |
| <> | |
| {control( | |
| "data-unhide", "Unhide Rows and Columns", <IconOutlineUnhide />, | |
| () => act("unhide"), false, undefined, "U", | |
| )} | |
| </> | |
| ))} | |
| </> | |
| ); | |
| }; | |
| export const DATA_TAB = { | |
| id: "data", | |
| key: "tab-data", | |
| kt: "A", | |
| label: "Data", | |
| bodyClass: "sheet-ribbon-data", | |
| toolbarLabel: "Data", | |
| keys: DATA_KEYS, | |
| groups: dataGroups, | |
| } as const; | |