File size: 31,000 Bytes
a83fcff f5696cb a83fcff 9a6cb9a a83fcff 9a6cb9a f5696cb b80130d f5696cb a83fcff f5696cb b80130d f5696cb b80130d f5696cb b80130d f5696cb b80130d f5696cb b80130d f5696cb b80130d f5696cb b80130d f5696cb bdac669 f5696cb b80130d f5696cb b80130d f5696cb bdac669 f5696cb b80130d f5696cb b80130d f5696cb bdac669 a83fcff 9a6cb9a f5696cb a83fcff b80130d bdac669 a83fcff b80130d a83fcff bdac669 f5696cb bdac669 a83fcff f5696cb b80130d f5696cb bdac669 f5696cb b80130d f5696cb a83fcff 9a6cb9a b80130d bdac669 f5696cb bdac669 a83fcff b80130d a83fcff bdac669 a83fcff b80130d a83fcff bdac669 a83fcff b80130d a83fcff | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368 369 370 371 372 373 374 375 376 377 378 379 380 381 382 383 384 385 386 387 388 389 390 391 392 393 394 395 396 397 398 399 400 401 402 403 404 405 406 407 408 409 410 411 412 413 414 415 416 417 418 419 420 421 422 423 424 425 426 427 428 429 430 431 432 433 434 435 436 437 438 439 440 441 442 443 444 445 446 447 448 449 450 451 452 453 454 455 456 457 458 459 460 461 462 463 464 465 466 467 468 469 470 471 472 473 474 475 476 477 478 479 480 481 482 483 484 485 486 487 488 489 490 491 492 493 494 495 496 497 498 499 500 501 502 503 504 505 506 507 508 509 510 511 512 513 514 515 516 517 518 519 520 521 522 523 524 525 526 527 528 529 530 531 532 533 534 535 536 537 538 539 540 541 542 543 544 545 546 547 548 549 550 551 552 553 554 555 556 557 558 559 560 561 562 563 564 565 566 567 568 569 570 571 572 573 574 575 576 577 578 579 580 581 582 583 584 585 586 587 588 589 590 591 592 593 594 595 596 597 598 599 600 601 602 603 604 605 606 607 608 609 610 611 612 613 614 615 616 617 618 619 620 621 622 623 624 625 626 627 628 629 630 631 632 633 634 635 636 637 638 639 640 641 642 643 644 645 646 647 648 649 650 651 652 653 654 655 656 657 658 659 660 661 662 663 664 665 666 667 668 669 670 671 672 673 674 675 676 677 678 679 680 681 682 683 684 685 686 687 688 689 690 691 692 693 694 695 696 697 698 699 700 701 702 703 704 705 706 | // 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;
|