jQuery Calx 2.0.0 documentation

jQuery Calx is an excel calculation engine that wrapped as jQuery plugin, It is useful for building calculation in html page using excel formula, create calculation table, build custom calculator, convert excel spreadsheet into web page, etc.

jQuery Calx also come with a lot formula function defined, and adopted from formula.js. If the default formula set is not enough, you can always define your own formula function and register it via registerFunction method.

Server Side Calculation

In case you don't want the calculation formula to be exposed to the end user, you can always hide it by processing the calculation logic in the server side by using the SERVER() formula.

Chart Drawing

When you need to represent your data in graphical way, there is GRAPH() formula to help you draw the chart.

Resport Bug/Issue

If some bug or abnormal behaviour is found, you can always report the issue via jQuery Calx issue tracker here https://bitbucket.org/xsanisty/jquery-calx-2/issues

The very first step to enable jQuery Calx, is to load jQuery and the jQuery Calx itself

<script type="text/javascript" src="path/to/jquery-1.10.2.min.js"></script>
<script type="text/javascript" src="path/to/jquery-calx-2.0.0.min.js"></script>

Like any other jQuery plugin, it is easy to initialize jQuery Calx, you just need to prepare the element with configured data-cell, data-formula, or data-format attribute and call $('selector').calx() to enable jQuery Calx.

We are using data-cell attribute to define the cell address, data-formula attribute to define the calculation formula, and data-format attribute to define formatting rule, and then, let jQuery Calx do the magic.

Once jQuery Calx is initialized, it will respond to any changes occured in cell's element, and do the calculation based on the cell value and formula, let's see below example:

<form id="sheet">
    <input type="text" data-cell="A1"> <br>
    <input type="text" data-cell="A2"> <br>
    <input type="text" data-cell="A3"> <br>
    <input type="text" data-cell="A4" data-formula="SUM(A1:A3)">
</form>

<script>
$('#sheet').calx();
</script>

In the above sample code snippet, A4 will display whatever result of SUM(A1:A3), any change occured in A1, A2, or A3 will be represented in A4 since A4 depend on cell ranged from A1 to A3.

If you are familiar with any spreadsheet application like Microsoft Excel or LibreOffice, you will also get familiar with jQuery Calx. We use term sheet for wrapper element like form#sheet where the jQuery Calx is initialized, and cell for all element inside the form#sheet that involved in calculation process, this mean all element that has data-cell and/or data-formula attribute.

In case data-formula attribute present without data-cell attribute, jQuery Calx will assign reserved cell address prefixed with CALX and count the index incrementally, the resulting cell address will be like CALX1, CALX2,CALX3,CALX4

You can configure how jQuery Calx should behave by passing configuration object when initializing jQuery Calx, the default configuration is used when you pass nothing to initialize jQuery Calx. The default configuration is shown as below:

    
/************************************************
 *             Default Configuration            *
 ************************************************/

var defaultConfig = {
    /**
     * tell calx to perform auto calculation after change has been made,
     * if autoCalculate is false, you need to trigger calculation manually
     * by calling the calculate method $(selector).calx('calculate');
     */
    'autoCalculate'         : true,

    /**
     * event that trigger calculation to be executed when autoCalculate is true
     */
    'autoCalculateTrigger'  : 'blur',

    /**
     * callback triggered right before calculation is performed
     * when callback is executed, jQuery Calx will pass sheet object as the context
     * so you can access all sheet API via <this> keyword
     */
    'onBeforeCalculate'     : null ,

    /**
     * callback triggered right after calculation is performed
     */
    'onAfterCalculate'      : null ,

    /**
     * callback triggered right before calculation result is rendered
     */
    'onBeforeRender'         : null ,

    /**
     * callback triggered right after calculation result is rendered
     */
    'onAfterRender'          : null ,

    /**
     * default fomatting rule when data-format is not present
     */
    'defaultFormat'         : false,

    /**
     * used for server side formula, when you call the SERVER() function,
     * jQuery Calx will pass everything to this URL, and wait for the response
     * before processing the next calculation
     */
    'ajaxUrl'               : null,

    /**
     * ajax method used for requesting formula result from the server side
     */
    'ajaxMethod'            : 'get',

    /**
     * check for circular reference upon initialization, default false
     */
    'checkCircularReference': false

};

