Conditional formatting

Format specified cells, based on dynamic conditions.

Overview

Conditional formatting can be used to set the font, color, typeface, etc., for cell content and can also be used to format the style of a cell, all based on a predefined set of criteria.

Example of conditional formatting

This demo shows how to use the cell type renderer feature to make some conditional formatting:

1. The first row is read-only and formatted as bold green text.
2. All cells in the Nissan column are formatted as italic text.
3. Empty cells are formatted with a silver background.
4. Negative numbers are formatted as red text.

Source code

JavaScript

import { HotTable } from '@handsontable/react-wrapper';
import Handsontable from 'handsontable/base';
import { registerAllModules } from 'handsontable/registry';
import { registerRenderer } from 'handsontable/renderers';
import { textRenderer } from 'handsontable/renderers/textRenderer';

// register Handsontable's modules
registerAllModules();

const ExampleComponent = () => {
  const data = [
    ['', 'Tesla', 'Nissan', 'Toyota', 'Honda'],
    ['2017', -5, '', 12, 13],
    ['2018', '', -11, 14, 13],
    ['2019', '', 15, -12, 'readOnly'],
  ];

  const firstRowRenderer = (instance, td, ...rest) => {
    textRenderer(instance, td, ...rest);
    td.style.fontWeight = 'bold';
    td.style.color = 'green';
    td.style.background = '#CEC';
  };

  const negativeValueRenderer = (instance, td, row, col, prop, value, cellProperties) => {
    textRenderer(instance, td, row, col, prop, value, cellProperties);

    // if the row contains a negative number
    if (parseInt(value, 10) < 0) {
      // add class 'make-me-red'
      td.className = 'make-me-red';
    }

    if (!value || value === '') {
      td.style.background = 'rgb(238, 238, 238, 0.4)';
    } else {
      if (instance.getDataAtCell(0, col) === 'Nissan') {
        td.style.fontStyle = 'italic';
      }

      td.style.background = '';
    }
  };

  //  maps function to a lookup string
  registerRenderer('negativeValueRenderer', negativeValueRenderer);

  return (
    <HotTable
      data={data}
      autoWrapRow={true}
      autoWrapCol={true}
      licenseKey="non-commercial-and-evaluation"
      height="auto"
      afterSelection={function (_row, _col, row2, col2) {
        const meta = this.getCellMeta(row2, col2);

        if (meta.readOnly) {
          this.updateSettings({
            fillHandle: false,
          });
        } else {
          this.updateSettings({
            fillHandle: true,
          });
        }
      }}
      cells={function (row, col) {
        const cellProperties = {};
        const data = this.instance.getData();

        if (row === 0 || (data[row] && data[row][col] === 'readOnly')) {
          cellProperties.readOnly = true; // make cell read-only if it is first row or the text reads 'readOnly'
        }

        if (row === 0) {
          cellProperties.renderer = firstRowRenderer; // uses function directly
        } else {
          cellProperties.renderer = 'negativeValueRenderer'; // uses lookup map
        }

        return cellProperties;
      }}
    />
  );
};

export default ExampleComponent;

TypeScript

import { HotTable } from '@handsontable/react-wrapper';
import Handsontable from 'handsontable/base';
import { registerAllModules } from 'handsontable/registry';
import { BaseRenderer, registerRenderer } from 'handsontable/renderers';
import { textRenderer } from 'handsontable/renderers/textRenderer';

// register Handsontable's modules
registerAllModules();

const ExampleComponent = () => {
  const data = [
    ['', 'Tesla', 'Nissan', 'Toyota', 'Honda'],
    ['2017', -5, '', 12, 13],
    ['2018', '', -11, 14, 13],
    ['2019', '', 15, -12, 'readOnly'],
  ];

  const firstRowRenderer: BaseRenderer = (instance, td, ...rest) => {
    textRenderer(instance, td, ...rest);
    td.style.fontWeight = 'bold';
    td.style.color = 'green';
    td.style.background = '#CEC';
  };

  const negativeValueRenderer: BaseRenderer = (instance, td, row, col, prop, value, cellProperties) => {
    textRenderer(instance, td, row, col, prop, value, cellProperties);

    // if the row contains a negative number
    if (parseInt(value, 10) < 0) {
      // add class 'make-me-red'
      td.className = 'make-me-red';
    }

    if (!value || value === '') {
      td.style.background = 'rgb(238, 238, 238, 0.4)';
    } else {
      if (instance.getDataAtCell(0, col) === 'Nissan') {
        td.style.fontStyle = 'italic';
      }

      td.style.background = '';
    }
  };

  //  maps function to a lookup string
  registerRenderer('negativeValueRenderer', negativeValueRenderer);

  return (
    <HotTable
      data={data}
      autoWrapRow={true}
      autoWrapCol={true}
      licenseKey="non-commercial-and-evaluation"
      height="auto"
      afterSelection={function (this: Handsontable, _row, _col, row2, col2) {
        const meta = this.getCellMeta(row2, col2);

        if (meta.readOnly) {
          this.updateSettings({
            fillHandle: false,
          });
        } else {
          this.updateSettings({
            fillHandle: true,
          });
        }
      }}
      cells={function (row, col) {
        const cellProperties: Handsontable.CellMeta = {};
        const data = this.instance.getData();

        if (row === 0 || (data[row] && data[row][col] === 'readOnly')) {
          cellProperties.readOnly = true; // make cell read-only if it is first row or the text reads 'readOnly'
        }

        if (row === 0) {
          cellProperties.renderer = firstRowRenderer; // uses function directly
        } else {
          cellProperties.renderer = 'negativeValueRenderer'; // uses lookup map
        }

        return cellProperties;
      }}
    />
  );
};

export default ExampleComponent;

CSS

.make-me-red {
  color: #FF5A12 !important;
}

Related articles

Related guides

Formatting cells→

Configuration options

activeHeaderClassName→

className→

commentedCellClassName→

currentColClassName→

currentHeaderClassName→

currentRowClassName→

customBorders→

invalidCellClassName→

noWordWrapClassName→

placeholder→

placeholderCellClassName→

readOnlyCellClassName→

tableClassName→

Plugins

CustomBorders→