This page covers a next version of Handsontable, and is not published yet.

This page covers a non-latest version of Handsontable.

# Column summary

# Overview

With this feature, you can create a summary row at the bottom of the table.

Each cell in the summary row presents the result of a calculation based on the values in the column. Think of this plugin as a pre-defined set of functions such as sum, average, max, and more.

# Basic example

This example retrieves information about the values in the following three columns:

  • The min value in Column 1
  • The max value in Column 3
  • The sum of all values in Column 5

The index of the first column and row in Handsontable always starts from 0. In this case Column 1 has index 0.

    # Basic setup

    To initialize the columnSummary plugin, you need to set a property in the Settings object. The columnSummary property should be declared as an array of objects, where each object represents a single endpoint, i.e., 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

    You need to provide the destination coordinates of a row and a column for the cell to display the calculations results. To do this, you need to set two options in the Handsontable configuration, as shown in the example below:

      If the destination cell is at the bottom of the table, you might find the reversedRowCoords useful. It counts the rows' coordinates from the bottom up.

      In the example below, enabling this option will put the calculation result in a cell in the 5th column (starting from 0) and the 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 by column and row.

        The properties responsible for this are ranges and sourceColumn.

        # Row ranges

        The **ranges** option specifies the row range that will be included in the calculations. It should be declared as an array of arrays, where each of the arrays represents a single row range.

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

          # Column source

          The sourceColumn option specifies the column to work on.

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

            # Providing the settings as a function

            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. See the example below:

              This allows many possible usages: for example, you can sum subtotals for nested groups.

                # Available calculations

                # Sum

                Calculates the 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 it to the values in the specified column and row range.

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

                # Example of calculations

                  # 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" thanks to this option. To enable this feature, you will need to set the forceNumeric property to true.

                  Enabling this option can prove useful, as text-based Handsontable cells stores their contents as strings.

                  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 cells with the calculation results "read-only" by setting the readOnly option to true.

                    This option is set to 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. This setting rounds the calculation result to the appropriate amount of digits.