Macro is a unified workspace for teams: email, chat, docs, tasks, agents, calls, and CRM — @-linked together with shared AI memory.
TypeScript agentaiai-agentsall-in-onecrmcrm-systememaillinearmcpmessagingnotesnotionofficerustslackslack-alternativestartupstartup-toolstypescriptworkspace
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 }),
};
}