Skip to content

armgit / macro

Public Imported from GitHub
0

Macro is a unified workspace for teams: email, chat, docs, tasks, agents, calls, and CRM — @-linked together with shared AI memory.

44.5 KB
Raw Blame
import { pivotLayout } from '@macro-inc/spreadsheet/pivot-layout';
import {
  MAX_NOTE_LENGTH,
  MAX_SHEET_NOTES,
} from '@macro-inc/spreadsheet/sheet-rules';
import {
  displayStructuredReferences,
  resolveStructuredReferences,
  type WorkbookTable,
} from '@macro-inc/spreadsheet/structured-references';
import type { WorkbookSheetMetadata } from '@macro-inc/spreadsheet/workbook-metadata';
import {
  DEFAULT_COLUMN_WIDTH,
  formatCellAddress,
  MAX_COLUMN_WIDTH,
  MIN_COLUMN_WIDTH,
  parseCellAddress,
  SPREADSHEET_COLUMNS,
  SPREADSHEET_MAX_CELL_LENGTH,
  SPREADSHEET_MAX_COLUMNS,
  SPREADSHEET_MAX_ROWS,
  SPREADSHEET_ROWS,
  type SpreadsheetCell,
  type SpreadsheetCellStyle,
  type SpreadsheetCells,
} from './spreadsheet-document';
import {
  type WorkbookFileData,
  type WorkbookFileSheet,
  XLSX_MAX_CELLS,
  XLSX_MAX_SHEETS,
} from './workbook-file-types';
import type { XlsxArchive } from './xlsx-archive';
import { readCheckboxComplements } from './xlsx-checkboxes';
import {
  type DrawingImports,
  drawingImports,
  readSheetDrawings,
} from './xlsx-drawings';
import {
  expandSheetRanges,
  formulaFunctionNames,
  hasExternalReference,
  importSingleFunction,
  markImplicitIntersections,
  normalizeReferences,
  pivotTableArguments,
  stripFunctionPrefixes,
  translateFormula,
} from './xlsx-formula';
import {
  type Attributes,
  attributes,
  EXCEL_MAX_ROWS,
  parse,
  parseExcelAddress,
  rangeBounds,
  relationships,
  unescapeText,
} from './xlsx-parts';
import {
  PIVOT_RECORDS_WARNING,
  PIVOT_VALUES_WARNING,
  readSheetPivotTables,
  withoutRecords,
} from './xlsx-pivots';
import { createSheetRulesReader } from './xlsx-sheet-rules';
import {
  readXlsxStylesheet,
  readXlsxTheme,
  type XlsxStylesheet,
} from './xlsx-stylesheet';
import { readXlsxTables, type XlsxTable } from './xlsx-tables';

/** Elements of conditional formatting and data validation rules. */
const RULE_ELEMENTS = new Set([
  'conditionalFormatting',
  'cfRule',
  'cfvo',
  'color',
  'iconSet',
  'dataBar',
  'formula',
  'dataValidation',
  'formula1',
  'formula2',
  'f',
  'sqref',
  // An Excel 2010 rule's inline format.
  'dxf',
  'font',
  'b',
  'i',
  'strike',
  'u',
  'numFmt',
  'fill',
  'patternFill',
  'fgColor',
  'bgColor',
  'gradientFill',
  'stop',
  'border',
]);

const MAIN_NAMESPACES = new Set([
  'http://schemas.openxmlformats.org/spreadsheetml/2006/main',
  // Some generators omit the namespace entirely.
  '',
  // ISO/IEC 29500 Strict uses different URIs for the same vocabulary.
  'http://purl.oclc.org/ooxml/spreadsheetml/main',
]);
/** A sheet-qualified range, capturing its two corners. */
const MULTI_CELL_REFERENCE =
  /^(?:'(?:[^']|'')+'|[^'!:]+)!(\$?[A-Z]{0,3}\$?\d*):(\$?[A-Z]{0,3}\$?\d*)$/i;
const KNOWN_ERRORS = new Set([
  '#DIV/0!',
  '#N/A',
  '#NAME?',
  '#NULL!',
  '#NUM!',
  '#REF!',
  '#VALUE!',
]);
/** Functions that return dynamic arrays when an older writer omits metadata. */
const SPILL_FUNCTIONS = new Set([
  'CHOOSECOLS',
  'CHOOSEROWS',
  'DROP',
  'EXPAND',
  'FILTER',
  'HSTACK',
  'SEQUENCE',
  'SORT',
  'SORTBY',
  'TAKE',
  'TOCOL',
  'TOROW',
  'UNIQUE',
  'VSTACK',
  'WRAPCOLS',
  'WRAPROWS',
]);

/** OOXML escapes control characters in text as `_xHHHH_`. */
/** Sheet metadata Macro stores, at most; parts that keep export fidelity
 * give way first. */
const MAX_METADATA_LENGTH = 900_000;

