JavaScript Data GridFormulas

On this page

• Description
• Options
  • formulas
• Members
  • columnAxisSyncer
  • engine
  • indexSyncer
  • rowAxisSyncer
  • sheetId
  • sheetName
• Methods
  • addSheet
  • destroy
  • disablePlugin
  • enablePlugin
  • getCellType
  • isEnabled
  • isFormulaCellType
  • switchSheet

Description

This plugin allows you to perform Excel-like calculations in your business applications. It does it by an integration with our other product, HyperFormula (opens new window), which is a powerful calculation engine with an extensive number of features.

To test out HyperFormula, see this guide.

Options

formulas

Source code

formulas.formulas : object

The formulas option configures the Formulas plugin.

The Formulas plugin uses the HyperFormula (opens new window) calculation engine. To install HyperFormula (opens new window), 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 (opens new window) instance | A HyperFormula configuration (opens new window) object
sheetId	A number
sheetName	A string

Read more:

• Plugins: Formulas
• Formula calculation
• HyperFormula documentation: Client-side installation (opens new window)
• HyperFormula documentation: Configuration options (opens new window)

Default: undefined
Example

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

// 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'
}

Members

columnAxisSyncer

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

Source code

formulas.engine : HyperFormula | null

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

indexSyncer

Source code

formulas.indexSyncer : IndexSyncer | null

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

rowAxisSyncer

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.

sheetId

Source code

formulas.sheetId : number | null

HyperFormula's sheet id.

sheetName

Source code

formulas.sheetName : string | null

HyperFormula's sheet name.

Methods

addSheet

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

Source code

formulas.destroy()

Destroys the plugin instance.

disablePlugin

Source code

formulas.disablePlugin()

Disables the plugin functionality for this Handsontable instance.

enablePlugin

Source code

formulas.enablePlugin()

Enables the plugin functionality for this Handsontable instance.

getCellType

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

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

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.

switchSheet

Source code

formulas.switchSheet(sheetName)

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

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