Handsontable 9.0.0: new formula plugin
The new formula plugin is now out! It integrates our powerful calculation engine – HyperFormula® – under the hood, which gives you more features, better performance, and compatibility with Excel and Sheets.
How it worked before
Up to version 8.4.0 there was a formula plugin that was built by us based on two other projects: our own formula parser, and an external library of spreadsheet functions, called formula.js. The problem with this solution was that we couldn’t easily extend or improve it. We also ran into some performance issues which required a completely different approach in the way we parse formulas and build AST, as well as calculate and recalculate input data.
How it works now
In 2018 we started working on a new formula engine. We named it HyperFormula and, once it was ready, published it on GitHub under an open-source license. At that point, we started to incorporate it into Handsontable, which was our ultimate goal from the very beginning.
If you are interested in how HyperFormula works under the hood, we wrote about it on this key concepts page.
The list of features below is merely a sample – for more information please read the guide.
- High-speed formula calculations
- Function syntax compatible with Excel and Google Sheets
- A library of 386 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
Still, the performance of the formula engine was our first priority during the development phase and we did our best to measure it. In each phase of the project, we measured the time required for delivering the output result. To make it really fast we applied multiple techniques:
- Graph of dependencies between cells
- Succinct representation of dependencies between ranges.
- Cycle detection and decomposition of the graph into strongly connected components.
- Smart recalculation after CRUDs
- Recalculating only the cells that depend on the changed cell.
- Lazy update of formulas (one-row insertion/deletion possibly modifies all formulas in the sheet).
- Efficient calculation of involved functions
- Indexes for VLOOKUP.
- Exploiting associativity property of range functions.
- Support for matrices
- Recognition of number matrices.
- GPU accelerated functions: MMULT / MAXPOOL / MEDIANPOOL.
- The compressed representation of data
- Repeating/similar formulas are identified and not stored separately.
- Different approaches for storing sparsely or densely filled sheets.
- Efficient processing of formulas
- We check for repeating and similar formulas before parsing them.
- Handling dates without external libraries.
We also ran the following experiments:
- Using WebWorkers for simultaneous calculation of data in several branches of the dependency tree.
- Using WebAssembly to increase the speed of formula evaluation.
- Using GPU acceleration to speed up calculations in matrix functions like MMULT.
Among these, only GPU acceleration proved useful.
How to use the new formula plugin
Both initialization and usage are now different from what it was before 9.0 so please head to the migration guide before using the new plugin.
How to upgrade
|npm install email@example.com|
Handsontable for React
|npm install firstname.lastname@example.org @email@example.com|
Handsontable for Angular
|npm install firstname.lastname@example.org @email@example.com|
Handsontable for Vue 2
|npm install firstname.lastname@example.org @email@example.com|
We are committed to bringing more features of HyperFormula into the Handsontable formula plugin in the upcoming weeks.
Other fixes and additions in v9.0.0
Below is a complete list of changes in version 9.0.0 released on May 26th, 2021:
- Breaking change: New Formulas plugin, with an entirely different API. See the migration guide for a full list of changes. Removed the required
hot-formula-parserdependency for the sake of an optional one,
- Breaking change: Changed the
beforeAutofillhooks’ signatures. #7987
eslintand eslint-related modules. #7531
fdescribeto restricted globals in test files. #8088
- Fixed a problem with the column indicator of the Collapsible Columns plugin not being displayed properly on styled headers. #7970
- Fixed a problem with duplicated
afterCreateColhooks being triggered after undoing a removal of a column. #8076
- Fixed a problem with formulas not being calculated in certain conditions. #4430
- Fixed a bug with formulas displaying incorrect values after inserting new rows. #4654
- Fixed a problem with the
AVARAGEformula being updated incorrectly. #4675
- Fixed a problem with the
IFformulas not working properly. #5870
- Fixed a bug with using the
clearmethod broke the formulas in the table. #6248
- Breaking change: Removed the deprecated plugins – Header Tooltips and Observe Changes. #8083
- Deprecated the
beforeAutofillInsidePopulatehook. It will be removed in the next major release. #8095
The HyperFormula project was co-financed from European Union funds under the European Regional Development Funds as a part of the Smart Growth Operational Programme. Project implemented as a part of the National Centre for Research and Development: Fast Track.