jQuery Calx 2.0.0 documentation

### jQuery Calx Overview

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

### Initializing

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`

### Configuration

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

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 |
      +-------+-------+

### Formatting

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 API

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.

### Sheet API

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 API

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)`

or

`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%.

### Dependencies

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>