Check documentation for the latest version of dhtmlxSuite Using Math Formulas for Data DHTMLX Docs

Using Math Formulas for Data

You have the possibility to use mathematical expressions for data you load. dhtmlxGrid parses, evalutes such expressions and presents the result in the related cells.

Beware, cells that are supposed to contain the calculation result must be empty (don't have data to load). Otherwise, loaded data 'rewrite' the calculation result.

The stated functionality requires PRO version of the dhtmlxGrid package.

Applying a formula to a column

If you need to apply a formula to the entire column use the following technique while configuring the column:

mygrid = new dhtmlXGridObject('gridbox');
mygrid.setColTypes("ed,ed,ed[=c2*c3]");
...

Syntax:

  1. Formula must start from the '=' sign and be placed in square brackets '[]'.
  2. Within formulas can be used:

    • basic mathematical operators, which are : +, -, /, *.
    • column referencies.

You can refer to a column as c[col_index]:

  • c - is the hardcoded abbreviation for a column.
  • col_index - the index of a column you want to get data from. Zero-based numbering.

Related sample:  Math calculations

Applying a formula to a cell

To apply a mathematical expression to a certain cell you should turn to the XML configuration of this cell.

Syntax:

  1. Formula must start from the '=' sign.
  2. Within formulas can be used:

    • basic mathematical operators, which are : +, -, /, *.
    • cell referencies.

You can refer to a cell in 2 ways:

  • [[row_id,col_ind]] - takes the value of the specified cell (takes the row id and column index (zero-based numbering)).
  • c[col_index] - takes the value of a cell of the specified column within a chosen row (takes the column index (zero-based numbering)).
    Beware, you can't combine 2 variants.
<rows>
    <row id="row1">
        <cell>1500</cell>
        <cell>400</cell>
        <cell>=[[row1,0]]+[[row1,1]]</cell><!-- the result is 1900 --> 
    </row>
    <row id="row2">
        <cell>1500</cell>
        <cell>400</cell>
        <cell>=c0+c1</cell> <!-- the result is 1900 -->
    </row>

Related sample:  Math calculations

Setting the permission for editing

All cells containing formulas are read-only by default, no matter what type of content was specified for the column(cell) initially.

If you want to allow users to edit formulas you should call the enableMathEditing method with the true parameter:

mygrid = new dhtmlXGridObject('gridbox');
mygrid.setColTypes("ed,ed,ed[=c0*c1]");
mygrid.enableMathEditing(true);
...
mygrid.init();

The method sets permission for editing all formulas defined in the grid at a time. You can't enable editing just for a specific column(cell).

Related sample:  Math calculations

Rounding calculation results

To round calculation results and set a number of significant digits use the setMathRound method. As a parameter the method takes the number of significant digits.

mygrid = new dhtmlXGridObject('gridbox');
mygrid.setColTypes("ed,ed,ed[=c0*c1]");
mygrid.setMathRound(2);
...
mygrid.init();

Related sample:  Custom output with math calculations

Back to top