sailfork / t3code-github / apps/ web/ src/ components/ files/ office/ spreadsheet.worker.ts

// Adapted from opencode (MIT). Copyright (c) 2025 opencode. See LICENSE.opencode.
import {
  initWasm,
  openWorkbook,
  toTsv,
  type SheetInfo,
  type WorkbookHandle,
  type XlsxCellRead,
  type XlsxRangeTarget,
} from "@betteroffice/xlsx";
import type { CellRange } from "@betteroffice/xlsx/headless";
import { anchorRange, attempt, lastCell, parseRange, parseSpans } from "./sheet-cells";
import { drawFrame, paintFrame } from "./sheet-frame";
import { frozenSize } from "./sheet-paint";
import type { SheetLayout, SheetMethods, SheetTab } from "./sheet-protocol";
import { serveWorker, Transfer } from "./worker-rpc";

// One worker holds one open workbook: its engine, recalculation, display lists and the painting of its cells. The
// window terminates the worker when the workbook closes, which is the only way the engine's memory is given back.

type Opened = {
  readonly workbook: WorkbookHandle;
  /** The active sheet's metadata; the engine draws only the active sheet. */
  info: SheetInfo;
  /** Where each sheet's last cell ends, by sheet and frozen panes' size; measured once, as it costs a frame. */
  readonly ends: Map<string, { readonly x: number; readonly y: number } | undefined>;
  /** The one canvas every frame paints on and hands over as a bitmap. */
  readonly surface: OffscreenCanvas;
};

/** The workbook this worker opened, once it has. */
type State = { opened: Opened | undefined };

const state: State = { opened: undefined };

serveWorker<SheetMethods>({
  open: async (input) => {
    if (state.opened) throw new Error("The worker already holds a workbook");

    await initWasm();

    const workbook = openWorkbook(new Uint8Array(input.bytes));
    const info = workbook.sheetInfo();

    state.opened = { workbook, info, ends: new Map(), surface: new OffscreenCanvas(1, 1) };

    const tabs = visibleTabs(workbook, info);
    // A workbook saved on a hidden sheet opens on the first visible one, as Excel does.
    const first = tabs.find((tab) => tab.index === info.activeSheet) ?? tabs[0];

    if (!first) return undefined;

    return { tabs, layout: show(state.opened, first.index) };
  },
  show: (input) => {
    const opened = required();
    const current = opened.info.activeSheet;
    const layout = attempt(() => show(opened, input.sheet));

    if (layout) return layout;

    // One sheet that fails to open leaves the others readable: go back to the sheet on show.
    attempt(() => show(opened, current));

    throw new Error(`Sheet ${input.sheet} could not open`);
  },
  frame: (input) => {
    const opened = active(input.sheet);
    const frozen = { rows: opened.info.frozenRows, cols: opened.info.frozenCols };
    const viewport = { x: input.x, y: input.y, width: input.width, height: input.height };
    const drawn = drawFrame(opened.workbook, viewport, frozen, input.wider);

    if (!drawn) return undefined;

    const surface = opened.surface;
    const width = Math.ceil(input.offset.x + drawn.viewport.width * input.ratio);
    const height = Math.ceil(input.offset.y + drawn.viewport.height * input.ratio);

    if (surface.width !== width) surface.width = width;

    if (surface.height !== height) surface.height = height;

    const context = surface.getContext("2d");

    if (!context) return undefined;

    paintFrame(context, drawn, { ratio: input.ratio, origin: input.offset });

    const bitmap = surface.transferToImageBitmap();
    const grid = drawn.list.grid;
    const panes = frozenSize(grid, frozen);

    return new Transfer(
      {
        bitmap,
        viewport: drawn.viewport,
        grid,
        hyperlinks: drawn.list.hyperlinks ?? [],
        frozen: panes,
        end: end(opened, input.sheet, panes),
      },
      [bitmap],
    );
  },
  position: (input) => {
    const workbook = required().workbook;
    const cell = input.cell;
    const start = attempt(() => workbook.cellPosition(input.sheet, cell.row, cell.col));

    const next = attempt(() =>
      workbook.cellPosition(
        input.sheet,
        Math.min(cell.row + 1, lastCell.row),
        Math.min(cell.col + 1, lastCell.col),
      ),
    );

    return start && next ? { start, next } : undefined;
  },
  cells: (input) => {
    const cells = read(required(), input.sheet, input.range);
    const range = input.range;

    return Array.from({ length: range.bottom - range.top + 1 }, (_, row) =>
      Array.from(
        { length: range.right - range.left + 1 },
        (_, col) => cells?.[row]?.[col]?.displayText ?? "",
      ),
    );
  },
  // Copies the cells as they show, which is what a reader expects to paste, not the formulas behind them.
  copy: (input) => {
    const cells = read(required(), input.sheet, input.range);

    if (!cells) return undefined;

    return toTsv(
      cells.map((row) =>
        row.map((cell) => ({ input: cell.displayText, isFormula: literal(cell) })),
      ),
    );
  },
});

function required() {
  if (!state.opened) throw new Error("The worker holds no workbook");

  return state.opened;
}

/** The workbook with `sheet` active, as a frame from a grid that has since given way to another sheet may ask. */
function active(sheet: number) {
  const opened = required();

  if (opened.info.activeSheet !== sheet) show(opened, sheet);

  return opened;
}

