/ S

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

This page covers a non-latest version of Handsontable.

# Column summary

Table of contents

You can easily summarize your columns, using the ColumnSummary plugin.

# About column summary

The ColumnSummary plugin lets you quickly calculate and display a column summary.

To customize your column summaries, you can:

# Column summary example

The example below calculates and displays five different column summaries:

    # Built-in summary functions

    To decide how a column summary is calculated, you can use one of the following summary functions:

    Function Description
    sum (opens new window) Returns the sum of all values in a column.
    min (opens new window) Returns the lowest value in a column.
    max (opens new window) Returns the highest value in a column.
    count (opens new window) Returns the number of all non-empty cells in a column.
    average (opens new window) Returns the sum of all values in a column,
    divided by the number of non-empty cells in that column.
    custom (opens new window) Lets you implement a custom summary function.

    # Column summary options

    You can customize each of your column summaries with configuration options.

    For the full list of available options, see the API reference.

    # Setting up a column summary

    To set up a column summary, follow the steps below.

    # Step 1: Enable the ColumnSummary plugin

    To enable the ColumnSummary plugin, set the columnSummary configuration option to an array of objects.

    Each object represents a single column summary.

    const hot = new Handsontable(container, {
  licenseKey: 'non-commercial-and-evaluation',
  data: [
    [1, 2, 3, 4, 5],
    [6, 7, 8, 9, 10],
    [11, 12, 13, 14, 15]
  ],
  colHeaders: true,
  rowHeaders: true,
  // set the `columnSummary` configuration option to an array of objects
  columnSummary: [
    {},
    {}
  ]
});

    TIP

    You can also set the columnSummary option to a function.

    # Step 2: Select cells that you want to summarize

    By default, a column summary takes all cells of the column in which it displays its result (see the destinationColumn option in step 4).

    To summarize any other column, use the sourceColumn option:

    const hot = new Handsontable(container, {
  licenseKey: 'non-commercial-and-evaluation',
  data: [
    [1, 2, 3, 4, 5],
    [6, 7, 8, 9, 10],
    [11, 12, 13, 14, 15]
  ],
  colHeaders: true,
  rowHeaders: true,
  columnSummary: [
    {
      // set this column summary to summarize the first column
      // (i.e. a column with physical index `0`)
      sourceColumn: 0,
    },
    {
      // set this column summary to summarize the second column
      // (i.e. a column with physical index `1`)
      sourceColumn: 1,
    }
  ]
});

    You can also summarize individual ranges of rows (rather than a whole column). To do this, set the ranges option to an array of arrays, where each array represents a single row range.

    const hot = new Handsontable(container, {
  licenseKey: 'non-commercial-and-evaluation',
  data: [
    [1, 2, 3, 4, 5],
    [6, 7, 8, 9, 10],
    [11, 12, 13, 14, 15]
    [1, 2, 3, 4, 5],
    [6, 7, 8, 9, 10],
    [11, 12, 13, 14, 15]
    [1, 2, 3, 4, 5],
    [6, 7, 8, 9, 10],
    [11, 12, 13, 14, 15]
  ],
  colHeaders: true,
  rowHeaders: true,
  columnSummary: [
    {
      sourceColumn: 0,
      // set this column summary to only summarize rows with physical indexes 0-2, 4, and 6-8
      ranges: [
        [0, 2], [4], [6, 8]
      ],
    },
    {
      sourceColumn: 0,
      // set this column summary to only summarize rows with physical indexes 0-5
      ranges: [
        [0, 5]
      ],
    }
  ]
});

    # Step 3: Calculate your summary

    Now, decide how you want to calculate your column summary.

    You can:

    const hot = new Handsontable(container, {
  licenseKey: 'non-commercial-and-evaluation',
  data: [
    [1, 2, 3, 4, 5],
    [6, 7, 8, 9, 10],
    [11, 12, 13, 14, 15]
  ],
  colHeaders: true,
  rowHeaders: true,
  columnSummary: [
    {
      sourceColumn: 0,
      // set this column summary to return the sum all values in the summarized column
      type: 'sum',
    },
    {
      sourceColumn: 1,
      // set this column summary to return the lowest value in the summarized column
      type: 'min',
    }
  ]
});

    # Step 4: Provide the destination cell's coordinates

    To display your column summary result in a cell, provide the destination cell's coordinates.

    Set the destinationRow and destinationColumn options to the physical coordinates of your required cell.

    const hot = new Handsontable(container, {
  licenseKey: 'non-commercial-and-evaluation',
  data: [
    [1, 2, 3, 4, 5],
    [6, 7, 8, 9, 10],
    [11, 12, 13, 14, 15]
  ],
  colHeaders: true,
  rowHeaders: true,
  columnSummary: [
    {
      sourceColumn: 0,
      type: 'sum',
      // set this column summary to display its result in cell (4, 0)
      destinationRow: 4,
      destinationColumn: 0
    },
    {
      sourceColumn: 1,
      type: 'min',
      // set this column summary to display its result in cell (4, 1)
      destinationRow: 4,
      destinationColumn: 1
    }
  ]
});

    # Step 5: Make room for the destination cell

    The ColumnSummary plugin doesn't automatically add new rows to display its summary results.

    So, if you always want to display your column summary result below your existing rows, you need to:

    1. Add an empty row to the bottom of your grid (to avoid overwriting your existing rows).
    2. Reverse row coordinates for your column summary (to always display your summary result at the bottom).

      TIP

      To reverse row coordinates for your column summary, set the reversedRowCoords option to true, and adjust the destinationRow coordinate.

    const hot = new Handsontable(container, {
  licenseKey: 'non-commercial-and-evaluation',
  data: [
    [1, 2, 3, 4, 5],
    [6, 7, 8, 9, 10],
    [11, 12, 13, 14, 15],
    // add an empty row
    [null]
  ],
  colHeaders: true,
  rowHeaders: true,
  columnSummary: [
    {
      sourceColumn: 0,
      type: 'sum',
      // for this column summary, count row coordinates backward
      reversedRowCoords: true,
      // now, to always display this column summary in the bottom row,
      // set `destinationRow` to `0` (i.e. the last possible row)
      destinationRow: 0,
      destinationColumn: 0
    },
    {
      sourceColumn: 1,
      type: 'min',
      // for this column summary, count row coordinates backward
      reversedRowCoords: true,
      // now, to always display this column summary in the bottom row,
      // set `destinationRow` to `0` (i.e. the last possible row)
      destinationRow: 0,
      destinationColumn: 1
    }
  ]
});

    # Setting up column summaries, using a function

    Instead of setting up the column summary options manually, you can provide the whole column summary configuration as a function that returns a required array of objects.

    The example below sets up five different column summaries. To do this, it:

    • Defines a function named generateData which generates an array of arrays with dummy numeric data, and which lets you add an empty row at the bottom of the grid (to make room for displaying column summaries)
    • Sets Handsontable's columnSummary configuration option to a function that:
      • Iterates over visible columns
      • For each visible column, adds a column summary with a configuration
      • To display the column summaries in the empty row added by generateData, sets the reversedRowCoords option to true, and the destinationRow option to 0

      Using a function to provide a column summary configuration lets you set up all sorts of more complex column summaries. For example, you can sum subtotals for nested groups:

        # Implementing a custom summary function

        Apart from using the built-in summary functions, you can also implement your own custom function that performs any summary calculation you want.

        To implement a custom summary function:

        1. Set up your column summary.
        2. In your column summary object, set the type option to 'custom':
          columnSummary: [
    {
      sourceColumn: 1,
      // set the `type` option to `'custom'`
      type: 'custom',
      destinationRow: 0,
      destinationColumn: 5,
      reversedRowCoords: true
    },
        3. In your column summary object, add your custom summary function:
          columnSummary: [
    {
      type: 'custom',
      destinationRow: 0,
      destinationColumn: 5,
      reversedRowCoords: true,
      // add your custom summary function
      customFunction(endpoint) {
        // implement your function here
      },

        The example below implements a function that counts the number of even values in a column:

          # Rounding a column summary result

          You can round a column summary result to a specific number of digits after the decimal point.

          To enable this feature, set the roundFloat option to your preferred number of digits. For example:

            # Dealing with non-numeric values

            To summarize a column that contains non-numeric data, you can:

            # Forcing numeric values

            You can force your column summary to treat non-numeric values as numeric values.

            TIP

            The forceNumeric option uses JavaScript's parseFloat() (opens new window) function.

            This means that e.g. 3c is treated as 3, but c3 is still treated as c3.

            To enable this feature, set the forceNumeric option to true (by default, forceNumeric is set to false). For example:

              # Throwing data type errors

              You can throw a data type error whenever a non-numeric value is passed to your column summary.

              To throw data type errors, set the suppressDataTypeErrors option to false (by default, suppressDataTypeErrors is set to true). For example:

                ← Column virtualization →