This is a demo of the alteration I made to Handsontable to make it
behave more like a spreadsheet with some math and
we have some formula evaluation.
You need to include the file <script src="jquery.handsontable-excel.js"></script>
in your document, after you have included 'Handsontable' itself, for this to work.
In order to enable this plugin, you can add 'useFormula: true' to default
settings in your Handsontable. This rearanges things within Handsontable
so that the default renderer (if nothing is set explicitly with either 'columns'
or 'cells') is: 'Handsontable.ExecRenderer'. If you set the type to 'excel'
in any cell, that enables you to have a formula (similar to excel or libreoffice/openoffice)
for that cell even without enable the useFormula above.
Note that this plugin is very beta at the moment. There are several issues with it
(see below).
The new renderer do the following:
- We can have plain text in the cells
- We can have numeric values in the cells
- We can have forced text in the cells so that
numbers are treated as text. This is done by having
the first char of the text as a ' (apostroph)
- We can have formulas in the cells with a = as the first char:
- Everything can be wrapped in paranthesis (). It has the highest priority
- Cell references are done by A1 or BD23. If a cell have an other formula, it is reevaluated.
Beware of circular references as it currently locks the browser!!
- Cell ranges are done with a : as in A1:B23
- Normal operators + - * / are available. * and / have precedience
- You can save the result of a logical operation, = < > <> >= or <=
- Some functions are defined:
Sum
- To sum over a range or each parameter
Count
- To count all numeric values in a range
Avg
- Take average over a range
Min
- Get the minimum value in a range or each parameter
Max
- Get the maximum value in a range or each parameter
If
- If takes 2 or 3 parameters. First parameter must evaluate to a boolean.
If that is true, 'IF' have the second parameter as result. If a third
exsists and the first parameter evaluates to false, 'IF' will have the third
parameter as result, else a null.
And
- Do logical AND over a range or parameters, as in AND(A2>3;C3<0)
Or
- Do logical OR over a range or parameters
Xor
- Do logical XOR over a range or parameters
Not
- Do logical NOT of a parameter
Concat
- Concatenate all ranges and parameters as a string
- If you want to have the result as a text message, you encloses the text within a pair
of " (cite-chars) as in ="This text is a string constant" or =if(A2=12;"Ja";"Nej") to get either text depending on A2 containing '12' or not.
Known issues:
When we use the wrong decimal separator (ie. other then '.'), even if we set other
language definition in numerals.js, it looks like a number on screen, but is actually
a string, which break formulas for example.
Always use '.' as decimal separator.
When we copy a cell that contains a formula, that formula isn't modified to
compensate for 'relative' cell-addressing. That is, all cell references,
wether we use '$' or not, is always absolute.