Formulas

Options

formulas

Can be set at: grid

Ask AI

Source code

formulas.formulas : object

The formulas option configures the Formulas plugin.

The Formulas plugin uses the HyperFormula calculation engine. To install HyperFormula, read the following:

• Formula calculation: Initialization methods

You can set the formulas option to an object with the following properties:

Property	Possible values
engine	HyperFormula | A HyperFormula instance | A HyperFormula configuration object
sheetId	A number
sheetName	A string
language	A HyperFormula language pack, imported from hyperformula/es/i18n/languages
hyperlinks	true | false (default) | An object with target and schemes

Set hyperlinks to true to render a cell whose formula is HYPERLINK() as a link. The cell keeps its own renderer, and the link label is the value the formula returns. Only a cell whose root expression is HYPERLINK() becomes a link, so a nested call such as =CONCATENATE("see ", HYPERLINK("https://example.com")) renders as plain text.

Set hyperlinks to an object to configure the links: target is '_blank' (default) or '_self', and schemes narrows the allowed URL schemes to a subset of 'http', 'https', 'mailto', and 'tel'.

A link is created only for the http, https, mailto and tel schemes. Any other scheme, javascript: included, renders the label as plain text instead. Press Alt+Enter to open the link of the selected cell. Each link element gets the ht-link and ht-hyperlink classes. To link plain URLs in cell values, see autoLink.

Read more:

• Formula calculation
• HyperFormula documentation: Client-side installation
• HyperFormula documentation: Configuration options

This option can only be set at the grid level. It has no effect when set in the columns, cells, or cell options.

Default: undefined
Example

// either add the `HyperFormula` class
formulas: {
  // set `engine` to `HyperFormula`
  engine: HyperFormula,
  sheetId: 1,
  sheetName: 'Sheet 1'
}

// or, render `HYPERLINK()` formulas as links
formulas: {
  engine: HyperFormula,
  hyperlinks: true
}

// or, open `HYPERLINK()` links in the same tab and link only web URLs
formulas: {
  engine: HyperFormula,
  hyperlinks: {
    target: '_self',
    schemes: ['http', 'https']
  }
}

// or, add a HyperFormula instance
// initialized with the `'internal-use-in-handsontable'` license key
const hyperformulaInstance = HyperFormula.buildEmpty({
  licenseKey: 'internal-use-in-handsontable',
});

formulas: {
  // set `engine` to a HyperFormula instance
  engine: hyperformulaInstance,
  sheetId: 1,
  sheetName: 'Sheet 1'
}

// or, add a HyperFormula configuration object
formulas: {
  // set `engine` to a HyperFormula configuration object
  engine: {
    hyperformula: HyperFormula // or `engine: hyperformulaInstance`
    leapYear1900: false,       // this option comes from HyperFormula
    // add more HyperFormula configuration options
  },
  sheetId: 1,
  sheetName: 'Sheet 1'
}

// use the same HyperFormula instance in multiple Handsontable instances

// a Handsontable instance `hot1`
formulas: {
  engine: HyperFormula,
  sheetId: 1,
  sheetName: 'Sheet 1'
}

// a Handsontable instance `hot2`
formulas: {
  engine: hot1.getPlugin('formulas').engine,
  sheetId: 1,
  sheetName: 'Sheet 1'
}

// set a language pack for the built-in function names and formula syntax
import plPL from 'hyperformula/es/i18n/languages/plPL';

formulas: {
  engine: HyperFormula,
  sheetName: 'Sheet 1',
  language: plPL
}

// update the language at runtime
hot.updateSettings({
  formulas: {
    language: plPL
  }
});

preserveTextValue

Can be set at: grid columns cells cell

Ask AI

Source code

formulas.preserveTextValue : boolean

The preserveTextValue option configures whether the Formulas plugin passes values of text-type cells to the calculation engine as strings. This protects them from number coercion.

By default, the engine parses number-like strings into numbers. A text cell that holds 0123456 reaches formulas as 123456 – the leading zero is lost. With preserveTextValue enabled, the value stays a string: =LEN(A1) returns 7, and concatenation keeps the leading zero.