Formula is used to define calculation rule, you can define the formula inside the data-formula attribute and write the formula like the you write it in excel. There are a lot excel compatible formula can be used from simple SUM, HLOOKUP, VLOOKUP to complex financial function like NPV, IRR, etc.

Beside the excel compatible formula, there is also custom formula built in jQuery Calx, they are SERVER() and GRAPH()

SERVER()

This is special function to perform calculation in server side, you must define ajaxUrl parameter when configuring calx. The first parameter of this function is the formula name, and the rest is formula parameters

When it invoked, jQuery Calx will send the request to the configured ajaxUrl and wait for the response before continue to the next formula execution.

SERVER('SUM', A1:A3, B4)

Formula above will send request looks like below


[
    function    => 'SUM',
    params      => array(
        /* range from A1 to A3 */
        1   => array(
            A1  => 'value of A1',
            A2  => 'value of A2',
            A3  => 'value of A3'
        ),

        /* the value of B4 */
        2   => 'value of B4'
    )
]

#ERROR_SEND_REQUEST! will be returned when error occured.

GRAPH()

This is special function used to draw graphic or chart to represent the data in graphical way. You need to place the GRAPH formula in the div element with specified height and width.

<div data-formula="GRAPH(B2:G8, ['type=bar', 'label=B1:G1', 'legend=A2:A8'])"></div>

The first parameter is cell range contains data that need to be represented as graphic.
The second parameter is array containing some 'key=value' to define how the chart should be rendered.

type:
type could be one of the following bar, line, pie, or doughnut, default is line.
label:
label is used in bar or line type chart to draw label in the x-axis of the chart. If none is given, the label will be incremental number starting from 0.
legend:
legend is used to give explanation on the chart. If none is given, the legend will be blank

orientation:
orientation is used to define the table orientation, it could be vertical or horizontal, default it horizontal
Horizontal table:
jQuery Calx will parse single row as single series.

+-------+-------+-------+-------+-------+
| val 1 | val 2 | val 3 | val 4 | val 5 |
+-------+-------+-------+-------+-------+
| val 1 | val 2 | val 3 | val 4 | val 5 |
+-------+-------+-------+-------+-------+

Vertical table:
jQuery Calx will parse single column as single series.

+-------+-------+
| val 1 | val 1 |
+-------+-------+
| val 2 | val 2 |
+-------+-------+
| val 3 | val 3 |
+-------+-------+
| val 4 | val 4 |
+-------+-------+
| val 5 | val 5 |
+-------+-------+

jQuery Calx depends on numeral.js for output formatting. In jQuery Calx version 1.x , numeral is integrated into the core, but no longer integrated in jQuery Calx 2.x and listed as dependency, you need to load it before loading jQuery Calx if you need the formatting feature.

<script src="js/numeral.min.js"></script>
<script src="jquery-1.9.1.min.js"></script>
<script src="jquery-calx-2.0.0.min.js"></script>

Setting up locale

In jQuery Calx 1.x, locale settings are defined inside the jQuery Calx configuration, since jQuery Calx 2 no longer integrated with numeral.js, local settings are defined in numeral config

// load a language
    numeral.language('fr', {
        delimiters: {
            thousands: ' ',
            decimal: ','
        },
        abbreviations: {
            thousand: 'k',
            million: 'm',
            billion: 'b',
            trillion: 't'
        },
        ordinal : function (number) {
            return number === 1 ? 'er' : 'ème';
        },
        currency: {
            symbol: '€'
        }
    });

