HyperFormula Integration

We have created a migration guide for developers upgrading to v9. If you use the formula plugin please make sure you carefully read it before upgrading.

Overview

The Formulas plugin provides extensive calculation capabilities based on formulas using the spreadsheet notation. Under the hood, it uses an engine called HyperFormula created by the Handsontable team as an independent library to help developers build complex data management apps.

This plugin comes with a library of 386 functions grouped into categories, such as Math and trigonometry, Engineering, Statistical, Financial, and Logical. Using these, you can create complex data entry rules in business apps and much more. Below are some ideas on what you can do with it:

  • Fully-featured spreadsheet apps
  • Smart documents
  • Educational apps
  • Business logic builders
  • Forms and form builders
  • Online calculators
  • Low connectivity apps

Features:

  • High-speed formula calculations
  • Function syntax compatible with Excel and Google Sheets
  • A library of built-in functions available in 16 languages
  • Support for wildcard characters
  • Support for CRUD operations
  • Support for cross-sheet references
  • Support for multiple Handsontable instances
  • Uses GPU acceleration for better performance

Known limitations:

  • Doesn't work with nested rows
  • Doesn't work with undo/redo

Available options and methods

For the list of available settings and methods, visit the API reference.

Available functions

This plugin inherits the calculation powers from HyperFormula. The complete functions reference can be found in the HyperFormula documentation.

Basic multi-sheet example

It is possible to use the plugin in single sheet mode or multiple Handsontable instances with cross-sheet references.

Double click on a cell to open the editor and preview the formula.


Data grid example

This example is more typical of data grids than spreadsheets. Calculations are present in two places – in the column "Total due (fx)" and in the summary row at the bottom.

Initialization methods

There're multiple ways of initializing the plugin. You can select the most convenient one depending on your use case.

In all cases, it is required to either pass in the HyperFormula object or a HyperFormula instance:

import { HyperFormula } from 'hyperformula';

There are also other installation methods available. Check out HyperFormula's installation documentation.

Passing the HyperFormula class/instance to Handsontable

{
  formulas: {
    engine: HyperFormula,
    // [plugin configuration]
  }
}

or

{
  formulas: {
    engine: {
      hyperformula: HyperFormula, // or `engine: hyperformulaInstance`
      leapYear1900: false,
      binarySearchThreshold: 15,
      // ...and more engine configuration options. See https://handsontable.github.io/hyperformula/api/interfaces/configparams.html#number
    },
    // [plugin configuration]
  }
}

Single Handsontable instance with an external HyperFormula instance

const hyperformulaInstance = HyperFormula.buildEmpty({})

{
  formulas: {
    engine: hyperformulaInstance
  }
}

Multiple independent Handsontable instances

// Instance 1
{
  formulas: {
    engine: HyperFormula,
    // [plugin configuration]
  }
}

// Instance 2
{
  formulas: {
    engine: HyperFormula,
    // [plugin configuration]
  }
}

Multiple Handsontable instances with a shared HyperFormula instance

// Instance 1
{
  formulas: {
    engine: HyperFormula,
    sheetName: 'Sheet1'
    // [plugin configuration]
  }
}

// Instance 2
{
  formulas: {
    engine: hot1.getPlugin('formulas').engine,
    sheetName: 'Sheet2'
    // [plugin configuration]
  }
}

Multiple Handsontable instances with an external shared HyperFormula instance

const hyperformulaInstance = HyperFormula.buildEmpty({});

// Instance 1
{
  formulas: {
    engine: hyperformulaInstance,
    sheetName: 'Sheet1'
    // [plugin configuration]
  }
}

// Instance 2
{
  formulas: {
    engine: hyperformulaInstance,
    sheetName: 'Sheet2'
    // [plugin configuration]
  }
}

`afterFormulasValuesUpdate` hook

This hook listens for any changes to cells in the calculation engine, including dependent cells containing formulas.

const afterFormulasValuesUpdate = (changes) => {
  changes.forEach((change) => {
    console.log('change', change.address, change.newValue)
  })
}

new Handsontable(element, {
  formulas: {
    engine: HyperFormula
  },
  afterFormulasValuesUpdate
})

Named expressions

You can use custom-named expressions in your formula expressions. A named expression can be either plain values or formulas with references to absolute cell addresses. To register a named expression, pass an array with name and expression to your formulas configuration object:

For more information about named expressions, please refer to the HyperFormula docs.

View the explainer video

Learn more

Edit this page

Tutorial: HyperFormula Integration