You can set the preserveTextValue option to one of the following:

Setting	Description
false (default)	The engine parses values of text cells (number coercion)
true	Values of text cells reach the engine as strings

The option takes effect only for cells of the text type – the default cell type, so setting preserveTextValue at the grid level affects every cell that doesn’t declare another type – and only when the Formulas plugin is enabled. Custom cell types aren’t supported, even when they reuse the text editor or renderer.

Set the option globally, per column, or per cell (through the cell option or the cells function).

Read more:

• Formula calculation

Default: false
Since: 18.1.0
Example

columns: [
  {
    type: 'text',
    // keep leading zeros of number-like strings in formula results
    preserveTextValue: true,
  },
],

Members

columnAxisSyncer

Ask AI

Source code

formulas.columnAxisSyncer : AxisSyncer | null

Index synchronizer responsible for syncing the order of HOT and HF’s data for the axis of the columns.

engine

Ask AI

Source code

formulas.engine : HyperFormula | null

The engine instance that will be used for this instance of Handsontable.

indexSyncer

Ask AI

Source code

formulas.indexSyncer : IndexSyncer | null

Index synchronizer responsible for manipulating with some general options related to indexes synchronization.

PLUGIN_KEY

Ask AI

Source code

Formulas.PLUGIN_KEY

Returns the plugin key used to identify this plugin in Handsontable settings.

PLUGIN_PRIORITY

Ask AI

Source code

Formulas.PLUGIN_PRIORITY

Returns the priority order used to determine the order in which plugins are initialized.

rowAxisSyncer

Ask AI

Source code

formulas.rowAxisSyncer : AxisSyncer | null

Index synchronizer responsible for syncing the order of HOT and HF’s data for the axis of the rows.

SETTING_KEYS

Ask AI

Source code

Formulas.SETTING_KEYS

Returns the list of settings keys observed by the plugin for configuration changes.

sheetId

Ask AI

Source code

formulas.sheetId : number | null

HyperFormula’s sheet id.

sheetName

Ask AI

Source code

formulas.sheetName : string | null

HyperFormula’s sheet name.

Methods

addSheet

Ask AI

Source code

formulas.addSheet([sheetName], [sheetData]) ⇒ boolean | string

Add a sheet to the shared HyperFormula instance.

Param	Type	Description
[sheetName]	string null	optional The new sheet name. If not provided (or a null is passed), will be auto-generated by HyperFormula.
[sheetData]	Array	optional Data passed to the shared HyperFormula instance. Has to be declared as an array of arrays - array of objects is not supported in this scenario.

Returns: boolean | string - false if the data format is unusable or it is impossible to add a new sheet to the engine, the created sheet name otherwise.

destroy

Ask AI

Source code

formulas.destroy()

Destroys the plugin instance.

disablePlugin

Ask AI

Source code

formulas.disablePlugin()

Disables the plugin functionality for this Handsontable instance.

enablePlugin

Ask AI

Source code

formulas.enablePlugin()

Enables the plugin functionality for this Handsontable instance.

getCellDependents

Ask AI

Source code

formulas.getCellDependents(address) ⇒ Array

Returns the cells and cell ranges that depend on the provided cell or range (its out-neighbors in HyperFormula’s dependency graph). These are the cells whose formulas reference the given address.

The address argument and the returned coordinates are in HyperFormula’s index space, the same space as hot.getPlugin('formulas').engine. HyperFormula indexes match Handsontable visual indexes only when no rows or columns are trimmed, hidden, moved, or sorted. Named-expression references are returned with a sheet id of -1. The result may include ranges that contain the given address, not only single cells.

Throws:

• Error When address is neither an address nor a range, names a sheet id that does not exist, or is a range whose start and end are on different sheets.

Param	Type	Description
address	object	The cell address { sheet, row, col } or range { start, end }, in HyperFormula’s index space.

Returns: Array - An array of cell addresses and/or ranges in HyperFormula’s index space.

getCellPrecedents

Ask AI

Source code

formulas.getCellPrecedents(address) ⇒ Array