// switch between languages
    numeral.language('fr');

Formatting Value

Cell value formatting is defined in the data-format attribute using pre-defined rule, below is example of how to format the cell value and list of available formatting rules.

<input data-cell="A1" data-format="$ 0,0[.]00" />

Numbers

Number	Format	String
10000	'0,0.0000'	10,000.0000
10000.23	'0,0'	10,000
10000.23	'+0,0'	+10,000
-10000	'0,0.0'	-10,000.0
10000.1234	'0.000'	10000.123
10000.1234	'0[.]00000'	10000.12340
-10000	'(0,0.0000)'	(10,000.0000)
-0.23	'.00'	-.23
-0.23	'(.00)'	(.23)
0.23	'0.00000'	0.23000
0.23	'0.0[0000]'	0.23
1230974	'0.0a'	1.2m
1460	'0 a'	1 k
-104000	'0a'	-104k
1	'0o'	1st
52	'0o'	52nd
23	'0o'	23rd
100	'0o'	100th

Percentages

Number	Format	String
1	'0%'	100%
0.974878234	'0.000%'	97.488%
-0.43	'0 %'	-43 %
0.43	'(0.000 %)'	43.000 %

Currency

Number	Format	String
1000.234	'$0,0.00'	$1,000.23
1000.2	'0,0[.]00 $'	1,000.20 $
1001	'$ 0,0[.]00'	$ 1,001
-1000.234	'($0,0)'	($1,000)
-1000.234	'$0.00'	-$1000.23
1230974	'($ 0.00 a)'	$ 1.23 m

Bytes

Number	Format	String
100	'0b'	100B
2048	'0 b'	2 KB
7884486213	'0.0b'	7.3GB
3467479682787	'0.000 b'	3.154 TB

Time

Number	Format	String
25	'00:00:00'	0:00:25
238	'00:00:00'	0:03:58
63846	'00:00:00'	17:44:06

Calx comes with several API that can be invoked using this syntax $('selector').calx('method'), here is list of available method that can be accessed by jQuery Calx:

calculate

$('selector').calx('calculate')

This method is used to trigger calculation process on the sheet related to the selected element, which is useful when you configure jQuery Calx with autoCalculate : false or working with large sheet where the calculation process take some times to finish and need to be triggerred manually.
destroy

$('selector').calx('destroy')

This method is used to destroy sheet object related to the selected element, any formula referenced to the cells inside this sheet will become invalid, and may result in wrong calculation.
evaluate

$('selector').calx('evaluate', formula)

This method is used to evaluate formula against the current selected sheet, all cell adrresses and variables are referenced to the current sheet.

You can do something like : $('selector').calx('evaluate', 'SUM(A1:A5)') and jQuery Calx will return the result of the formula.
getCell

$('selector').calx('getCell', cellAddress)

This method is used to retreive specified cell object of the selected sheet. Please refer to Cell API for detailed documentation about cell object.
getSheet

$('selector').calx('getSheet')

This method is used to retreive sheet object related to the selected element. Please refer to Sheet API for detailed documentation about sheet object.
getUtility

$('selector').calx('getUtility')

This method is used to retreive utility object when you need some help with the cell or cell range.
refresh

$('selector').calx('refresh')

This method is used to force jQuery Calx to rebuild the sheet of the selected element. It will destroy the cell registry and rebuild it from scratch.
registerFunction

$('selector').calx('registerFunction', FUNCTION_NAME, function_definition [, override])

This method is used to register new function and can be used in data-formula attribute. The parameters is described as below:
- FUNCTION_NAME
  the function name such as SUM, AVG, etc, must be uppercase letter
- function_definition
  the function definition define how the function should behave function(){ /** bla bla bla **/ }, jQuery Calx will pass the sheet object as the context, so you can access all the sheet API via this keyword.
- override
  the optional override flag, could be true or false to indicate if new function should override the original one or not. If true, the built in function will be overrided, default value is false.
