handsontable / hyperformula

HyperFormula is an open-source headless spreadsheet for business web apps. It comes with over 400 formulas, CRUD operations, undo-redo, clipboard support, and sorting. Built in TypeScript, supported by the Handsontable Team.

Handsontable is a JavaScript component that combines data grid features with spreadsheet-like UX.
It provides data binding, data validation, filtering, sorting, and CRUD operations.

---

Get started with Handsontable

React

Angular

Vue

Vue 3

JavaScript

---

Features

The most popular features of Handsontable:

  ✓  Multiple column sorting
  ✓  Non-contiguous selection
  ✓  Filtering data
  ✓  Export to file
  ✓  Validating data
  ✓  Conditional formatting
  ✓  Merging cells
  ✓  Freezing rows/columns
  ✓  Moving rows/columns
  ✓  Resizing rows/columns
  ✓  Hiding rows/columns
  ✓  Context menu
  ✓  Comments

Documentation

• Developer guides
• API Reference
• Changelog
• Demo

Get started

1. Install Handsontable

Using a package manager

Get Handsontable from npm, Yarn or NuGet.

npm install handsontable

import Handsontable from 'handsontable';

import 'handsontable/dist/handsontable.full.min.css';

Using a CDN

<script type="text/javascript" src="https://cdn.jsdelivr.net/npm/handsontable/dist/handsontable.full.min.js"></script>

<link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/handsontable/dist/handsontable.full.min.css" />

2. Create a container

<div id="example"></div>

3. Initialize your grid

const container = document.querySelector('#example');
const hot = new Handsontable(container, {
  data: [
    ['', 'Tesla', 'Volvo', 'Toyota', 'Ford'],
    ['2019', 10, 11, 12, 13],
    ['2020', 20, 11, 14, 13],
    ['2021', 30, 15, 12, 13]
  ],
  rowHeaders: true,
  colHeaders: true,
  licenseKey: 'non-commercial-and-evaluation' // for non-commercial use only
});

Support

We provide support for developers working with commercial version via contact form or at [email protected].

If you use a non-commercial version then please ask your tagged question on StackOverflow.

License

Handsontable is a commercial software with two licenses available:

• Free for non-commercial purposes such as teaching, academic research, and evaluation. Read it here.
• Commercial license with support and maintenance included. See pricing plans.

License key

If you use Handsontable in a project that supports your commercial activity, then you must purchase the license key at handsontable.com.

If you use the free for non-commercial license of Handsontable, then pass the phrase 'non-commercial-and-evaluation', as described in this documentation.

Proudly created and maintained by the Handsontable Team.

hyperformula's Issues

Set cell value methods

Description

I would remove the need for two different methods. On setCellsContents will do. If someone has a single cell value to set, she would use an array of arrays argument. Simpler API, with single isItPossibleToChangeContent. It should be clearer and more readable. WDYT?

API proposal

public setCellsContents(cellAddress: SimpleCellAddress, values: CellValue[][]): CellValueChange[];
public isItPossibleToChangeContent(cellAddress: SimpleCellAddress, width = 1, height = 1): boolean;

or

public isItPossibleToChangeContent(cellAddress: SimpleCellAddress, values: CellValue[][]): boolean;

and read the width and height of the data?

Previous API

public setCellContent(cellAddress: SimpleCellAddress, value: CellValue): CellValueChange[];
public setMultipleCellContents(cellAddress: SimpleCellAddress, values: CellValue[][]): CellValueChange[];
public isItPossibleToChangeContent(cellAddress: SimpleCellAddress): boolean;

Note the missing method to check if it is possible to change multiple cells contents.

Spreadsheet data types

⚠️ WIP

Description

There are four data types recognized by spreadsheet software [4]:

• Logical (aka Boolean): TRUE and FALSE
• Number: integer, float but also percentage, date, time and duration (which is tricky [1])
• Label (aka String)
• Array
• Error

4.1 General

• 4.2 Text (String)
• 4.3 Number
  • 4.3.1 General
  • 4.3.2 Time
  • 4.3.3 Date
  • 4.3.4 DateTime
  • 4.3.5 Percentage
  • 4.3.6 Currency
  • 4.3.7 Logical (Number)
• 4.4 Complex Number
• 4.5 Logical (Boolean)
• 4.6 Error
• 4.7 Empty Cell
• 4.8 Reference
• 4.9 ReferenceList
• 4.10 Array
• 4.11 Pseudotypes
  • 4.11.1 General
  • 4.11.2 Scalar
  • 4.11.3 DateParam
  • 4.11.4 TimeParam
  • 4.11.5 Integer
  • 4.11.6 TextOrNumber
  • 4.11.7 Basis
  • 4.11.8 Criterion
  • 4.11.9 Database
  • 4.11.10 Field
  • 4.11.11 Criteria
  • 4.11.12 Sequences (NumberSequence, NumberSequenceList, DateSequence, LogicalSequence, and ComplexSequence)
  • 4.11.13 Any

References:
[1] https://stackoverflow.com/questions/17715841/how-to-read-the-correct-time-duration-values-from-google-spreadsheet
[2] http://www.hep.by/gnu/gnumeric/sect-data-types.shtml
[3] https://help.gnome.org/users/gnumeric/stable/quick-data.html.en
[4] https://support.office.com/en-us/article/TYPE-function-45B4E688-4BC3-48B3-A105-FFA892995899
[5] https://help.gnome.org/users/gnumeric/stable/sect-configuration-localization.html.en
[6] https://support.office.com/en-us/article/Data-types-in-Data-Models-E2388F62-6122-4E2B-BCAD-053E3DA9BA90
[7] https://docs.oasis-open.org/office/v1.2/os/OpenDocument-v1.2-os-part2.html#Infix_Operator_MINUS
[8] https://www.ablebits.com/office-addins-blog/2014/12/10/excel-not-equal-to-greater-than-less-than/

Better README.md

Description

Readme will be published to NPM, we should move technical details to documentation and rework the readme.md for an upcoming release. We can use the table of content from HoT:

• Logo
• Description and badges
• Screenshot? 🤔
• Installation
• Usage
• Features
• Documentation
• Support and contribution
• Browsers compatibility
• License
• Credits

Work in progress is here: https://docs.google.com/document/d/1YXckeIczaB24QrsFGdNR5JxeXJjuKxdRUX5Qh2rjg8o/edit

Missing tests for numerical values of TRUE and FALSE

Description

Works as expected. But there is no explicit test for this use case.

Add tests

FALSE

it('has a numerical value of 0', () => {
  const engine = HyperFormula.buildFromArray([['=FALSE()*8']])

  expect(engine.getCellValue(adr('A1'))).toEqual(0)
})

