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 {
cellDateMention,
cellPlainText,
} from '@macro-inc/spreadsheet/cell-mentions';
import {
parseChartReference,
type SheetDrawing,
} from '@macro-inc/spreadsheet/sheet-drawings';
import { sheetTableLayouts } from '@macro-inc/spreadsheet/sheet-tables';
import { qualifyStructuredReferences } from '@macro-inc/spreadsheet/structured-references';
import { parseWorkbookMetadata } from '@macro-inc/spreadsheet/workbook-metadata';
import { strToU8, zipSync } from 'fflate';
import type { CalculatedCell } from './calculation';
import {
DEFAULT_COLUMN_WIDTH,
formatCellAddress,
isSpreadsheetCellStyle,
parseCellAddress,
SPREADSHEET_MAX_CELL_LENGTH,
SPREADSHEET_MAX_COLUMNS,
SPREADSHEET_MAX_ROWS,
type SpreadsheetCell,
} from './spreadsheet-document';
import {
type WorkbookFileData,
type WorkbookFileExport,
XLSX_MAX_EXPANDED_BYTES,
XLSX_MAX_SHEETS,
} from './workbook-file-types';
import type { CellChartValue, ChartValues } from './xlsx-chart-values';
import { CHARTEX_CONTENT_TYPE, isChartEx } from './xlsx-chartex';
import {
CHECKBOX_BAGS,
CHECKBOX_CONTENT_TYPE,
CHECKBOX_PART,
CHECKBOX_RELATIONSHIP,
} from './xlsx-checkboxes';
import { chartPart, drawingPart, imageFile, themePart } from './xlsx-drawings';
import {
addFunctionPrefixes,
exportImplicitIntersections,
markImplicitIntersections,
} from './xlsx-formula';
import { connectionsPart, pivotParts, pivotTableName } from './xlsx-pivots';
import {
choiceColorFormats,
conditionalFormattingXml,
dataValidationsXml,
notesParts,
} from './xlsx-sheet-rules';
import { cellNumberFormat } from './xlsx-styles';
import { withTableHeaders, xlsxTablePart } from './xlsx-tables';
import {
escapeText,
escapeXml,
numberText,
SharedStrings,
StyleTable,
} from './xlsx-workbook-parts';
const MAIN = 'http://schemas.openxmlformats.org/spreadsheetml/2006/main';
const RELATIONSHIPS =
'http://schemas.openxmlformats.org/officeDocument/2006/relationships';
const PACKAGE_RELATIONSHIPS =
'http://schemas.openxmlformats.org/package/2006/relationships';
const KNOWN_ERRORS = new Set([
'#DIV/0!',
'#N/A',
'#NAME?',
'#NULL!',
'#NUM!',
'#REF!',
'#VALUE!',
]);
/** Macro's default column width in Excel character units. */
const DEFAULT_COLUMN_CHARACTERS =
Math.round(((DEFAULT_COLUMN_WIDTH - 5) / 7) * 256) / 256;
type WrittenValue =
| { kind: 'number'; value: number }
| { kind: 'string'; value: string }
| { kind: 'boolean'; value: boolean }
| { kind: 'error'; value: string }
| { kind: 'formula'; formula: string; result?: CalculatedCell }
| { kind: 'empty' };
/** A cell holding one date pill exports as the calculated date serial. */
export function exportedDateMention(
cell: SpreadsheetCell,
calculated?: CalculatedCell
) {
return cell.format !== 'text' &&
calculated?.number !== undefined &&
cellDateMention(cell.value)
? calculated.number
: undefined;
}
function writtenValue(
cell: SpreadsheetCell,
calculated: CalculatedCell | undefined,
warnings: Set<string>
): WrittenValue {
const dateSerial = exportedDateMention(cell, calculated);
if (dateSerial !== undefined) return { kind: 'number', value: dateSerial };
if (cellPlainText(cell.value) !== cell.value) {
warnings.add(
'Macro mentions are exported as their display text; interactive pills remain in Macro.'
);
return { kind: 'string', value: cellPlainText(cell.value) };
}
if (!cell.value) return { kind: 'empty' };
if (cell.format === 'text') return { kind: 'string', value: cell.value };
if (cell.value.startsWith('='))
return {
kind: 'formula',
formula: cell.value.slice(1),
result: calculated,
};
if (cell.value.startsWith("'"))
return { kind: 'string', value: cell.value.slice(1) };
if (/^(?:true|false)$/i.test(cell.value))
return { kind: 'boolean', value: cell.value.toUpperCase() === 'TRUE' };
if (/^[+-]?(?:\d+(?:\.\d*)?|\.\d+)(?:e[+-]?\d+)?$/i.test(cell.value.trim()))
return { kind: 'number', value: Number(cell.value) };
if (KNOWN_ERRORS.has(cell.value)) return { kind: 'error', value: cell.value };
if (/^#[A-Z_/]+[!?]?$/.test(cell.value)) {
warnings.add('Newer Excel error values are exported as text.');
return { kind: 'string', value: cell.value };
}
if (calculated?.number !== undefined)
return { kind: 'number', value: calculated.number };
return { kind: 'string', value: cell.value };
}
/** Formulas whose meaning depends on array evaluation must be marked as
* dynamic arrays, or Excel would apply legacy implicit intersection: array
* constants, and ranges in positions where legacy Excel reads one value. */
function needsArrayEvaluation(formula: string, rangeNames: Set<string>) {
if (formula.replace(/"(?:[^"]|"")*"/g, '""').includes('{')) return true;
return markImplicitIntersections(formula, rangeNames) !== formula;
}
function quoteSheet(name: string) {
return `'${name.replaceAll("'", "''")}'`;
}
/**
* What a chart reads from a reference: each cell's displayed text and number,
* row by row. References without a sheet read `home`.
*/
function chartValues(
sheets: WorkbookFileData['sheets'],
home: WorkbookFileData['sheets'][number]
): ChartValues {
const byName = new Map(
sheets.map((sheet) => [sheet.name.toLowerCase(), sheet])
);
return (reference) => {
const range = parseChartReference(reference);
const sheet = range?.sheet ? byName.get(range.sheet.toLowerCase()) : home;
if (!range || !sheet) return;
const values: CellChartValue[] = [];
for (let row = range.top; row <= range.bottom; row++)
for (let column = range.left; column <= range.right; column++) {
if (values.length >= 100_000) return values;
const address = formatCellAddress(row, column);
const cell = sheet.cells[address];
// Charts format their axes and caches like the cells they read.
const format = cell && cellNumberFormat(cell);
const result = sheet.values?.[address];
if (result) {
values.push({
text: result.display,
...(result.number !== undefined && {
number: result.number,
...(format && { format }),
}),
});
continue;
}
const text = cell?.value ?? '';
const number = text.trim() === '' ? Number.NaN : Number(text);
values.push({
text,
...(Number.isFinite(number) && {
number,
...(format && { format }),
}),
});
}
return values;
};
}
/** Write Macro sheets as a SpreadsheetML package without an intermediate model. */
export function writeXlsxWorkbook(
input: Pick<WorkbookFileData, 'sheets' | 'images'>
): WorkbookFileExport {
if (!input.sheets.length || input.sheets.length > XLSX_MAX_SHEETS)
throw new Error(`Export a workbook with 1–${XLSX_MAX_SHEETS} sheets.`);
const warnings = new Set<string>();
const names = new Set<string>();
let contentSize = 0;
const encoder = new TextEncoder();
for (const sheet of input.sheets) {
if (
!sheet.name ||
sheet.name.length > 31 ||
/[\\/?*:[\]]/.test(sheet.name) ||
names.has(sheet.name.toLowerCase())
)
throw new Error(
'Excel sheet names must be unique and up to 31 characters, without \\ / ? * : [ ].'
);
names.add(sheet.name.toLowerCase());
if (
!Number.isInteger(sheet.rowCount) ||
sheet.rowCount < 1 ||
sheet.rowCount > SPREADSHEET_MAX_ROWS
)
throw new Error(
`Sheets must have between 1 and ${SPREADSHEET_MAX_ROWS.toLocaleString('en-US')} rows.`
);
if (
sheet.metadata &&
!parseWorkbookMetadata(JSON.stringify(sheet.metadata))
)
throw new Error('Invalid workbook metadata.');
for (const [address, cell] of Object.entries(sheet.cells)) {
if (!parseCellAddress(address))
throw new Error(`Unsupported cell address ${sheet.name}!${address}.`);
if (!isSpreadsheetCellStyle(cell))
throw new Error('The workbook contains unsupported cell formatting.');
if (cell.value.length > SPREADSHEET_MAX_CELL_LENGTH)
throw new Error(
`Cell ${sheet.name}!${address} exceeds 10,000 characters.`
);
contentSize += encoder.encode(cell.value).byteLength;
if (contentSize > XLSX_MAX_EXPANDED_BYTES)
throw new Error('The workbook exceeds the Excel content limit.');
}
}
const workbookFont = input.sheets.find((sheet) => sheet.metadata?.defaultFont)
?.metadata?.defaultFont ?? { name: 'Arial', size: 10 };
const styles = new StyleTable(workbookFont);
const strings = new SharedStrings();
const rangeNames = new Set(
input.sheets.flatMap((sheet) =>
(sheet.metadata?.definedNames ?? [])
.filter((entry) => entry.formula.includes(':'))
.map((entry) => entry.name.toLowerCase())
)
);
let dynamicArrays = false;
const firstVisible = Math.max(
0,
input.sheets.findIndex((sheet) => !sheet.metadata?.hidden)
);
const files: Record<string, Uint8Array> = {};
// Sheets with notes, by 1-based number.
const noted: number[] = [];
// Drawing parts by number, chart parts, and images written once each.
let drawingCount = 0;
let chartCount = 0;
let chartExCount = 0;
const media = new Map<string, string | undefined>();
const imageExtensions = new Set<string>();
// Pivot tables, each with its own cache, numbered from 1, the caches that
// keep their records, and the data connections caches read.
let pivotCount = 0;
const pivotRecords: number[] = [];
const connections: string[] = [];
// Tables, numbered from 1 across the workbook, which formulas name.
let tableCount = 0;
const tableLayouts = input.sheets.flatMap((sheet) =>
sheetTableLayouts(sheet)
);
// Pivot charts stay linked only to pivot tables the download contains.
const pivotNames = new Set(
input.sheets.flatMap((sheet) =>
(sheet.metadata?.pivotTables ?? []).map(
(pivot) =>
`${sheet.name.toLowerCase()}!${pivotTableName(pivot)?.toLowerCase()}`
)
)
);
const pivotExists = (sheet: string, name: string) =>
pivotNames.has(`${sheet.toLowerCase()}!${name.toLowerCase()}`);
const writeImage = (key: string) => {
if (media.has(key)) return media.get(key);
const url = input.images?.[key];
const file = url ? imageFile(url) : undefined;
let path: string | undefined;
if (file) {
path = `xl/media/image${media.size + 1}.${file.extension}`;
files[path] = file.bytes;
imageExtensions.add(file.extension);
} else
warnings.add('Some images were missing and are not in the download.');
media.set(key, path);
return path;
};
input.sheets.forEach((sheet, sheetIndex) => {
const metadata = sheet.metadata ?? {};
const values = sheet.values ?? {};
const arrays = metadata.arrayFormulas ?? {};
// Rows of [column, xml], built once and sorted by position.
const rows = new Map<number, [number, string][]>();
const add = (row: number, column: number, xml: string) => {
let cells = rows.get(row);
if (!cells) {
cells = [];
rows.set(row, cells);
}
cells.push([column, xml]);
};
const occupied = new Set<string>();
let maxRow = 0;
let maxColumn = 0;
const children = new Map<string, CalculatedCell>();
const cells = withTableHeaders(sheet.cells, metadata.tables);
for (const [address, cell] of Object.entries(cells)) {
const position = parseCellAddress(address)!;
const calculated = values[address];
const written = writtenValue(cell, calculated, warnings);
const dateMention = exportedDateMention(cell, calculated) !== undefined;
const s = styles.index(
dateMention && (!cell.format || cell.format === 'general')
? { ...cell, format: 'date' }
: cell,
metadata.defaultFont
);
let xml: string | undefined;
const reference = formatCellAddress(position.row, position.column);
switch (written.kind) {
case 'empty':
if (s) xml = `<c r="${reference}" s="${s}"/>`;
break;
case 'number':
xml = `<c r="${reference}"${s ? ` s="${s}"` : ''}><v>${numberText(written.value)}</v></c>`;
break;
case 'boolean':
xml = `<c r="${reference}"${s ? ` s="${s}"` : ''} t="b"><v>${written.value ? 1 : 0}</v></c>`;
break;
case 'error':
xml = `<c r="${reference}"${s ? ` s="${s}"` : ''} t="e"><v>${escapeXml(written.value)}</v></c>`;
break;
case 'string':
xml = `<c r="${reference}"${s ? ` s="${s}"` : ''} t="s"><v>${strings.index(written.value)}</v></c>`;
break;
case 'formula': {
const result = written.result;
const legacy = arrays[address];
const spill = result?.spill;
const dynamic =
!legacy &&
(!!spill || needsArrayEvaluation(written.formula, rangeNames));
const formula = escapeXml(
addFunctionPrefixes(
exportImplicitIntersections(
qualifyStructuredReferences(
written.formula,
tableLayouts,
sheet.name,
position.row,
position.column
),
rangeNames,
!legacy && !dynamic
)
)
);
let attributes = '';
let array = '';
if (legacy) array = ` t="array" ref="${legacy}"`;
else if (dynamic) {
dynamicArrays = true;
attributes = ' cm="1"';
array = ` t="array" ref="${
spill
? `${reference}:${formatCellAddress(position.row + spill.rows - 1, position.column + spill.columns - 1)}`
: reference
}"`;
}
if (spill || legacy) {
const range =
legacy ??
`${reference}:${formatCellAddress(position.row + spill!.rows - 1, position.column + spill!.columns - 1)}`;
const [, last = range] = range.split(':');
const end = parseCellAddress(last);
if (end)
for (let r = position.row; r <= end.row; r++)
for (let c = position.column; c <= end.column; c++) {
const child = formatCellAddress(r, c);
if (child !== address && values[child])
children.set(child, values[child]);
}
}
let cached = '';
let type = '';
if (result?.error) {
type = ' t="e"';
cached = `<v>${escapeXml(result.display)}</v>`;
} else if (result?.type === 'boolean') {
type = ' t="b"';
cached = `<v>${result.value ? 1 : 0}</v>`;
} else if (result?.number !== undefined)
cached = `<v>${numberText(result.number)}</v>`;
else if (result) {
type = ' t="str"';
cached = `<v>${escapeText(String(result.value ?? result.display))}</v>`;
}
xml = `<c r="${reference}"${s ? ` s="${s}"` : ''}${type}${attributes}><f${array}>${formula}</f>${cached}</c>`;
break;
}
}
if (!xml) continue;
occupied.add(address);
add(position.row, position.column, xml);
maxRow = Math.max(maxRow, position.row);
maxColumn = Math.max(maxColumn, position.column);
}
// Array results: Excel stores each result cell's cached value.
for (const [address, value] of children) {
if (occupied.has(address)) continue;
const position = parseCellAddress(address);
if (!position) continue;
const cached = value.error
? `t="e"><v>${escapeXml(value.display)}</v>`
: value.type === 'boolean'
? `t="b"><v>${value.value ? 1 : 0}</v>`
: value.number !== undefined
? `><v>${numberText(value.number)}</v>`
: `t="str"><v>${escapeText(String(value.value ?? value.display))}</v>`;
add(
position.row,
position.column,
`<c r="${address}" ${cached}</c>`.replace(' >', '>')
);
maxRow = Math.max(maxRow, position.row);
maxColumn = Math.max(maxColumn, position.column);
}
const heights = metadata.rowHeights ?? {};
// Import drops heights equal to the sheet's default, so pick a default
// that no stored height uses.
const defaultRowHeight =
[15, 15.75, 14.25, 16.5].find((candidate) =>
Object.values(heights).every(
(height) => Math.abs(height - candidate) > 0.01
)
) ?? 15;
const hiddenRows = new Set(metadata.hiddenRows ?? []);
for (const row of [...Object.keys(heights).map(Number), ...hiddenRows])
if (!rows.has(row) && row < SPREADSHEET_MAX_ROWS) rows.set(row, []);
const rowXml = [...rows.keys()]
.sort((a, b) => a - b)
.map((row) => {
const cells = rows.get(row)!;
cells.sort((a, b) => a[0] - b[0]);
const height = heights[row];
return `<row r="${row + 1}"${height !== undefined ? ` ht="${numberText(height)}" customHeight="1"` : ''}${hiddenRows.has(row) ? ' hidden="1"' : ''}>${cells.map(([, xml]) => xml).join('')}</row>`;
})
.join('');
if (sheet.rowCount > Math.max(200, maxRow + 1))
warnings.add(
'Extra blank rows beyond the used range are not preserved when importing this Excel file back into Macro.'
);
const hiddenColumns = new Set(metadata.hiddenColumns ?? []);
const widthColumns = new Set([
...Object.keys(sheet.columnWidths).map(Number),
...hiddenColumns,
]);
const cols = [...widthColumns]
.filter(
(column) =>
Number.isInteger(column) &&
column >= 0 &&
column < SPREADSHEET_MAX_COLUMNS
)
.sort((a, b) => a - b)
.map((column) => {
const pixels = sheet.columnWidths[column];
// Excel adds 5px of padding from one character on; narrower columns
// scale by 12px per character.
const width = Number.isFinite(pixels)
? Math.round((pixels < 12 ? pixels / 12 : (pixels - 5) / 7) * 256) /
256
: 8.43;
return `<col min="${column + 1}" max="${column + 1}" width="${width}"${Number.isFinite(pixels) ? ' customWidth="1"' : ''}${hiddenColumns.has(column) ? ' hidden="1"' : ''}/>`;
})
.join('');
const merges: string[] = [];
for (const range of metadata.merges ?? []) {
const [a, b = a] = range.split(':');
const first = parseCellAddress(a)!;
const last = parseCellAddress(b)!;
let blocked = false;
for (let r = first.row; r <= last.row && !blocked; r++)
for (let c = first.column; c <= last.column; c++)
if (
(r !== first.row || c !== first.column) &&
sheet.cells[formatCellAddress(r, c)]?.value
) {
blocked = true;
break;
}
if (blocked)
warnings.add(
`Merge ${sheet.name}!${range} was omitted to preserve values entered in its covered cells.`
);
else merges.push(`<mergeCell ref="${range}"/>`);
}
const freeze = metadata.freeze;
const pane =
freeze && (freeze.rows || freeze.columns)
? `<pane${freeze.columns ? ` xSplit="${freeze.columns}"` : ''}${freeze.rows ? ` ySplit="${freeze.rows}"` : ''} topLeftCell="${formatCellAddress(freeze.rows, freeze.columns)}" activePane="${freeze.rows && freeze.columns ? 'bottomRight' : freeze.rows ? 'bottomLeft' : 'topRight'}" state="frozen"/>`
: '';
const notes = notesParts(metadata.notes, sheetIndex + 1);
const readValues = chartValues(input.sheets, sheet);
const width = (column: number) =>
sheet.columnWidths[column] ?? DEFAULT_COLUMN_WIDTH;
const height = (row: number) => (heights[row] ?? defaultRowHeight) / 0.75;
const drawing = metadata.drawings?.length
? drawingPart({
drawings: metadata.drawings,
image: writeImage,
chart: (chart) => {
// Excel 2016's charts are chartEx parts, numbered on their own.
const path = isChartEx(chart)
? `xl/charts/chartEx${++chartExCount}.xml`
: `xl/charts/chart${++chartCount}.xml`;
files[path] = strToU8(chartPart(chart, readValues, pivotExists));
return path;
},
origin: (point) => {
let x = point.x;
for (let column = 0; column < point.column; column++)
x += width(column);
let y = point.y;
for (let row = 0; row < point.row; row++) y += height(row);
return { x, y };
},
size: (value: SheetDrawing, minimum = 1) => {
if (!value.to)
return { width: value.width ?? 0, height: value.height ?? 0 };
let horizontal = value.to.x - value.from.x;
for (
let column = value.from.column;
column < value.to.column;
column++
)
horizontal += width(column);
let vertical = value.to.y - value.from.y;
for (let row = value.from.row; row < value.to.row; row++)
vertical += height(row);
return {
width: Math.max(minimum, horizontal),
height: Math.max(minimum, vertical),
};
},
})
: undefined;
const drawingNumber = drawing ? ++drawingCount : 0;
// Relationships of the sheet: its drawing, then its notes' parts.
const sheetRelations: string[] = [];
const relate = (type: string, target: string) => {
sheetRelations.push(
`<Relationship Id="rId${sheetRelations.length + 1}" Type="${RELATIONSHIPS}/${type}" Target="${target}"/>`
);
return `rId${sheetRelations.length}`;
};
const drawingId = drawing
? relate('drawing', `../drawings/drawing${drawingNumber}.xml`)
: undefined;
const notesId = notes
? relate('vmlDrawing', `../drawings/vmlDrawing${sheetIndex + 1}.vml`)
: undefined;
if (notes) relate('comments', `../comments${sheetIndex + 1}.xml`);
for (const pivot of metadata.pivotTables ?? []) {
const connection = pivot.connection
? connections.indexOf(pivot.connection) + 1 || connections.length + 1
: undefined;
const parts = pivotParts(
pivot,
pivotCount + 1,
(code) => styles.format(code),
(style) => styles.dxf(style),
connection
);
if (!parts) {
warnings.add(
'Some pivot tables lost their data and are downloaded as values.'
);
continue;
}
if (pivot.connection && connection === connections.length + 1)
connections.push(pivot.connection);
const number = ++pivotCount;
if (parts.records) {
files[`xl/pivotCache/pivotCacheRecords${number}.xml`] = strToU8(
parts.records
);
pivotRecords.push(number);
}
if (parts.relationships.length)
files[`xl/pivotCache/_rels/pivotCacheDefinition${number}.xml.rels`] =
strToU8(
`<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<Relationships xmlns="${PACKAGE_RELATIONSHIPS}">${parts.relationships
.map(
(relationship, index) =>
`<Relationship Id="rId${index + 1}" Type="${RELATIONSHIPS}/${relationship.type}" Target="${escapeXml(relationship.target)}"${relationship.external ? ' TargetMode="External"' : ''}/>`
)
.join('')}</Relationships>`
);
files[`xl/pivotTables/pivotTable${number}.xml`] = strToU8(parts.table);
files[`xl/pivotTables/_rels/pivotTable${number}.xml.rels`] = strToU8(
`<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<Relationships xmlns="${PACKAGE_RELATIONSHIPS}"><Relationship Id="rId1" Type="${RELATIONSHIPS}/pivotCacheDefinition" Target="../pivotCache/pivotCacheDefinition${number}.xml"/></Relationships>`
);
files[`xl/pivotCache/pivotCacheDefinition${number}.xml`] = strToU8(
parts.cache
);
relate('pivotTable', `../pivotTables/pivotTable${number}.xml`);
}
const tableIds = (metadata.tables ?? []).map((table) => {
const number = ++tableCount;
files[`xl/tables/table${number}.xml`] = strToU8(
xlsxTablePart({
table,
id: number,
sheet: sheet.name,
cells: sheet.cells,
tables: tableLayouts,
})
);
return relate('table', `../tables/table${number}.xml`);
});
if (drawing) {
files[`xl/drawings/drawing${drawingNumber}.xml`] = strToU8(drawing.xml);
files[`xl/drawings/_rels/drawing${drawingNumber}.xml.rels`] = strToU8(
drawing.rels
);
}
const dimension =
rows.size && occupied.size
? `A1:${formatCellAddress(maxRow, maxColumn)}`
: 'A1';
files[`xl/worksheets/sheet${sheetIndex + 1}.xml`] = strToU8(
`<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<worksheet xmlns="${MAIN}" xmlns:r="${RELATIONSHIPS}">${
metadata.tabColor
? `<sheetPr><tabColor rgb="FF${metadata.tabColor.slice(1).toUpperCase()}"/></sheetPr>`
: ''
}<dimension ref="${dimension}"/><sheetViews><sheetView workbookViewId="0"${sheetIndex === firstVisible ? ' tabSelected="1"' : ''}${metadata.gridlines === false ? ' showGridLines="0"' : ''}${pane ? `>${pane}</sheetView>` : '/>'}</sheetViews><sheetFormatPr defaultColWidth="${DEFAULT_COLUMN_CHARACTERS}" defaultRowHeight="${defaultRowHeight}"/>${cols ? `<cols>${cols}</cols>` : ''}<sheetData>${rowXml}</sheetData>${
metadata.autoFilter ? `<autoFilter ref="${metadata.autoFilter}"/>` : ''
}${merges.length ? `<mergeCells count="${merges.length}">${merges.join('')}</mergeCells>` : ''}${conditionalFormattingXml(
[
...(metadata.conditionalFormats ?? []),
...choiceColorFormats(metadata.validations),
],
(style) => styles.dxf(style),
addFunctionPrefixes
)}${dataValidationsXml(metadata.validations, addFunctionPrefixes)}<pageMargins left="0.7" right="0.7" top="0.75" bottom="0.75" header="0.3" footer="0.3"/>${drawingId ? `<drawing r:id="${drawingId}"/>` : ''}${notesId ? `<legacyDrawing r:id="${notesId}"/>` : ''}${tableIds.length ? `<tableParts count="${tableIds.length}">${tableIds.map((id) => `<tablePart r:id="${id}"/>`).join('')}</tableParts>` : ''}</worksheet>`
);
if (notes) {
const number = sheetIndex + 1;
noted.push(number);
files[`xl/comments${number}.xml`] = strToU8(notes.comments);
files[`xl/drawings/vmlDrawing${number}.vml`] = strToU8(notes.drawing);
}
if (sheetRelations.length)
files[`xl/worksheets/_rels/sheet${sheetIndex + 1}.xml.rels`] = strToU8(
`<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<Relationships xmlns="${PACKAGE_RELATIONSHIPS}">${sheetRelations.join('')}</Relationships>`
);
});
const definedNames = input.sheets.flatMap((sheet, index) => [
...(sheet.metadata?.definedNames ?? []).map(
(entry) =>
`<definedName name="${escapeXml(entry.name)}"${entry.local ? ` localSheetId="${index}"` : ''}>${escapeXml(addFunctionPrefixes(entry.formula.replace(/^=/, '')))}</definedName>`
),
...(sheet.metadata?.autoFilter
? [
`<definedName name="_xlnm._FilterDatabase" localSheetId="${index}" hidden="1">${escapeXml(`${quoteSheet(sheet.name)}!${sheet.metadata.autoFilter.replace(/([A-Z]+)(\d+)/g, '$$$1$$$2')}`)}</definedName>`,
]
: []),
]);
const sheetEntries = input.sheets
.map(
(sheet, index) =>
`<sheet name="${escapeXml(sheet.name)}" sheetId="${index + 1}"${sheet.metadata?.hidden && index !== firstVisible ? ' state="hidden"' : ''} r:id="rId${index + 1}"/>`
)
.join('');
const count = input.sheets.length;
const checkboxes = styles.hasCheckboxes();
files['[Content_Types].xml'] = strToU8(
`<?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"/>${noted.length ? '<Default Extension="vml" ContentType="application/vnd.openxmlformats-officedocument.vmlDrawing"/>' : ''}${[
...imageExtensions,
]
.map(
(extension) =>
`<Default Extension="${extension}" ContentType="image/${extension === 'emf' || extension === 'wmf' ? `x-${extension}` : extension}"/>`
)
.join('')}${Array.from(
{ length: drawingCount },
(_, index) =>
`<Override PartName="/xl/drawings/drawing${index + 1}.xml" ContentType="application/vnd.openxmlformats-officedocument.drawing+xml"/>`
).join('')}${Array.from(
{ length: chartCount },
(_, index) =>
`<Override PartName="/xl/charts/chart${index + 1}.xml" ContentType="application/vnd.openxmlformats-officedocument.drawingml.chart+xml"/>`
).join('')}${Array.from(
{ length: chartExCount },
(_, index) =>
`<Override PartName="/xl/charts/chartEx${index + 1}.xml" ContentType="${CHARTEX_CONTENT_TYPE}"/>`
).join('')}${Array.from(
{ length: pivotCount },
(_, index) =>
`<Override PartName="/xl/pivotTables/pivotTable${index + 1}.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.pivotTable+xml"/><Override PartName="/xl/pivotCache/pivotCacheDefinition${index + 1}.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.pivotCacheDefinition+xml"/>`
).join('')}${pivotRecords
.map(
(number) =>
`<Override PartName="/xl/pivotCache/pivotCacheRecords${number}.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.pivotCacheRecords+xml"/>`
)
.join('')}${
connections.length
? '<Override PartName="/xl/connections.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.connections+xml"/>'
: ''
}${Array.from(
{ length: tableCount },
(_, index) =>
`<Override PartName="/xl/tables/table${index + 1}.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.table+xml"/>`
).join('')}${noted
.map(
(number) =>
`<Override PartName="/xl/comments${number}.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.comments+xml"/>`
)
.join(
''
)}<Override PartName="/xl/workbook.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml"/>${input.sheets
.map(
(_, index) =>
`<Override PartName="/xl/worksheets/sheet${index + 1}.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml"/>`
)
.join(
''
)}<Override PartName="/xl/theme/theme1.xml" ContentType="application/vnd.openxmlformats-officedocument.theme+xml"/><Override PartName="/xl/styles.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.styles+xml"/>${checkboxes ? `<Override PartName="/${CHECKBOX_PART}" ContentType="${CHECKBOX_CONTENT_TYPE}"/>` : ''}<Override PartName="/xl/sharedStrings.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.sharedStrings+xml"/>${dynamicArrays ? '<Override PartName="/xl/metadata.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.sheetMetadata+xml"/>' : ''}<Override PartName="/docProps/core.xml" ContentType="application/vnd.openxmlformats-package.core-properties+xml"/><Override PartName="/docProps/app.xml" ContentType="application/vnd.openxmlformats-officedocument.extended-properties+xml"/></Types>`
);
files['_rels/.rels'] = strToU8(
`<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<Relationships xmlns="${PACKAGE_RELATIONSHIPS}"><Relationship Id="rId1" Type="${RELATIONSHIPS}/officeDocument" Target="xl/workbook.xml"/><Relationship Id="rId2" Type="http://schemas.openxmlformats.org/package/2006/relationships/metadata/core-properties" Target="docProps/core.xml"/><Relationship Id="rId3" Type="${RELATIONSHIPS}/extended-properties" Target="docProps/app.xml"/></Relationships>`
);
files['docProps/app.xml'] = strToU8(
`<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<Properties xmlns="http://schemas.openxmlformats.org/officeDocument/2006/extended-properties"><Application>Macro</Application></Properties>`
);
files['docProps/core.xml'] = strToU8(
`<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<cp:coreProperties xmlns:cp="http://schemas.openxmlformats.org/package/2006/metadata/core-properties" xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:dcterms="http://purl.org/dc/terms/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"><dc:creator>Macro</dc:creator><dcterms:created xsi:type="dcterms:W3CDTF">${new Date().toISOString().replace(/\.\d+Z$/, 'Z')}</dcterms:created></cp:coreProperties>`
);
files['xl/workbook.xml'] = strToU8(
`<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<workbook xmlns="${MAIN}" xmlns:r="${RELATIONSHIPS}"><workbookPr/><bookViews><workbookView activeTab="${firstVisible}"/></bookViews><sheets>${sheetEntries}</sheets>${
definedNames.length
? `<definedNames>${definedNames.join('')}</definedNames>`
: ''
}<calcPr calcId="191029" fullCalcOnLoad="1"/>${
pivotCount
? `<pivotCaches>${Array.from(
{ length: pivotCount },
(_, index) =>
`<pivotCache cacheId="${index + 1}" r:id="rId${count + 5 + index}"/>`
).join('')}</pivotCaches>`
: ''
}</workbook>`
);
files['xl/_rels/workbook.xml.rels'] = strToU8(
`<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<Relationships xmlns="${PACKAGE_RELATIONSHIPS}">${input.sheets
.map(
(_, index) =>
`<Relationship Id="rId${index + 1}" Type="${RELATIONSHIPS}/worksheet" Target="worksheets/sheet${index + 1}.xml"/>`
)
.join(
''
)}<Relationship Id="rId${count + 1}" Type="${RELATIONSHIPS}/styles" Target="styles.xml"/><Relationship Id="rId${count + 2}" Type="${RELATIONSHIPS}/sharedStrings" Target="sharedStrings.xml"/>${dynamicArrays ? `<Relationship Id="rId${count + 3}" Type="${RELATIONSHIPS}/sheetMetadata" Target="metadata.xml"/>` : ''}<Relationship Id="rId${count + 4}" Type="${RELATIONSHIPS}/theme" Target="theme/theme1.xml"/>${Array.from(
{ length: pivotCount },
(_, index) =>
`<Relationship Id="rId${count + 5 + index}" Type="${RELATIONSHIPS}/pivotCacheDefinition" Target="pivotCache/pivotCacheDefinition${index + 1}.xml"/>`
).join('')}${
connections.length
? `<Relationship Id="rId${count + 5 + pivotCount}" Type="${RELATIONSHIPS}/connections" Target="connections.xml"/>`
: ''
}${
checkboxes
? `<Relationship Id="rId${count + 6 + pivotCount}" Type="${CHECKBOX_RELATIONSHIP}" Target="featurePropertyBag/featurePropertyBag.xml"/>`
: ''
}</Relationships>`
);
if (checkboxes) files[CHECKBOX_PART] = strToU8(CHECKBOX_BAGS);
if (connections.length)
files['xl/connections.xml'] = strToU8(connectionsPart(connections));
// Charts without their own colors take them from the theme they came with.
files['xl/theme/theme1.xml'] = strToU8(
themePart(
input.sheets
.flatMap((sheet) => sheet.metadata?.drawings ?? [])
.find((drawing) => drawing.type === 'chart')?.chart.colors
)
);
files['xl/styles.xml'] = strToU8(styles.xml());
files['xl/sharedStrings.xml'] = strToU8(strings.xml());
if (dynamicArrays)
files['xl/metadata.xml'] = strToU8(
`<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<metadata xmlns="${MAIN}" xmlns:xda="http://schemas.microsoft.com/office/spreadsheetml/2017/dynamicarray"><metadataTypes count="1"><metadataType name="XLDAPR" minSupportedVersion="120000" copy="1" pasteAll="1" pasteValues="1" merge="1" splitFirst="1" rowColShift="1" clearFormats="1" clearComments="1" assign="1" coerce="1" cellMeta="1"/></metadataTypes><futureMetadata name="XLDAPR" count="1"><bk><extLst><ext uri="{bdbb8cdc-fa1e-496e-a857-3c3f30c029c3}"><xda:dynamicArrayProperties fDynamic="1" fCollapsed="0"/></ext></extLst></bk></futureMetadata><cellMetadata count="1"><bk><rc t="1" v="0"/></bk></cellMetadata></metadata>`
);
const bytes = zipSync(files, { level: 6 });
return { bytes, warnings: [...warnings] };
}