Skip to content

A desktop coding agent with free access to frontier models.

TypeScript
33.2 KB
Raw Blame
import {
  MAX_ENTRY_UNCOMPRESSED_BYTES,
  buildZip,
  escapeXml,
  firstXmlText,
  listZipEntries,
  parsedXmlText,
  readZipTextEntry,
  relationshipTargets,
  utf8ByteLength,
  utf8Bytes,
  xmlBlocks,
  xmlElements,
  xmlStartTagAttributes,
  xmlText,
  zipEntryMap,
  type ZipFileInput,
} from "./ooxml-package.js";

/**
 * XLSX reading and writing on top of the bounded OOXML package primitives,
 * shared by the agent tools, the attachment normalizer, and the artifact
 * spreadsheet preview. The reader exposes a row/column grid so agents can page
 * through any sheet and editors can show a dense table; the writer produces
 * plain workbooks (typed values, formulas, an optional bold header row, and
 * auto-sized columns) that spreadsheet applications open without repair.
 */

export const XLSX_MAX_SHEETS = 64;
export const XLSX_MAX_SHARED_STRINGS = 200_000;
export const XLSX_MAX_CELLS_PER_SHEET = 250_000;
export const XLSX_WRITE_MAX_SHEETS = 32;
export const XLSX_WRITE_MAX_CELLS = 50_000;
const EXCEL_MAX_ROWS = 1_048_576;
const EXCEL_MAX_COLUMNS = 16_384;
const SHEET_NAME_MAX_CHARS = 31;
const CELL_TEXT_MAX_CHARS = 32_767;
const EXCEL_EPOCH_1900_MS = Date.UTC(1899, 11, 30);
const EXCEL_EPOCH_1904_MS = Date.UTC(1904, 0, 1);
const MS_PER_DAY = 86_400_000;

export type XlsxSheetInfo = {
  position: number;
  name: string;
  sheetId: string;
  relationshipId: string;
  path: string;
  hidden: boolean;
};

export type XlsxCell = {
  reference: string;
  row: number;
  column: number;
  type: string;
  rawValue?: string;
  displayedValue?: string;
  formula?: string;
  formulaType?: string;
  formulaRef?: string;
  styleIndex?: string;
  numberFormat?: string;
};

export type XlsxSheetData = {
  info: XlsxSheetInfo;
  dimension: string;
  mergedRanges: string[];
  cells: XlsxCell[];
  omittedCells: number;
  firstRow: number;
  lastRow: number;
  firstColumn: number;
  lastColumn: number;
  formulaCount: number;
};

export type XlsxWorkbook = {
  sheets: XlsxSheetInfo[];
  /** Sheets declared by the workbook beyond XLSX_MAX_SHEETS, which are not read. */
  omittedSheets: number;
  sharedStringCount: number;
  styleCount: number;
  date1904: boolean;
  readSheet(sheet: XlsxSheetInfo, options?: { cellLimit?: number }): Promise<XlsxSheetData>;
};

/** A formula written on request, without the leading "=", e.g. `SUM(B2:B9)`. */
export type XlsxFormulaInput = { formula: string };

/**
 * Strings are always written as text, even when they start with "=", so data
 * copied from untrusted sources can never become executable spreadsheet code.
 * A formula has to be asked for explicitly with `{ formula }`.
 */
export type XlsxCellInput = string | number | boolean | null | XlsxFormulaInput;

export type XlsxSheetInput = {
  name?: string;
  rows: XlsxCellInput[][];
  header?: boolean;
};

export type XlsxTableOptions = {
  startRow?: number;
  maxRows?: number;
  maxColumns?: number;
  maxCellChars?: number;
  formulas?: boolean;
  range?: string;
};

export type XlsxTable = {
  text: string;
  renderedRows: number;
  nextStartRow: number | null;
  columns: string[];
  truncatedColumns: number;
};

export function columnLetters(column: number): string {
  let value = column;
  let letters = "";
  while (value > 0) {
    const remainder = (value - 1) % 26;
    letters = String.fromCharCode(65 + remainder) + letters;
    value = Math.floor((value - 1) / 26);
  }
  return letters;
}

export function columnIndex(letters: string): number {
  let value = 0;
  for (const char of letters.toUpperCase()) {
    const code = char.charCodeAt(0);
    if (code < 65 || code > 90) return 0;
    value = value * 26 + (code - 64);
  }
  return value;
}

export function parseCellReference(reference: string): { row: number; column: number } | null {
  const match = /^\$?([A-Za-z]{1,3})\$?(\d{1,7})$/.exec(reference.trim());
  if (!match) return null;
  const column = columnIndex(match[1]);
  const row = Number.parseInt(match[2], 10);
  if (column < 1 || column > EXCEL_MAX_COLUMNS || row < 1 || row > EXCEL_MAX_ROWS) return null;
  return { row, column };
}

