Tutorial: Summary calculations

Summary calculations


Edit this page

The Column Summary plugin allows making pre-defined calculations on the cell values and display the results within Handsontable.
You can think of this plugin as pre-defined formulas.

The basic setup.

  1. Setting the destination cell
  2. Setting the calculation range
  3. Providing the settings as a function

Available calculations:

  1. Sum
  2. Min
  3. Max
  4. Count
  5. Average
  6. Custom

Additional options:

  1. Forcing numeric values
  2. Throwing datatype errors
  3. Making destination cells read-only
  4. Rounding values after the decimal point



Basic setup

To initialize the columnSummary plugin, you need to properly set a property in the Handsontable initial config.
The columnSummary property should be declared as an array of objects, where each object represents a single endpoint (the "output" cell, or a single calculation).

    columnSummary: [
  {
    destinationRow: 2,
    destinationColumn: 2,
    type: 'min',
    // other options...
  },
  {
    destinationRow: 3,
    destinationColumn: 3,
    type: 'max',
    // other options...
  }
]

Setting the destination cell

The columnSummary plugin requires the user to provide the destination coordinates (row and column number) for the cell to display the calculations results in.

To do that, you need to set two properties in the Handsontable config object:

If the destination cell should be closer to the bottom of the table, you might find the reversedRowCoords useful. What it does is counting the rows from the bottom, not the top, as it usually does.

So, for example, defining the plugin like this puts the calculation result in a cell in the 5th column (we're counting from 0) and 2nd row from the bottom of the table.

Setting the calculation range

By default, the plugin makes calculations on data from all rows in the endpoint's destination column. However, you can specify it differently (both column and row-wise).

The properties responsible for this are ranges and sourceColumn.

  1. ranges option specifies the row range to make the calculations on. It should be declared as an array of arrays , where each of the arrays represent a single row range.

    For example, this configuration would do the calculations for rows: 0, 1, 2, 3, 4, 6, 8 and 9.

  2. sourceColumn option specifies the column to work on.

    For example, this will make operations on the 3rd column (again, we're counting from 0):

Providing the settings as a function

Since version 1.8.1, you can provide a function instead of an array as the config item. The function has to return an array of objects, similarly to a traditional setup method.

Take a look at the example below:

This allows many possible usages: for example, you can easily calculate a total for a group in a group parent combining this plugin with the Nested Rows plugin.

Take a look at this simple demo:


Available calculations

Sum

Calculates a sum of values in the specified column and row range.

Usage:

columnSummary: [
  {
    // ...
    type: 'sum'
  }
]

Min

Finds the lowest value in the specified column and row range.

columnSummary: [
  {
    // ...
    type: 'min'
  }
]

Max

Finds the highest value in the specified column and row range.

columnSummary: [
  {
    // ...
    type: 'max'
  }
]

Count

Counts the non-empty values in the specified column and row range.

columnSummary: [
  {
    // ...
    type: 'count'
  }
]

Average

Calculates the average from the values in the specified column and row range.

columnSummary: [
  {
    // ...
    type: 'average'
  }
]

Custom

Takes a custom function and applies ot to the values in the specified column and row range.

columnSummary: [
  {
    // ...
    type: 'custom',
    customFunction: function(endpoint) {
      // endpoint is an object containing the endpoint data

      // your function
    }
  }
]

Example:

Additional options

Forcing numeric values

If your table doesn't contain only numeric data, you can try to force the values to be numeric in the calculations. For example, 9a can be treated as 9. To enable this feature, you'll need to set the forceNumeric property to true.

Enabling this option might sometimes be a good idea, as text-based Handsontable cells stores their contents as strings, which might cause problems with, for example, the sum function.

By default this option is disabled.

Throwing datatype errors

If your table doesn't contain only numeric data, you can either skip the non-numeric entries in the calculation, throw an error or try to parse them to float using the forceNumeric option.
If you choose to throw the errors, you need to set the suppressDataTypeErrors property to false.

By default, suppressDataTypeErrors is set to true.

columnSummary: [
  {
    // ...
    suppressDataTypeErrors: false
  }
]

Making the endpoint cells read-only

You can make the the cells with the calculation results read-only by setting the readOnly option to true.

This option is true by default.

Rounding values after the decimal point

If you wish to round the calculation result to a specific number of digits after the decimal point, you need to use the roundFloat parameter.
It's value will result in rounding the result to the appropriate amount of digits.