Formula support
优质
小牛编辑
127浏览
2023-12-01
Overview
The Formulas plugin allows Handsontable to process formula expressions defined in the provided data. This plugin uses a formula-parser library which takes most of functions from formula.js.
Features:
- Any numbers, negative and positive as float or integer;
- Arithmetic operations such as:
+,-,/,*,%,^; - Logical operations such as:
AND(),OR(),NOT(),XOR(); - Comparison operations such as:
=,>,>=,<,<=,; - All JavaScript Math constants such as:
PI(),E(),LN10(),LN2(),LOG10E(),LOG2E(),SQRT1_2(),SQRT2(); - Error handling:
#DIV/0!,#ERROR!,#VALUE!,#REF!,#NAME?,#N/A,#NUM!; - String operations such as:
&(concatenation eq.=-(2&5)will return-25); - All excel formulas defined in formula.js;
- Relative and absolute cell references such as:
A1,$A1,A$1,$A$1; - Build-in variables such as:
TRUE,FALSE,NULL; - Custom variables;
- Nested functions;
- Dynamic updates.
Known limitations:
- Not working with filtering and column sorting;
- Not working with trimming rows.
Quick setup
To enable the plugin you need to set the formulas property to true. Cells that are dependent on the edited value will be dynamically recalculated.
var data = [ ['=$B$2', "Maserati", "Mazda", "Mercedes", "Mini", "=A$1"], [2009, 0, 2941, 4303, 354, 5814], [2010, 5, 2905, 2867, '=SUM(A4,2,3)', '=$B1'], [2011, 4, 2517, 4822, 552, 6127], [2012, '=SUM(A2:A5)', '=SUM(B5,E3)', '=A2/B2', 12, 4151] ];
var example1 = document.getElementById('example1');
var hot1 = new Handsontable(example1, {
data: data,
colHeaders: true,
rowHeaders: true,
contextMenu: true,
formulas: true
});Custom variables
You can pass your custom variables which can be ready to use in your formula expressions. To set custom variables pass an object with key:value pairs to the formulas property.
var data = [ ['Anderson', '92', '=IF(B1<RANGE_F, "F", IF(B1<RANGE_D, "D", IF(B1<RANGE_C, "C", IF(B1<RANGE_B, "B", "A"))))', '', '', '0-63', 'F'], ['Bautista', '85', '=IF(B2<RANGE_F, "F", IF(B2<RANGE_D, "D", IF(B2<RANGE_C, "C", IF(B2<RANGE_B, "B", "A"))))', '', '', '64-72', 'D'], ['Block', '96', '=IF(B3<RANGE_F, "F", IF(B3<RANGE_D, "D", IF(B3<RANGE_C, "C", IF(B3<RANGE_B, "B", "A"))))', '', '', '73-84', 'C'], ['Burrows', '79', '=IF(B4<RANGE_F, "F", IF(B4<RANGE_D, "D", IF(B4<RANGE_C, "C", IF(B4<RANGE_B, "B", "A"))))', '', '', '85-94', 'B'], ['Chandler', '82', '=IF(B5<RANGE_F, "F", IF(B5<RANGE_D, "D", IF(B5<RANGE_C, "C", IF(B5<RANGE_B, "B", "A"))))', '', '', '95-100', 'A'], ['Colby', '95', '=IF(B6<RANGE_F, "F", IF(B6<RANGE_D, "D", IF(B6<RANGE_C, "C", IF(B6<RANGE_B, "B", "A"))))', '', '', '', ''], ['Crosby', '90', '=IF(B7<RANGE_F, "F", IF(B7<RANGE_D, "D", IF(B7<RANGE_C, "C", IF(B7<RANGE_B, "B", "A"))))', '', '', '', ''], ['Dove', '65', '=IF(B8<RANGE_F, "F", IF(B8<RANGE_D, "D", IF(B8<RANGE_C, "C", IF(B8<RANGE_B, "B", "A"))))', '', '', '', ''], ];
var example2 = document.getElementById('example2');
var hot2 = new Handsontable(example2, {
data: data,
colHeaders: true,
rowHeaders: true,
contextMenu: true,
formulas: {
variables: {
RANGE_F: 64,
RANGE_D: 73,
RANGE_C: 85,
RANGE_B: 95,
}
},
});Advanced example
The advanced example shows how to manage nested formulas. Also you can see how to set dependencies between different types of formulas.
var data = [ ['Example #1 (looking for particular words in a sentence)', '', '', '', '', '', '', ''], ['Text', 'yellow', 'red', 'blue', 'green', 'pink', 'gray'], ['Yellow dog on green grass', "=IF(ISNUMBER(SEARCH(B2, A3)), B2, '')", "=IF(ISNUMBER(SEARCH(C2, A3)), C2, '')", "=IF(ISNUMBER(SEARCH(D2, A3)), D2, '')", "=IF(ISNUMBER(SEARCH(E2, A3)), E2, '')", "=IF(ISNUMBER(SEARCH(F2, A3)), F2, '')", "=IF(ISNUMBER(SEARCH(G2, A3)), G2, '')"], ['Gray sweater with blue stripes', "=IF(ISNUMBER(SEARCH(B2, A4)), B2, '')", "=IF(ISNUMBER(SEARCH(C2, A4)), C2, '')", "=IF(ISNUMBER(SEARCH(D2, A4)), D2, '')", "=IF(ISNUMBER(SEARCH(E2, A4)), E2, '')", "=IF(ISNUMBER(SEARCH(F2, A4)), F2, '')", "=IF(ISNUMBER(SEARCH(G2, A4)), G2, '')"], ['A red sun on a pink horizon', "=IF(ISNUMBER(SEARCH(B2, A5)), B2, '')", "=IF(ISNUMBER(SEARCH(C2, A5)), C2, '')", "=IF(ISNUMBER(SEARCH(D2, A5)), D2, '')", "=IF(ISNUMBER(SEARCH(E2, A5)), E2, '')", "=IF(ISNUMBER(SEARCH(F2, A5)), F2, '')", "=IF(ISNUMBER(SEARCH(G2, A5)), G2, '')"], ['Blue neon signs everywhere', "=IF(ISNUMBER(SEARCH(B2, A6)), B2, '')", "=IF(ISNUMBER(SEARCH(C2, A6)), C2, '')", "=IF(ISNUMBER(SEARCH(D2, A6)), D2, '')", "=IF(ISNUMBER(SEARCH(E2, A6)), E2, '')", "=IF(ISNUMBER(SEARCH(F2, A6)), F2, '')", "=IF(ISNUMBER(SEARCH(G2, A6)), G2, '')"], ['Waves of blue and green', "=IF(ISNUMBER(SEARCH(B2, A7)), B2, '')", "=IF(ISNUMBER(SEARCH(C2, A7)), C2, '')", "=IF(ISNUMBER(SEARCH(D2, A7)), D2, '')", "=IF(ISNUMBER(SEARCH(E2, A7)), E2, '')", "=IF(ISNUMBER(SEARCH(F2, A7)), F2, '')", "=IF(ISNUMBER(SEARCH(G2, A7)), G2, '')"], ['Hot pink socks and gray socks', "=IF(ISNUMBER(SEARCH(B2, A8)), B2, '')", "=IF(ISNUMBER(SEARCH(C2, A8)), C2, '')", "=IF(ISNUMBER(SEARCH(D2, A8)), D2, '')", "=IF(ISNUMBER(SEARCH(E2, A8)), E2, '')", "=IF(ISNUMBER(SEARCH(F2, A8)), F2, '')", "=IF(ISNUMBER(SEARCH(G2, A8)), G2, '')"], ['Deep blue eyes', "=IF(ISNUMBER(SEARCH(B2, A9)), B2, '')", "=IF(ISNUMBER(SEARCH(C2, A9)), C2, '')", "=IF(ISNUMBER(SEARCH(D2, A9)), D2, '')", "=IF(ISNUMBER(SEARCH(E2, A9)), E2, '')", "=IF(ISNUMBER(SEARCH(F2, A9)), F2, '')", "=IF(ISNUMBER(SEARCH(G2, A9)), G2, '')"], ['Count of colors', '=COUNTIF(B3:B9, B2)', '=COUNTIF(C3:C9, C2)', '=COUNTIF(D3:D9, D2)', '=COUNTIF(E3:E9, E2)', '=COUNTIF(F3:F9, F2)', '=COUNTIF(G3:G9, G2)', '="SUM: "&SUM(B10:G10)'], ['', '', '', '', '', '', ''], ['Example #2 (extracting domains from emails)', '', '', '', '', '', ''], ['Name', 'Email', 'Email domain', '', '', ''], ['Ann Chang', 'achang@gmailtr.com', '=RIGHT(B14, LEN(B14) - FIND(EMAIL_SPLITTER, B14))', '', '', '', ''], ['Jan Siuk', 'jan@yahootr.com', '=RIGHT(B15, LEN(B15) - FIND(EMAIL_SPLITTER, B15))', '', '', '', ''], ['Ken Siuk', 'ken@gmailtr.com', '=RIGHT(B16, LEN(B16) - FIND(EMAIL_SPLITTER, B16))', '', '', '', ''], ['Marcin Kowalski', 'ken@gmailtr.pl', '=RIGHT(B17, LEN(B17) - FIND(EMAIL_SPLITTER, B17))', '', '', '', ''], ];
var example3 = document.getElementById('example3');
var hot3 = new Handsontable(example3, {
data: data,
colHeaders: true,
rowHeaders: true,
contextMenu: true,
formulas: {
variables: {
EMAIL_SPLITTER: '@',
}
},
mergeCells: [
{row: 0, col: 0, rowspan: 1, colspan: 8},
{row: 11, col: 0, rowspan: 1, colspan: 8},
],
});