sailfork / t3code-github / apps/ web/ src/ components/ files/ office/ sheet-cells.ts

// Adapted from opencode (MIT). Copyright (c) 2025 opencode. See LICENSE.opencode.
import type { XlsxAnchor } from "@betteroffice/xlsx";
import {
  selectionAt,
  type CellAddr,
  type CellRange,
  type Selection,
} from "@betteroffice/xlsx/headless";

/** The sheet's last cell, zero-based: row 1,048,576 in column XFD. */
export const lastCell = { row: 1048575, col: 16383 };

/** A column's letters, such as "A", "Z" or "AA". */
export function columnName(index: number): string {
  return (
    (index < 26 ? "" : columnName(Math.floor(index / 26) - 1)) +
    String.fromCharCode(65 + (index % 26))
  );
}

/** The cells an export anchor names: one range for a cell or range anchor, none for any other kind. */
export function anchorRange(anchor: XlsxAnchor): CellRange[] {
  if (anchor.kind !== "cell" && anchor.kind !== "range") return [];

  const range = parseRange(anchor.a1);

  return range ? [range] : [];
}

/** An A1 cell or range, such as "B2" or "A1:F20"; undefined for anything else. */
export function parseRange(a1: string): CellRange | undefined {
  // A lone cell is its own last corner; an empty one, as in "A1:", names no range.
  const corners = a1.split(":");
  const first = parseCell(corners[0] ?? "");
  const last = corners.length === 1 ? first : parseCell(corners[1] ?? "");

  if (!first || !last) return undefined;

  return {
    top: Math.min(first.row, last.row),
    left: Math.min(first.col, last.col),
    bottom: Math.max(first.row, last.row),
    right: Math.max(first.col, last.col),
  };
}

/** Grows a range until it covers every merged cell it touches, as Excel selects. */
export function coverMerges(range: CellRange, merges: readonly CellRange[]): CellRange {
  const grown = merges.reduce(
    (current, merge) =>
      overlaps(current, merge)
        ? {
            top: Math.min(current.top, merge.top),
            left: Math.min(current.left, merge.left),
            bottom: Math.max(current.bottom, merge.bottom),
            right: Math.max(current.right, merge.right),
          }
        : current,
    range,
  );

  // A merge the range grew into can touch another one it did not reach before.
  if (same(grown, range)) return grown;

  return coverMerges(grown, merges);
}

/**
 * Moves a collapsed selection the way Excel moves its active cell around merged cells: landing in one selects its
 * first cell, and a step that stays inside the merged cell it started from steps past it.
 */
export function settleMerges(
  previous: Selection,
  next: Selection,
  merges: readonly CellRange[],
): Selection {
  if (!collapsed(next)) return next;

  const merge = merges.find((candidate) => contains(candidate, next.focus));

  if (!merge) return next;

  if (!collapsed(previous) || !contains(merge, previous.focus))
    return selectionAt({ row: merge.top, col: merge.left });

  const cell = {
    row: Math.min(
      lastCell.row,
      Math.max(0, past(previous.focus.row, next.focus.row, merge.top, merge.bottom)),
    ),
    col: Math.min(
      lastCell.col,
      Math.max(0, past(previous.focus.col, next.focus.col, merge.left, merge.right)),
    ),
  };

  const landed = merges.find((candidate) => contains(candidate, cell));

  return selectionAt(landed ? { row: landed.top, col: landed.left } : cell);
}

/** A run of hidden rows or columns, zero-based and inclusive. */
export type Span = { readonly start: number; readonly end: number };

/** Hidden row spans such as "5:7", or column spans such as "C:D", as the export lists them. */
export function parseSpans(spans: readonly string[], axis: "rows" | "columns"): Span[] {
  return spans.flatMap((span) => {
    const [first = "", last = first] = span.split(":");
    const start = axis === "rows" ? Number(first) - 1 : columnIndex(first);
    const end = axis === "rows" ? Number(last) - 1 : columnIndex(last);

    return Number.isInteger(start) && Number.isInteger(end) && start >= 0 && end >= start
      ? [{ start, end }]
      : [];
  });
}

/**
 * Moves a selection's focus off hidden rows and columns, on in the direction it moved, as Excel's arrow keys skip
 * them. A move that would leave the sheet stays where it started.
 */
export function skipHidden(
  from: CellAddr,
  selection: Selection,
  hidden: { readonly rows: readonly Span[]; readonly columns: readonly Span[] },
): Selection {
  const focus = {
    row: clear(from.row, selection.focus.row, hidden.rows, lastCell.row),
    col: clear(from.col, selection.focus.col, hidden.columns, lastCell.col),
  };

  if (focus.row === selection.focus.row && focus.col === selection.focus.col) return selection;

  if (collapsed(selection)) return selectionAt(focus);

  return { anchor: selection.anchor, focus };
}

/** Runs an engine call that throws on input it cannot take, such as a viewport with too many cells to draw. */
export function attempt<T>(run: () => T): T | undefined {
  try {
    return run();
  } catch {
    return undefined;
  }
}

/** Whether a range holds a cell. */
export function contains(range: CellRange, cell: CellAddr) {
  return (
    cell.row >= range.top &&
    cell.row <= range.bottom &&
    cell.col >= range.left &&
    cell.col <= range.right
  );
}

function parseCell(a1: string): CellAddr | undefined {
  const match = /^\$?([A-Z]{1,3})\$?([1-9]\d*)$/i.exec(a1);

  if (!match) return undefined;

  const col = columnIndex(match[1]!);
  const row = Number(match[2]) - 1;

  if (col > lastCell.col || row > lastCell.row) return undefined;

  return { row, col };
}

/** A column's zero-based index from its letters; NaN for anything but one to three letters. */
function columnIndex(letters: string) {
  if (!/^[A-Z]{1,3}$/i.test(letters)) return Number.NaN;

  return (
    [...letters.toUpperCase()].reduce(
      (total, letter) => total * 26 + letter.charCodeAt(0) - 64,
      0,
    ) - 1
  );
}

/** Where a move along one axis lands once past any hidden span it ended in. */
function clear(from: number, to: number, spans: readonly Span[], last: number): number {
  const span = spans.find((candidate) => to >= candidate.start && to <= candidate.end);

  if (!span || to === from) return to;

  const next = to > from ? span.end + 1 : span.start - 1;

  if (next < 0 || next > last) return from;

  return clear(from, next, spans, last);
}

/** One step along an axis: past the merge's far edge in the direction of the move, or where it was. */
function past(from: number, to: number, low: number, high: number) {
  if (to > from) return high + 1;

  if (to < from) return low - 1;

  return from;
}

function collapsed(selection: Selection) {
  return (
    selection.anchor.row === selection.focus.row && selection.anchor.col === selection.focus.col
  );
}

function overlaps(a: CellRange, b: CellRange) {
  return a.left <= b.right && a.right >= b.left && a.top <= b.bottom && a.bottom >= b.top;
}

function same(a: CellRange, b: CellRange) {
  return a.top === b.top && a.left === b.left && a.bottom === b.bottom && a.right === b.right;
}