```
$('selector').calx('registerFunction', 'CUSTOM', function(args1, args2, ... , argsN){
    //<this> keyword will be sheet object where the current formula is evaluated
    //if data-formula look like CUSTOM(A1), the value of A1 will be passed as args1
    //if data-formula look like CUSTOM(A1:B2), the value of args1 will be like
    //{A1:value, A2:value, B1:value, B2:value}

    //function should return calculated value to be rendered into the cell that invoke this function
});
```
And after the function is registered, you can simply write it in the data-formula attribute: <span data-formula="CUSTOM(A1,A2,100,C1:D5)"></span>
registerVariable

$('selector').calx('registerVariable', var_name [, var_value])

This method is used to register variables to the calx, and are available to all sheet. The variable name should be all lowercase and underscore character ([a-z_]) and the value could be anything as far as the function can handle it.
```
$().calx('registerVariable', 'the_year_i_was_born', 1988)
```
Or you can define multiple variable at one time using javascript object
```
$().calx('registerVariable', {varname: 'value', another_var: 'another value'})
```
After variable is registered, you can reference it in data-formula attribute like data-formula="CONCAT('I was born in ', the_year_i_was_born)"

Please note that there are predefined variables: true, false, and null disregard of the character is lower case or upper case, or mix of both, which mean true, TRUE, tRue are all the same.
update

$('selector').calx('update')

This method is used to update cell registry against any change in the element related to the sheet, update is similar to refresh, but instead of rebuild the cell registry from the scratch, it only add or delete cell that has been added or removed from the sheet's element.

This is useful when you are working with dynamic form where form elements are added or removed on the fly.
reset

$('selector').calx('reset')

This method is used to reset the form inside the sheet element to its original state.

Each time jQuery Calx is initialized, sheet object is created for each selected element and stored in the sheet registry inside the calx object, you can retreive this sheet object using getSheet method.

$('selector').calx('getSheet')

Please note, that selector should retrieve single dom element to get correct sheet object. After sheet object is retreived, you can call all the method available.

calculate

sheet.calculate()

Calculate the whole sheet and display the result in each cell.

checkCircularReference

sheet.checkCircularReference()

Checking if circular reference exist in the sheet.

evaluate

sheet.evaluate(formula)

Evaluating formula in the current sheet.

getCell

sheet.getCell(cellAddress)

Get the cell object on the specified address.

getCellValue

sheet.getCellValue(cellAddress)

get value of the cell on specified address

getCellRange

sheet.getCellRange(rangeStart, rangeStop)

Get the cells object in the range, the result will be object looks like below

{
    A1: cellObject,
    A2: cellObject,
    ...
}

getCellRangeValue

sheet.getCellRangeValue(rangeStart, rangeStop)

Get value of the cells in the range, the result will be object looks like below

{
    A1: 'some value',
    A2: 100
    ...
}

getVariable

sheet.getVariable(varName)

Get the defined variable value.

refresh

sheet.refresh()

Rebuild cell registry from the scratch.

reset

sheet.reset()

Reset the form inside sheet element to its original state.

update

sheet.update()

Update cell registry against any change in the sheet element.

Cell object is created after sheet object is completely initialized, and is stored in the cells registry inside the sheet object. You can retreive this cell object using two methods, via calx api, and via sheet object

$(selector).calx('getCell', cellAddress)

sheet.getCell(cellAddress)

After cell object is retrieved, you can access all the method available in cell object

calculate

cell.calculate()

Evaluate the formula of the current cell, and all it's dependant (all cells that depends on this cell)

evaluateFormula

cell.evaluateFormula()

Calculate only formula of this cell, and return the value.

getAddress

cell.getAddress()

Get the cell address of current cell object.

getFormat

cell.getFormat()

Get the formatting rule.

getFormattedValue

cell.getFormattedValue()

Get the formatted value

getFormula