export type CellRange = { firstRow: number; lastRow: number; firstColumn: number; lastColumn: number };

export function parseRange(range: string): CellRange | null {
  const trimmed = range.trim();
  const [startText, endText = startText] = trimmed.split(":");
  const start = parseCellReference(startText);
  const end = parseCellReference(endText);
  if (!start || !end) return null;
  return {
    firstRow: Math.min(start.row, end.row),
    lastRow: Math.max(start.row, end.row),
    firstColumn: Math.min(start.column, end.column),
    lastColumn: Math.max(start.column, end.column),
  };
}

function parseWorkbookSheets(workbookXml: string, relsXml: string | null): { sheets: XlsxSheetInfo[]; omittedSheets: number } {
  const targets = relationshipTargets(relsXml, "xl");
  const declared = xmlStartTagAttributes(workbookXml, "sheet");
  const sheets = declared.slice(0, XLSX_MAX_SHEETS).map((attributes, index) => {
    const relationshipId = attributes["r:id"] ?? attributes.id ?? "";
    const path = relationshipId && targets.has(relationshipId)
      ? targets.get(relationshipId) ?? ""
      : `xl/worksheets/sheet${index + 1}.xml`;
    return {
      position: index + 1,
      name: attributes.name ?? `Sheet${index + 1}`,
      sheetId: attributes.sheetId ?? String(index + 1),
      relationshipId,
      path,
      hidden: attributes.state === "hidden" || attributes.state === "veryHidden",
    };
  });
  return { sheets, omittedSheets: declared.length - sheets.length };
}

function sharedStringText(xml: string): string {
  // Keep interior line breaks (multi-line cells) and only trim the edges; the
  // table renderer flattens whitespace where a single line is needed.
  const text = xmlBlocks(xml, "t").map((block) => parsedXmlText(block.inner, "")).join("").trim();
  return text || xmlText(xml);
}

function parseSharedStrings(xml: string | null): string[] {
  if (!xml) return [];
  const strings: string[] = [];
  for (const block of xmlBlocks(xml, "si")) {
    if (strings.length >= XLSX_MAX_SHARED_STRINGS) break;
    strings.push(sharedStringText(block.inner));
  }
  return strings;
}

function builtinNumberFormat(id: string): string {
  switch (id) {
    case "0": return "General";
    case "1": return "0";
    case "2": return "0.00";
    case "3": return "#,##0";
    case "4": return "#,##0.00";
    case "9": return "0%";
    case "10": return "0.00%";
    case "11": return "0.00E+00";
    case "14": return "mm-dd-yy";
    case "15": return "d-mmm-yy";
    case "16": return "d-mmm";
    case "17": return "mmm-yy";
    case "18": return "h:mm AM/PM";
    case "19": return "h:mm:ss AM/PM";
    case "20": return "h:mm";
    case "21": return "h:mm:ss";
    case "22": return "m/d/yy h:mm";
    case "27": case "28": case "29": case "30": case "31": case "36":
    case "50": case "51": case "52": case "53": case "54": case "57": case "58":
      return "yyyy-mm-dd";
    case "32": case "33": case "34": case "35": case "55": case "56":
      return "h:mm:ss";
    case "45": return "mm:ss";
    case "46": return "[h]:mm:ss";
    case "47": return "mmss.0";
    case "49": return "@";
    default: return "";
  }
}

function parseNumberFormats(stylesXml: string | null): string[] {
  if (!stylesXml) return [];
  const custom = new Map<string, string>();
  for (const attributes of xmlStartTagAttributes(stylesXml, "numFmt")) {
    if (attributes.numFmtId && attributes.formatCode) custom.set(attributes.numFmtId, attributes.formatCode);
  }
  const cellXfs = xmlBlocks(stylesXml, "cellXfs")[0]?.inner ?? "";
  return xmlStartTagAttributes(cellXfs, "xf").map((attributes) => {
    const id = attributes.numFmtId ?? "0";
    return custom.get(id) ?? builtinNumberFormat(id);
  });
}

export function isDateNumberFormat(format: string | undefined): boolean {
  if (!format || format === "General" || format === "@") return false;
  const stripped = format
    .replace(/"[^"]*"/g, "")
    .replace(/\[[^\]]*\]/g, "")
    .replace(/\\./g, "")
    .replace(/[Ee][+-]/g, "");
  return /[ymdhs]/i.test(stripped);
}

function pad(value: number, width = 2): string {
  return String(value).padStart(width, "0");
}