TRUE

it('has a numerical value of 1', () => {
  const engine = HyperFormula.buildFromArray([['=TRUE()*8']])

  expect(engine.getCellValue(adr('A1'))).toEqual(8)
})

Resources

https://www.lifewire.com/boolean-value-logical-value-3123388

Add build formats and set up package.json fields for CommonJS, UMD and ES

Description

Before we release HyperFormula we should set up the correct builds for NPM and CDN ecosystem.

• Create UMD build, set as JSDelivr and UnPKG fields
  • with minified version
  • add sourcemaps
• Create CommonJS build and set it as main field
• Create ES build and set it as module field
• Create typescript definitions and set them as typings field

package.json

  "main": "commonjs/index.js",
  "module": "es/index.js",
  "jsnext:main": "es/index.js",
  "jsdelivr": "dist/hyperformula.min.js",
  "unpkg": "dist/hyperformula.min.js",

Nested functions depth

Description

Similar desktop spreadsheet application supports up to 64 levels for nested function. We should check how many we can support, add a test and document the limitation.

Create a CHANGELOG.md file

Description

Let us start with a basic changelog. We're going to introduce it in HOT, but its a chore to add it later. If we do it now, it will be easier to create release notes later.

Cleanup R&D tools before we release the lib

Description

• Remove CsvImporter i CsvExporter and their dependancies
• Hide or remove from the runtime this.stats.measure, this.dependencyGraph.stats.start i this.dependencyGraph.stats.end

CSV tools

Methods and their dependencies are not necessary for library users. Those should be moved to devDependancies and put together with tests or moved outside of this repo.

Stats

Unless they are useful for the end-user to debug the performance of her worksheets (we should make that optional anyway) we should remove those from the runtime.

Cell range value and formulas getters

Description

There is getValues method currently but we should extend this API to support both values and formulas like we did with getCellValue and getCellFormula.

Both methods are necessary to export the data: file export, server-side save, copy & paste.

API proposal

public getRangeValues(sheet: number: range: Range = /* default to everything */): CellValue[][];
public getRangeFormulas(sheet, number, range: Range = /* default to everything */): CellValue[][];

Batch operations

Description

There is no point in evaluating every CRUD operation, every data change if we know that more fields are going to change their values. Formulas evaluation should be triggered at the end of the process.

Sometimes developers want to trigger recalculation only on demand. I.e. by the user pressing a button within their application. This is similar to other spreadsheet software when the user (or developer) can switch operation mode when she expects many time-consuming worksheets.

Smart recalculation

The default operation mode of Handsontable.

batch method

By @bardek8

engine.batch(function () {
 engine.addRows();
 engine.addColumns();
});

But it would break the operations if it's not possible to apply one of them. A user wouldn't know which one was applied and which failed.

Manual calculation mode

In manual calculation mode all CRUD operations do not trigger calculations.

We can do it through API:

• Manually trigger smart recalculate recalculate()
• Full calculation of all the formulas recalculate(force = true)
• Complete rebuild of the dependencies and a full calculation rebuildAndRecalculate()

And add keyboard shortcuts that are familar to our users. UI / Handsontable

Formula Format

Description

Basic rules:

• One formula per file.
• Categorized in subdirectories.
• Translations in the translations folder as a single file.

Structure

• Formula structure
  • Formula files are stored in /src/interpreter/plugin folder
  • Formula files may be stored in category subfolders (optionaly, to avoid 500 files in a single dir): /src/interpreter/plugin/financial
  • Single formula (formula class) per file, file name is the formula ID followed by Plugin suffix: SumPlugin.ts
• Internationalization structure
  • Language files stored in /src/i18n folder
  • Single language per file, file name is the language code: plPL.ts

Formula format proposal

/src/interpreter/interpreter.ts

type FormulaType = (ast: ProcedureAst, address: SimpleCellAddress) => CellValue;

const formulaCache: Map<string, FormulaType> = new Map();

export function getFormula(id: string): FormulaType {
   return formulaCache.get(id);
}

export function registerFormula(id: string, formula: FormulaType) {
   formulaCache.set(id, formula);
}

/src/interpreter/plugin/mathematics/SumPlugin.ts

import { evaluateEst, dependencyGraph, columnSearch, config } from '../Interpreter'


export function SumFormula(ast: ProcedureAst, address: SimpleCellAddress): CellValue {
    // SUM implementation
}

Hyperformula.registerFormula('SUM', SumFormula);

Custom formulas

Moved to #37

Async functions support

Description

Common request for our previous formula engine. Let's keep it as a feature request.

Sample

parser.setFunction('SUM_ASYNC', (params) => {
  return new Promise((resolve) => {
    return setTimeout(() => resolve(params[0] + params[1]), 1000));
  }
});

parser.parse('SUM_ASYNC(2, 3)').then((result, error) => { });

Support coercion of string with percent sign into number

Description

Mentioned by @swistak35 in #6 coercion to string with percent sign into number will not work correctly in some (all?) cases.

Test that will not pass

it('has a numerical value of 1 and works with %', () => {
  const engine = HyperFormula.buildFromArray([['=TRUE()%*1']])

  expect(engine.getCellValue(adr('A1'))).toEqual(0.01)
})

Export version and build date as static properties

Description

Testing, debugging and sometimes production apps will definitely use this to verify that the correct version of the engine is used and the necessary features are available.

Proposed properties

public static version: string;
public static buildDate: string;

References

Ask @jansiegel how this can be done. We're already exporting version for Handsontable and our wrappers so this is a standard for use and a good practice.

Add numeric separator as a configuration option

This issue was extracted from #36. And rewritten, Cultures are not necessary.

Description

We already support dateFormats and two callbacks to support more dates. We need to introduce similar functionality for numbers to the config object #58.

New config options

decimalSeparator: ".",
thousandSeparator: ",",

Add a method to destroy engine instance

Description

It's always good to clean after ourselves ✨ This request is about destroy public method that will cleanup memory allocated by HyperFormula nad internal cache/variables.

API proposal

public destroy(): void;

Create a COPYING file with license terms (GNU GPL 3+)

Description

We're missing a very important file here. Add LICENSE.txt file.

There is no API to replace sheet content

Description

We might use setMultipleCellsContents for this but it won't clear the remaining worksheet cells. It will be necessary to always get sheet dimensions and generate changes with empty cells which I think can be done inside the engine more efficient and less error-prone.

API proposal

public isItPossibleToSetSheetContent(sheet: string): boolean;
public setSheetContent(sheet: string, values: CellValue[][]): CellValueChange[];