function fitMetadata(metadata: WorkbookSheetMetadata, warnings: Set<string>) {
  const length = () => JSON.stringify(metadata).length;
  if (length() <= MAX_METADATA_LENGTH) return;
  // Charts keep their data and type without Excel's formatting.
  const charts = (metadata.drawings ?? [])
    .flatMap((drawing) => (drawing.type === 'chart' ? [drawing.chart] : []))
    .filter((chart) => chart.source)
    .sort((a, b) => b.source!.length - a.source!.length);
  for (const chart of charts) {
    delete chart.source;
    warnings.add(
      'Some charts keep only their data and type; their formatting is not exported.'
    );
    if (length() <= MAX_METADATA_LENGTH) return;
  }
  // Pivot tables are only kept for export; their values stay in cells.
  // The records Excel saved go first: Excel refreshes a table without them.
  const pivots = metadata.pivotTables ?? [];
  for (const index of pivots
    .map((_, index) => index)
    .sort(
      (a, b) =>
        (pivots[b].records?.length ?? 0) - (pivots[a].records?.length ?? 0)
    )) {
    if (!pivots[index].records || length() <= MAX_METADATA_LENGTH) break;
    pivots[index] = withoutRecords(pivots[index]);
    warnings.add(PIVOT_RECORDS_WARNING);
  }
  while (metadata.pivotTables?.length && length() > MAX_METADATA_LENGTH) {
    metadata.pivotTables.pop();
    warnings.add(PIVOT_VALUES_WARNING);
  }
  if (metadata.pivotTables && !metadata.pivotTables.length)
    delete metadata.pivotTables;
  while (metadata.drawings?.length && length() > MAX_METADATA_LENGTH) {
    metadata.drawings.pop();
    warnings.add('Some images and charts are not imported.');
  }
  if (metadata.drawings && !metadata.drawings.length) delete metadata.drawings;
}

/**
 * A sheet's notes by cell address: legacy Excel comments, and threaded
 * comments with their replies, which Excel also saves as legacy comments
 * with placeholder text.
 */
function readNotes(
  context: WorkbookContext,
  entry: SheetEntry
): Record<string, string> | undefined {
  const notes: Record<string, string> = {};
  let truncated = false;
  const add = (ref: string | undefined, note: string) => {
    const position = parseCellAddress(ref ?? '');
    const text = note.replace(/\r\n?/g, '\n').trim();
    if (!position || position.row >= SPREADSHEET_MAX_ROWS || !text) return;
    if (Object.keys(notes).length >= MAX_SHEET_NOTES) {
      truncated = true;
      return;
    }
    if (text.length > MAX_NOTE_LENGTH) truncated = true;
    notes[formatCellAddress(position.row, position.column)] = text.slice(
      0,
      MAX_NOTE_LENGTH
    );
  };
  const relations = [
    ...relationships(context.archive, entry.path).values(),
  ].filter((relation) => !relation.external);
  const threaded = new Set<string>();
  for (const relation of relations) {
    if (relation.type !== 'threadedComment') continue;
    const bytes = context.archive.read(relation.target);
    if (!bytes) continue;
    const threads = new Map<string, { ref: string; texts: string[] }>();
    let current: { ref: string; texts: string[] } | undefined;
    let reading = false;
    parse(bytes, relation.target, (parser) => {
      parser.on('opentag', (node) => {
        const value = attributes(node);
        if (node.local === 'threadedComment') {
          const parent = value.parentId && threads.get(value.parentId);
          current = parent || { ref: value.ref ?? '', texts: [] };
          current.texts.push('');
          if (!parent && value.id) threads.set(value.id, current);
        } else if (node.local === 'text' && current) reading = true;
      });
      parser.on('text', (chunk) => {
        if (reading && current)
          current.texts[current.texts.length - 1] += chunk;
      });
      parser.on('closetag', (node) => {
        if (node.local === 'text') reading = false;
        else if (node.local === 'threadedComment') current = undefined;
      });
    });
    for (const { ref, texts } of threads.values()) {
      add(
        ref,
        texts
          .map((text) => text.trim())
          .filter(Boolean)
          .join('\n\n')
      );
      threaded.add(ref);
    }
    if (threads.size)
      context.warnings.add(
        'Threaded comments are imported as notes; replies are kept in the note text.'
      );
  }
  for (const relation of relations) {
    if (relation.type !== 'comments') continue;
    const bytes = context.archive.read(relation.target);
    if (!bytes) continue;
    let ref: string | undefined;
    let note = '';
    let inText = false;
    let inPhonetic = false;
    parse(bytes, relation.target, (parser) => {
      parser.on('opentag', (node) => {
        if (node.local === 'comment') {
          ref = attributes(node).ref;
          note = '';
        } else if (node.local === 'text') inText = true;
        else if (node.local === 'rPh') inPhonetic = true;
      });
      parser.on('text', (chunk) => {
        if (inText && !inPhonetic && ref !== undefined) note += chunk;
      });
      parser.on('closetag', (node) => {
        if (node.local === 'rPh') inPhonetic = false;
        else if (node.local === 'text') inText = false;
        else if (node.local === 'comment') {
          if (ref && !threaded.has(ref)) add(ref, unescapeText(note));
          ref = undefined;
        }
      });
    });
  }
  if (truncated)
    context.warnings.add(
      `Notes are limited to ${MAX_SHEET_NOTES} per sheet and ${MAX_NOTE_LENGTH} characters each.`
    );
  return Object.keys(notes).length ? notes : undefined;
}

/** Text that the cell parser would otherwise read as a number, date or formula. */
function needsLiteralPrefix(text: string) {
  if (!/^\p{L}/u.test(text)) return true;
  if (/^\s*(?:true|false)\s*$/i.test(text)) return true;
  // Excel and IronCalc read "Jan 2024" or "Mar-24" as dates.
  return /^(?:jan|feb|mar|apr|may|jun|jul|aug|sep|oct|nov|dec)[a-z]*\.?[\s\-/,]*\d/i.test(
    text
  );
}

function isDateFormat(code: string) {
  // Keep elapsed-time markers such as [h]; drop colors, locales and conditions.
  const tokens = code
    .replace(/"[^"]*"|\\.|\[(?![hms]+\])[^\]]*\]/gi, '')
    .split(';')[0];
  return /[dy]/i.test(tokens) || (/m/i.test(tokens) && !/[hs]/i.test(tokens));
}

/** Any valid Excel address, including rows Macro cannot hold. */
type SheetEntry = {
  name: string;
  path: string;
  hidden: boolean;
  kind: string;
};