export function excelSerialToIso(serial: number, date1904 = false): string | null {
  if (!Number.isFinite(serial) || serial < 0 || serial > 2_958_465) return null;
  // Excel's 1900 date system counts a phantom 1900-02-29, so serials before
  // that day sit one day later than the post-leap epoch implies.
  const epoch = date1904 ? EXCEL_EPOCH_1904_MS : serial < 60 ? EXCEL_EPOCH_1900_MS + MS_PER_DAY : EXCEL_EPOCH_1900_MS;
  const ms = Math.round(serial * MS_PER_DAY);
  const date = new Date(epoch + ms);
  if (Number.isNaN(date.getTime())) return null;
  const time = `${pad(date.getUTCHours())}:${pad(date.getUTCMinutes())}:${pad(date.getUTCSeconds())}`;
  // A serial below one day carries no calendar date: it is a time of day.
  if (serial < 1 && !date1904) return time;
  const day = `${date.getUTCFullYear()}-${pad(date.getUTCMonth() + 1)}-${pad(date.getUTCDate())}`;
  if (ms % MS_PER_DAY === 0) return day;
  return `${day} ${time}`;
}

function cellTypeLabel(type: string): string {
  if (type === "s") return "shared_string";
  if (type === "inlineStr") return "inline_string";
  if (type === "str") return "formula_string";
  if (type === "b") return "boolean";
  if (type === "e") return "error";
  if (type === "d") return "date";
  return type || "number";
}

function displayedCellValue(type: string, rawValue: string | undefined, body: string, sharedStrings: string[], numberFormat: string | undefined, date1904: boolean): string | undefined {
  if (type === "inlineStr") {
    const text = sharedStringText(body);
    return text || undefined;
  }
  if (type === "s" && rawValue !== undefined) {
    const index = Number.parseInt(rawValue, 10);
    return Number.isInteger(index) ? sharedStrings[index] : undefined;
  }
  if (type === "b" && rawValue !== undefined) return rawValue === "1" ? "TRUE" : "FALSE";
  if ((type === "" || type === "n") && rawValue !== undefined && isDateNumberFormat(numberFormat)) {
    return excelSerialToIso(Number(rawValue), date1904) ?? rawValue;
  }
  return rawValue;
}

function parseSheetData(xml: string, sharedStrings: string[], numberFormats: string[], date1904: boolean, cellLimit: number) {
  const dimension = xmlStartTagAttributes(xml, "dimension")[0]?.ref ?? "";
  const mergedRanges: string[] = [];
  for (const attributes of xmlStartTagAttributes(xml, "mergeCell")) {
    if (attributes.ref) mergedRanges.push(attributes.ref);
  }
  const sheetData = xmlBlocks(xml, "sheetData")[0]?.inner ?? xml;
  const cells: XlsxCell[] = [];
  let omittedCells = 0;
  let formulaCount = 0;
  let impliedRow = 0;
  for (const rowBlock of xmlElements(sheetData, "row")) {
    const declaredRow = Number.parseInt(rowBlock.attributes.r ?? "", 10);
    const row = Number.isInteger(declaredRow) && declaredRow > 0 ? declaredRow : impliedRow + 1;
    impliedRow = row;
    let impliedColumn = 0;
    for (const cellBlock of xmlElements(rowBlock.inner, "c")) {
      const attributes = cellBlock.attributes;
      const body = cellBlock.inner;
      const declared = attributes.r ? parseCellReference(attributes.r) : null;
      const column = declared?.column ?? impliedColumn + 1;
      const cellRow = declared?.row ?? row;
      impliedColumn = column;
      const formulaBlock = xmlElements(body, "f")[0];
      const rawValue = firstXmlText(body, "v");
      const type = attributes.t ?? "";
      if (formulaBlock) formulaCount += 1;
      if (rawValue === undefined && !formulaBlock && type !== "inlineStr") continue;
      if (cells.length >= cellLimit) {
        omittedCells += 1;
        continue;
      }
      const styleIndex = attributes.s;
      const numberFormat = styleIndex !== undefined ? numberFormats[Number.parseInt(styleIndex, 10)] : undefined;
      const displayedValue = displayedCellValue(type, rawValue, body, sharedStrings, numberFormat, date1904);
      const formula = formulaBlock ? xmlText(formulaBlock.inner) : "";
      cells.push({
        reference: `${columnLetters(column)}${cellRow}`,
        row: cellRow,
        column,
        type: cellTypeLabel(type),
        ...(styleIndex !== undefined ? { styleIndex } : {}),
        ...(numberFormat ? { numberFormat } : {}),
        ...(formulaBlock ? { formula } : {}),
        ...(formulaBlock?.attributes.t ? { formulaType: formulaBlock.attributes.t } : {}),
        ...(formulaBlock?.attributes.ref ? { formulaRef: formulaBlock.attributes.ref } : {}),
        ...(rawValue !== undefined ? { rawValue } : {}),
        ...(displayedValue !== undefined ? { displayedValue } : {}),
      });
    }
  }
  return { dimension, mergedRanges, cells, omittedCells, formulaCount };
}