cell.getFormula()

Get the formula

getValue

cell.getValue()

Get the raw value of the cell, if cell has formula defined, it will return the calculated value

renderComputedValue

cell.renderComputedValue()

Render the computed value to the cell's element

setConditionalStyle

cell.setConditionalStyle(function(value, element){})

Setup conditional styling for the cell element, it should be function with the cell value as first parameter, and jQuery object of the cell element as second parameter


cell.setConditionalStyle(function(cellValue, cellElement){
    if(cellValue < 0){
        cellElement.css('color', 'red');
    }else{
        cellElement.css('color', 'green');
    }
});

setFormat

Set the formatting rule of the current cell.

Please note that you must not set the format on the fly via $(selector).attr('data-format', format) since jQuery Calx will not notice the change.

setFormula

cell.setFormula(formula)

Set the calculation formula of the current cell.

Please note that you must not set the formula on the fly via $(selector).attr('data-formula', formula) since jQuery Calx will not notice the change.

setValue

cell.setValue(value)

Set the value of the current cell.

Please note that you must not set the value on the fly via $(selector).val(value) since jQuery Calx will not notice the change.
Cell with formula defined, will not affected by this change since it will always return the calculated value.
Cell with data-format caontains % like 0%, 0.00 %, will parse 10 as 10% (0.1), 10% as 10%.

By default, the only dependency of jQuery Calx is jQuery, the other dependencies located in js directory only required when you perform specific formula, including value formatting, date operation, and statistic operation. jQuery Calx using formula sets from formula.js in the core, but it already modified to work seamlessly with jQuery Calx to minimize dependencies.

Value Formatting

In regards to format value using the data-format attribute, you need to include numeral.min.js located in js directory, or you can download the latest version from https://github.com/adamwdraper/Numeral-js. If numeral.js is not included, jQuery Calx will render the raw value instead of formatted one.

Date Processing

Most of the date processing formula like DATE, DATEDIF() require moment.js to be executed correctly, you need to include moment.min.js located in js directory. If moment.js is not loaded, jQuery Calx will return #ERROR_MOMENT_JS_REQUIRED error.

Statistical Calculation

Most of the statistical processing formula like CORREL(), EXPONDIF() require jstat.js to be executed. you need to include jstat.min.js located in js directory. If jstat.js is not loaded, jQuery Calx will return #ERROR_JSTAT_JS_REQUIRED error.

Chart Drawing and Plotting

Currently, jQuery calx only support chart drawing and plotting using jQuery Flot (http://www.flotcharts.org) therefore you need to include jQuery Flot script and required plugin to draw the chart.

<script src="jquery-1.9.1.min.js"></script>
<script src="js/jquery.flot.min.js"></script>
<script src="js/flot_plugin/jquery.flot.pie.js"></script>
<script src="js/flot_plugin/jquery.flot.categries.js"></script>
<script src="js/numeral.min.js"></script>
<script src="js/moment.min.js"></script>
<script src="js/jstat.min.js"></script>
<script src="jquery-calx-2.0.0.min.js"></script>

jQuery Calx Overview

Server Side Calculation

Chart Drawing

Resport Bug/Issue

Initializing

Configuration

Formula

SERVER()

GRAPH()

Formatting

Setting up locale

Formatting Value

Numbers

Percentages

Currency

Bytes

Time

Calx API

calculate

destroy

evaluate

getCell

getSheet

getUtility

refresh

registerFunction

registerVariable

update

reset

Sheet API

calculate

checkCircularReference

evaluate

getCell

getCellValue

getCellRange

getCellRangeValue

getVariable

refresh

reset

update

Cell API

calculate

evaluateFormula

getAddress

getFormat

getFormattedValue

getFormula

getValue

renderComputedValue

setConditionalStyle

setFormat

setFormula

setValue

Dependencies

Value Formatting

Date Processing

Statistical Calculation

Chart Drawing and Plotting