Add support for custom functions

[wojciechczerniak]

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.

[krzysztofspilka]

Custom functions aka User-defined functions have limitations which we can't effectively mitigate, but at least we can let users know about them. Users should never use methods in their functions that can:

• Perform CRUD actions (insert, delete cells, rows, columns)
• Sort or filter rows
• Manipulate worksheets (rename, delete, move, add) and workbooks (in the future)
• Change the values of other cells

[wojciechczerniak]

DW @scarletfog, we should add this to the docs custom functions subpage:

Custom functions aka User-defined functions have limitations which we can't effectively mitigate, but at least we can let users know about them. Users should never use methods in their functions that can:

• Perform CRUD actions (insert, delete cells, rows, columns)
• Sort or filter rows
• Manipulate worksheets (rename, delete, move, add) and workbooks (in the future)
• Change the values of other cells

[wojciechczerniak]

Modularization

We should consider if we're going to load all formulas by default. It's easier to add formulas than remove them. But this will complicate quick start with one more step: add formulas.

import {HyperFormula, formulas} from 'hyperformula'

HyperFormula.registerFunctions(formulas)

or just a part

import {HyperFormula, formulas} from 'hyperformula'

HyperFormula.registerFunctions(formulas.trigonometry)
HyperFormula.registerFunctions(formulas.finanse)
HyperFormula.registerFunctions(formulas.statistics)

UMD

We will need to build them into separate UMD modules as we do with languages in Handsontable.

[izulin]

@wojciechczerniak why is this task not covered by existing plugin system?