function sheetExtents(cells: XlsxCell[], dimension: string) {
  const parsed = cells.length === 0 && dimension ? parseRange(dimension) : null;
  if (parsed) return parsed;
  if (cells.length === 0) return { firstRow: 0, lastRow: 0, firstColumn: 0, lastColumn: 0 };
  let firstRow = Number.POSITIVE_INFINITY;
  let lastRow = 0;
  let firstColumn = Number.POSITIVE_INFINITY;
  let lastColumn = 0;
  for (const cell of cells) {
    firstRow = Math.min(firstRow, cell.row);
    lastRow = Math.max(lastRow, cell.row);
    firstColumn = Math.min(firstColumn, cell.column);
    lastColumn = Math.max(lastColumn, cell.column);
  }
  return { firstRow, lastRow, firstColumn, lastColumn };
}

export async function openXlsxWorkbook(bytes: Uint8Array): Promise<XlsxWorkbook> {
  const entries = zipEntryMap(listZipEntries(bytes));
  const workbookXml = await readZipTextEntry(bytes, entries, "xl/workbook.xml");
  if (!workbookXml) throw new Error("XLSX workbook.xml was not found.");
  const sharedStrings = parseSharedStrings(await readZipTextEntry(bytes, entries, "xl/sharedStrings.xml"));
  const numberFormats = parseNumberFormats(await readZipTextEntry(bytes, entries, "xl/styles.xml"));
  const { sheets, omittedSheets } = parseWorkbookSheets(workbookXml, await readZipTextEntry(bytes, entries, "xl/_rels/workbook.xml.rels"));
  if (sheets.length === 0) throw new Error("XLSX workbook contained no sheets.");
  const date1904 = xmlStartTagAttributes(workbookXml, "workbookPr").some((attributes) => attributes.date1904 === "1" || attributes.date1904 === "true");

  return {
    sheets,
    omittedSheets,
    sharedStringCount: sharedStrings.length,
    styleCount: numberFormats.length,
    date1904,
    async readSheet(sheet, options = {}) {
      const safePath = sheet.path.startsWith("xl/worksheets/") && sheet.path.endsWith(".xml") ? sheet.path : "";
      const sheetXml = safePath ? await readZipTextEntry(bytes, entries, safePath) : null;
      if (!sheetXml) throw new Error(`Worksheet XML for "${sheet.name}" was not found or was outside xl/worksheets.`);
      const data = parseSheetData(sheetXml, sharedStrings, numberFormats, date1904, options.cellLimit ?? XLSX_MAX_CELLS_PER_SHEET);
      return { info: sheet, ...data, ...sheetExtents(data.cells, data.dimension) };
    },
  };
}

export function findSheet(workbook: XlsxWorkbook, selector: string | number | undefined): XlsxSheetInfo {
  if (selector === undefined || selector === "") return workbook.sheets[0];
  if (typeof selector === "number") {
    const sheet = workbook.sheets[selector - 1];
    if (!sheet) throw new Error(`Sheet position ${selector} is out of range; the workbook has ${workbook.sheets.length} sheet(s).`);
    return sheet;
  }
  const exact = workbook.sheets.find((sheet) => sheet.name === selector);
  if (exact) return exact;
  const lower = selector.trim().toLowerCase();
  const relaxed = workbook.sheets.find((sheet) => sheet.name.trim().toLowerCase() === lower);
  if (relaxed) return relaxed;
  const numeric = Number.parseInt(selector, 10);
  if (Number.isInteger(numeric) && String(numeric) === selector.trim()) return findSheet(workbook, numeric);
  throw new Error(`Sheet "${selector}" was not found. Available sheets: ${workbook.sheets.map((sheet) => JSON.stringify(sheet.name)).join(", ")}.`);
}

// A raw pipe would split a Markdown table cell. Swap it for the look-alike
// box-drawing bar instead of backslash-escaping, so backslashes in paths and
// regexes reach the model exactly as the sheet stores them.
const TABLE_SAFE_PIPE = "\u2502";

