Workbook

Returns

void

_rewriteFormulasReferencingRenamedSheet()

_rewriteFormulasReferencingRenamedSheet(
   sheet,
   currentName,
   newName): void;

Precondition: formula parser has finished importing (formulaParserReady has resolved).

Parameters

sheet

currentName

string

newName

string

Returns

void

_writesIter()

_writesIter(): IterableIterator<[string, CellValue, KnownVertexId]>;

Precondition: Workbook must be initialized

Returns

IterableIterator<[string, CellValue, KnownVertexId]>

addSheet()

addSheet(sheetName?, index?): WorkSheet;

Add a new empty sheet to the workbook

Parameters

sheetName?

if no sheet name is provided a unique sheet name will be generated

string | null

index?

number

if no index is provided the sheet will be appended to the list of sheets

Returns

clearCachedFormulasExcept()

clearCachedFormulasExcept(formulas): void;

Parameters

formulas

string[]

Returns

void

clearCells()

clearCells(ref): boolean;

Precondition: Workbook must be initialized

Parameters

ref

A reference to the cell, or range of cells, to clear. Defined names are not supported.

string | Reference

Returns

boolean

true if any changes were made (so a recalculation is in order).

deleteColumns()

deleteColumns(
   sheetName,
   columnIndex,
   count): RewriteFormula;

Delete count columns at a specific index in the given sheet.

Parameters

sheetName

string

columnIndex

number

0-based column index

count

number

how many columns should be deleted

Returns

deleteRows()

deleteRows(
   sheetName,
   rowIndex,
   count): RewriteFormula;

Delete count rows at a specific index in the given sheet.

Parameters

sheetName

string

rowIndex

number

0-based row index

count

number

how many rows should be deleted

Returns

emit()

emit(event, ...arguments_): Emitter;

Emit an event, invoking all handlers registered for it.

Parameters

event

string

The name of the event to emit.

arguments_

…any[]

Arguments to pass to the event handlers.

Returns

Emitter

The Emitter instance for method chaining.

Inherited from

EventEmitter.emit

getCell()

getCell(cellId, sheetName?): Cell | null;

Parameters

cellId

string

sheetName?

string | null

Returns

Cell | null

getGlobal()

getGlobal(name):
  | FormulaError
  | DefinedName;

Parameters

name

string

Returns

| FormulaError | DefinedName

getSheet()

getSheet(sheetName?): WorkSheet | null;

Parameters

sheetName?

string | null

Returns

WorkSheet | null

the named sheet if sheetName is truthy (null if not found), else the first sheet.

getSheetByIndex()

getSheetByIndex(index?): WorkSheet | null;

Get a sheet of the given index or else, if index is not provided, the first sheet of this workbook.

Parameters

index?

the index of a sheet

number | null

Returns

WorkSheet | null

the sheet found at the given index, or else the first sheet if none was given. If no sheets are present this method returns null.

getSheetIndex()

getSheetIndex(sheetName?): number | null;

Get the order index of a sheet with the given name.

Parameters

sheetName?

string

the name of the sheet whose index should be returned. It will be matched case-insensitively.

Returns

number | null

The index of the sheet with the given name, or null if no sheet with that name exists, or 0 if no/empty name was given.

getSheets()

getSheets(): WorkSheet[];

Returns

WorkSheet[]

getSheetSize()

getSheetSize(sheetName?): [number, number];

Get the size of a sheet with the given name.

Parameters

sheetName?

the name of the sheet whose size should be returned. It will be matched case-insensitively. If not provided, or empty, the size of the first sheet (0, 0) is returned.

string | null

Returns

[number, number]

The size of the sheet with the given name, or null if no sheet with that name exists, or (0, 0) if no/empty name was given.

getTable()

getTable(name): Table | null;

Parameters

name

string

Returns

Table | null

getTables()

getTables(): Table[];

Returns

Table[]

getViews()

getViews(): WorkbookView[];

Gets the workbook’s saved view configurations (active sheet, etc).

Copies are returned to prevent external mutation of the workbook’s views.

Returns

WorkbookView[]

hasListeners()

Call Signature

hasListeners(event): boolean;

Check if there are any handlers registered for a specific event.

Parameters

event

string

The name of the event.

Returns

boolean

true if there are one or more handlers, false otherwise.

Inherited from

EventEmitter.hasListeners

Call Signature

hasListeners(): boolean;

Check if there are any handlers registered for any event.

Returns

boolean

true if there are one or more handlers for any event, false otherwise.

Inherited from

