HyperFormula 1.0.0 has landed

Jakub Wiśniewski Release Notes / July 15, 2021

HyperFormula 1.0.0 has landed

We just rolled out HyperFormula 1.0.0: a JavaScript, headless calculation engine with spreadsheet-like notation, developed by the same team that’s responsible for Handsontable.

Initially planning it as a plugin for Handsontable, we soon realized that what we were developing presented many more possibilities, and we shifted to launching it as a new tool, totally independent of Handsontable.

HyperFormula makes it easier to integrate complex calculation capabilities into your web app without imposing UI limits or restrictions, allowing for faster development of business web apps.

Within our last milestone before the 1.0.0 version, we added array formulas. It’s been a truly great finish of the 3-year-long adventure.

What’s new in 1.0.0

Since version 0.6.0, we have fixed some bugs and added new features. Now, you can:

  • Operate on arrays of values, and pass arrays to scalar functions
  • Quickly convert ranges from the SimpleCellRange type notation to the A1:A2 notation, and vice versa, with the new simpleCellRangeFromString and simpleCellRangeToString helpers.
  • Consistently use all sheet-related methods with sheetId, rather than sheetName

New array features

To let you operate on arrays of values, similarly to Google Sheets and Microsoft Excel, we’ve introduced a new array arithmetic mode. This means that now you can:

  • Perform arithmetic operations on arrays, just like on single values
  • Pass arrays to functions that would normally accept single values
  • Filter arrays, with the new FILTER function
  • Constrain arrays’ sizes, with the new ARRAY_CONSTRAIN function

You can either enable the array arithmetic mode locally with the new ARRAYFORMULA function, or enable it globally with the new useArrayArithmetic configuration option.

Read more about arrays in the HyperFormula documentation.

Because of the array formula changes, we removed the matrix formula notation ({=FORMULA}), removed numerical matrix detection, and changed the naming of some array-related methods, exceptions, and configuration options. For details, see this migration guide and the Breaking changes section below.

Breaking changes

Some of the changes we made modify the way you interact with HyperFormula, so there’s a chance you’ll need to change your application’s code.

To successfully upgrade from version 0.6.x to 1.0.0, see this migration guide.

  • License changes
    We changed the AGPLv3 license to GPLv3, and removed the “free for non-commercial” license.
  • Some methods now take sheetId instead of sheetName
    To make sheet-related methods more consistent, we updated many of them to take sheetId instead of sheetName as an argument. For a detailed list of affected methods, read this GitHub issue.
  • Deprecated matrix formula notation
    Now that HyperFormula supports array formulas, we removed support for the matrix formula notation. If you’re using the {=FORMULA} notation, you’ll need to switch to the new array formulas.
  • Deprecated numerical matrix detection
    To improve and simplify HyperFormula matrices, we removed numerical matrix detection. We also removed the related configuration options: matrixDetection and matrixDetectionThreshold.
  • matrix ⟶ array changes
    To unify the naming of array-related methods, exceptions, and configuration options, we changed the following:
    Before After
    CellType.MATRIX CellType.ARRAY
    matrixColumnSeparator arrayColumnSeparator
    matrixRowSeparator arrayRowSeparator
    matrixMapping arrayMapping
    isCellPartOfMatrix isCellPartOfArray
    SourceLocationHasMatrixError SourceLocationHasArrayError
    TargetLocationHasMatrixError TargetLocationHasArrayError
  • SimpleCellRange type argument
    We updated some methods to take the new SimpleCellRange type argument. For a detailed list of affected methods, see the Release notes: what we changed section below.
  • Array changes
    To let you perform CRUD operations with the new array formulas, we changed the behavior of the setCellContents, addRows, removeRows, addColumns, and removeColumns methods. For details, see the Release notes: what we changed section below.