function cellText(cell: XlsxCell | undefined, formulas: boolean, maxChars: number): string {
  if (!cell) return "";
  const value = formulas && cell.formula
    ? `=${cell.formula}`
    : cell.displayedValue ?? cell.rawValue ?? (cell.formula ? `=${cell.formula}` : "");
  const flat = value.replace(/\s*\r?\n\s*/g, " ").replaceAll("|", TABLE_SAFE_PIPE);
  return flat.length > maxChars ? `${flat.slice(0, maxChars - 1)}…` : flat;
}

/**
 * Render a page of a sheet as a Markdown table with real sheet row numbers and
 * column letters, so a model can cite cells exactly. Empty rows are skipped
 * (their absence is visible from the row numbers); columns are the sheet's
 * used columns within the optional A1 range.
 */
export function renderSheetTable(sheet: XlsxSheetData, options: XlsxTableOptions = {}): XlsxTable {
  const range = options.range ? parseRange(options.range) : null;
  if (options.range && !range) throw new Error(`Range "${options.range}" is not a valid A1-style range such as A1:D20.`);
  const maxRows = Math.max(1, options.maxRows ?? 100);
  const maxColumns = Math.max(1, options.maxColumns ?? 40);
  const maxCellChars = Math.max(8, options.maxCellChars ?? 120);
  const formulas = options.formulas ?? false;
  const firstRow = Math.max(options.startRow ?? 1, range?.firstRow ?? 1);
  const lastRow = range?.lastRow ?? Number.POSITIVE_INFINITY;
  const firstColumn = range?.firstColumn ?? 1;
  const lastColumn = range?.lastColumn ?? Number.POSITIVE_INFINITY;

  const grid = new Map<number, Map<number, XlsxCell>>();
  const usedColumns = new Set<number>();
  for (const cell of sheet.cells) {
    if (cell.column < firstColumn || cell.column > lastColumn) continue;
    if (cell.row < (range?.firstRow ?? 1) || cell.row > lastRow) continue;
    usedColumns.add(cell.column);
    let row = grid.get(cell.row);
    if (!row) {
      row = new Map();
      grid.set(cell.row, row);
    }
    row.set(cell.column, cell);
  }

  const orderedColumns = [...usedColumns].sort((left, right) => left - right);
  const visibleColumns = orderedColumns.slice(0, maxColumns);
  const truncatedColumns = orderedColumns.length - visibleColumns.length;
  const rowNumbers = [...grid.keys()].filter((row) => row >= firstRow).sort((left, right) => left - right);
  const pageRows = rowNumbers.slice(0, maxRows);
  const nextStartRow = rowNumbers.length > pageRows.length ? rowNumbers[pageRows.length] : null;
  const columns = visibleColumns.map(columnLetters);

  const lines: string[] = [];
  if (pageRows.length === 0) {
    lines.push("(no cells in this range)");
  } else {
    lines.push(`| # | ${columns.join(" | ")} |`);
    lines.push(`|---|${columns.map(() => "---").join("|")}|`);
    for (const rowNumber of pageRows) {
      const row = grid.get(rowNumber);
      lines.push(`| ${rowNumber} | ${visibleColumns.map((column) => cellText(row?.get(column), formulas, maxCellChars)).join(" | ")} |`);
    }
  }
  return { text: lines.join("\n"), renderedRows: pageRows.length, nextStartRow, columns, truncatedColumns };
}

export function sheetHeaderRow(sheet: XlsxSheetData, maxColumns = 30): string[] {
  if (sheet.cells.length === 0) return [];
  const firstRow = Math.min(...sheet.cells.map((cell) => cell.row));
  return sheet.cells
    .filter((cell) => cell.row === firstRow)
    .sort((left, right) => left.column - right.column)
    .slice(0, maxColumns)
    .map((cell) => cell.displayedValue ?? cell.rawValue ?? (cell.formula ? `=${cell.formula}` : ""));
}

export function numberFormatSummary(sheet: XlsxSheetData, maxFormats = 8, maxExamples = 3): string[] {
  const examples = new Map<string, string[]>();
  for (const cell of sheet.cells) {
    if (!cell.numberFormat || cell.numberFormat === "General") continue;
    const list = examples.get(cell.numberFormat) ?? [];
    if (list.length < maxExamples) list.push(cell.reference);
    examples.set(cell.numberFormat, list);
  }
  return [...examples.entries()].slice(0, maxFormats).map(([format, refs]) => `${JSON.stringify(format)} (${refs.join(", ")})`);
}

export function formulaSummary(sheet: XlsxSheetData, maxFormulas = 20): string[] {
  return sheet.cells
    .filter((cell) => cell.formula)
    .slice(0, maxFormulas)
    .map((cell) => {
      const cached = cell.displayedValue ?? cell.rawValue;
      return `${cell.reference}: =${cell.formula}${cached !== undefined ? ` → ${cached}` : ""}`;
    });
}

