ts-xlsx

Appearance

Cell values ​

ArrayFormulaValue ​

interface

A cell holding an array formula (<f t="array">): one formula whose result fills ref, a range starting at this cell. The other cells of the range hold only the values the formula produced, which is how Excel stores them, so they are plain values here too.

Excel stores two kinds this way. A legacy array formula, entered with Ctrl+Shift+Enter, shows in braces and fills the range it was entered over. A dynamic-array formula is dynamic: it spills, ref is the range its last calculation filled, and no braces are shown. Excel keeps that mark in the workbook's cell metadata rather than on the formula, and a formula that loses it opens as a legacy array formula that no longer spills.

ts
interface ArrayFormulaValue {
  readonly shareType: 'array';
  readonly formula: string;
  /** The range the result fills, starting at this cell: `'B1:B3'`, or `'B1'` for one cell. */
  readonly ref: string;
  /** Whether Excel evaluates the formula as a dynamic array rather than a Ctrl+Shift+Enter one. */
  readonly dynamic?: boolean;
  readonly result?: FormulaResult;
}

CellValue ​

type

Everything a cell's value can be. null is the empty cell.

ts
type CellValue =
  | null
  | number
  | string
  | boolean
  | Date
  | ErrorValue
  | FormulaValue
  | SharedFormulaValue
  | ArrayFormulaValue
  | DataTableFormulaValue
  | RichTextValue;

cellValueToText ​

function

The plain text of any cell value, total over CellValue, so a caller reading a sheet whose cells it did not write never has to switch on the union itself.

This is the value's text, not the cell's display text: a number renders as JavaScript renders it, with no number format applied (0.1 + 0.2 is "0.30000000000000004", a currency cell has no currency sign), because the format lives on the style and this function is given only the value. What each kind yields:

  • the empty cell (null) and an invalid Date → "", the two ways a cell has no text
  • a boolean → "TRUE" / "FALSE", Excel's own literals rather than JavaScript's
  • a Date → a full ISO-8601 timestamp
  • an error → its literal, e.g. "#REF!", the same string the grid shows
  • rich text → every run concatenated (richTextToPlain)
  • any formula kind → the text of the cached result, and "" when the cell carries no cached result: the formula source is not text the sheet ever displayed
ts
function cellValueToText(value: CellValue): string;

coerceCellValue ​

function

Normalise a raw assignment into a stored CellValue. undefined becomes the empty cell (null); every other kind is validated by detectValueType. The model never rewrites one value kind into another (a numeric-looking string stays a string). The single exception is formula text, which is canonicalised to the OOXML stored form (no leading =) so round-trips are idempotent regardless of how the caller supplied it.

ts
function coerceCellValue(value: CellValue | undefined): CellValue;

Throws: TypeError if the value is not a recognised cell-value shape.

DataTableFormulaValue ​

interface

A cell computed by a What-If-Analysis data table (<f t="dataTable">), the OOXML formula kind that fills a range by re-evaluating a model against a grid of substituted input cells. The library does not evaluate it; it preserves the declaration so a read-modify-write cycle re-emits it verbatim rather than silently dropping the data-table kind.

ts
interface DataTableFormulaValue {
  readonly shareType: 'dataTable';
  /** The range the data table fills, e.g. `'B2:B5'`. */
  readonly ref: string;
  /** Whether the table substitutes two inputs (a 2-D data table) rather than one. */
  readonly dataTable2D?: boolean;
  /** For a 1-D table, whether the input runs along the row rather than down the column. */
  readonly dataTableRow?: boolean;
  /** The first (row) input-cell reference. */
  readonly r1?: string;
  /** The second (column) input-cell reference, present for a 2-D table. */
  readonly r2?: string;
  /**
   * Whether the cell {@link r1} named has been deleted. Excel keeps the reference as it was written and
   * sets this flag, and the table then shows `#REF!`; a row or column delete that takes the input cell
   * does the same here.
   */
  readonly r1Deleted?: boolean;
  /** Whether the cell {@link r2} named has been deleted, as {@link r1Deleted} is for {@link r1}. */
  readonly r2Deleted?: boolean;
  readonly result?: FormulaResult;
}

detectValueType ​

function

Classify a value into its observable ValueType. This is total over CellValue: every legal value has exactly one type. A Date is a date even when its time is NaN (an invalid date is still a date-typed cell); serialization, not the model, decides what to do with it.

ts
function detectValueType(value: CellValue): ValueType;

ERROR_CODES ​

const

The errors a cell (or formula result) can hold.