Add CODE OF CONDUCT.md

Description

I hope this one can be copied 1:1 from https://github.com/handsontable/handsontable/blob/master/CODE_OF_CONDUCT.md 🙏

Move the code to Handsontable organization

Description

It would be easier for us to code review and plan tasks if we could link to the source code fragments. Github has great tools for this.

We can either work on this repository or sync it daily.

Cell helpers: hasValue, hasFormula, getType

Description

It's often useful to check if the given cell has value or formula. We should add simple helpers to optimize these calls.

Additionally, it will be necessary to check the cell type that engine recognized for a given cell.

API proposal

public getCellType(cellAddress: SimpleCellAddress): CellType;
public hasCellValue(cellAddress: SimpleCellAddress): boolean;
public hasCellFormula(cellAddress: SimpleCellAddress): boolean;

Public API proposal

Description

A proposal for a public API. It will be easier to discuss it here.

Requirements

• Sheets are sometimes used as a number, sometimes as a string. This should be unified.
• addSheet` method should not generate an ID. Instead, it should accept a name as an argument. This way we can name it the same as Handsontable instance and use this for multiple worksheets support.
• Add general-purpose and lifecycle methods: destroy, updateConfig
• Add better formula support: registerFormula, getFormula, listAllFormulas
• Add better language support: registerLanguage, getLanguage, listAllLanguages
• Add helpers for formulas: calculateFormula, validateFormula, normalizeFormula
• Add value accesors: getCellValue, getCellFormula, getRangeValues, getRangeFormulas
• Add helpers for address translations
• Add events to notify about graph / value changes: valueUpdated, formulaUpdated
• Remove methods that are unnecessary as a public API: getSheetDimensions, getSheetsDimensions, getStats, forceApplyPostponedTransformations, disableNumericMatrices, getSheetsDimensions, getSheetDimensions
• isItPossibleToChangeContent is a neat idea, but it will work only for a single cell in cooperation with setCellContent. How do we validate if we can proceed with setMultipleCellContents ?
• Export all enums, types and errors as statics so they are availble for developers
• API methods addColumns and removeColumns have inconsistent signatures. Same for addRow, removeRow, isItPossibleToAddColumns, isItPossibleToAddColumns.

API for V1

• Constructor

  public static buildFromSheets(sheets: Sheets, config?: Config): HyperFormula;
  public static buildFromArray(sheet: Sheet, config?: Config): HyperFormula;
  public static buildEmpty(config?: Config): HyperFormula;
  
  public constructor(config?: Config): HyperFormula;

• Destroy instance #45

  public destroy(): void;

• Sheet operations #26 / #28

  public addSheet(name: string): string;
  public removeSheet(name: string);
  public isItPossibleToAddSheet(name: string): boolean;
  public isItPossibleToRemoveSheet(name: string): boolean;

• Sheet helpers #32

  public sheetId(sheetName: string): number | undefined;
  public sheetName(sheetId: number): string | undefined;
  public doesSheetExist(sheetName: string): boolean;
  public numberOfSheets(): number;
  public renameSheet(sheetId: number, newName: string): void;

• Cell getters

  public getCellValue(address: SimpleCellAddress): CellValue; 
  public getCellFormula(address: SimpleCellAddress): CellFormula;
  public getCellSerialized(address: SimpleCellAddress): CellValue;

• Cells value setters #31

  public setCellsContents(cellAddress: SimpleCellAddress, values: CellValue[][]): CellValueChange[];
  public isItPossibleSetCellContents(cellAddress: SimpleCellAddress, width = 1, height = 1): boolean;

• Sheet set content #33

  public isItPossibleToSetSheetContent(sheet: string): boolean;
  public setSheetContent(sheet: string, values: CellValue[][]): CellValueChange[];

• Cell helpers #30

  public getCellType(address: SimpleCellAddress): CellType
  public doesCellHaveSimpleValue(address: SimpleCellAddress): boolean 
  public doesCellHaveFormula(address: SimpleCellAddress): boolean
  public isCellEmpty(address: SimpleCellAddress): boolean 
  public isCellPartOfMatrix(address: SimpleCellAddress): boolean 
  public getCellValueType(address: SimpleCellAddress): CellValueType

• Create / remove row / column with noncontigous support #29

  type Index  = [number, number] // [startIndex, amount]
  
  public addRows(sheet: number, ...indexes: Index[]): CellValueChange[]
  public removeRows(sheet: number, ...indexes: Index[]): CellValueChange[]
  public addColumns(sheet: number, ...indexes: Index[]): CellValueChange[]
  public removeColumns(sheet: number, ...indexes: Index[]): CellValueChange[]
  
  public isItPossibleToAddRows(sheet: number, ...indexes: Index[]): CellValueChange[]
  public isItPossibleToRemoveRows(sheet: number, ...indexes: Index[]): CellValueChange[]
  public isItPossibleToAddColumns(sheet: number, ...indexes: Index[]): CellValueChange[]
  public isItPossibleToRemoveColumns(sheet: number, ...indexes: Index[]): CellValueChange[]

• Address translation helpers #22

  public simpleCellAddressToString(address: SimpleCellAddress, sheet: number): string
  public simpleCellAddressFromString(stringAddress: string, sheet: number): SimpleCellAddress

• Batch operations #18

  public suspendEvaluation(): void
  public resumeEvaluation(): void
  public isEvaluationSuspended(): boolean
  public batch(callback: function): CellValueChange[]
  public rebuildAndRecalculate(): void

• Export version and buildDate from package.json #19

  static version: string; // semver
  static buildDate: string;

• moveRows / moveColumns / moveSheets methods #29

  public moveRows(sheet: number, ... ? ...): CellValueChange[]
  public isItPossibleToMoveRows(sheet: number, ... ? ...): CellValueChange[]
  
  public moveColumns(sheet: number, ... ? ...): CellValueChange[]
  public isItPossibleToMoveColumns(sheet: number, ... ? ...): CellValueChange[]

• Formulas helpers #24

   public calculateFormula(formula: string): any;
   public validateFormula(formula: string): boolean; 
   public normalizeFormula(formula: string): string;

• Events #135

  public on(eventName: string | enum, callback): void;
  public once(eventName: string | enum, callback): void;
  public off(eventName: string | enum, callback): void;

• Named Expressions #239

   public addNamedExpression(name: string, value, scope, { comment, visibility })
   public removeNamedExpression(name: string, scope: string)
   public updateNamedExpression(name: string, value, scope, { comment, visibility })
   public getNamedExpressionValue(name string, scope: string)
   public getNamedExpressionFormula(name: string, scope: string)
   public listAllNamedExpressions(scope?: string)

• Get all sheet data #178

  public getAllSheetsFormulas(): Record<string, Maybe<string>[][]>
  public getAllSheetsValues(): Record<string, CellValue[][]>
  public getAllSheetsSerialized(): Record<string, CellValue[][]>
  
  public getSheetSerialized(sheet: number): CellValue[][]
  public getSheetFormulas(sheet: number): Maybe<string>[][]
  public getSheetValues(sheet: number): CellValue[][]

• Range getters #34

  public getRangeValues(range: AbsoluteCellRange): CellValue[][]
  public getRangeFormulas(range: AbsoluteCellRange): CellValue[][]
  public getRangeSerialized(range: AbsoluteCellRange): CellValue[][]

• Update configuration #117

  public updateConfig(config: ConfigParams): void
  public getConfig(): ConfigParams

• Custom formulas API #37

  public static registerFormula(formula: Formula): void
  public static getFormulas(): string[]

• Custom languages API #36

  public static registerLanguage(code: string, lang: RawTranslationPackage): void
  public static getLanguages(code: string): TranslationPackage
  public static getRegisteredLanguagesCodes(): string[]
  public static unregisterLanguage(code: string): void

Worksheet reference

Current notation is =$Sheet1.A1, while the more popular is using exclamation mark =Sheet1!A1 and does support spaces ='Sheet number two'!B4

We need:

• test for spaces in the sheet name
• test for special characters in the sheet name
• configuration option for the separation dot/exclamation mark

FALSE() and TRUE() functions aliases are missing

Description

For compatibility reasons functions =TRUE() and =FALSE() should allow to leave off the parenthesis. =TRUE and =FALSE should be an alias to the corresponding function. Supported by Excel and Google Spreadsheet.

Add tests

TRUE

it('works without parenthesis', () => {
  const engine = HyperFormula.buildFromArray([['=TRUE']])

  expect(engine.getCellValue(adr('A1'))).toEqual(true)
})

FALSE

it('works without parenthesis', () => {
  const engine = HyperFormula.buildFromArray([['=FALSE']])

  expect(engine.getCellValue(adr('A1'))).toEqual(false)
})

Reference

https://support.office.com/en-us/article/true-function-7652c6e3-8987-48d0-97cd-ef223246b3fb

Worksheet helpers and name to id translation

Description

Mainly our API operates on worksheet numerical ID. Which is OK, but as a developer in Handsontable we have reference to the sheet kept and used mostly as a string name. To integrate those two worlds we will need to read Hyperformula name to id mapping.

The best option here is to add name translation methods:

API proposal

public getSheetID(sheetName: string): number;
public getSheetName(sheetID: number): string;
public updateSheet(sheet: number, { name: string, language: string });

Move operations on Columns, Rows

Description

We're missing the ability to move rows, columns and sheets.

API proposal

public moveRows(sheet: number, ... ? ...): CellValueChange[]
public isItPossibleToMoveRows(sheet: number, ... ? ...): CellValueChange[]

public moveColumns(sheet: number, ... ? ...): CellValueChange[]
public isItPossibleToMoveColumns(sheet: number, ... ? ...): CellValueChange[]

Verify dependancy licensing

Description

We're releasing a new library. Before we do that we should verify all the dependencies for this project. We can use FOSSA or any other software to check the dependency tree carefully.

• SAP/chevrotain (Apache 2.0, 1 dep)
  • bd82/regexp-to-ast (MIT, no deps)
• csv-parser and csv-stringify should be moved outside of this repo #23. If not, verify them too removed in #23
• moment, MIT
• gpu.js MIT. Not mentioned in the readme! Added in 05de3ce
  • acron (MIT, no deps)
  • gl (BSD-2-Clause, 6 deps)
    • bindings (MIT, 1 dep)
      • file-uri-to-path (MIT, no deps)
    • bit-twiddle (MIT, no deps)
    • glsl-tokenizer (MIT, 1 dep)
      • through2 (MIT, 1 dep)
        • readable-stream (MIT, no deps)
    • nan (MIT, no deps)
    • node-gyp (MIT, 11 deps)
    • prebuild-install (MIT, 15 deps)
  • gl-wiretap, (MIT, no deps)
  • gpu-mock.js (MIT, no deps)

Create an issue and PR templates

Description

It would be easier to manage tasks, changes and bugs if we had issue template like we have in all our repos. Copy from one of them or propose something better

Add support for custom functions

Description

Spec requires that the user is able to implement external functions listed in #14. We have to provide public methods and a mechanism to register custom functions. Custom functions should be structured the same as built-in functions #43 and described with the same metadata as build-in functions #2

API proposal

public static registerFormula(id: string, formula: FormulaDefinition): void;
public static getFormula(id: string): FormulaDefinition;
public static getFormulas(): string[];

Usa case

import { HyperFormula } from 'hyperformula';
import { CustomSum } from './myFormulas/customSUM';

Hyperformula.registerFormulas(CustomSum);

Customizing loaded formulas

It should be possible to initialize HyperFormula with a subset of built-in formulas:

import { HyperFormula } from 'hyperformula/core'
import { Trigonometry } from `hyperformula/plugins/trigonometry`
import { CustomSum } from './myFormulas/customSUM';

HyperFormula.registerFormulas(Trigonometry)
HyperFormula.registerFormulas(CustomSum)

Aliasing

ODFF recommends keeping all functions namespaced. Therefore all functions implemented by us should be named with HYPERFORMULA. prefix. ie: HYPERFORMULA.COS, HYPERFORMULA.SUM. And then aliased to common names: COS, SUM. It will be easy then to overwrite some of them with custom implementation just by aliasing different namespaced functions: MYOWN.SUM to canonical SUM name.

External functions for the initial release

Description

Those functions should be possible to register externally for the initial release in January.

ToDo

I guess we have to implement all of them to prove this is possible 🤔

Use case example

import { HyperFormula } from 'hyperformula';

HyperFormula.registerFunction('DEVSQ', ...); 

const engine = new HyperFormula()

// function '=DEVSQ()' is now available as a formula in 'engine'

Funtions list

✓	Function Name	Funtion Type*	✓	Function Name	Funtion Type*
	DEVSQ	Q		EXACT	Q
	SUMX2MY2	Q		SUMX2PY2	Q
	SUMXMY2	Q		CEILINGMATH	Q
	CEILINGPRECISE	Q		ACOSH	M
	ACOTH	M		ASINH	M
	ATAN2	M		ATANH	M
	COSH	M		COTH	M
	CSC	M		CSCH	M
	SINH	M		SQRTPI	M
	TANH	M		AVEDEV	Q
	DATEVALUE	DATE		DAYS360	DATE
	EDATE	DATE		EOMONTH	DATE
	ACCRINT	FIN		CUMIPMT	FIN
	CUMPRINC	FIN		DB	FIN
	DDB	FIN		DOLLAR	FIN
	DOLLARDE	FIN		DOLLARFR	FIN
	EFFECT	FIN		BESSELI	M
	BESSELJ	M		BESSELK	M
	BESSELY	M		COMBIN	M
	COMBINA	M		BETADIST	STAT
	BETAINV	STAT		BINOMDIST	STAT
	BINOMDISTRANGE	STAT		BINOMINV	STAT
	CHISQDIST	STAT		CHISQINV	STAT
	CONFIDENCENORM	STAT		CONFIDENCET	STAT
	COVARIANCEP	STAT		COVARIANCES	STAT
	EXPONDIST	STAT		FDIST	STAT
	FINV	STAT		FISHER	STAT
	FISHERINV	STAT

Total: 56

Function Types:

• Q - basic
• M - mathematics
• STAT - statistics
• FIN - finance
• DATE - date and time

Reference

Appendix 5

NUMBER type precision is not precise enough

Description

Engine inherits precision errors from Javascript. It should use the language and its limitations to provide results that are expected by final users. 0.1 + 0.2 that does not equal 0.3 isn't the best result we can provide.

Javascript has a precision of 17 significant number, spreadsheet apps require precision of 15. Additionally, they use an algorithm called "snap to zero". See resources.

Issue 1: Engine doesn't handle Javascript arithmetics quirks

it('should correctly calculate 0.2 + 0.1 as 0.3', () => {
  const engine = HyperFormula.buildFromArray([
    ['=0.2+0.1'],
  ])

  expect(engine.getCellValue(adr('A1'))).toBe(0.3)
})

Result:

Expected: 0.3
Received: 0.30000000000000004

Issue 2: Visual rounding is not an option, the error propagates to comparison operator

 it('works for obvious case', () => {
    const engine = HyperFormula.buildFromArray([
      ['=0.2+0.1', '=IF(A1=0.3, "True", "False")'],
    ])

    expect(engine.getCellValue(adr('B1'))).toBe('Excel')
  })

Result:

Expected: "True"
Received: "False"

Issue 3: Should handle repeating decimal

it('should handle 1/3 correctly', () => {
  const engine = HyperFormula.buildFromArray([
    ['=1/3', '=A1*3'],
  ])

  expect(engine.getCellValue(adr('A1'))).toBe(0.33333333333333300000)
  expect(engine.getCellValue(adr('B1'))).toBe(1.00000000000000000000)
})

Issue 4: Should handle decimal that looks like repeating decimal but it isn't one

it('0.33333333333333300000 is not the same as 1/3', () => {
  const engine = HyperFormula.buildFromArray([
    ['0.333333333333333', '=A1*3'],
  ])

  expect(engine.getCellValue(adr('A1'))).toBe(0.33333333333333300000)
  expect(engine.getCellValue(adr('B1'))).toBe(0.99999999999999900000) // Excel
  expect(engine.getCellValue(adr('B1'))).toBe(1.00000000000000000000) // Calc
})

Warning: There is a difference between other spreadsheets. Differences are described somewhere within resources links.

Resoruces

[1] https://docs.microsoft.com/en-us/office/troubleshoot/excel/floating-point-arithmetic-inaccurate-result
[2] http://www.gnumeric.org/numerical-issues.html
[3] https://support.google.com/docs/forum/AAAABuH1jm0Kc6fcJAsfok/?hl=en
[4] https://en.wikipedia.org/wiki/Numeric_precision_in_Microsoft_Excel
[5] http://cpearson.com/Excel/rounding.htm

Documentation

• We should create a documentation page that explains precision
• A section that describes differences in precision between spreadsheets would be helpful

Does not support reference operators

Description

HyperFormula does not support reference operators for union and intersection.

⚠️ Because there are differences in syntax it might be necessary to add configuration #58 options.

Differences in implementations

Excel

Reference operator	Meaning	Example
: (colon)	Range operator, which produces one reference to all the cells between two references, including the two references	B5:B15
, (comma)	Union operator, which combines multiple references into one reference	SUM(B5:B15,D5:D15)
(space)	Intersection operator, which returns a reference to the cells common to the ranges in the formula. In this example, cell C7 is found in both ranges, so it is the intersection.

Libre Calc

Operator	Name	Example
: (Colon)	Range	A1:C108
! (Exclamation point)	Intersection	SUM(A1:B6!B5:C12) Calculates the sum of all cells in the intersection; in this example, the result yields the sum of cells B5 and B6.
~ (Tilde)	Concatenation or union	Takes two references and returns a reference list, which is a concatenation of the left reference followed by the right reference. Double entries are referenced twice.

Google Spreadsheet

Not supported.

Built-in functions required for the initial release

Description

Those functions are REQUIRED for the initial release in January.

Funtions list

✓	Function Name	Funtion Type*	✓	Function Name	Funtion Type*
✓	DATE	DATE	✓	DAY	DATE
✓	DAYS	DATE	✓	ACOS	M
✓	ASIN	M	✓	ATAN	M
✓	COS	M	✓	COT	M
✓	E	M	✓	ERF	M
✓	ERFC	M	✓	LN	M
✓	LOG	M	✓	LOG10	M
✓	PI	M	✓	POWER	M
✓	SIN	M	✓	SQRT	M
✓	TAN	M	✓	ABS	Q
✓	AND	Q	✓	AVERAGE	Q
✓	AVERAGEA	Q	✓	AVERAGEIF	Q
✓	BASE	Q	✓	BIN2DEC	Q
✓	BIN2HEX	Q	✓	BIN2OCT	Q
✓	BITAND	Q	✓	BITLSHIFT	Q
✓	BITOR	Q	✓	BITRSHIFT	Q
✓	BITXOR	Q	✓	CEILING	Q
✓	CHAR	Q	✓	CODE	Q
✓	CONCATENATE	Q	✓	CORREL	Q
✓	COUNTBLANK	Q	✓	COUNTA	Q
✓	COUNTIFS	Q	✓	COUNTUNIQUE	Q
✓	COUNTIF	Q
✓	DEC2BIN	Q	✓	DEC2HEX	Q
✓	DEC2OCT	Q	✓	DECIMAL	Q
✓	DEGREES	Q	✓	DELTA	Q
✓	EVEN	Q	✓	IF	Q
✓	INT	Q	✓	ISEVEN	Q
✓	ISODD	Q	✓	MAX	Q
✓	MAXA	Q	✓	MEDIAN	Q
✓	MIN	Q	✓	MINA	Q
✓	MOD	Q	✓	NOT	Q
✓	ODD	Q	✓	OR	Q
✓	ROUND	Q	✓	ROUNDDOWN	Q
✓	ROUNDUP	Q	✓	SPLIT	Q
✓	SUM	Q	✓	SUMIF	Q
✓	SUMIFS	Q	✓	SUMPRODUCT	Q
✓	SUMSQ	Q	✓	TRUNC	Q
✓	XOR	Q	✓	TRUE	Q
✓	FALSE	Q

Total: 77

Function Types:

• Q - basic
• M - mathematics
• STAT - statistics
• FIN - finance
• DATE - date and time

Reference

Appendix 5

Emty-ish values parsing

HyperFormula is parsing Handsontable's commonly used empty-ish values in a way that makes them unusable in the table.

For example, passing a dataset/sheet as:
[['', ' ']] _{(empty string, space)}
translates to:
[Symbol(), 0]

and with a null value we get the following error:

Uncaught TypeError: Cannot read property 'length' of null
    at Object.isMatrix (ParserWithCaching.ts:123)
    at HyperFormula.setMultipleCellContents (HyperFormula.ts:258)

(similar in case of undefined)

First Release

Description

A checklist for the first release in January

• #61 Small Group Evaluator requirements
• Final package build
  • #50 All package.json fields should be correctly filled in
  • #49 Add ES, CommonJS, and UMD builds. Add all package types to package.json
  • #83 Refactor main exports for use in UMD builds
  • #19 Export version and build date as static properties
  • #23 Benchmarks and R&D code from the software release
  • #118 Remove moment.js dependancy
  • #71 Support newer versions of node.js
  • #142 Support NPM
  • #123 Configure CI
  • Release process
    • Update dependencies (remove lock)
    • Change version in package.json
    • Set buildDate and version
    • Change private flag in package.json
    • Add license field in package.json
    • Check license banner in UMD build
    • Add Google Analytics to docs and API reference
  • #219 Test runner for web browsers
  • #222 Add LicenseKey verification
  • Performance benchmark
• Functionality
  • #231 Implement VERSION function
  • #7 API
    • #45 Add destroy method to clean up after engine instance
    • #33 We need a way to clear all data from a worksheet
    • #32 Worksheet helpers and name to id translation
    • #74 Add support for copy/cut/paste
    • #22 Address translation helpers
    • #146 Add better config validation
    • #178 Get all sheet data method
    • #34 Range getters
    • #24 Add formula helpers: calculate, validate, normalize
    • #38 Support sort and filters integration
    • #18 Finish batch operations API
    • #122 Input and output types should be consistent
    • #31 Cleanup set cell value methods
    • #179 Add sheet self-references to fix copy/cut/paste limitation
    • #58 Global config settings
    • #182 It's not possible to edit an invalid formula
    • #207 It's not possible to add worksheet with references after engine initialization
    • #29 Add move rows and columns operations
    • #37 Add support to register custom formula / customize loaded ones
    • #36 Add custom languages support
    • #209 Undo/Redo support
    • #135 Add support for events
  • Syntax
    • #70 Add support for "unary plus" operator
    • #44 Support coercion of strings to number with % sign
    • #12 Worksheet references and special characters in names
    • #68 Keep the whitespace
    • #129 Parse leading apostrophe to set the value as text
    • #76 Support omitting optional function parameters
    • #198 References (cell, range, sheet) are not fully supported
    • #13 Built-in function required for the initial release
  • Data types
    • #229 time support, HH:mm, HH:mm:ss, AM/PM, duration
    • #4 numeric operations precision
    • #3 Support date type in arithmetic and comparison operators
    • #130 string comparison operators are very naive, we need better support
    • #177 Handle octal, binary, hexadecimal, and numeric separators
    • #42 Add numeric separator as a configuration option
    • #10 ISBLANK and null values
    • #173 #142 BLANK should be coerced to 0 when used with operators
    • #139 Support leap year 1900 in date type
    • #176 Unsupported data types should report a meaningful error message
    • #172 Operations that overflow integer with Infinity should report #NUM! error
    • #119 Parse decimal without leading zero consistently
    • #217 Distinct Logical type support
  • Errors support
    • #63 Constant errors support
    • #48 CellError should return a localized error value
    • #144 #145 Errors should be propagated correctly when used with operators
• #205 Documentation (part of it)
  • #8 Readme.md
  • #51 License.txt
  • #202 Improve API reference
  • Describe all config options #58
  • #125 Generate syntax diagram for grammar
  • #149 Add developer documentation
  • #53 Code_of_Conduct.md
  • #55 Changelog.md
  • #52 Contributing.md and CLA
  • #21 Create issue and PR templates
  • Examples for usage with Vue, React, Angular, TS
  • Demo with export/import external JSON
  • Example Custom Formula: called =FOO() will return "bar"
• #54 (comment) Acceptance tests @aninde

Create a CONTRIBUTING.md

Description

Should be roughly the same as for HoT, but it's too specific to copy & paste 1 to 1.

Languages support

Description

The only small change proposed here is to add language name and language code.

• Add language code
• If the specified language is not found, fallback to default, English.
• If a function is not translated it cannot be used. Return error as for unknown formula.
• Add test for language files that compare language.functions keys with registeredPlugins keys
• Language should be set as language code in Config #58
  

  hyperformula/src/Config.ts

  Line 41 in 37c1d1d

  language: enGB,

Language definition

const lang: LanguageDefinition = {
  languageCode: 'pl-PL',
  errors: {
    DIV_BY_ZERO: '#DZIEL/0!',
    NAME: '#NAZWA?',
    VALUE: '#ARG!',
    NUM: '#LICZBA!',
    NA: '#N/A',
    CYCLE: '#CYKL!',
    REF: '#ADR!',
  },
  interface: {
    NEW_SHEET_PREFIX: 'Arkusz'
  },
  functions: {
    'SUM': 'SUMA',
    'ADD': 'DODAJ',
    // ... more functions
  },
}

API proposal

public static registerLanguage(lang: LanguageDefinition): void;
public static getLanguage(name: string): LanguageDefinition;
public static getLanguages(): string[]; // ['pl-PL', 'en-GB', ...]

Fill in the missing package.json fields

Description

We have to fill in the gaps before we release the package. Let's ask @scarletfog for help with a short package description and keywords.

package.json

  "name": "hyperformula",
  "description": " ??? ",
  "homepage": "https://handsontable.com/",
  "repository": {
    "type": "git",
    "url": "https://github.com/handsontable/hyperformula.git"
  },
  "bugs": {
    "url": "https://github.com/handsontable/hyperformula/issues"
  },
  "author": "Handsoncode <[email protected]>",
  "keywords": [
    " ??? ",
    " ??? ",
    " ??? ",
  ],
  "license": "SEE LICENSE IN LICENSE.txt",

AddSheet should return generated name

Description

It's cool that we can set name or have it generated. But if we do not set it we will lose the handle on the added worksheet. In other words, the sheet will be added but we don't know which one it is. We can't fill it with data or reference in any way.

const newOrSetName = engine.addSheet();

Sort and filter support

Description

We have to figure out how to handle Handsontable filters and sorting rows. This might be tricky if move operations are not permanent.

Function meta data format

Description

Function meta can be used to provide syntax highlight, editor help, contextual help or generate documentation pages similar to those attached at the bottom of this issue.

We don't have to use all the fields that are described here. But should be ready to extend the schema if we need to do it in the future.

Inspiration source:
https://docs.microsoft.com/en-us/office/dev/add-ins/excel/custom-functions-json

Interesting fact:
https://docs.microsoft.com/en-us/office/dev/add-ins/excel/custom-functions-json-autogeneration
JSON metadata might be generated from JSDoc. For the English language only, I guess.

Google Spreadsheet has a very similar JSDoc format for custom functions:
https://developers.google.com/apps-script/guides/sheets/functions

Example

{
  id: "SUM",
  name: "SUM",
  description: "Short description for editor and category list.",
  notes: "Longer article about formula and it's use cases. Might include Markdown for rich editing",
  helpUrl: "https://hyperformula.com/formulas/en/sum", // can be generated?
  categories: ["Financial"],
  examples: [
    "=SUM(A1:A4, 101)",
  ],
  parameters: [
    {
      name: "Parameter name",
      descriptions: "Parameter short description",
      optional: false,
      repeating: true,
    }
  ],
  since: "1.2.0",
  see: ["SUMIF"],
  options: {
    useGPU: false,
    volatile: false,
  }
}

Function

Property	Data type	Required	Description
description	string	No
helpUrl	string	No	URL that provides information about the function.
id	string	Yes	A unique ID for the function.
name	string	Yes	The name of the function that end-users see.
options	object	No	See options for details.
parameters	array	Yes	Array that defines the input parameters for the function.
examples	array	No
notes	string	No	field to describe quirks and use cases
see	array	No	for the references to similar formulas
categories	array	No	to group formulas
since	string	No	in which the formula was introduced

Options:

Property	Data type	Required	Description
volatile	boolean	No	If true, the function will recalculate each time
useGPU	boolean	No	If true, the engine should use GPU to solve this function

Parameters:

Property	Data type	Required	Description
description	string	Yes	A description of the parameter
name	string	Yes	The name of the parameter.
optional	boolean	No	If true, the parameter is optional.
repeating	boolean	No	If true, parameters will populate from a specified array. Note that functions all repeating parameters are considered optional parameters by definition.

ID description

• Should be unique
• Uppercase
• only alphanumeric characters and periods

More: https://docs.microsoft.com/en-us/office/dev/add-ins/excel/custom-functions-naming

Use cases

With all this metadata and structure we can generate multiple developer experience tools:

Editor helper

Better...

or worse...

Help box modal

In all spreadsheet apps this is a responsive version of help pages listed below.

Formula builder

With the list of parameters and their types we can generate form just like Excel does:

Help pages

Excel

Source: https://support.office.com/en-us/article/average-function-047bac88-d466-426c-a32b-8f33eb960cf6

Google Spreadsheet

Source: https://support.google.com/docs/answer/3093139

Gnumeric

Source: https://help.gnome.org/users/gnumeric/stable/gnumeric.html#gnumeric-function-SUM

DATE type is not supported by arithmetic and comparison operators

Description

Issue 1: Dates are not supported by the arithmetic operators.

Test:

it('subtract two dates', () => {
  const engine = HyperFormula.buildFromArray([
    ['02/02/2020', '02/06/2019', '=A1-B1'],
  ])

  expect(engine.getCellValue(adr('C1'))).toBe(245)
})

Results:

Expected: 245
Received: {"type": "VALUE"}

Note: Expected value depends on date format.

Issue 2: Dates are not supported by the comparison operators

Test

it('compare two dates', () => {
  const engine = HyperFormula.buildFromArray([
    ['02/02/2020', '02/06/2019', '=A1>B1'],
  ])

  expect(engine.getCellValue(adr('C1'))).toBe(true)
})

Result

Expected: true
Received: {"type": "VALUE"}

Bundles fail to load without `core-js` and `@babel/runtime` installed

When trying to import a bundle (e.g. /dist/unoptimized-full/bundle.js) I get a bunch of errors:

Module not found: Error: Can't resolve '@babel/runtime/helpers/interopRequireDefault'
Module not found: Error: Can't resolve '@babel/runtime/helpers/typeof'
Module not found: Error: Can't resolve 'core-js/modules/es.json.to-string-tag'
Module not found: Error: Can't resolve 'core-js/modules/es.math.to-string-tag'
Module not found: Error: Can't resolve 'core-js/modules/es.object.to-string'
Module not found: Error: Can't resolve 'core-js/modules/es.symbol'
Module not found: Error: Can't resolve 'core-js/modules/es.symbol.description'
Module not found: Error: Can't resolve 'core-js/modules/es.symbol.to-string-tag'

After manually installing core-js and @babel/runtime they go away, so I'm guessing they should be added to package.json as dependencies or at least peerDependencies?

Variables support

Description

Our previous formula engine had support for custom variables. The use case was that we can assign a value to a constant name. If we could assign anything a cell could hold we could assign range reference to a name.

Which gives us NamedRanges support. Worth considering.

Example

parser.setVariable('MY_VARIABLE', 5);
parser.setVariable('fooBar', 10);

parser.parse('(1 + MY_VARIABLE + (5 * fooBar)) / fooBar'); // returns `5.6`

API proposal

public setVariable(name: string, value: CellValue);
public getVariable(name: string): CellValue;

Local and global scope

There should be a global (workbook/engine) and local (worksheet) scope of variables. We can add an argument to the API proposal mentioned above.

Reference

https://github.com/handsontable/formula-parser

MVP

Issue #1 is reserved for our first release 🎉

Description

WIP

Tasks

• #1 Use Github to organize tasks
• #20 Move Hyperformula code to handsontable/hyperformula
• #7 Finish public API
  • #36 Add support for custom languages
  • #36 Add support for cultures
  • #37 Add support for custom formulas
  • #2 Define formula and meta data format
  • #24 Function helpers: normalize, validate
  • #24 Evaluate function from string
  • #22 Address translation helpers a1ToCoords, coordsToA1
  • #19 Export static version and buildDate from package.json
  • #18 Refactor CRUD operations to remove auto recalculate
  • #18 Add manual recalculate method
  • #18 Add batch operations support
• #13 Implement all required functions
• #14 Implement all external function
• #38 Figure out how do we handle filtering and sorting
• #16 Full support for data types
  • #4 Number precision
  • #3 Date/Time support
  • #5 True/False aliases
  • #16 Array constant support
• Full support for references
  • #12 Worksheet references
  • #11 3D references
• Support all operators
  • Arithmetic operators
  • Logical/Comparison operators
  • Text Concatenation operator
  • #15 Reference operators
• Clean up the development tools
  • #23 Remove CsvImporter i CsvExporter and their dependancies
  • #23 Hide or remove from the runtime this.stats.measure, this.dependencyGraph.stats.start i this.dependencyGraph.stats.end
• Write documentation for the engine
  • Better descriptions for generated TSDoc
  • How to guide for quick start and usage instructions
  • #8 Readme that is NPM ready
• Create new plugin for Handsontable handsontable/handsontable#6466
• #17 Create a kitchen sink demo to prove that integration works well
• #25 Check Hyperformula dependencies with FOSSA (https://fossa.com)

Yet to be addressed

• Undo and redo
• Copy, cut, paste
• Merged cells
• Cell validation
• Error reporting
• Moving rows / columns
• Syntax highlight

Browser support

Browser Name	Version
Firefox	58
Safari	11
Chrome	64
iOS Safari	11.2
Chrome for Android	64
IE	11
Edge	16
Android browser	62
Firefox Android	57

Appendix 9

Formula helpers

Description

The point is to provide public API for the methods we already have internally:

 public calculateFormula(formula: string, cellPosition: SimpleCellAddress): any;
 public validateFormula(formula: string): boolean; 
 public normalizeFormula(formula: string): string;

While normalize might be a static, validate and calculate should be considered with the context of the workbook.

Use case

A full use case is described in #17 but lets link to the screenshot once again:

Events

Extracted to #135

Problem?

The formula is stored outside of the graph. We won't know if it should be updated. We would have to recalculate each time something changes in the workbook.

Is there any alternative? Should we register the formula in HyperFormula so it would become a part of the graph?

Solved by @bardek8! Formulas will be added to sheet that is indexed at -1. It will be a part of the graph and change lists.

Address translation helpers

Description

This was already committed to the master branch on Friday.

public simpleCellAddressToString(address: SimpleCellAddress, sheet: number): string
public simpleCellAddressFromString(stringAddress: string, sheet: number): SimpleCellAddress

I'm just wondering if it's necessary to validate the sheet number? Shouldn't this be a static that just parses the address both ways? Why do we need a fallback?

If the sheet number is missing we can add it later. Moreover, the missing sheet property might be the information of its own.

Also, we don't always know the sheet id (number) might be necessary to have a helper to translate sheet name to sheet id. Done in #32

AddSheet helpers have different sytnax

Description

The syntax for addSheet and removeSheet and their helpers isn't consistent. addSheet and isItPossibleToAddSheet has name as an argument, where the removeSheet and isItPossibleToRemoveSheet take sheet id as a number.

Should we keep it like that?

From API

addSheet(name?: undefined | string): void
removeSheet(sheet: number): CellValueChange[]
isItPossibleToAddSheet(name: string): boolean
isItPossibleToRemoveSheet(sheet: number): boolean

CellError does not provide localized error value that should be displayed in a cell

Description

While working on integration we've noticed [1] that we have to map CellError.type to a human-readable string. Unfortunately, we can't use the translated error message then.

hyperformula/src/i18n/plPL.ts

Lines 42 to 50 in e696d1d

	errors: {
	DIV_BY_ZERO: '#DZIEL/0!',
	NAME: '#NAZWA?',
	VALUE: '#ARG!',
	NUM: '#LICZBA!',
	NA: '#N/A',
	CYCLE: '#CYKL!',
	REF: '#ADR!',
	},

This should be handled by the engine and exposed as the CellError class property.

hyperformula/src/Cell.ts

Lines 79 to 82 in e696d1d

	export class CellError {
	constructor(public readonly type: ErrorType) {
	}
	}

API Proposal

export class CellError {
  public readonly type: ErrorType
  public readonly message: string

  constructor(type, message = null) { 
    this.type = type;
    this.message = message;
  }
  
  /** 
    * Localized cell value that will be displayed in a worksheet cell renderer
    */
  public get value() {
    return getLocalizedErrorValue(this.type);
  }
}

At first I thought about naming it message property but #VALUE! error can have a custom message returned by the user [2]. So it would be cool to extend the CellError with one more readonly property:

constructor(public readonly type: ErrorType, public readonly message: string) { }

Error types

Name	Comments
#DIV/0!	Attempt to divide by zero, including division by an empty cell. ERROR.TYPE of 2 6.13.11
#NAME?	Unrecognized/deleted name. ERROR.TYPE of 5.
#N/A	Not available. ISNA() applied to this value will return TRUE. Lookup functions that failed, and NA(), return this value. ERROR.TYPE of 7.
#NULL!	Intersection of ranges produced zero cells. ERROR.TYPE of 1.
#NUM!	Failed to meet domain constraints (e.g., input was too large or too small). ERROR.TYPE of 6.
#REF!	Reference to invalid cell (e.g., beyond the application’s abilities). ERROR.TYPE of 4.
#VALUE!	Parameter is wrong type. ERROR.TYPE of 3.

Reference

[1] https://github.com/handsontable/handsontable/pull/6512/files#r352545073
[2] https://docs.microsoft.com/en-us/office/dev/add-ins/excel/custom-functions-errors

Support 3D references

Description

Since we're supporting Worksheets it would be cool to support all features.

=SUM(Sheet1:Sheet4!B3)

We need to support all CRUD operations on Worksheets:

• Add sheet
• Remove sheet
• Move sheet

Rename should not have an impact on the sheet sequence.

Reference

https://support.office.com/en-us/article/Create-a-3-D-reference-to-the-same-cell-range-on-multiple-worksheets-40ca91ff-9dcb-4ad1-99d2-787d0bc888b6#bmlearn_more_about_a_3_d_refererence