type WorkbookContext = {
  archive: XlsxArchive;
  theme: (string | undefined)[];
  drawings: DrawingImports;
  sharedStrings: { text: string; rich: boolean }[];
  styles: XlsxStylesheet;
  date1904: boolean;
  dynamicArrays: boolean;
  tables: WorkbookTable[];
  /** The table parts, with the tables Macro keeps. */
  importedTables: XlsxTable[];
  /** Lowercase names of the tables Macro keeps. */
  keptTables: ReadonlySet<string>;
  sheetNames: string[];
  /** Lowercase names defined as multi-cell references. */
  rangeNames: ReadonlySet<string>;
  warnings: Set<string>;
  unsupportedFunctions: Set<string>;
  supportedFunctions?: ReadonlySet<string>;
  /** GETPIVOTDATA formulas, kept only if their pivot table is. */
  pivotLookups: {
    sheet: string;
    address: string;
    formula: string;
    literal: string;
  }[];
};

type PendingShared = {
  row: number;
  column: number;
  si: string;
  style: SpreadsheetCellStyle;
};

/**
 * A cell formula as Macro stores it, or undefined when a table reference
 * cannot be converted. `intersect` applies the implicit intersection Excel
 * uses for formulas saved without dynamic-array or array evaluation.
 */
function importFormula(
  source: string,
  context: WorkbookContext,
  sheet: string,
  row: number,
  column: number,
  intersect: boolean
): string | undefined {
  const stripped = normalizeReferences(
    stripFunctionPrefixes(importSingleFunction(source))
  );
  // References to tables Macro keeps stay, shown as Excel shows them.
  const resolved = resolveStructuredReferences(
    stripped,
    context.tables,
    sheet,
    row,
    column,
    { resolves: (table) => !context.keptTables.has(table.name.toLowerCase()) }
  );
  if (resolved === undefined) return;
  if (resolved !== stripped)
    context.warnings.add(
      'Excel tables without a header row, or beyond Macro’s sheet size or table limit, are imported as ordinary cells; formulas refer to their cell ranges.'
    );
  const shown = displayStructuredReferences(
    resolved,
    context.tables,
    sheet,
    row,
    column
  );
  let formula = expandSheetRanges(shown, context.sheetNames);
  if (formula !== shown)
    context.warnings.add(
      'References spanning several sheets are expanded to list each sheet.'
    );
  if (intersect)
    formula = markImplicitIntersections(formula, context.rangeNames);
  if (context.supportedFunctions)
    for (const name of formulaFunctionNames(formula))
      if (!context.supportedFunctions.has(name))
        context.unsupportedFunctions.add(name);
  return formula;
}