EventEmitter.hasListeners

insertColumns()

insertColumns(
   sheetName,
   columnIndex,
   count,
   toTheRight): RewriteFormula;

Insert count columns at a specific index in the given sheet.

Parameters

sheetName

string

columnIndex

number

0-based column index

count

number

how many columns to insert

toTheRight

boolean

determines whether the inserted columns are inserted to the left or right of the column at columnIndex.

Returns

insertRows()

insertRows(
   sheetName,
   rowIndex,
   count,
   below): RewriteFormula;

Insert count rows at a specific index in the given sheet.

Parameters

sheetName

string

rowIndex

number

0-based row index

count

number

how many rows to insert

below

boolean

determines whether the inserted rows are inserted below or above of the row at rowIndex.

Returns

isGlobal()

isGlobal(name): boolean;

Parameters

name

string

Returns

boolean

iterDataTableCells()

iterDataTableCells(): IterableIterator<Cell>;

Returns

IterableIterator<Cell>

iterFormulaCells()

iterFormulaCells(): IterableIterator<
  | Cell
| DefinedName>;

Returns

IterableIterator< | Cell | DefinedName>

listenerCount()

Call Signature

listenerCount(event): number;

Get the count of listeners for a specific event.

Parameters

event

string

The name of the event.

Returns

number

The number of listeners for the event.

Inherited from

EventEmitter.listenerCount

Call Signature

listenerCount(): number;

Get the count of all event handlers in total.

Returns

number

The total number of event handlers.

Inherited from

EventEmitter.listenerCount

listeners()

listeners(event): (...arguments_) => void[];

Retrieve the event handlers registered for a specific event.

Parameters

event

string

The name of the event.

Returns

(…arguments_) => void[]

An array of functions registered as handlers for the event.

Inherited from

EventEmitter.listeners

mergeCells()

mergeCells(
   sheetName,
   rangeStr,
   options): void;

Merge a range of cells on the given sheet.

Parameters

sheetName

string

name of the sheet

rangeStr

string

range in A1 notation, e.g. “A1:B2”

options

recalcNow?

boolean = true

if true (default), recalculate after merging; set to false to batch multiple merges before a single recalc

Returns

void

Throws

if the sheet does not exist, the range is a single cell, or overlaps an existing merge

moveCells()

moveCells(
   from,
   to,
   recalcNow): RewriteFormula;

Precondition: formula parser has finished importing (formulaParserReady has resolved).

Parameters

from

Must be the same dimensions as to

string | A1Reference

to

Must be the same dimensions as from

string | A1Reference

recalcNow

boolean = true

If false, skip the recalculate() call at the end. Useful when the caller will write more data before recalculating.

Returns

moveColumns()

moveColumns(
   sheetName,
   from,
   to,
   count): RewriteFormula;

Parameters

sheetName

string

from

number

0-based index of first column to move

to

number

0-based index of end-position of first column to move

count

number

the number of columns to move

Returns

moveRows()

moveRows(
   sheetName,
   from,
   to,
   count): RewriteFormula;

Parameters

sheetName

string

from

number

0-based index of first row to move

to

number

0-based index of end-position of first row to move

count

number

the number of rows to move

Returns

off()

Call Signature

off(event, listener): Emitter;

Remove a specific event handler for a specified event.

Parameters

event

string

The name of the event.

listener

(…arguments_) => void

The specific handler function to remove.

Returns

Emitter

The Emitter instance for method chaining.

Inherited from

EventEmitter.off

Call Signature

off(event): Emitter;

Remove all event handlers for a specified event.

Parameters

event

string

The name of the event for which to remove all handlers.

Returns

Emitter

The Emitter instance for method chaining.

Inherited from

EventEmitter.off

Call Signature

off(): Emitter;

Remove all event handlers for all events.

Returns

Emitter

The Emitter instance for method chaining.

Inherited from

EventEmitter.off

on()

on(event, listener): Emitter;

Parameters

event

string

The name of the event to listen to.

listener

(…arguments_) => void

The function to execute when the event is emitted.

Returns

Emitter

The Emitter instance for method chaining.

Inherited from

EventEmitter.on

once()

once(event, listener): Emitter;

Parameters

event

string

The name of the event to listen to.

listener

(…arguments_) => void

The function to execute once when the event is emitted.

Returns

Emitter

The Emitter instance for method chaining.

Inherited from

EventEmitter.once

removeDefinedName()

removeDefinedName(name, sheetName?): boolean;

