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 { literalListItems } from '@macro-inc/spreadsheet/data-validation';
import {
CONDITIONAL_FORMAT_TYPES,
type ComparisonOperator,
type ConditionalFormat,
type ConditionalThreshold,
DATA_VALIDATION_TYPES,
type DataValidation,
MAX_SHEET_RULES,
TIME_PERIODS,
} from '@macro-inc/spreadsheet/sheet-rules';
import {
formatCellAddress,
parseCellAddress,
SPREADSHEET_MAX_COLUMNS,
SPREADSHEET_MAX_ROWS,
} from './spreadsheet-document';
import type { XlsxStylesheet } from './xlsx-stylesheet';
const MAIN = 'http://schemas.openxmlformats.org/spreadsheetml/2006/main';
const STRICT = 'http://purl.oclc.org/ooxml/spreadsheetml/main';
const X14 = 'http://schemas.microsoft.com/office/spreadsheetml/2009/9/main';
const XM = 'http://schemas.microsoft.com/office/excel/2006/main';
type Attributes = Record<string, string>;
const on = (value: string | undefined, fallback: boolean) =>
value === undefined ? fallback : value === '1' || value === 'true';
/** A cell reference's position, including one beyond Macro's grid. */
function position(reference: string) {
const match = /^\$?([A-Z]{1,3})\$?(\d{1,7})$/i.exec(reference);
if (!match || match[2].startsWith('0')) return;
let column = 0;
for (const letter of match[1].toUpperCase())
column = column * 26 + letter.charCodeAt(0) - 64;
return { row: Number(match[2]) - 1, column: column - 1 };
}
/** Excel's sqref within Macro's grid, or undefined when nothing remains. */
export function importSqref(sqref: string | undefined): string | undefined {
const ranges = (sqref ?? '')
.trim()
.split(/\s+/)
.flatMap((range) => {
const parts = range.split(':');
const first = position(parts[0]);
const last = parts.length === 1 ? first : position(parts[1]);
if (parts.length > 2 || !first || !last) return [];
const top = Math.min(first.row, last.row);
const left = Math.min(first.column, last.column);
if (top >= SPREADSHEET_MAX_ROWS || left >= SPREADSHEET_MAX_COLUMNS)
return [];
const bottom = Math.min(
Math.max(first.row, last.row),
SPREADSHEET_MAX_ROWS - 1
);
const right = Math.min(
Math.max(first.column, last.column),
SPREADSHEET_MAX_COLUMNS - 1
);
const start = formatCellAddress(top, left);
const end = formatCellAddress(bottom, right);
return [start === end ? start : `${start}:${end}`];
});
return ranges.length ? ranges.join(' ') : undefined;
}
/** The cell Excel evaluates a rule's relative references from: the top-left
* cell of its ranges. */
function anchor(sqref: string) {
let row = Number.POSITIVE_INFINITY;
let column = Number.POSITIVE_INFINITY;
for (const range of sqref.split(' ')) {
const first = parseCellAddress(range.split(':')[0]);
if (!first) continue;
row = Math.min(row, first.row);
column = Math.min(column, first.column);
}
return { row, column };
}
/** Excel 2010 rules Macro reads: those defined by formulas. */
const FORMULA_RULES: ReadonlySet<string> = new Set([
'expression',
'cellIs',
'containsText',
'notContainsText',
'beginsWith',
'endsWith',
'containsBlanks',
'notContainsBlanks',
'containsErrors',
'notContainsErrors',
'timePeriod',
]);
const TEXT_RULES: ReadonlySet<string> = new Set([
'containsText',
'notContainsText',
'beginsWith',
'endsWith',
]);
const THRESHOLD_TYPES: Record<string, ConditionalThreshold['type']> = {
min: 'min',
max: 'max',
num: 'num',
percent: 'percent',
percentile: 'percentile',
formula: 'formula',
// Excel 2010 data bars: the automatic minimum and maximum.
autoMin: 'min',
autoMax: 'max',
};
/** The text and fill of a rule shaped as export writes a choice's color. */
function choiceFill(rule: ConditionalFormat) {
const quoted =
rule.type === 'cellIs' &&
rule.operator === 'equal' &&
rule.formulas?.length === 1 &&
!rule.stopIfTrue &&
/^"((?:[^"]|"")*)"$/.exec(rule.formulas[0]);
const fill = rule.style?.fillColor;
if (!quoted || !fill || Object.keys(rule.style ?? {}).length !== 1) return;
const item = quoted[1].replace(/""/g, '"').trim();
return item ? { item, color: fill.toLowerCase() } : undefined;
}
/**
* Takes the rules that color a dropdown's choices, as export writes them, back
* into the dropdown's colors: a fill when a cell of exactly the dropdown's
* range equals one of its choices.
*/
function foldChoiceColors(
rules: ConditionalFormat[],
validations: DataValidation[]
): ConditionalFormat[] {
return rules.filter((rule) => {
const choice = choiceFill(rule);
const dropdown =
choice &&
validations.find(
(validation) =>
validation.type === 'list' &&
validation.dropdown !== false &&
validation.range === rule.range
);
if (!choice || !dropdown) return true;
const key = choice.item.toLowerCase();
const items = literalListItems(dropdown.formulas?.[0] ?? '');
if (
(items && !items.some((item) => item.toLowerCase() === key)) ||
dropdown.colors?.some((entry) => entry.item.toLowerCase() === key)
)
return true;
dropdown.colors = [...(dropdown.colors ?? []), choice];
return false;
});
}
type Pending = {
attributes: Attributes;
formulas: string[];
thresholds: ConditionalThreshold[];
colors: string[];
extension?: boolean;
sqref?: string;
/** An Excel 2010 rule's inline format. */
style?: ConditionalFormat['style'];
};
/**
* Reads conditional formatting and data validation from a worksheet's
* elements as the worksheet parser streams them, including the Excel 2010
* extension list. `formula` imports a formula relative to a cell.
*/
export function createSheetRulesReader(options: {
styles: XlsxStylesheet;
warnings: Set<string>;
formula: (source: string, row: number, column: number) => string | undefined;
}) {
const { styles, warnings } = options;
const conditional: { priority: number; rule: ConditionalFormat }[] = [];
const validations: DataValidation[] = [];
let sqref: string | undefined;
let rule: Pending | undefined;
let validation: Pending | undefined;
let text: string | undefined;
let skipped = false;
// Excel 2010 rules: their ranges follow them, and formats are inline.
let extensionRules: Pending[] | undefined;
let extensionRule: Pending | undefined;
let extensionSqref: string | undefined;
let differential:
| {
reader: ReturnType<XlsxStylesheet['inlineDifferential']>;
stack: string[];
}
| undefined;
const formulas = (sources: string[], range: string) => {
const { row, column } = anchor(range);
const imported: string[] = [];
for (const source of sources) {
const formula = options.formula(source, row, column);
if (formula === undefined) return;
imported.push(formula);
}
return imported;
};
function finishRule(pending: Pending, range: string | undefined) {
const value = pending.attributes;
let type = value.type as ConditionalFormat['type'];
// Excel evaluates these rules' formulas. Some writers leave out the text
// or period they were made from; the formula still defines the rule.
if (
pending.formulas.length &&
((TEXT_RULES.has(type) && value.text === undefined) ||
(type === 'timePeriod' &&
!(TIME_PERIODS as readonly string[]).includes(value.timePeriod)))
)
type = 'expression';
if (
!range ||
!(CONDITIONAL_FORMAT_TYPES as readonly string[]).includes(type)
) {
skipped = true;
return;
}
const result: ConditionalFormat = { range, type };
if (value.operator) {
// Text operators are implied by the rule type.
if (type === 'cellIs')
result.operator = value.operator as ComparisonOperator;
}
if (
pending.formulas.length &&
(type === 'cellIs' || type === 'expression')
) {
const imported = formulas(pending.formulas.slice(0, 2), range);
if (!imported) {
skipped = true;
return;
}
result.formulas = imported;
} else if (type === 'cellIs' || type === 'expression') {
skipped = true;
return;
}
if (value.text !== undefined) result.text = value.text;
if (type === 'timePeriod') {
if (!(TIME_PERIODS as readonly string[]).includes(value.timePeriod)) {
skipped = true;
return;
}
result.period = value.timePeriod as ConditionalFormat['period'];
}
if (type === 'top10') {
result.rank = Math.max(1, Math.round(Number(value.rank ?? 10)) || 10);
if (on(value.percent, false)) result.percent = true;
if (on(value.bottom, false)) result.bottom = true;
}
if (type === 'aboveAverage' && !on(value.aboveAverage, true))
result.below = true;
if (['colorScale', 'dataBar', 'iconSet'].includes(type)) {
if (!pending.thresholds.length) {
skipped = true;
return;
}
result.thresholds = pending.thresholds;
if (pending.colors.length) result.colors = pending.colors;
if (type === 'iconSet') {
result.iconSet = pending.attributes.iconSet ?? '3TrafficLights1';
if (on(pending.attributes.reverse, false)) result.reverse = true;
}
if (!on(pending.attributes.showValue, true)) result.showValue = false;
} else {
const style =
pending.style ??
(value.dxfId === undefined
? undefined
: styles.differentialStyle(Number(value.dxfId)));
if (style && Object.keys(style).length) result.style = style;
}
if (on(value.stopIfTrue, false)) result.stopIfTrue = true;
conditional.push({
priority: Number(value.priority ?? 0) || 0,
rule: result,
});
}
function finishValidation(pending: Pending, range: string | undefined) {
const value = pending.attributes;
// Any value: only an input message makes it worth keeping.
const type =
(value.type ?? 'none') === 'none' ? 'any' : (value.type as string);
if (type === 'any' && !value.prompt) return;
if (
!range ||
!(DATA_VALIDATION_TYPES as readonly string[]).includes(type)
) {
skipped = true;
return;
}
const imported = formulas(pending.formulas.slice(0, 2), range);
if (!imported || (type !== 'any' && !imported.length)) {
skipped = true;
return;
}
const result: DataValidation = {
range,
type: type as DataValidation['type'],
...(imported.length && { formulas: imported }),
};
// Between is Excel's default comparison.
if (!['any', 'list', 'custom'].includes(type))
result.operator = (value.operator ?? 'between') as ComparisonOperator;
if (on(value.allowBlank, false)) result.allowBlank = true;
// OOXML's showDropDown="1" hides the in-cell dropdown.
if (on(value.showDropDown, false)) result.dropdown = false;
if (value.errorStyle === 'warning' || value.errorStyle === 'information')
result.errorStyle = value.errorStyle;
if (on(value.showErrorMessage, false)) result.showError = true;
if (value.errorTitle) result.errorTitle = value.errorTitle.slice(0, 64);
if (value.error) result.error = value.error.slice(0, 1024);
if (on(value.showInputMessage, false)) result.showPrompt = true;
if (value.promptTitle) result.promptTitle = value.promptTitle.slice(0, 64);
if (value.prompt) result.prompt = value.prompt.slice(0, 1024);
validations.push(result);
}
return {
/** Returns true when the element was a rule's. */
open(local: string, uri: string, value: Attributes): boolean {
if (differential) {
differential.reader.read(local, differential.stack.at(-1)!, value);
differential.stack.push(local);
return true;
}
const main = uri === MAIN || uri === STRICT || uri === '';
if (main && local === 'conditionalFormatting') {
sqref = importSqref(value.sqref);
return true;
}
if (main && local === 'cfRule') {
rule = { attributes: value, formulas: [], thresholds: [], colors: [] };
return true;
}
if (uri === X14 && local === 'conditionalFormatting') {
extensionRules = [];
extensionSqref = undefined;
return true;
}
if (uri === X14 && local === 'cfRule') {
// A data bar here extends the main list's rule with the same id.
if (value.type === 'dataBar') return true;
if (FORMULA_RULES.has(value.type ?? ''))
extensionRule = {
attributes: value,
formulas: [],
thresholds: [],
colors: [],
};
else skipped = true;
return true;
}
if (extensionRule && uri === X14 && local === 'dxf') {
differential = { reader: styles.inlineDifferential(), stack: ['dxf'] };
return true;
}
if (
extensionRules &&
uri === XM &&
(local === 'f' || local === 'sqref')
) {
text = '';
return true;
}
if (rule && (main || uri === X14) && local === 'cfvo') {
const type = THRESHOLD_TYPES[value.type ?? ''];
if (type)
rule.thresholds.push({
type,
...(value.val !== undefined && { value: value.val }),
...(value.gte === '0' && { gte: false }),
});
return true;
}
if (rule && main && local === 'color') {
const color = styles.color(value);
if (color) rule.colors.push(color);
return true;
}
if (rule && main && local === 'iconSet') {
rule.attributes = { ...rule.attributes, ...value };
return true;
}
if (rule && main && local === 'dataBar') {
if (value.showValue !== undefined)
rule.attributes = { ...rule.attributes, showValue: value.showValue };
return true;
}
if (rule && main && local === 'formula') {
text = '';
return true;
}
if ((main || uri === X14) && local === 'dataValidation') {
validation = {
attributes: value,
formulas: [],
thresholds: [],
colors: [],
extension: uri === X14,
};
return true;
}
if (
validation &&
((main && (local === 'formula1' || local === 'formula2')) ||
(uri === XM && local === 'f') ||
(uri === XM && local === 'sqref'))
) {
text = '';
return true;
}
return false;
},
text(chunk: string) {
if (text !== undefined) text += chunk;
},
close(local: string, uri: string) {
const main = uri === MAIN || uri === STRICT || uri === '';
if (differential) {
if (uri === X14 && local === 'dxf') {
if (extensionRule) extensionRule.style = differential.reader.style();
differential = undefined;
} else differential.stack.pop();
return;
}
if (extensionRules) {
if (uri === XM && local === 'f') {
extensionRule?.formulas.push(text ?? '');
text = undefined;
} else if (uri === XM && local === 'sqref') {
extensionSqref = text;
text = undefined;
} else if (extensionRule && uri === X14 && local === 'cfRule') {
extensionRules.push(extensionRule);
extensionRule = undefined;
} else if (uri === X14 && local === 'conditionalFormatting') {
const range = importSqref(extensionSqref);
for (const pending of extensionRules) finishRule(pending, range);
extensionRules = undefined;
}
return;
}
if (main && local === 'conditionalFormatting') sqref = undefined;
else if (rule && main && local === 'formula') {
rule.formulas.push(text ?? '');
text = undefined;
} else if (rule && main && local === 'cfRule') {
finishRule(rule, sqref);
rule = undefined;
} else if (
validation &&
main &&
(local === 'formula1' || local === 'formula2')
) {
validation.formulas.push(text ?? '');
text = undefined;
} else if (validation && uri === XM && local === 'f') {
validation.formulas.push(text ?? '');
text = undefined;
} else if (validation && uri === XM && local === 'sqref') {
validation.sqref = text;
text = undefined;
} else if (validation && local === 'dataValidation') {
finishValidation(
validation,
importSqref(
validation.extension
? validation.sqref
: validation.attributes.sqref
)
);
validation = undefined;
}
},
finish() {
if (skipped)
warnings.add(
'Some conditional formatting and data validation rules could not be imported.'
);
const rules = foldChoiceColors(
conditional
.sort((a, b) => a.priority - b.priority)
.map(({ rule: value }) => value),
validations
);
if (
rules.length > MAX_SHEET_RULES ||
validations.length > MAX_SHEET_RULES
)
warnings.add(
`Only the first ${MAX_SHEET_RULES} conditional formatting and data validation rules of a sheet are imported.`
);
return {
conditionalFormats: rules.slice(0, MAX_SHEET_RULES),
validations: validations.slice(0, MAX_SHEET_RULES),
};
},
};
}
// Export -------------------------------------------------------------------
function xml(value: string) {
return value
.replace(/&/g, '&')
.replace(/</g, '<')
.replace(/>/g, '>')
.replace(/"/g, '"');
}
/** An attribute when the value is set. */
function attribute(name: string, value: string | number | undefined) {
return value === undefined || value === ''
? ''
: ` ${name}="${xml(String(value))}"`;
}
/** The first cell of a rule's ranges, which its formulas are written for. */
function firstCell(range: string) {
const { row, column } = anchor(range);
return formatCellAddress(row, column);
}
/** The formulas Excel writes for rules defined by text or by a period. */
function impliedFormula(rule: ConditionalFormat): string | undefined {
const cell = firstCell(rule.range);
const text = `"${(rule.text ?? '').replace(/"/g, '""')}"`;
const day = `FLOOR(${cell},1)`;
const date = `ROUNDDOWN(${cell},0)`;
switch (rule.type) {
case 'containsText':
return `NOT(ISERROR(SEARCH(${text},${cell})))`;
case 'notContainsText':
return `ISERROR(SEARCH(${text},${cell}))`;
case 'beginsWith':
return `LEFT(${cell},LEN(${text}))=${text}`;
case 'endsWith':
return `RIGHT(${cell},LEN(${text}))=${text}`;
case 'containsBlanks':
return `LEN(TRIM(${cell}))=0`;
case 'notContainsBlanks':
return `LEN(TRIM(${cell}))>0`;
case 'containsErrors':
return `ISERROR(${cell})`;
case 'notContainsErrors':
return `NOT(ISERROR(${cell}))`;
case 'timePeriod':
return {
today: `${day}=TODAY()`,
yesterday: `${day}=TODAY()-1`,
tomorrow: `${day}=TODAY()+1`,
last7Days: `AND(TODAY()-${day}<=6,${day}<=TODAY())`,
thisWeek: `AND(TODAY()-${date}<=WEEKDAY(TODAY())-1,${date}-TODAY()<=7-WEEKDAY(TODAY()))`,
lastWeek: `AND(TODAY()-${date}>=(WEEKDAY(TODAY())),TODAY()-${date}<(WEEKDAY(TODAY())+7))`,
nextWeek: `AND(${date}-TODAY()>(7-WEEKDAY(TODAY())),${date}-TODAY()<(15-WEEKDAY(TODAY())))`,
thisMonth: `AND(MONTH(${cell})=MONTH(TODAY()),YEAR(${cell})=YEAR(TODAY()))`,
lastMonth: `AND(MONTH(${cell})=MONTH(EDATE(TODAY(),0-1)),YEAR(${cell})=YEAR(EDATE(TODAY(),0-1)))`,
nextMonth: `AND(MONTH(${cell})=MONTH(EDATE(TODAY(),0+1)),YEAR(${cell})=YEAR(EDATE(TODAY(),0+1)))`,
}[rule.period ?? 'today'];
}
}
function thresholdXml(
threshold: ConditionalThreshold,
formula: (value: string) => string
) {
const value =
threshold.value === undefined
? undefined
: threshold.type === 'formula' ||
!Number.isFinite(Number(threshold.value))
? formula(threshold.value)
: threshold.value;
return `<cfvo type="${threshold.type}"${attribute('val', value)}${threshold.gte === false ? ' gte="0"' : ''}/>`;
}
const colorXml = (color: string) =>
`<color rgb="FF${color.slice(1).toUpperCase()}"/>`;
/**
* A sheet's `<conditionalFormatting>` elements, in priority order. `dxf`
* registers a rule's format in the stylesheet; `formula` prepares a formula
* for Excel.
*/
export function conditionalFormattingXml(
rules: ConditionalFormat[] | undefined,
dxf: (style: ConditionalFormat['style']) => number,
formula: (source: string) => string
): string {
return (rules ?? [])
.map((rule, index) => {
const priority = index + 1;
let body = '';
let attributes = `type="${rule.type}" priority="${priority}"`;
if (['colorScale', 'dataBar', 'iconSet'].includes(rule.type)) {
const thresholds = (rule.thresholds ?? [])
.map((threshold) => thresholdXml(threshold, formula))
.join('');
const colors = (rule.colors ?? []).map(colorXml).join('');
if (rule.type === 'colorScale')
body = `<colorScale>${thresholds}${colors}</colorScale>`;
else if (rule.type === 'dataBar')
body = `<dataBar${rule.showValue === false ? ' showValue="0"' : ''}>${thresholds}${colors || colorXml('#638EC6')}</dataBar>`;
else
body = `<iconSet${attribute('iconSet', rule.iconSet)}${rule.reverse ? ' reverse="1"' : ''}${rule.showValue === false ? ' showValue="0"' : ''}>${thresholds}</iconSet>`;
} else {
attributes += ` dxfId="${dxf(rule.style)}"`;
if (rule.type === 'cellIs')
attributes += attribute('operator', rule.operator);
if (rule.text !== undefined) {
attributes += attribute(
'operator',
{
containsText: 'containsText',
notContainsText: 'notContains',
beginsWith: 'beginsWith',
endsWith: 'endsWith',
}[rule.type as string]
);
attributes += attribute('text', rule.text);
}
if (rule.type === 'timePeriod')
attributes += attribute('timePeriod', rule.period);
if (rule.type === 'top10') {
attributes += attribute('rank', rule.rank ?? 10);
if (rule.percent) attributes += ' percent="1"';
if (rule.bottom) attributes += ' bottom="1"';
}
if (rule.type === 'aboveAverage' && rule.below)
attributes += ' aboveAverage="0"';
const formulas = rule.formulas ?? [];
const implied = impliedFormula(rule);
body = [...formulas, ...(formulas.length || !implied ? [] : [implied])]
.map((source) => `<formula>${xml(formula(source))}</formula>`)
.join('');
}
if (rule.stopIfTrue) attributes += ' stopIfTrue="1"';
return `<conditionalFormatting sqref="${rule.range}"><cfRule ${attributes}>${body}</cfRule></conditionalFormatting>`;
})
.join('');
}
/**
* Dropdown choice colors as Excel can show them: a fill when a cell equals
* the choice. Import reads these rules back into the dropdown's colors.
*/
export function choiceColorFormats(
validations: DataValidation[] | undefined
): ConditionalFormat[] {
return (validations ?? []).flatMap((rule) =>
(rule.colors ?? []).map(({ item, color }) => ({
range: rule.range,
type: 'cellIs' as const,
operator: 'equal' as const,
formulas: [`"${item.replace(/"/g, '""')}"`],
style: { fillColor: color },
}))
);
}
/** A sheet's `<dataValidations>` element, or nothing. */
export function dataValidationsXml(
validations: DataValidation[] | undefined,
formula: (source: string) => string
): string {
if (!validations?.length) return '';
const items = validations.map((rule) => {
const formulas = (rule.formulas ?? [])
.map(
(source, index) =>
`<formula${index + 1}>${xml(formula(source))}</formula${index + 1}>`
)
.join('');
return `<dataValidation${rule.type === 'any' ? '' : ` type="${rule.type}"`}${attribute('errorStyle', rule.errorStyle)}${rule.operator ? attribute('operator', rule.operator) : ''}${rule.allowBlank ? ' allowBlank="1"' : ''}${rule.dropdown === false ? ' showDropDown="1"' : ''}${rule.showPrompt ? ' showInputMessage="1"' : ''}${rule.showError ? ' showErrorMessage="1"' : ''}${attribute('errorTitle', rule.errorTitle)}${attribute('error', rule.error)}${attribute('promptTitle', rule.promptTitle)}${attribute('prompt', rule.prompt)} sqref="${rule.range}">${formulas}</dataValidation>`;
});
return `<dataValidations count="${items.length}">${items.join('')}</dataValidations>`;
}
/**
* A sheet's notes as Excel's comments part and the legacy drawing Excel
* shows them with. `sheet` is the 1-based sheet number, which keeps the
* drawing's shape ids unique in the workbook.
*/
export function notesParts(
notes: Record<string, string> | undefined,
sheet: number
): { comments: string; drawing: string } | undefined {
const entries = Object.entries(notes ?? {}).flatMap(([address, note]) => {
const position = parseCellAddress(address);
return position ? [{ address, note, ...position }] : [];
});
if (!entries.length) return;
const comments = `<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<comments xmlns="${MAIN}"><authors><author>Macro</author></authors><commentList>${entries
.map(
({ address, note }) =>
`<comment ref="${address}" authorId="0"><text><r><t xml:space="preserve">${xml(note)}</t></r></text></comment>`
)
.join('')}</commentList></comments>`;
const shapes = entries
.map(
({ row, column }, index) =>
`<v:shape id="_x0000_s${sheet * 1024 + index + 1}" type="#_x0000_t202" style="position:absolute;margin-left:59.25pt;margin-top:1.5pt;width:108pt;height:59.25pt;z-index:${index + 1};visibility:hidden" fillcolor="#ffffe1" o:insetmode="auto"><v:fill color2="#ffffe1"/><v:shadow on="t" color="black" obscured="t"/><v:path o:connecttype="none"/><v:textbox style="mso-direction-alt:auto"><div style="text-align:left"></div></v:textbox><x:ClientData ObjectType="Note"><x:MoveWithCells/><x:SizeWithCells/><x:Anchor>${column + 1}, 15, ${row}, 2, ${column + 3}, 15, ${row + 4}, 16</x:Anchor><x:AutoFill>False</x:AutoFill><x:Row>${row}</x:Row><x:Column>${column}</x:Column></x:ClientData></v:shape>`
)
.join('');
const drawing = `<xml xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel"><o:shapelayout v:ext="edit"><o:idmap v:ext="edit" data="${sheet}"/></o:shapelayout><v:shapetype id="_x0000_t202" coordsize="21600,21600" o:spt="202" path="m,l,21600r21600,l21600,xe"><v:stroke joinstyle="miter"/><v:path gradientshapeok="t" o:connecttype="rect"/></v:shapetype>${shapes}</xml>`;
return { comments, drawing };
}
/** A conditional format's differential format as a stylesheet `<dxf>`. */
export function differentialXml(
style: ConditionalFormat['style'] = {},
numberFormatId?: number
): string {
const font = [
style.bold !== undefined && (style.bold ? '<b/>' : '<b val="0"/>'),
style.italic !== undefined && (style.italic ? '<i/>' : '<i val="0"/>'),
style.strikethrough !== undefined &&
(style.strikethrough ? '<strike/>' : '<strike val="0"/>'),
style.underline !== undefined &&
(style.underline ? '<u/>' : '<u val="none"/>'),
style.textColor && colorXml(style.textColor),
]
.filter(Boolean)
.join('');
const fill = style.fillColor
? `<fill><patternFill><bgColor rgb="FF${style.fillColor.slice(1).toUpperCase()}"/></patternFill></fill>`
: '';
const format =
style.numberFormat && numberFormatId !== undefined
? `<numFmt numFmtId="${numberFormatId}"${attribute('formatCode', style.numberFormat)}/>`
: '';
return `<dxf>${font ? `<font>${font}</font>` : ''}${format}${fill}</dxf>`;
}