The JSS Formula Premium is a JavaScript software to parse and execute spreadsheet-like formulas. It handles ranges,
variables, worksheets, and a great number of formulas available in other spreadsheet software such as Excel or
Google Spreadsheet. It deals with the JavaScript precision issues and, it is compatible with Jspreadsheet Pro plugins.
This plugin is distributed in two different versions: basic and premium.
| Formula Basic | Formula Premium | |
|---|---|---|
| License | MIT | Required a license |
| Scope | window | restricted scope |
| Parser | new Function | Custom parser |
| JavaScript precision issues | No | Yes |
| Date operations | No | Yes |
| Cross worksheets/spreadsheets calculations | No | Yes * |
| Defined names | No | Yes ** |
| Matrix calculations | No | Yes ** |
| Number of implemented formulas | 403 | 455 |
This software can be used stand-alone or integrated with Jspreadsheet CE, BASE, and PRO.
* Only available on stand-alone and with the PRO distributions.
** Only available on stand-alone and with the PRO v8 distributions.
This plugin requires a license that should be associated with one specified domain. If you need a license for
redistribution or SaaS, please keep in touch with [email protected]
JSS Formula Premium tackles the JavaScript numeric precision.
// Activate the precision adjust
formula.adjustPrecision = true;
formula('37.02 + 2.56');
// Without adjustPrecision: 39.580000000000005
// With adjustPrecision: 39.58
formula('185.32 - 84.78');
// Without adjustPrecision: 100.53999999999999
// With adjustPrecision: 100.54
formula('25.92 * 3.33');
// Without adjustPrecision: 86.31360000000001
// With adjustPrecision: 86.3136
formula('9.15 / 6');
// Without adjustPrecision: 1.5250000000000001
// With adjustPrecision: 1.525
NOTE: When this option is enabled, the results will be round in a maximum ten decimal places.
The formula method receives the expression and the variables that will support the calculations.
@param {string} expression - the formula to be calculated
@param {object} variables - the variables and values necessary to parse the expression
@param {number=} x - a optional coordinate reference
@param {number=} y - a optional coordinate reference
formula(expression: String, variables: Object, [x: Number], [y: Number]) : string|array
You can run the following tests on the console of the browser when initiation as above.
formula('A1:B1*2', { A1: 2, B1: 4 });
// Returns Array[ 4, 8 ]
formula('SUM(A1:B1*2)', { A1: 1, B1: 'A2+B2', A2: 3, B2: 4 })
// Returns 16
formula('SUM(A1:A6)', { A1: 2, A2: 4, A3: 5, A4: 1, A5: 5, A6: 1 });
// Returns 18
formula('AVERAGE(CALCULATION*10)', { CALCULATION: 'A1:A3', A1: 1, A2: 2, A3: 3 })
// Returns 20
formula('SUM(B:B)', { B1: 1, B2: 1, B3: 14 });
// Returns 16
formula('IF(B1=0,0,B9/B1)', { B1:0, B9: 3 });
// Returns zero when B1 is zero
formula('IF(true, CALCULATION, 10)', { CALCULATION: 'A1:A3', A1: 1, A2: 2, A3: 3 })
// Returns [[1], [2], [3]]
formula('IF(C16+C15!=0,C13+C14,false)', { C16: 0, C15: 0, C14: 3, C13: 12 })
// Returns false
formula('1*2<1^4');
// Returns false
formula('(1==1)<>(2>2)')
// Returns true
formula('NOW()+1');
// Today + one day
formula('DATE(2021,1,1) > DATE(2021,2,1)');
// false
formula('SHEET1!A1*A1', { 'SHEET1!A1': 2, 'A1': 3 });
// Returns 6
formula('SUM(SHEET3!B1:B3)', { 'SHEET3!B1': 3, 'SHEET3!B2': 3, 'SHEET3!B3': 4 });
// Returns 10
formula('SUM(B:B)', { 'B1': 1, 'B2': 2, 'B3': 4 });
// Returns 7
formula('SHEET1!A1*10', { SHEET1: [[1,2,3],[4,5,6]] });
// Returns 1 * 10
formula('SHEET1!B1*SHEET2!B1', { SHEET1: [[1,2,3],[4,5,6]], SHEET2: [[10,20,30]] });
// Returns 2 * 20
How to integrate Jspreadsheet and the formula-pro plugin.
<html>
<script src="https://jspreadsheet.com/v8/jspreadsheet.js"></script>
<script src="https://jsuites.net/v4/jsuites.js"></script>
<link rel="stylesheet" href="https://jspreadsheet.com/v7/jspreadsheet.css" type="text/css" />
<link rel="stylesheet" href="https://jsuites.net/v4/jsuites.css" type="text/css" />
<script src="https://jspreadsheet.com/v8/plugins/formula-pro.js"></script>
<div id='spreadsheet'></div>
<script>
// License for Formula Plugin
jspreadsheet.license = 'your-license';
// Add-on for Jspreasheet
jspreadsheet.setExtensions({ formula });
// Create the spreadsheets
jspreadsheet(document.getElementById('spreadsheet'), {
worksheets: [
{ minDimensions: [10, 10] },
{ minDimensions: [10, 10] },
],
});
</script>
</html>
// Jspreadsheet Pro
import jspreadsheet from 'jspreadsheet-pro';
// Formula Premium Plugin
import formula from '@jspreadsheet/formula-pro';
// License for Formula Plugin
jspreadsheet.license = 'your-license';
// Add-on for Jspreasheet
jspreadsheet.setExtensions({ formula });
// Create a spreadsheet
jspreadsheet(document.getElementById('spreadsheet'), {
worksheets: [
{ minDimensions: [10, 10] },
{ minDimensions: [10, 10] },
]
});