const GRID_MAX_CELLS = 250_000;

/**
 * Dense rows for an editor: every row from 1 to the last used row and every
 * column from A to the last used column, so positions survive a save.
 */
export function sheetGridRows(sheet: XlsxSheetData, options: { maxCells?: number; formulas?: boolean } = {}): string[][] {
  if (sheet.cells.length === 0) return [[""]];
  const maxCells = options.maxCells ?? GRID_MAX_CELLS;
  if (sheet.lastRow * sheet.lastColumn > maxCells) {
    throw new Error(`This sheet spans ${sheet.lastRow} rows by ${sheet.lastColumn} columns, which is too large to show as an editable grid.`);
  }
  const rows: string[][] = Array.from({ length: sheet.lastRow }, () => Array.from({ length: sheet.lastColumn }, () => ""));
  for (const cell of sheet.cells) {
    rows[cell.row - 1][cell.column - 1] = options.formulas && cell.formula
      ? `=${cell.formula}`
      : cell.displayedValue ?? cell.rawValue ?? (cell.formula ? `=${cell.formula}` : "");
  }
  return rows;
}

const CANONICAL_NUMBER = /^-?(?:0|[1-9]\d*)(?:\.\d+)?(?:[eE][+-]?\d+)?$/;
const FORMULA_MAX_CHARS = 8_192;
// Functions that fetch remote data, run registered code, or pull other files
// when a workbook recalculates. A generated report never needs them.
const OUTSIDE_WORKBOOK_FUNCTION = /\b(DDE|WEBSERVICE|FILTERXML|RTD|CALL|REGISTER(?:\.ID)?|EXEC|SQL\.REQUEST|IMPORT(?:DATA|XML|HTML|FEED|RANGE)|IMAGE)\s*\(/i;
// A bracket group that belongs to a sheet reference ("[Book]Sheet!A1",
// "[Book.xlsx]シート1!A1", "'path\\[Book]Sheet'!A1", "[1]Sheet!A1") names
// another workbook. An unquoted sheet name may contain any script, so the
// span between "]" and "!" is anything that is not a formula operator or
// delimiter; structured table references ("Table1[Amount]+Sheet2!A1",
// "[@Amount]*2") are always followed by an operator or the end of the formula.
const EXTERNAL_WORKBOOK_REFERENCE = /\[[^\]]*\][^!'"(),;+\-*/^&=<>\[\]{}]*!|'[^']*\[[^\]]*\][^']*'!/u;
// A quoted sheet reference whose path is a URL or UNC share.
const REMOTE_PATH_REFERENCE = /'[^']*(?::\/\/|\\\\)[^']*'!/;

export function isFormulaInput(value: XlsxCellInput): value is XlsxFormulaInput {
  return typeof value === "object" && value !== null && typeof value.formula === "string";
}

/**
 * Why a formula must not be written, or null when it is a plain calculation.
 * Formulas that reach outside the workbook are refused even when requested:
 * DDE command references (`cmd|'/c …'!A0`), functions that fetch remote data
 * or execute registered code, and references into other workbooks.
 */
export function unsafeFormulaReason(formula: string): string | null {
  const body = formula.trim().replace(/^=/, "");
  if (!body) return "is empty";
  if (body.length > FORMULA_MAX_CHARS) return `is longer than ${FORMULA_MAX_CHARS} characters`;
  if (body.includes("|")) return "contains a DDE-style external command reference";
  const remote = OUTSIDE_WORKBOOK_FUNCTION.exec(body);
  if (remote) return `uses ${remote[1].toUpperCase()}, which reaches outside the workbook when it recalculates`;
  if (EXTERNAL_WORKBOOK_REFERENCE.test(body)) return "references another workbook";
  if (REMOTE_PATH_REFERENCE.test(body)) return "references a remote or network path";
  return null;
}

function formulaBody(value: XlsxFormulaInput, reference: string): string {
  const reason = unsafeFormulaReason(value.formula);
  if (reason) throw new Error(`Formula in ${reference} ${reason}; only calculations inside the workbook are written. Write the value instead.`);
  return value.formula.trim().replace(/^=/, "");
}

/**
 * Turn edited text back into a typed cell without guessing: canonical numbers
 * become numbers (so "02134" and "1,000" stay text), TRUE/FALSE become
 * booleans, and everything else is text. Text is never promoted to a formula:
 * an editor cannot tell a typed formula from imported text that happens to
 * start with "=", so a workbook saved from the editor contains no executable
 * cells it did not already contain as `{ formula }` input.
 */
export function cellInputFromText(text: string): XlsxCellInput {
  if (text === "") return null;
  if (text === "TRUE") return true;
  if (text === "FALSE") return false;
  if (CANONICAL_NUMBER.test(text)) {
    const value = Number(text);
    if (Number.isFinite(value)) return value;
  }
  return text;
}

function sanitizeSheetName(name: string | undefined, position: number, taken: Set<string>): string {
  const cleaned = (name ?? "")
    .replace(/[\u0000-\u001f\u007f[\]:*?/\\]/g, " ")
    .replace(/\s+/g, " ")
    .replace(/^'+|'+$/g, "")
    .trim()
    .slice(0, SHEET_NAME_MAX_CHARS)
    .trim();
  let candidate = cleaned || `Sheet${position}`;
  let suffix = 2;
  // Excel reserves "History" for its change-tracking sheet.
  while (taken.has(candidate.toLowerCase()) || candidate.toLowerCase() === "history") {
    const base = candidate.slice(0, SHEET_NAME_MAX_CHARS - String(suffix).length - 1);
    candidate = `${base}-${suffix}`;
    suffix += 1;
  }
  taken.add(candidate.toLowerCase());
  return candidate;
}

function cellXml(reference: string, value: XlsxCellInput, styleIndex: number): string {
  const style = styleIndex > 0 ? ` s="${styleIndex}"` : "";
  if (value === null || value === "") return "";
  if (typeof value === "number") {
    if (!Number.isFinite(value)) return `<c r="${reference}" t="e"${style}><v>#NUM!</v></c>`;
    return `<c r="${reference}"${style}><v>${value}</v></c>`;
  }
  if (typeof value === "boolean") return `<c r="${reference}" t="b"${style}><v>${value ? 1 : 0}</v></c>`;
  if (isFormulaInput(value)) return `<c r="${reference}"${style}><f>${escapeXml(formulaBody(value, reference))}</f></c>`;
  if (value.length > CELL_TEXT_MAX_CHARS) throw new Error(`Cell ${reference} holds ${value.length} characters of text; Excel allows ${CELL_TEXT_MAX_CHARS}. Shorten it or split it across cells.`);
  const preserve = /^\s|\s$|\n/.test(value) ? ' xml:space="preserve"' : "";
  return `<c r="${reference}" t="inlineStr"${style}><is><t${preserve}>${escapeXml(value)}</t></is></c>`;
}

function displayWidth(value: XlsxCellInput): number {
  if (value === null) return 0;
  if (isFormulaInput(value)) return 12;
  return String(value).split("\n").reduce((max, line) => Math.max(max, line.length), 0);
}

function worksheetXml(sheet: XlsxSheetInput): { xml: string; rows: number; columns: number } {
  const rows = sheet.rows;
  const columnCount = rows.reduce((max, row) => Math.max(max, row.length), 0);
  if (rows.length > EXCEL_MAX_ROWS) throw new Error(`Sheet "${sheet.name ?? ""}" has ${rows.length} rows; Excel allows ${EXCEL_MAX_ROWS}.`);
  if (columnCount > EXCEL_MAX_COLUMNS) throw new Error(`Sheet "${sheet.name ?? ""}" has ${columnCount} columns; Excel allows ${EXCEL_MAX_COLUMNS}.`);
  const header = sheet.header ?? true;
  const widths = Array.from({ length: columnCount }, () => 0);
  const rowXml: string[] = [];
  rows.forEach((row, rowIndex) => {
    const cells: string[] = [];
    row.forEach((value, columnIndexZero) => {
      widths[columnIndexZero] = Math.max(widths[columnIndexZero], displayWidth(value));
      const xml = cellXml(`${columnLetters(columnIndexZero + 1)}${rowIndex + 1}`, value, header && rowIndex === 0 ? 1 : 0);
      if (xml) cells.push(xml);
    });
    if (cells.length) rowXml.push(`<row r="${rowIndex + 1}">${cells.join("")}</row>`);
  });
  const dimension = rows.length && columnCount ? `A1:${columnLetters(columnCount)}${rows.length}` : "A1";
  const cols = columnCount
    ? `<cols>${widths.map((width, index) => `<col min="${index + 1}" max="${index + 1}" width="${Math.min(60, Math.max(8, width + 2))}" customWidth="1"/>`).join("")}</cols>`
    : "";
  const xml = `<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships"><dimension ref="${dimension}"/><sheetViews><sheetView workbookViewId="0"${header && rows.length > 1 ? `><pane ySplit="1" topLeftCell="A2" activePane="bottomLeft" state="frozen"/></sheetView>` : "/>"}</sheetViews><sheetFormatPr defaultRowHeight="15"/>${cols}<sheetData>${rowXml.join("")}</sheetData><pageMargins left="0.7" right="0.7" top="0.75" bottom="0.75" header="0.3" footer="0.3"/></worksheet>`;
  const size = utf8ByteLength(xml);
  if (size > MAX_ENTRY_UNCOMPRESSED_BYTES) {
    throw new Error(`Sheet ${JSON.stringify(sheet.name ?? "")} would be ${size} bytes of worksheet XML; the limit is ${MAX_ENTRY_UNCOMPRESSED_BYTES} bytes (roughly 30,000 text cells). Split the rows across sheets or files, shorten cell text, or write a CSV instead.`);
  }
  return { xml, rows: rows.length, columns: columnCount };
}

const STYLES_XML = `<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"><fonts count="2"><font><sz val="11"/><name val="Calibri"/></font><font><b/><sz val="11"/><name val="Calibri"/></font></fonts><fills count="2"><fill><patternFill patternType="none"/></fill><fill><patternFill patternType="gray125"/></fill></fills><borders count="1"><border><left/><right/><top/><bottom/><diagonal/></border></borders><cellStyleXfs count="1"><xf numFmtId="0" fontId="0" fillId="0" borderId="0"/></cellStyleXfs><cellXfs count="2"><xf numFmtId="0" fontId="0" fillId="0" borderId="0" xfId="0"/><xf numFmtId="0" fontId="1" fillId="0" borderId="0" xfId="0" applyFont="1"/></cellXfs><cellStyles count="1"><cellStyle name="Normal" xfId="0" builtinId="0"/></cellStyles></styleSheet>`;

export type XlsxWriteResult = {
  bytes: Uint8Array;
  sheets: Array<{ name: string; rows: number; columns: number }>;
};

export async function writeXlsxWorkbook(input: XlsxSheetInput[]): Promise<XlsxWriteResult> {
  if (input.length === 0) throw new Error("A workbook needs at least one sheet.");
  if (input.length > XLSX_WRITE_MAX_SHEETS) throw new Error(`A workbook may contain at most ${XLSX_WRITE_MAX_SHEETS} sheets.`);
  const totalCells = input.reduce((sum, sheet) => sum + sheet.rows.reduce((rowSum, row) => rowSum + row.length, 0), 0);
  if (totalCells > XLSX_WRITE_MAX_CELLS) throw new Error(`Workbook has ${totalCells} cells; spreadsheet_write accepts at most ${XLSX_WRITE_MAX_CELLS}. Split the data across files or write a CSV.`);

  const taken = new Set<string>();
  const sheets = input.map((sheet, index) => ({ ...sheet, name: sanitizeSheetName(sheet.name, index + 1, taken) }));
  const worksheets = sheets.map((sheet) => worksheetXml(sheet));

  const files: ZipFileInput[] = [
    {
      name: "[Content_Types].xml",
      data: utf8Bytes(`<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<Types xmlns="http://schemas.openxmlformats.org/package/2006/content-types"><Default Extension="rels" ContentType="application/vnd.openxmlformats-package.relationships+xml"/><Default Extension="xml" ContentType="application/xml"/><Override PartName="/xl/workbook.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml"/><Override PartName="/xl/styles.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.styles+xml"/>${sheets.map((_sheet, index) => `<Override PartName="/xl/worksheets/sheet${index + 1}.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml"/>`).join("")}</Types>`),
    },
    {
      name: "_rels/.rels",
      data: utf8Bytes(`<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships"><Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument" Target="xl/workbook.xml"/></Relationships>`),
    },
    {
      name: "xl/workbook.xml",
      data: utf8Bytes(`<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<workbook xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships"><bookViews><workbookView/></bookViews><sheets>${sheets.map((sheet, index) => `<sheet name="${escapeXml(sheet.name)}" sheetId="${index + 1}" r:id="rId${index + 1}"/>`).join("")}</sheets><calcPr fullCalcOnLoad="1"/></workbook>`),
    },
    {
      name: "xl/_rels/workbook.xml.rels",
      data: utf8Bytes(`<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">${sheets.map((_sheet, index) => `<Relationship Id="rId${index + 1}" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet" Target="worksheets/sheet${index + 1}.xml"/>`).join("")}<Relationship Id="rId${sheets.length + 1}" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/styles" Target="styles.xml"/></Relationships>`),
    },
    { name: "xl/styles.xml", data: utf8Bytes(STYLES_XML) },
    ...worksheets.map((worksheet, index) => ({ name: `xl/worksheets/sheet${index + 1}.xml`, data: utf8Bytes(worksheet.xml) })),
  ];

  return {
    bytes: await buildZip(files),
    sheets: sheets.map((sheet, index) => ({ name: sheet.name, rows: worksheets[index].rows, columns: worksheets[index].columns })),
  };
}