Remove a defined name (global or sheet-scoped) from the workbook. This will schedule cells depending on that name for recalculation, but will not perform that recalculation.

Parameters

name

string

the name of the defined name to remove. It will be matched case-insensitively.

sheetName?

the name of a sheet the defined name is scoped to, null if it is a global defined name. It will be matched case-insensitively.

string | null

Returns

boolean

true if the name was removed, false if it did not exist (or the sheet did not exist)

removeSheet()

removeSheet(sheetName): boolean;

Remove a sheet from the workbook.

Parameters

sheetName

string

the name of the sheet to delete. It will be matched case-insensitively.

Returns

boolean

true if the sheet was removed, false if it did not exist

Throws

if there are writes that have not been reset

renameSheet()

renameSheet(currentName, newName): void;

Rename the given sheet to a new name, updating all formulas referencing it Precondition: formula parser has finished importing (formulaParserReady has resolved).

Parameters

currentName

string

newName

string

must be different from currentName

Returns

void

Throws

if no sheet exists named currentName

requireSheet()

requireSheet(sheetName): WorkSheet;

Like getSheet, but throws if the sheet does not exist.

Parameters

sheetName

string

Returns

reset()

reset(): void;

Returns

void

rewriteFormulas()

rewriteFormulas(rewriteFormula, recalcNow): number;

Parameters

rewriteFormula

(formula) => string

recalcNow

boolean = true

If false, skip the trailing recalculate(). The caller is responsible for triggering recalculation after any additional mutations.

Returns

number

number of formulas changed

rowHeight()

rowHeight(rowIndex, sheetName): number;

Height of the given column in the given sheet, in pixels.

Parameters

rowIndex

number

1-based row index

sheetName

string

name of the sheet in which to look up a row height

Returns

number

setColumnWidth()

setColumnWidth(
   sheetName,
   column,
   width): void;

Parameters

sheetName

string

column

number

0-based column index

width

number

non-negative

Returns

void

Throws

if there is no sheet with sheetName in this workbook, or if col and/or width are invalid

setDefinedName()

setDefinedName<AbortOnError>(
   name,
   formula,
   sheet?,
   abortOnError?,
   validateNow?):
  | DefinedName
  | AbortOnError extends true ? null : never;

Add a defined name in this workbook with the given formula. If any error is detected in the formula (syntax error, or use of something we do not support), abort and return null if abortOnError is true, else go ahead and make the defined name but record the error with this.addError.

This method does not create a vertex in the dependency graph. If the graph is already built (i.e. outside initial workbook construction), the caller must call updateDependencies([dn.vertexId]) on the returned DefinedName to register its vertex and outgoing edges.

Type Parameters

AbortOnError

AbortOnError extends boolean = false

Parameters

name

string

formula

string

sheet?

sheet to scope the name to, or null for workbook scope

WorkSheet | null

abortOnError?

AbortOnError

true to return null and make no change if formula has any errors

validateNow?

boolean = true

set to false to skip formula validation here (caller is responsible for it then)

Returns

| DefinedName | AbortOnError extends true ? null : never

setRowHeight()

setRowHeight(
   sheetName,
   rowNum,
   height): void;

Sets the height of a row.

One function, SUBTOTAL, may yield different results depending on whether some cells in the range belong to hidden rows or not. Therefore, if the row becomes hidden, or was hidden before this change, a recalculation of formulas referencing cells in the affected row will be required. This call will register state about that, so that the next recalculation will update formula cells as needed. It is up to the caller to make sure a recalculation is eventually triggered.

Precondition: Workbook is initialized.

Parameters

sheetName

string

rowNum

number

0-based row index

height

number

non-negative

Returns

void

Throws

if there is no sheet with sheetName in this workbook, or if rowNum and/or height are invalid

toCSF()

toCSF(): CSFOutput;

Returns

CSFOutput

toJsf()

toJsf(): JSF;

Serialize this workbook to JSF (JSON Spreadsheet Format).

This is the inverse of Workbook.fromJsf, producing a JSF object that can be used to reconstruct the workbook state.

Returns

JSF

A JSF object representing this workbook

toXlsx()

toXlsx<T>(outputType): Promise<XlsxOutputMap[T]>;

Convert this workbook to XLSX format and return the result as a Buffer.

This method converts the workbook to JSF format first, then generates an XLSX file from that representation.

Type Parameters

T

T extends keyof XlsxOutputMap = "nodebuffer"

Parameters

outputType

T = ...

The output format: 'nodebuffer' (default) or 'arraybuffer'

Returns

