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?

string | null

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

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

string | Reference

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

Returns

boolean

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

columnWidth()

columnWidth(columnIndex, sheetName): number;

Width of the given column in the given sheet, in pixels. When no span covers the column, falls back to the sheet’s default column width if set (sheet.defaults.colWidth), otherwise to the Excel default (EXCEL_DEFAULT_COLUMN_WIDTH_PX, 65).

Parameters

columnIndex

number

1-based column index

sheetName

string

name of the sheet in which to look up a column width

Returns

number

Throws

if the sheet does not exist

See

Workbook.rowHeight (the same precedence chain on the row axis).

copySheet()

copySheet(sheetName, newName?): WorkSheet;

Copy a sheet and insert the copy immediately after the source.

Parameters

sheetName

string

the name of the sheet to copy (matched case-insensitively)

newName?

string

name for the copy; if omitted, generates one following Excel’s convention (e.g. “Sheet1 (2)“)

Returns

the new sheet

Throws

if the source sheet does not exist, the new name is invalid or already taken, or there are writes that have not been reset

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

editCell()

editCell(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.

Preconditions:

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

Parameters

cellRef

string | Reference

the cell to edit, as a Reference or a string representing one. If this indicates a range, the top-left cell of that range is edited. May also be a name reference (defined name).

cellData

CellData

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.

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?

number | null

the index of a sheet

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?

string | null

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.

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.

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;

Move the cells in the from range to the to range, preserving cell values, formulas, styles, comments, and notes. References inside the moved formulas are adjusted to track their new positions, and tables fully contained within from move with the cells.

Returns a RewriteFormula function that adjusts references to the moved cells in formulas elsewhere in the workbook (or in any other workbook). The caller is responsible for applying it via Workbook.rewriteFormulas for any workbook whose formulas may reference the moved cells; this method does not invoke it automatically.

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

Parameters

from

string | A1Reference

Must be the same dimensions as to

to

string | A1Reference

Must be the same dimensions as from

recalcNow?

boolean = true

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

Returns

A RewriteFormula function for adjusting references to the moved cells.

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?

string | null

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.

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

reorderSheet()

reorderSheet(sheetName, newIndex): void;

Move a sheet to a new position in the workbook.

Parameters

sheetName

string

the name of the sheet to move (matched case-insensitively)

newIndex

number

the target zero-based position

Returns

void

Throws

if no sheet exists with that name, index is out of bounds, or there are writes that have not been reset

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;

Rewrite every formula cell’s formula string by passing it through the given transform; cells whose rewriteFormula(formula) return value differs from the original are updated and their dependency-graph edges rebuilt.

Parameters

rewriteFormula

(formula) => string

Transform applied to each formula. Returning the input unchanged (by === identity or string equality) is treated as “no change” and skips the cell.

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 row in the given sheet, in pixels. When no span covers the row, falls back to the sheet’s default row height if set (sheet.defaults.rowHeight), otherwise to the Excel default (EXCEL_DEFAULT_ROW_HEIGHT_PX, 16).

Parameters

rowIndex

number

1-based row index

sheetName

string

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

Returns

number

Throws

if the sheet does not exist

See

Workbook.columnWidth (the same precedence chain on the column axis).

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?

WorkSheet | null

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

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;

Returns

JSF

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?, options?): Promise<XLSXOutputMap[T]>;

Type Parameters

T

T extends keyof XLSXOutputMap = "nodebuffer"

Parameters

outputType?

T

options?

Returns

Promise<XLSXOutputMap[T]>

toXLSX()

toXLSX<T>(outputType?, options?): 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'

options?

Optional settings (e.g. compression level)

Returns

Promise<XLSXOutputMap[T]>

A Buffer containing the XLSX file data

toXlsxFile()

toXlsxFile(path, options?): Promise<void>;

Parameters

path

string

options?

Returns

Promise<void>

toXLSXFile()

toXLSXFile(path, options?): 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

options?

Optional settings (e.g. compression level)

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

string | Reference

the address or global name to write to

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;

Parameters

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;

Parameters

jsf

JSF

model

options?

WorkbookOptions = {}

Returns

Workbook

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>;

Parameters

data

| ArrayBuffer | Buffer<ArrayBufferLike> | Uint8Array<ArrayBufferLike>

model

filename

string

options?

Returns

Promise<Workbook>

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

| ArrayBuffer | Buffer<ArrayBufferLike> | Uint8Array<ArrayBufferLike>

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

model

The Model instance to attach this workbook to

filename

string

Filename to associate with the workbook

options?

Workbook initialization options

Returns

Promise<Workbook>

A Promise resolving to a new Workbook instance

fromXlsxFile()

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

Parameters

path

string

model

options?

Returns

Promise<Workbook>

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

The Model instance to attach this workbook to

options?