They are stored two ways. The classic seven, #GETTING_DATA and #BUSY! are literals: a typed cell holds the spelling and Excel reads it back as that error. Excel has no literal for #SPILL!, #CONNECT!, #BLOCKED!, #UNKNOWN!, #FIELD! and #CALC!, and stores each as #VALUE! beside a rich value naming the real one, which is how they are read and written here. #PYTHON!, #EXTERNAL! and #TIMEOUT! are not here: Excel reads no literal spelling of them, and no rich value was seen to read back as one of them unambiguously (Excel 16.0 build 20326).

ts
const ERROR_CODES: readonly ["#N/A", "#REF!", "#NAME?", "#DIV/0!", "#NULL!", "#VALUE!", "#NUM!", "#GETTING_DATA", "#BUSY!", "#SPILL!", "#CONNECT!", "#BLOCKED!", "#UNKNOWN!", "#FIELD!", "#CALC!"]

ErrorCode ​

type

ts
type ErrorCode = (typeof ERROR_CODES)[number];

ErrorValue ​

interface

An in-cell error, e.g. {error: '#REF!'}.

ts
interface ErrorValue {
  readonly error: ErrorCode;
}

FormulaResult ​

type

The cached result a formula carries: any scalar, a date, or an error.

ts
type FormulaResult = number | string | boolean | Date | ErrorValue;

FormulaValue ​

interface

A cell whose value is computed by its own formula.

ts
interface FormulaValue {
  readonly formula: string;
  readonly result?: FormulaResult;
}

isArrayFormulaValue ​

function

Whether a value is an array formula, legacy or dynamic (ArrayFormulaValue).

ts
function isArrayFormulaValue(value: CellValue): value is ArrayFormulaValue;

isDataTableFormulaValue ​

function

Whether a value is a What-If-Analysis data-table formula (DataTableFormulaValue).

ts
function isDataTableFormulaValue(value: CellValue): value is DataTableFormulaValue;

isErrorCode ​

function

Whether a string is one of Excel's canonical error literals.

ts
function isErrorCode(text: string): text is ErrorCode;

isErrorValue ​

function

Whether a value is an in-cell error (ErrorValue). The narrowing counterpart of detectValueType(value) === ValueType.Error: use this one when the branch goes on to read .error, and detectValueType when it dispatches over all eight kinds at once.

ts
function isErrorValue(value: CellValue): value is ErrorValue;

isFormulaValue ​

function

Whether a value is a cell's own plain formula (FormulaValue): a shared-formula master, or a formula belonging to no group. A shared-formula clone is not one of these, and nor is an array formula; see isSharedFormulaValue and isArrayFormulaValue. Every formula kind reports as ValueType.Formula, so a caller that means "any formula-shaped cell" wants detectValueType, not this.

ts
function isFormulaValue(value: CellValue): value is FormulaValue;

isRichTextValue ​

function

Whether a value is composed of formatted runs (RichTextValue). This is the test to make before richTextToPlain, which accepts nothing else.

ts
function isRichTextValue(value: CellValue): value is RichTextValue;

isSharedFormulaValue ​

function

Whether a value is a clone participating in a shared formula (SharedFormulaValue).

ts
function isSharedFormulaValue(value: CellValue): value is SharedFormulaValue;

RichTextRun ​

interface

One formatted run of a rich-text value.

ts
interface RichTextRun {
  readonly text: string;
  readonly font?: Font;
}

richTextToPlain ​

function

Flatten a rich-text value to its plain text by concatenating every run's text in order. This is the text a consumer that cannot render per-run formatting (a CSV field, a pivot cache entry) sees, and the string a rich cell reads as when its formatting is discarded.

ts
function richTextToPlain(value: RichTextValue): string;

RichTextValue ​

interface

A value composed of independently-formatted text runs.

ts
interface RichTextValue {
  readonly richText: readonly RichTextRun[];
}

SharedFormulaValue ​

interface

A cell that participates in a shared formula: a clone of a master formula cell filled across a range. sharedFormula is the master cell's address (e.g. 'B1'); the master itself is a plain FormulaValue. On read, the clone's own formula is the master's translated to the clone's position and result is the clone's cached value; on write, the clones of a master collapse into OOXML's shared-formula grouping.

ts
interface SharedFormulaValue {
  readonly sharedFormula: string;
  /** The master's formula translated to this cell's position. Filled in on read; a clone assigned by
   * a caller carries only `sharedFormula`, and the writer recovers the formula from the master. */
  readonly formula?: string;
  readonly result?: FormulaResult;
}

ValueType ​

const

The observable kind of a cell's value. Every formula kind reports as Formula.

ts
const ValueType: { readonly Null: 'null'; readonly Number: 'number'; readonly String: 'string'; readonly Boolean: 'boolean'; readonly Date: 'date'; readonly Error: 'error'; readonly Formula: 'formula'; readonly RichText: 'richText'; }

Released under the MIT License.

Copyright (c) shbernal