Returns the cells and cell ranges that the provided cell or range depends on (its in-neighbors in HyperFormula’s dependency graph). These are the cells and ranges the given cell’s formula reads.

The address argument and the returned coordinates are in HyperFormula’s index space, the same space as hot.getPlugin('formulas').engine. HyperFormula indexes match Handsontable visual indexes only when no rows or columns are trimmed, hidden, moved, or sorted. Named-expression references are returned with a sheet id of -1. The result may include ranges contained in the given cell or range, not only single cells.

Throws:

• Error When address is neither an address nor a range, names a sheet id that does not exist, or is a range whose start and end are on different sheets.

Param	Type	Description
address	object	The cell address { sheet, row, col } or range { start, end }, in HyperFormula’s index space.

Returns: Array - An array of cell addresses and/or ranges in HyperFormula’s index space.

getCellType

Ask AI

Source code

formulas.getCellType(row, column, [sheet]) ⇒ string

Get the cell type under specified visual coordinates.

Param	Type	Description
row	number	Visual row index.
column	number	Visual column index.
[sheet]	number	optional The target sheet id, defaults to the current sheet.

Returns: string - Possible values: ‘FORMULA’ | ‘VALUE’ | ‘ARRAYFORMULA’ | ‘EMPTY’.

isEnabled

Ask AI

Source code

formulas.isEnabled() ⇒ boolean

Checks if the plugin is enabled in the handsontable settings. This method is executed in Hooks#beforeInit hook and if it returns true then the Formulas#enablePlugin method is called.

isFormulaCellType

Ask AI

Source code

formulas.isFormulaCellType(row, column, [sheet]) ⇒ boolean

Returns true if under specified visual coordinates is formula.

Param	Type	Description
row	number	Visual row index.
column	number	Visual column index.
[sheet]	number	optional The target sheet id, defaults to the current sheet.

registerShortcuts Deprecated

Ask AI

Source code

formulas.registerShortcuts()

Caution

Since 19.0.0. The `Alt`+`Enter` shortcut that opens a cell's link is a core grid shortcut now, registered for every grid, so the plugin has nothing to register. The method does nothing and will be removed in 19.0.0.

Deprecated. The `Alt`+`Enter` shortcut that opens a cell's link is a core grid shortcut now, registered for every grid, so the plugin has nothing to register. This method is a no-op.

switchSheet

Ask AI

Source code

formulas.switchSheet(sheetName)

Switch the sheet used as data in the Handsontable instance (it loads the data from the shared HyperFormula instance).

The engine’s serialized content keeps the escape apostrophe that dates and preserved text values were written with, so it is unescaped before the load – otherwise the apostrophe becomes part of the grid’s data.

The unescaping has to run BEFORE loadData, because afterwards the apostrophe is already part of the grid’s data, past every reader that could tell it apart from a user’s own leading apostrophe.

The two cases are unescaped differently. A RELOAD of the sheet this grid is already synced to (what #onAfterCellMetaReset performs on the empty-data branch) is confirmed against the grid’s own source data – see #unescapeAgainstSourceData – so it survives the escaping configuration being turned off between the write and the reload.

A switch to a genuinely DIFFERENT sheet has no such reference: the grid’s data belongs to the sheet being left. It is confirmed against the cell meta instead, with an accepted limitation – that sheet’s layout has no relation to this grid’s index maps, so a physically-keyed meta layer (the cell array, or a column-level one under a non-identity column map) can be matched against the wrong cell. Only the global settings layer is layout-independent and always matches.

Param	Type	Description
sheetName	string	Sheet name used in the shared HyperFormula instance.

unregisterShortcuts Deprecated

Ask AI

Source code

formulas.unregisterShortcuts()

Caution

Since 19.0.0. The `Alt`+`Enter` shortcut that opens a cell's link is a core grid shortcut now, registered for every grid, so the plugin has nothing to unregister. The method does nothing and will be removed in 19.0.0.

Deprecated. The `Alt`+`Enter` shortcut that opens a cell's link is a core grid shortcut now, registered for every grid, so the plugin has nothing to unregister. This method is a no-op.