/** Makes a sheet the active one and reads what its grid needs. */
function show(opened: Opened, sheet: number): SheetLayout {
  opened.workbook.setActiveSheet(sheet);
  opened.info = opened.workbook.sheetInfo();

  return { info: opened.info, ...layout(opened.workbook, sheet) };
}

/**
 * The used range, the merged cells, the hidden rows and columns, and how far the charts reach, from one export of the
 * sheet's metadata. One cell is enough: the lists before the cells are complete however soon the export stops.
 */
function layout(workbook: WorkbookHandle, sheet: number) {
  const result = attempt(() =>
    workbook.exportStructured({
      scope: [{ sheet, range: "A1:XFD1048576" }],
      maxCells: 1,
      includeDefinedNames: false,
    }),
  );

  const exported = result?.ok ? result.content.sheets[0] : undefined;

  const charts = (exported?.objects ?? [])
    .filter((object) => object.kind === "chart" && !object.hidden)
    .flatMap((object) => (object.anchor ? anchorRange(object.anchor) : []))
    // The corner cell holds the chart's edge somewhere inside it, so the cell past it bounds the chart.
    .flatMap((range) => {
      const corner = attempt(() =>
        workbook.cellPosition(
          sheet,
          Math.min(range.bottom + 1, lastCell.row),
          Math.min(range.right + 1, lastCell.col),
        ),
      );

      return corner ? [corner] : [];
    });

  return {
    used: exported?.usedRange ? parseRange(exported.usedRange) : undefined,
    merges: (exported?.merges ?? []).flatMap((merge) => anchorRange(merge.anchor)),
    hidden: {
      rows: parseSpans(exported?.hiddenRows ?? [], "rows"),
      columns: parseSpans(exported?.hiddenColumns ?? [], "columns"),
    },
    chartWidth: Math.max(0, ...charts.map((corner) => corner.x)),
    chartHeight: Math.max(0, ...charts.map((corner) => corner.y)),
  };
}

/** Where the sheet's last cell ends, past the frozen panes. A viewport past it makes the engine throw. */
function end(
  opened: Opened,
  sheet: number,
  frozen: { readonly width: number; readonly height: number },
) {
  const key = `${sheet}:${frozen.width}:${frozen.height}`;

  if (opened.ends.has(key)) return opened.ends.get(key);

  const measured = measureEnd(opened.workbook, sheet, frozen);

  opened.ends.set(key, measured);

  return measured;
}

function measureEnd(
  workbook: WorkbookHandle,
  sheet: number,
  frozen: { readonly width: number; readonly height: number },
) {
  const last = attempt(() => workbook.cellPosition(sheet, lastCell.row, lastCell.col));

  if (!last) return undefined;

  // A viewport one pixel past the frozen panes, on the last cell, reports where that cell ends.
  const grid = attempt(
    () =>
      workbook.displayList({
        x: last.x,
        y: last.y,
        width: frozen.width + 1,
        height: frozen.height + 1,
      }).grid,
  );

  // The engine works in single precision there, so stay a little short of the edge.
  const margin = 2;

  return {
    x:
      last.x +
      Math.max(0, (grid ? (grid.colOffsets[grid.colOffsets.length - 1] ?? 0) : 0) - frozen.width) -
      margin,
    y:
      last.y +
      Math.max(0, (grid ? (grid.rowOffsets[grid.rowOffsets.length - 1] ?? 0) : 0) - frozen.height) -
      margin,
  };
}

/** The cells of a range on a sheet as the engine reads them, row by row; undefined when it cannot. */
function read(opened: Opened, sheet: number, range: CellRange) {
  if ((range.bottom - range.top + 1) * (range.right - range.left + 1) > 200_000) return undefined;
  const target: XlsxRangeTarget = {
    sheetId: opened.info.sheetIds[sheet]!,
    range: {
      kind: "rowCol",
      start: { row: range.top, col: range.left },
      end: { row: range.bottom, col: range.right },
    },
  };

  const result = attempt(() => opened.workbook.readCells({ ranges: [target] }));

  return result?.ok ? result.ranges[0]?.cells : undefined;
}

/** The workbook's sheets but its hidden and very hidden ones, which Excel lists only to unhide. */
function visibleTabs(workbook: WorkbookHandle, info: SheetInfo): SheetTab[] {
  const names = info.sheetNames;

  const result = attempt(() =>
    workbook.exportStructured({
      includeHiddenSheets: true,
      includeDefinedNames: false,
      scope: names.map((_, sheet) => ({ sheet, range: "A1" })),
    }),
  );

  const hidden = new Set(
    (result?.ok ? result.content.sheets : [])
      .filter((sheet) => sheet.visibility === "hidden" || sheet.visibility === "veryHidden")
      .flatMap((sheet) => (sheet.anchor.kind === "sheet" ? [sheet.anchor.sheet.index] : [])),
  );

  return names.flatMap((name, index) => (hidden.has(index) ? [] : [{ index, name }]));
}

/**
 * Whether a cell's text pastes as it is. `toTsv` quotes text that a spreadsheet would read as a formula; a number shown
 * with a sign, such as "-12.50", is safe, and quoting it would paste text instead of the number.
 */
function literal(cell: XlsxCellRead) {
  return (
    cell.value.kind === "number" && /^[-+][\d\s.,%\p{Sc}]+(?:e[-+]\d+)?$/iu.test(cell.displayText)
  );
}