Release notes: what we changed

  • Breaking change: Changed the APIs of many sheet-related methods to take sheetId instead of sheetName as arguments. (#645)
  • Breaking change: Removed support for the matrix formula notation ({=FORMULA}), as HyperFormula now supports formulas returning arrays of values. (#652)
  • Breaking change: Removed numerical matrix detection, along with the matrixDetection and matrixDetectionThreshold configuration options. (#669)
  • Breaking change: Changed the following methods’ APIs to take the SimpleCellRange type argument (#687):
    • copy
    • cut
    • getCellDependents
    • getCellPrecedents
    • getFillRangeData
    • getRangeFormulas
    • getRangeSerialized
    • getRangeValues
    • isItPossibleToMoveCells
    • isItPossibleToSetCellContents
    • moveCells
  • Breaking change: Changed the AGPLv3 license to GPLv3.
  • Breaking change: Removed the free non-commercial license.
  • Breaking change: Changed the behavior of the setCellContents method, so that it’s possible to override space occupied by spilled arrays. (#708)
  • Breaking change: Changed the behavior of the addRows and removeRows methods, so that it’s possible to add or remove rows across a spilled array, without changing the array size. (#708)
  • Breaking change: Changed the behavior of the addColumns and removeColumns methods, so that it’s possible to add or remove columns across a spilled array, without changing the array size. (#732)
  • Breaking change: Changed CellType.MATRIX to CellType.ARRAY (#747)
  • Breaking change: Changed the following configuration options (#747):
    Before After
    matrixColumnSeparator arrayColumnSeparator
    matrixRowSeparator arrayRowSeparator
  • Breaking change: Changed the following API methods (#747):
    Before After
    matrixMapping arrrayMapping
    isCellPartOfMatrix isCellPartOfArray
  • Breaking change: Changed the following exceptions (#747):
    Before After
    SourceLocationHasMatrixError SourceLocationHasArrayError
    TargetLocationHasMatrixError TargetLocationHasArrayError
  • Changed the SWITCH function, so that it takes an array as its first argument.
  • Changed the TRANSPOSE function, so that it works with data of any type. (#708)
  • Changed the way gpu.js is included, making it even more optional (#753)

Release notes: what we added

  • Added support for array arithmetic. (#628)
  • Added performance improvements for array handling. (#629)
  • Added the ARRAYFORMULA function. (#630)
  • Added the FILTER function. (#668)
  • Added the ARRAY_CONSTRAIN function. (#661)
  • Added casting to scalars from non-range arrays. (#663)
  • Added support for range interpolation. (#665)
  • Added parsing of arrays in formulas (together with respective configuration options for separators). (#671)
  • Added support for the vectorization of scalar functions. (#673)
  • Added support for time in JavaScript Date() objects on the input. (#648)
  • Added validation of API argument types for simple types. (#654)
  • Added named expression handling to engine factories. (#680)
  • Added the getAllNamedExpressionsSerialized method. (#680)
  • Added a utility function for filling a range with source from another range. (#678)
  • Added pretty-print for detailedCellError. (#712)
  • Added the simpleCellRangeFromString and simpleCellRangeToString helpers. (#720)
  • Added CellError to exports. (#736)
  • Added mapping policies to the exports (#747):
    • AlwaysDense
    • AlwaysSparse
    • DenseSparseChooseBasedOnThreshold
  • Added the #SPILL! error type. (#708)
  • Added large tests for CRUD interactions. (#755)

Release notes: what we fixed

  • Fixed an issue with arrays and CRUD operations. (#651)
  • Fixed the handling of arrays for the ROWS and COLUMNS functions. (#677)
  • Fixed an issue with nested named expressions. (#679)
  • Fixed an issue with matrixDetection and number parsing. (#686)
  • Fixed an issue with the NOW and TODAY functions. (#709)
  • Fixed an issue with the MIN and MAX function caches. (#711)
  • Fixed an issue with caching and the order of evaluation. (#735)

Learn more about HyperFormula

The library is written in TypeScript, and it supports major JavaScript frameworks such as Vue, React, and Angular. You can use it in a browser or as a service, or with Node.js.

HyperFormula comes with a library of 386 built-in functions translated into 16 languages, grouped into categories:

The functions use the A1 notation and are compatible with popular spreadsheet software like Microsoft Excel or Google Sheets, which means you can easily transfer your data and formulas between them.

HyperFormula covers most user-triggered actions such as CRUD operations, undo/redo, and clipboard operations. It also supports the use of cross-sheet references, named expressions, different data types, and custom functions.

Demo app

Acknowledgments

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.

European funds logo