Cells & values

"Smart" setter: infers the cell value from a JS runtime value. - string starting with = → formula, except a lone '=', which is text - string matching an Excel error token → error variant - other primitives / Date / null pass through verbatim

This follows what Excel does with the same text typed into a cell. A string sent down the formula path has to be a formula: '==A1' and '= ' throw here the way setFormula throws for them, as Excel refuses them too. Data that may legitimately start with = (a CSV column holding '==>') belongs in setCellValue, which infers nothing.

Intentionally not the default — explicit is clearer for typed code, and inferring on every write costs measurable time on the worksheet write hot path.

function bindValue(c: Cell, value: string | number | boolean | Date | null): void

Parameters

Returns

void

Coerce a CellValue to boolean | undefined. Booleans pass through; 'TRUE' / 'true' and 'FALSE' / 'false' (case-insensitive) parse to true / false; numbers yield false for 0 and true for any other finite value (matching Excel's truthy-number coercion); formula cells return their cached boolean if any. Everything else (null, Date, error, duration, rich-text, non-bool strings) yields undefined.

function cellValueAsBoolean(v: CellValue): boolean | undefined

Parameters

Returns

boolean | undefined

Coerce a CellValue to a Date when one is meaningful. Pass-through for Date-typed values; ISO-8601 strings (anything new Date(s) parses to a finite time) round-trip; durations are interpreted as new Date(ms). Numbers, booleans, formulas, errors, rich text, and null all return undefined — this helper does **not** apply the Excel-serial-to-Date conversion (use excelToDate for that).

function cellValueAsDate(v: CellValue): Date | undefined

Parameters

Returns

Date | undefined

Coerce a CellValue to a number when one is meaningful. Booleans yield 0/1; numeric strings parse via Number(s); rich-text concats then parses; formulas with a numeric cached value pass through. Returns undefined when there's no sensible numeric reading (text strings, errors, dates, durations, null, empty).

function cellValueAsNumber(v: CellValue): number | undefined

Parameters

Returns

number | undefined

Map a CellValue to the most natural JS primitive for display / export. Unlike cellValueAsString/cellValueAsNumber/etc., which each force a single target type and return undefined when the value can't be coerced, this returns whatever primitive best represents the union variant:

- null → null - string / number / boolean / Date → passthrough - rich text → joined run text (string) - formula → recursive on cachedValue (null when uncached) - error → error code (string) - duration → ms (number)

function cellValueAsPrimitive(v: CellValue): string | number | boolean | Date | null

Parameters

Returns

string | number | boolean | Date | null

Coerce a CellValue to its plain-string display form. Numbers / booleans convert via String; rich text concatenates run text; formulas yield the cached value (or empty string when uncached); errors yield their Excel token; durations yield "<ms> ms" with no formatting; Dates yield Date.toISOString(); null yields "".

Pass opts.dateFormat to override the Date renderer (e.g. a locale-specific format) and opts.emptyText to substitute a different placeholder for null cells.

function cellValueAsString(v: CellValue, opts?: CellValueAsStringOptions): string

Parameters

Returns

string

Get the cached value Excel last computed for a formula cell, or undefined for non-formula / uncached cells. Useful for data_only read paths that want the displayed result without re-evaluating.

function getCachedFormulaValue(c: Cell): string | number | boolean | undefined

Parameters

Returns

string | number | boolean | undefined

Format a Cell's coordinate as the canonical "A1" string.

function getCoordinate(c: Cell): string

Parameters

Returns

string

Get the formula text from a formula-bearing cell, or undefined for non-formula cells. Equivalent to: isFormulaValue(c.value) ? c.value.formula : undefined but spares callers the type-narrow + member access.

function getFormulaText(c: Cell): string | undefined

Parameters

Returns

string | undefined

True iff v is the duration variant.

function isDurationValue(v: CellValue): v is { kind: "duration"; ms: number }

Parameters

Returns

v is { kind: "duration"; ms: number }

Returns true iff the cell has no content.

function isEmptyCell(c: Cell): boolean

Parameters

Returns

boolean

Returns true iff the cell holds an Excel error value (#REF!, #NAME?, …).

function isErrorCell(c: Cell): boolean

Parameters

Returns

boolean

True iff v is the error variant.

function isErrorValue(v: CellValue): v is { code: ...; kind: "error" }

Parameters

Returns

v is { code: ...; kind: "error" }

True iff c.value is the formula variant.

function isFormulaCell(c: Cell): boolean

Parameters

Returns

boolean

True iff v is the formula variant.

function isFormulaValue(v: CellValue): v is FormulaValue

Parameters

Returns

v is FormulaValue

Type guard for MergedCell — true iff the cell is a placeholder for a merged-range covered cell (the top-left of a merged range holds the value; the rest are MergedCell). Use this to filter merge-placeholders out of value-walking loops.

function isMergedCell(c: Cell): c is MergedCell

Parameters

Returns

c is MergedCell

True iff c.value is the rich-text variant.

function isRichTextCell(c: Cell): boolean

Parameters

Returns

boolean

True iff v is the rich-text variant.

function isRichTextValue(v: CellValue): v is { kind: "rich-text"; runs: RichText }

Parameters

Returns

v is { kind: "rich-text"; runs: RichText }

Build an array (CSE) formula value spanning ref. Belongs on the top-left cell of the range, since Excel reads ref to know how far the result spreads.

function makeArrayFormula(ref: string, formula: string, opts?: Pick<FormulaValue, "cachedValue" | "cachedValueType">): FormulaValue

Parameters

Returns

FormulaValue

Build a fresh Cell. Validates coordinates against the OOXML grid bounds.

function makeCell(row: number, col: number, value?: CellValue, styleId?: number): Cell

Parameters

Returns

Cell

Build a data-table formula value. Preserves all the dt-specific attributes so the writer can re-emit <f t="dataTable" r1="..." /> verbatim and Excel keeps treating the cell as a Data Table cell. Excel writes these with no formula text at all, so formula may be empty.

function makeDataTableFormula(formula: string, opts: DataTableFormulaOpts): FormulaValue

Parameters

Returns

FormulaValue

Build a { kind: 'duration', ms } cell value.

function makeDurationValue(ms: number): { kind: "duration"; ms: number }

Parameters

Returns

{ kind: "duration"; ms: number }

Build a { kind: 'error', code } cell value.

function makeErrorValue(code: ...): { code: ...; kind: "error" }

Parameters

Returns

{ code: ...; kind: "error" }

Build a formula cell value. Hand it to setCell when the write already carries a style id, so placing a formatted formula stays a single call; setFormula is the same thing applied to a cell you already hold.

A leading = is stripped, so '=SUM(A1:A3)' and 'SUM(A1:A3)' are interchangeable. Exactly one comes off: text that still starts with = after that ('==A1') is rejected with an OpenXmlSchemaError, since it is not a formula Excel accepts either and A1 is not what it meant.

A cached value is optional. Without one, Excel, LibreOffice and Google Sheets compute the result on open, but viewers that never calculate (Quick Look, Outlook and SharePoint previews, most thumbnailers) render the cell empty. Supply one whenever the producer can compute it. For an Excel error, supply { cachedValue: '#N/A', cachedValueType: 'error' }; without the type, the same token is a string result.

function makeFormula(formula: string, opts?: Pick<FormulaValue, "cachedValue" | "cachedValueType">): FormulaValue

Parameters

Returns

FormulaValue

Build a shared-formula value. The first cell in the group carries the formula text + ref; subsequent cells with the same si carry only the index, and Excel reconstructs their text by shifting the references, so their formula is legitimately empty.

function makeSharedFormula(si: number, formula?: string, ref?: string, opts?: Pick<FormulaValue, "cachedValue" | "cachedValueType">): FormulaValue

Parameters

Returns

FormulaValue

Array (CSE) formula spanning a ref range, applied in place.

function setArrayFormula(c: Cell, ref: string, formula: string, opts?: Pick<FormulaValue, "cachedValue" | "cachedValueType">): void

Parameters

Returns

void

Direct value setter. No type inference, no validation beyond the union — the caller is in charge. Use bindValue for the "do what I mean" path.

function setCellValue(c: Cell, value: CellValue): void

Parameters

Returns

void

Data-table formula, applied in place. See makeDataTableFormula.

function setDataTableFormula(c: Cell, formula: string, opts: DataTableFormulaOpts): void

Parameters

Returns

void

Plain A1+B1 style formula, applied in place. Same value as makeFormula, for a cell you already hold.

function setFormula(c: Cell, formula: string, opts?: Pick<FormulaValue, "cachedValue" | "cachedValueType">): void

Parameters

Returns

void

Shared formula, applied in place. See makeSharedFormula.

function setSharedFormula(c: Cell, si: number, formula?: string, ref?: string, opts?: Pick<FormulaValue, "cachedValue" | "cachedValueType">): void

Parameters

Returns

void

function makeRichText(runs: readonly (TextRun | { font?: InlineFont; text: string })[]): RichText

Parameters

Returns

RichText

function makeTextRun(text: string, font?: InlineFont): TextRun

Parameters

Returns

TextRun

Concatenate the plain-text content of a rich-text value (rich-text read paths often want the raw text without formatting).

function richTextToString(rt: RichText): string

Parameters

Returns

string