Promise<XlsxOutputMap[T]>

A Buffer containing the XLSX file data

toXlsxFile()

toXlsxFile(path): Promise<void>;

Convert this workbook to XLSX format and write it to a file.

This method converts the workbook to JSF format first, then generates an XLSX file from that representation and writes it to the specified path.

Parameters

path

string

The file path to write the XLSX file to

Returns

Promise<void>

unmergeCells()

unmergeCells(sheetName, rangeStr): void;

Unmerge cells on the given sheet. Any existing merge that overlaps the given range is fully removed. If no merges overlap, this is a no-op.

Parameters

sheetName

string

name of the sheet

rangeStr

string

range in A1 notation, e.g. “A1:B2”

Returns

void

Throws

if the sheet does not exist

write()

write(
   ref,
   val,
   neutralizeFormulaOnSingleCellWrite?): boolean;

Write the given value to the given cell (A1 address or global name)

Precondition: Workbook must be initialized

Parameters

ref

the address or global name to write to

string | Reference

val

CellValue

the value to write

neutralizeFormulaOnSingleCellWrite?

boolean = false

set to true if writes to single formula cells should neutralize them

Returns

boolean

true if a write occurred

writeCellData()

writeCellData(cellRef, cellData):
  | Cell
  | DefinedName;

Write the given cell data attributes (.v, .f, etc.) to an existing or new cell.

If cell value is an error value, normalize it to the corresponding singleton error value in errorTable.

If cellData has a formula, it is parsed and (if parsing succeeds) the AST is stored in the cell object.

If cellData has an .f attribute (whether null or not), the whole workbook’s dependency graph is rebuilt (unless you pass false for rebuildDependencyGraph).

Preconditions:

Formula parser has finished importing (formulaParserReady has resolved).
Workbook must be initialized.

Parameters

cellRef

the cell to write, as a Reference or a string representing one. If this indicates a range, the top-left cell of that range is written. This must not be a name reference as this method does not support those yet.

string | Reference

cellData

Omit<Cell, "s" | "f" | "v"> & object & object & object

object with attributes to write to the cell

Returns

| Cell | DefinedName

the existing or new Cell instance.

Throws

if precondition is not satisfied (the formula parser has not finished importing), or if a string is passed and it fails to parse as a Reference.

writes()

writes(): [string, CellValue][];

Precondition: Workbook must be initialized

Returns

[string, CellValue][]

fromCsf()

static fromCsf(
   csf,
   model,
   options): Workbook;

Create a Workbook from CSF format directly, bypassing JSF conversion. This provides significantly better performance than converting CSF→JSF→Workbook.

Parameters

csf

WorkbookCSF

Workbook data in CSF format

model

The Model instance to attach this workbook to

options

WorkbookOptions = {}

Workbook initialization options

Returns

Workbook

A new Workbook instance populated from CSF

fromJsf()

static fromJsf(
   jsf,
   model,
   options): Workbook;

Create a Workbook from JSF format.

Parameters

jsf

JSF

Workbook data in JSF format

model

The Model instance to attach this workbook to

options

WorkbookOptions = {}

Workbook initialization options

Returns

Workbook

A new Workbook instance populated from JSF

fromXlsx()

static fromXlsx(
   data,
   model,
   filename,
options?): Promise<Workbook>;

Create a Workbook from XLSX binary data.

This is a convenience method that wraps @borgar/xlsx-convert to load XLSX data directly into a Workbook without requiring users to manually import the conversion library.

Note: This creates only the main workbook. External references in the XLSX file are not automatically added to the model. Use Model.addWorkbookFromXlsx to also add externals.

Parameters

data

Binary XLSX data (works in browsers and Node.js)

ArrayBuffer | Buffer<ArrayBufferLike> | Uint8Array<ArrayBufferLike>

model

The Model instance to attach this workbook to

filename

string

Filename to associate with the workbook

options?

WorkbookOptions

Workbook initialization options

Returns

Promise<Workbook>

A Promise resolving to a new Workbook instance

fromXlsxFile()

static fromXlsxFile(
   path,
   model,
options?): Promise<Workbook>;

Create a Workbook from an XLSX file path.

This is a convenience method that wraps @borgar/xlsx-convert to load XLSX files directly into a Workbook without requiring users to manually import the conversion library.

Note: This creates only the main workbook. External references in the XLSX file are not automatically added to the model. Use Model.addWorkbookFromXlsxFile to also add externals.

Parameters

path

string

Path to the XLSX file (Node.js only - uses fs)

model