/** Read one worksheet part with a single streaming pass. */
function readWorksheet(
  entry: SheetEntry,
  context: WorkbookContext
): WorkbookFileSheet {
  const { styles, warnings, sharedStrings } = context;
  const bytes = context.archive.read(entry.path);
  if (!bytes) throw new Error(`The sheet “${entry.name}” is missing.`);
  const cells: SpreadsheetCells = {};
  const metadata: WorkbookSheetMetadata = {};
  const rowHeights: Record<number, number> = {};
  const hiddenRows: number[] = [];
  const columns: {
    min: number;
    max: number;
    width?: number;
    hidden: boolean;
  }[] = [];
  const merges: string[] = [];
  const shared = new Map<
    string,
    { formula: string; row: number; column: number }
  >();
  const pendingShared: PendingShared[] = [];
  const skipped = new Set<string>();
  const dataTableRanges: ReturnType<typeof rangeBounds>[] = [];
  const arrayFormulas: Record<string, string> = {};
  let maxRow = -1;
  let maxColumn = -1;
  let defaultWidth = 64;
  let defaultHeight = 15;
  let droppedRows = false;

  let rowIndex = -1;
  let columnIndex = -1;
  let rowStyle: { hidden: boolean; height?: number } | undefined;
  let cell:
    | {
        row: number;
        column: number;
        style: number;
        type: string;
        dynamic: boolean;
        formula?: Attributes;
        formulaText: string;
        value: string;
        inline: string;
      }
    | undefined;
  let text: 'formula' | 'value' | 'inline' | undefined;
  let inInline = false;
  let inPhonetic = false;
  let inSheetData = false;
  let elements = 0;
  const rules = createSheetRulesReader({
    styles,
    warnings,
    formula: (source, row, column) =>
      importFormula(source, context, entry.name, row, column, false),
  });

  const place = (row: number, column: number, placed: SpreadsheetCell) => {
    if (row >= SPREADSHEET_MAX_ROWS) {
      if (placed.value)
        throw new Error(
          `“${entry.name}” has data beyond row ${SPREADSHEET_MAX_ROWS.toLocaleString('en-US')}, the most Macro supports. No sheets were imported.`
        );
      droppedRows = true;
      return;
    }
    const long = placed.value.length > SPREADSHEET_MAX_CELL_LENGTH;
    if (long && placed.value.startsWith('='))
      throw new Error(
        `Cell ${entry.name}!${formatCellAddress(row, column)} exceeds 10,000 characters. No sheets were imported.`
      );
    // Excel allows 32,767 characters; keep the workbook and shorten long notes.
    if (long)
      warnings.add(
        'Text longer than 10,000 characters was shortened to fit Macro cells.'
      );
    cells[formatCellAddress(row, column)] = long
      ? { ...placed, value: placed.value.slice(0, SPREADSHEET_MAX_CELL_LENGTH) }
      : placed;
    maxRow = Math.max(maxRow, row);
    maxColumn = Math.max(maxColumn, column);
  };

  const literalText = (raw: string, style: SpreadsheetCellStyle) =>
    style.format === 'text' || !needsLiteralPrefix(raw) ? raw : `'${raw}`;

  const finishCell = () => {
    if (!cell) return;
    const current = cell;
    cell = undefined;
    const address = formatCellAddress(current.row, current.column);
    const style: SpreadsheetCellStyle = { ...styles.cellStyle(current.style) };
    if (skipped.has(address)) {
      // Array results and merged areas keep formatting, not cached values.
      if (Object.keys(style).length)
        place(current.row, current.column, { value: '', ...style });
      return;
    }
    let value = '';
    let calculated = false;
    const formula = current.formula;
    const cached = (): string => {
      if (current.type === 's')
        return sharedStrings[Number(current.value)]?.text ?? '';
      if (current.type === 'str' || current.type === 'inlineStr')
        return current.type === 'inlineStr' ? current.inline : current.value;
      return current.value;
    };
    const literal = (): string => {
      const raw = cached();
      switch (current.type) {
        case 's':
        case 'str':
        case 'inlineStr': {
          if (
            current.type === 's' &&
            sharedStrings[Number(current.value)]?.rich
          )
            warnings.add(
              'Rich text is imported as plain text with the cell’s formatting.'
            );
          return raw === '' ? '' : literalText(raw, style);
        }
        case 'b':
          return raw === '1' || raw.toLowerCase() === 'true' ? 'TRUE' : 'FALSE';
        case 'e':
          if (KNOWN_ERRORS.has(raw)) return raw;
          warnings.add('Some newer Excel errors are imported as text.');
          return `'${raw}`;
        case 'd': {
          const time = Date.parse(raw);
          if (!Number.isFinite(time)) return raw ? `'${raw}` : '';
          return String(time / 86_400_000 + 25_569);
        }
        default: {
          if (raw.trim() === '') return '';
          const number = Number(raw);
          if (!Number.isFinite(number)) return `'${raw}`;
          if (
            context.date1904 &&
            isDateFormat(styles.numberFormat(current.style))
          )
            return String(number + 1462);
          // Excel writes 17 significant digits (32475.200000000001); the
          // shortest text for the same double is what Excel itself displays.
          return String(number);
        }
      }
    };
    if (formula && formula.t === 'dataTable') {
      dataTableRanges.push(rangeBounds(formula.ref ?? address));
      warnings.add(
        'What-if data tables are imported as their last calculated values.'
      );
      value = literal();
    } else if (
      dataTableRanges.some(
        (range) =>
          range &&
          current.row >= range.top &&
          current.row <= range.bottom &&
          current.column >= range.left &&
          current.column <= range.right
      )
    ) {
      value = literal();
    } else if (formula) {
      let source = current.formulaText;
      if (formula.t === 'shared' && formula.si !== undefined) {
        if (source)
          shared.set(formula.si, {
            formula: source,
            row: current.row,
            column: current.column,
          });
        else {
          const master = shared.get(formula.si);
          if (!master) {
            pendingShared.push({
              row: current.row,
              column: current.column,
              si: formula.si,
              style,
            });
            return;
          }
          source = translateFormula(
            master.formula,
            current.row - master.row,
            current.column - master.column
          );
        }
      }
      if (formula.t === 'array' && source) {
        const range = rangeBounds(formula.ref ?? address);
        const leading = /^\s*(?:_xlfn\.)?(?:_xlws\.)?([A-Z][A-Z0-9.]*)\s*\(/i
          .exec(source)?.[1]
          .toUpperCase();
        const dynamic =
          current.dynamic ||
          (leading !== undefined && SPILL_FUNCTIONS.has(leading));
        if (
          range &&
          (range.top !== current.row || range.left !== current.column)
        )
          warnings.add(
            'Some array formulas with invalid ranges were imported as single-cell formulas.'
          );
        else if (range) {
          for (let row = range.top; row <= range.bottom; row++)
            for (let column = range.left; column <= range.right; column++)
              if (row !== current.row || column !== current.column)
                skipped.add(formatCellAddress(row, column));
          if (dynamic)
            warnings.add(
              'Dynamic array formulas are recalculated by Macro; cached spill values are discarded and their cell formatting is retained.'
            );
          else {
            arrayFormulas[address] =
              range.top === range.bottom && range.left === range.right
                ? address
                : `${address}:${formatCellAddress(range.bottom, range.right)}`;
          }
        }
      }
      if (!source) value = literal();
      else if (hasExternalReference(source)) {
        warnings.add(
          'Formulas that reference other workbooks are imported as their last calculated values.'
        );
        value = literal();
      } else {
        const resolved = importFormula(
          source,
          context,
          entry.name,
          current.row,
          current.column,
          formula.t !== 'array' && !current.dynamic
        );
        if (resolved === undefined) {
          warnings.add(
            'Some table references could not be converted; those formulas show their last calculated values.'
          );
          value = literal();
        } else {
          value = `=${resolved}`;
          calculated = true;
          if (
            formula.t !== 'array' &&
            formulaFunctionNames(resolved).includes('GETPIVOTDATA')
          )
            context.pivotLookups.push({
              sheet: entry.name,
              address,
              formula: resolved,
              literal: literal(),
            });
        }
      }
    } else value = literal();
    // A text format would show a formula as its source, whatever its result.
    if (
      style.format === 'text' &&
      value &&
      !/^'/.test(value) &&
      (calculated ||
        (current.type !== 's' &&
          current.type !== 'str' &&
          current.type !== 'inlineStr'))
    ) {
      delete style.format;
      warnings.add(
        'Text number formats on numeric, boolean, date or formula cells are reset to preserve the original value types.'
      );
    }
    if (value || Object.keys(style).length)
      place(current.row, current.column, { value, ...style });
  };

  parse(bytes, entry.path, (parser) => {
    parser.on('opentag', (node) => {
      if (
        RULE_ELEMENTS.has(node.local) &&
        rules.open(node.local, node.uri, attributes(node))
      )
        return;
      if (!MAIN_NAMESPACES.has(node.uri)) return;
      const value = attributes(node);
      switch (node.local) {
        case 'sheetData':
          inSheetData = true;
          return;
        case 'sheetFormatPr': {
          const width = Number(value.defaultColWidth);
          if (Number.isFinite(width) && width > 0)
            defaultWidth = Math.round(width * 7 + 5);
          const height = Number(value.defaultRowHeight);
          if (Number.isFinite(height) && height > 0) defaultHeight = height;
          return;
        }
        case 'sheetView':
          if (value.showGridLines === '0' || value.showGridLines === 'false')
            metadata.gridlines = false;
          return;
        case 'pane':
          if (value.state === 'frozen' || value.state === 'frozenSplit') {
            const rows = Math.min(
              SPREADSHEET_MAX_ROWS,
              Math.max(0, Math.round(Number(value.ySplit ?? 0)))
            );
            const columnsFrozen = Math.min(
              SPREADSHEET_MAX_COLUMNS,
              Math.max(0, Math.round(Number(value.xSplit ?? 0)))
            );
            if (
              (rows || columnsFrozen) &&
              Number.isFinite(rows) &&
              Number.isFinite(columnsFrozen)
            )
              metadata.freeze = { rows, columns: columnsFrozen };
          }
          return;
        case 'tabColor':
          if (/^[0-9a-f]{8}$/i.test(value.rgb ?? ''))
            metadata.tabColor = `#${value.rgb.slice(2).toUpperCase()}`;
          return;
        case 'col': {
          const min = Number(value.min);
          const max = Number(value.max);
          if (
            !Number.isInteger(min) ||
            !Number.isInteger(max) ||
            min < 1 ||
            min > max ||
            max > SPREADSHEET_MAX_COLUMNS
          )
            throw new Error(`Invalid column in ${entry.path}.`);
          const width = Number(value.width);
          columns.push({
            min: min - 1,
            max: max - 1,
            width: Number.isFinite(width) && width >= 0 ? width : undefined,
            hidden: value.hidden === '1' || value.hidden === 'true',
          });
          return;
        }
        case 'row': {
          finishCell();
          const row =
            value.r === undefined ? rowIndex + 1 : Number(value.r) - 1;
          if (!Number.isInteger(row) || row < 0 || row >= EXCEL_MAX_ROWS)
            throw new Error(`Invalid row in ${entry.path}.`);
          rowIndex = row;
          columnIndex = -1;
          const height = Number(value.ht);
          rowStyle = {
            hidden: value.hidden === '1' || value.hidden === 'true',
            height:
              (value.customHeight === '1' || value.customHeight === 'true') &&
              Number.isFinite(height) &&
              height >= 0 &&
              height <= 409.5
                ? height
                : undefined,
          };
          if (row < SPREADSHEET_MAX_ROWS) {
            if (rowStyle.hidden) hiddenRows.push(row);
            if (rowStyle.height !== undefined)
              rowHeights[row] = rowStyle.height;
          }
          return;
        }
        case 'c': {
          finishCell();
          if (!inSheetData) return;
          let row = rowIndex;
          let column = columnIndex + 1;
          if (value.r) {
            const position = parseExcelAddress(value.r);
            if (!position) {
              // Cells past Excel's own limits cannot be addressed; skip them.
              warnings.add('Cells outside Excel’s grid limits were skipped.');
              return;
            }
            ({ row, column } = position);
          }
          if (row < 0) row = 0;
          rowIndex = row;
          columnIndex = column;
          if (++elements > XLSX_MAX_CELLS * 2)
            throw new Error(
              `Too many cells in ${entry.name}. No sheets were imported.`
            );
          cell = {
            row,
            column,
            style: Number(value.s ?? 0) || 0,
            type: value.t ?? 'n',
            dynamic: value.cm !== undefined && context.dynamicArrays,
            formulaText: '',
            value: '',
            inline: '',
          };
          if (value.vm !== undefined)
            warnings.add(
              'Linked data types and pictures in cells are imported as their displayed values.'
            );
          return;
        }
        case 'f':
          if (cell) {
            cell.formula = value;
            text = 'formula';
          }
          return;
        case 'v':
          if (cell) text = 'value';
          return;
        case 'is':
          inInline = !!cell;
          return;
        case 't':
          if (inInline && !inPhonetic) text = 'inline';
          return;
        case 'rPh':
          inPhonetic = true;
          return;
        case 'mergeCell': {
          const range = rangeBounds(value.ref ?? '');
          if (!range) {
            warnings.add('Invalid merged ranges were ignored.');
            return;
          }
          if (range.top >= SPREADSHEET_MAX_ROWS) return;
          const bottom = Math.min(range.bottom, SPREADSHEET_MAX_ROWS - 1);
          if (range.top === bottom && range.left === range.right) return;
          merges.push(
            `${formatCellAddress(range.top, range.left)}:${formatCellAddress(bottom, range.right)}`
          );
          return;
        }
        case 'autoFilter': {
          const range = rangeBounds(value.ref ?? '');
          if (range && range.bottom < SPREADSHEET_MAX_ROWS)
            metadata.autoFilter = `${formatCellAddress(range.top, range.left)}:${formatCellAddress(range.bottom, range.right)}`;
          return;
        }
        case 'hyperlink':
          warnings.add(
            'Hyperlinks are imported as display text; link targets are not retained.'
          );
          return;
        case 'sheetProtection':
          warnings.add(
            'Excel sheet protection is not imported; Macro sharing controls determine edit access.'
          );
          return;
      }
    });
    parser.on('text', (chunk) => {
      rules.text(chunk);
      if (!cell || !text || inPhonetic) return;
      if (text === 'formula') cell.formulaText += chunk;
      else if (text === 'value') cell.value += chunk;
      else cell.inline += unescapeText(chunk);
    });
    parser.on('closetag', (node) => {
      if (RULE_ELEMENTS.has(node.local)) rules.close(node.local, node.uri);
      if (!MAIN_NAMESPACES.has(node.uri)) return;
      if (node.local === 'rPh') inPhonetic = false;
      else if (node.local === 'f' || node.local === 'v' || node.local === 't')
        text = undefined;
      else if (node.local === 'is') inInline = false;
      else if (node.local === 'c') finishCell();
      else if (node.local === 'sheetData') {
        finishCell();
        inSheetData = false;
      }
    });
  });
  for (const pending of pendingShared) {
    const master = shared.get(pending.si);
    const address = formatCellAddress(pending.row, pending.column);
    const value = master
      ? `=${
          importFormula(
            translateFormula(
              master.formula,
              pending.row - master.row,
              pending.column - master.column
            ),
            context,
            entry.name,
            pending.row,
            pending.column,
            true
          ) ?? '#REF!'
        }`
      : '';
    if (!master)
      warnings.add('Some shared formulas were missing their source formula.');
    if (!cells[address] && (value || Object.keys(pending.style).length))
      place(pending.row, pending.column, { value, ...pending.style });
  }
  // Only a merge's top-left cell keeps a value; covered cells keep borders.
  for (const merge of merges) {
    const range = rangeBounds(merge)!;
    const clear = (address: string) => {
      const covered = cells[address];
      if (!covered?.value) return;
      const { value: _value, ...style } = covered;
      if (Object.keys(style).length) cells[address] = { value: '', ...style };
      else delete cells[address];
    };
    const area =
      (range.bottom - range.top + 1) * (range.right - range.left + 1);
    if (area <= 10_000) {
      for (let r = range.top; r <= range.bottom; r++)
        for (let c = range.left; c <= range.right; c++)
          if (r !== range.top || c !== range.left)
            clear(formatCellAddress(r, c));
    } else
      for (const address of Object.keys(cells)) {
        const position = parseCellAddress(address)!;
        if (
          position.row >= range.top &&
          position.row <= range.bottom &&
          position.column >= range.left &&
          position.column <= range.right &&
          (position.row !== range.top || position.column !== range.left)
        )
          clear(address);
      }
  }
  if (droppedRows)
    warnings.add(
      `Formatting below row ${SPREADSHEET_MAX_ROWS.toLocaleString('en-US')} is not imported.`
    );
  const rowCount = Math.max(SPREADSHEET_ROWS, maxRow + 1);
  // Hidden or styled columns often extend to XFD; only the used area matters.
  let columnCount = Math.max(SPREADSHEET_COLUMNS, maxColumn + 1);
  for (const merge of merges) {
    const range = rangeBounds(merge);
    if (range) columnCount = Math.max(columnCount, range.right + 1);
  }
  const columnWidths: Record<number, number> = {};
  const hiddenColumns: number[] = [];
  for (let column = 0; column < columnCount; column++) {
    const definition = columns.findLast(
      (item) => column >= item.min && column <= item.max
    );
    const pixels = Math.max(
      MIN_COLUMN_WIDTH,
      Math.min(
        MAX_COLUMN_WIDTH,
        definition?.width !== undefined
          ? Math.round(
              definition.width < 1
                ? definition.width * 12
                : definition.width * 7 + 5
            )
          : defaultWidth
      )
    );
    // Excel's default is 64px; Macro's own default needs no stored width.
    if (pixels !== DEFAULT_COLUMN_WIDTH) columnWidths[column] = pixels;
    if (definition?.hidden) hiddenColumns.push(column);
  }
  if (hiddenColumns.length >= columnCount) hiddenColumns.length = 0;
  if (merges.length) metadata.merges = merges;
  const { conditionalFormats, validations } = rules.finish();
  if (conditionalFormats.length)
    metadata.conditionalFormats = conditionalFormats;
  if (validations.length) metadata.validations = validations;
  const notes = readNotes(context, entry);
  if (notes) metadata.notes = notes;
  const columnWidth = (column: number) =>
    columnWidths[column] ?? DEFAULT_COLUMN_WIDTH;
  const rowHeight = (row: number) => (rowHeights[row] ?? defaultHeight) / 0.75;
  const drawings = readSheetDrawings({
    archive: context.archive,
    sheetPath: entry.path,
    theme: context.theme,
    warnings,
    state: context.drawings,
    locate: (x, y) => {
      let column = 0;
      while (x >= columnWidth(column) && column < SPREADSHEET_MAX_COLUMNS - 1)
        x -= columnWidth(column++);
      let row = 0;
      while (y >= rowHeight(row) && row < SPREADSHEET_MAX_ROWS - 1)
        y -= rowHeight(row++);
      return { row, column, x: Math.round(x), y: Math.round(y) };
    },
    extent: ({ from, to, width = 0, height = 0 }) => {
      if (!to) return { cx: width * 9525, cy: height * 9525 };
      let horizontal = to.x - from.x;
      for (let column = from.column; column < to.column; column++)
        horizontal += columnWidth(column);
      let vertical = to.y - from.y;
      for (let row = from.row; row < to.row; row++) vertical += rowHeight(row);
      return {
        cx: Math.max(0, horizontal) * 9525,
        cy: Math.max(0, vertical) * 9525,
      };
    },
  });
  if (drawings.length) metadata.drawings = drawings;
  const pivotTables = readSheetPivotTables({
    archive: context.archive,
    sheetPath: entry.path,
    sheetName: entry.name,
    tables: context.tables,
    customFormat: context.styles.customFormat,
    differential: context.styles.differentialStyle,
    warnings,
  });
  if (pivotTables.length) metadata.pivotTables = pivotTables;
  const sheetTables = context.importedTables.flatMap(({ layout, table }) =>
    table && layout.sheet === entry.name ? [table] : []
  );
  if (sheetTables.length) metadata.tables = sheetTables;
  fitMetadata(metadata, warnings);
  if (hiddenRows.length && hiddenRows.length < rowCount)
    metadata.hiddenRows = hiddenRows.filter((row) => row < rowCount);
  if (hiddenColumns.length) metadata.hiddenColumns = hiddenColumns;
  // Rows at the sheet's default height already look the same in Macro.
  const heights = Object.entries(rowHeights).filter(
    ([, height]) => Math.abs(height - defaultHeight) > 0.01
  );
  if (heights.length) metadata.rowHeights = Object.fromEntries(heights);
  if (Object.keys(arrayFormulas).length) metadata.arrayFormulas = arrayFormulas;
  if (entry.hidden) {
    metadata.hidden = true;
    warnings.add(
      'Hidden sheets are shown in Macro; their visibility is retained in Excel downloads.'
    );
  }
  if (styles.defaultFont) metadata.defaultFont = styles.defaultFont;
  if (merges.length)
    warnings.add(
      'Merged ranges are shown as individual cells in Macro and restored on Excel export when their covered cells remain empty.'
    );
  if (metadata.freeze)
    warnings.add(
      'Frozen panes are retained for Excel export; Macro uses its own scrolling view.'
    );
  if (metadata.autoFilter)
    warnings.add(
      'Excel filters are retained for export; Macro displays all rows.'
    );
  // Tall rows show Excel's bottom alignment; ordinary rows look the same either
  // way. Measure against Excel's standard 15pt row so a round trip agrees.
  if (metadata.rowHeights)
    for (const [address, value] of Object.entries(cells)) {
      if (value.verticalAlign) continue;
      const row = parseCellAddress(address)?.row;
      const height = row === undefined ? undefined : metadata.rowHeights[row];
      if (height !== undefined && height > 22.5) value.verticalAlign = 'bottom';
    }
  return {
    name: entry.name,
    cells,
    rowCount,
    columnCount,
    columnWidths,
    metadata,
  };
}

function readSharedStrings(archive: XlsxArchive, path: string | undefined) {
  const strings: { text: string; rich: boolean }[] = [];
  const bytes = path ? archive.read(path) : undefined;
  if (!bytes) return strings;
  let current: { text: string; rich: boolean } | undefined;
  let inText = false;
  let inPhonetic = false;
  parse(bytes, path!, (parser) => {
    parser.on('opentag', (node) => {
      if (node.local === 'si') current = { text: '', rich: false };
      else if (node.local === 'rPh') inPhonetic = true;
      else if (node.local === 'r' && current) current.rich = true;
      else if (node.local === 't') inText = !inPhonetic;
    });
    parser.on('text', (text) => {
      if (current && inText) current.text += text;
    });
    parser.on('closetag', (node) => {
      if (node.local === 't') inText = false;
      else if (node.local === 'rPh') inPhonetic = false;
      else if (node.local === 'si' && current) {
        strings.push({ text: unescapeText(current.text), rich: current.rich });
        current = undefined;
      }
    });
  });
  return strings;
}

/** Decode a validated XLSX package into Macro sheets, without ExcelJS. */
/**
 * GETPIVOTDATA reads the cells of the pivot tables Macro keeps. A lookup of
 * any other table, or of one it cannot tell, keeps its last value.
 */
function keepPivotLookups(
  sheets: WorkbookFileSheet[],
  context: WorkbookContext
) {
  for (const lookup of context.pivotLookups) {
    const sheet = sheets.find((entry) => entry.name === lookup.sheet);
    const cell = sheet?.cells[lookup.address];
    if (!sheet || !cell) continue;
    const targets = pivotTableArguments(lookup.formula);
    const kept =
      targets.length > 0 &&
      targets.every((target) => {
        if (!target) return false;
        const holder =
          target.sheet === undefined
            ? sheet
            : sheets.find(
                (entry) =>
                  entry.name.toLowerCase() === target.sheet?.toLowerCase()
              );
        return !!holder?.metadata?.pivotTables?.some((pivot) => {
          const range = rangeBounds(pivot.location);
          return (
            !!range &&
            target.row >= range.top &&
            target.row <= range.bottom &&
            target.column >= range.left &&
            target.column <= range.right &&
            pivotLayout(pivot) !== undefined
          );
        });
      });
    if (kept) continue;
    context.warnings.add(
      'Pivot table lookups (GETPIVOTDATA) of pivot tables Macro could not keep are imported as their last calculated values.'
    );
    const { value: _formula, ...style } = cell;
    if (lookup.literal || Object.keys(style).length)
      sheet.cells[lookup.address] = { ...style, value: lookup.literal };
    else delete sheet.cells[lookup.address];
  }
}

export function readXlsxWorkbook(
  archive: XlsxArchive,
  options: { supportedFunctions?: ReadonlySet<string> } = {}
): WorkbookFileData {
  const warnings = new Set<string>();
  const workbookPath = 'xl/workbook.xml';
  const workbookBytes = archive.read(workbookPath)!;
  const rels = relationships(archive, workbookPath);
  const sheets: SheetEntry[] = [];
  const names: {
    name: string;
    formula: string;
    sheet?: number;
    hidden: boolean;
  }[] = [];
  let date1904 = false;
  let currentName: (typeof names)[number] | undefined;
  parse(workbookBytes, workbookPath, (parser) => {
    parser.on('opentag', (node) => {
      const value = attributes(node);
      if (node.local === 'sheet') {
        const relation = rels.get(value.id ?? '');
        sheets.push({
          name: value.name ?? '',
          path: relation?.target ?? '',
          hidden: value.state === 'hidden' || value.state === 'veryHidden',
          kind: relation?.type ?? '',
        });
      } else if (node.local === 'workbookPr')
        date1904 = value.date1904 === '1' || value.date1904 === 'true';
      else if (node.local === 'definedName')
        currentName = {
          name: value.name ?? '',
          formula: '',
          hidden: value.hidden === '1',
          ...(value.localSheetId !== undefined && {
            sheet: Number(value.localSheetId),
          }),
        };
    });
    parser.on('text', (text) => {
      if (currentName) currentName.formula += text;
    });
    parser.on('closetag', (node) => {
      if (node.local === 'definedName' && currentName) {
        names.push(currentName);
        currentName = undefined;
      }
    });
  });
  const worksheets = sheets.filter(
    (sheet) => sheet.kind === 'worksheet' && sheet.path
  );
  if (worksheets.length < sheets.length)
    warnings.add('Chart sheets and other non-grid sheets are not imported.');
  if (!worksheets.length)
    throw new Error('This workbook has no worksheets to import.');
  if (worksheets.length > XLSX_MAX_SHEETS)
    throw new Error(
      `Import up to ${XLSX_MAX_SHEETS} sheets at a time. No sheets were imported.`
    );
  const sharedPath = [...rels.values()].find(
    (item) => item.type === 'sharedStrings'
  )?.target;
  const stylesPath = [...rels.values()].find(
    (item) => item.type === 'styles'
  )?.target;
  const themePath = [...rels.values()].find(
    (item) => item.type === 'theme'
  )?.target;
  const checkboxPath = [...rels.values()].find(
    (item) => item.type === 'FeaturePropertyBag'
  )?.target;
  const checkboxBytes = checkboxPath ? archive.read(checkboxPath) : undefined;
  const metadataBytes = archive.read('xl/metadata.xml');
  const theme = readXlsxTheme(
    themePath
      ? { [themePath]: archive.read(themePath) ?? new Uint8Array() }
      : {},
    themePath
  );
  const stylesBytes = stylesPath ? archive.read(stylesPath) : undefined;
  const importedTables = readXlsxTables(archive, worksheets, warnings);
  const tables = importedTables.map(({ layout }) => layout);
  const rangeNames = new Set<string>();
  for (const entry of names) {
    const formula = resolveStructuredReferences(
      stripFunctionPrefixes(entry.formula),
      tables,
      worksheets[0].name,
      0,
      0
    );
    const range = formula && MULTI_CELL_REFERENCE.exec(formula);
    if (range && range[1].replaceAll('$', '') !== range[2].replaceAll('$', ''))
      rangeNames.add(entry.name.toLowerCase());
  }
  const context: WorkbookContext = {
    archive,
    theme,
    drawings: drawingImports(),
    sharedStrings: readSharedStrings(archive, sharedPath),
    styles: readXlsxStylesheet(
      stylesBytes ? new TextDecoder().decode(stylesBytes) : '',
      theme,
      warnings,
      checkboxPath && checkboxBytes
        ? readCheckboxComplements(checkboxBytes, checkboxPath)
        : new Set()
    ),
    date1904,
    dynamicArrays:
      !!metadataBytes &&
      new TextDecoder().decode(metadataBytes).includes('XLDAPR'),
    tables,
    importedTables,
    keptTables: new Set(
      importedTables.flatMap(({ table }) =>
        table ? [table.name.toLowerCase()] : []
      )
    ),
    sheetNames: worksheets.map((sheet) => sheet.name),
    rangeNames,
    warnings,
    unsupportedFunctions: new Set(),
    supportedFunctions: options.supportedFunctions,
    pivotLookups: [],
  };
  const result = worksheets.map((sheet) => readWorksheet(sheet, context));
  const total = result.reduce(
    (sum, sheet) =>
      sum + Object.values(sheet.cells).filter((cell) => cell.value).length,
    0
  );
  if (total > XLSX_MAX_CELLS)
    throw new Error(
      `This workbook has ${total.toLocaleString('en-US')} filled cells; Macro imports up to ${XLSX_MAX_CELLS.toLocaleString('en-US')}. No sheets were imported.`
    );
  const sheetIndex = new Map(
    sheets.map((sheet, index) => [index, worksheets.indexOf(sheet)])
  );
  let importedNames = 0;
  for (const entry of names) {
    if (!entry.name || /^_xl(?:nm|fn)\./i.test(entry.name)) continue;
    const target = entry.sheet === undefined ? 0 : sheetIndex.get(entry.sheet);
    if (target === undefined || target < 0) continue;
    if (hasExternalReference(entry.formula)) {
      warnings.add('Names that refer to other workbooks are not imported.');
      continue;
    }
    if (++importedNames > 10_000)
      throw new Error('Import up to 10,000 Excel name definitions.');
    const formula =
      resolveStructuredReferences(
        stripFunctionPrefixes(entry.formula),
        context.tables,
        worksheets[target].name,
        0,
        0
      ) ?? entry.formula;
    const sheet = result[target];
    sheet.metadata ??= {};
    sheet.metadata.definedNames ??= [];
    sheet.metadata.definedNames.push({
      name: entry.name,
      formula,
      ...(entry.sheet !== undefined && { local: true }),
    });
  }
  keepPivotLookups(result, context);
  if (context.unsupportedFunctions.size)
    warnings.add(
      `Macro cannot calculate these Excel functions yet, so their formulas show errors: ${[...context.unsupportedFunctions].sort().join(', ')}.`
    );
  for (const sheet of result)
    if (sheet.metadata && !Object.keys(sheet.metadata).length)
      delete sheet.metadata;
  const images = context.drawings.images;
  return {
    sheets: result,
    warnings: [...warnings],
    ...(Object.keys(images).length && { images }),
  };
}