Conditional Formatting

Conditional formatting applies visual styles to the cells of a range that meet a condition, so patterns, errors and key values stand out. In Jspreadsheet the rules are validations with a visual action: a format rule styles the cells that match its criteria, and the visual scales dataBar and colorScale paint every cell of the range by the position of its value. Rules follow the Excel model, stack in configuration order, and can be imported from and exported to XLSX.

What's new with Version 13

Rank and range rules: the types duplicate, unique, aboveAverage, belowAverage, top and bottom evaluate each cell against its whole range

Visual scales: the actions dataBar and colorScale paint every cell by the position of its value between the range minimum and maximum

Stacking and stop if true: every matching format rule applies, in configuration order, and a matched rule with stopIfTrue: true cuts the rules after it. Version 12 stopped at the first match

Sparse evaluation: a rule on a large range never materializes cells, and a changed value re-applies the covered range through the dependency chain

Documentation

Conditional formatting rules are managed with the validation methods, setValidations, getValidations and resetValidations, and they fire the onvalidation event. See the validations section for the methods and the events.

Rule object

Property	Description
`range: string`	The cells the rule covers. Example: `Sheet1!A1:A8`, or a whole column as `Sheet1!E:E`.
`action: string`	`'format'` styles the matching cells; `'dataBar'` and `'colorScale'` paint every cell of the range by the position of its value between the range minimum and maximum.
`type: string`	For `format` rules, the comparison type: `'number' \| 'text' \| 'date' \| 'time' \| 'textLength' \| 'empty' \| 'notEmpty' \| 'formula'`, or one of the range rules `'duplicate' \| 'unique' \| 'aboveAverage' \| 'belowAverage' \| 'top' \| 'bottom'`.
`criteria: string`	For comparison types: `'=' \| '!=' \| '>=' \| '>' \| '<=' \| '<' \| 'between' \| 'not between' \| 'contains' \| 'not contains' \| 'begins with' \| 'ends with'`. For `top` and `bottom`: `'percent'` reads the rank size as a percentage.
`value: array`	The comparison values. For `top` and `bottom`, `[N]` sets the rank size, default 10. For `formula`, the formula evaluated on each cell.
`format: object`	For `format`: `color`, `background-color`, `font-weight`, `font-style`. For `dataBar`: `color`, the bar color. For `colorScale`: `colors`, two or three color stops, Excel's red, yellow and green by default.
`className: string`	For `format`: a CSS class added to the matching cells.
`stopIfTrue: boolean`	For `format`: when this rule matches a cell, the rules after it are not applied to that cell.

Range rules

The range rules evaluate each cell against the whole range of the rule, not against a fixed value.

Type	Matches
`duplicate`	Cells whose value appears more than once in the range.
`unique`	Cells whose value appears exactly once in the range.
`aboveAverage`	Cells above the average of the numeric values in the range.
`belowAverage`	Cells below the average of the numeric values in the range.
`top`	The N highest numeric values, `value: [N]`, or the top N percent with `criteria: 'percent'`.
`bottom`	The N lowest numeric values, `value: [N]`, or the bottom N percent with `criteria: 'percent'`.

The range statistics are computed on demand and never materialize cells of the sparse storage. A changed value re-applies the covered range through the dependency chain.

Visual scales

Action	Description
`dataBar`	A proportional background bar, `format.color`, whose length is the position of the value between the minimum and the maximum of the range.
`colorScale`	A background color interpolated over two or three stops, `format.colors`, by the position of the value between the minimum and the maximum of the range.

Visual scales are presentation only: they have no pass or fail condition and no message.

Stacking and stop if true

Every matching format rule applies to a cell, stacking in configuration order. When two rules set the same property, the first rule wins. A matched rule with stopIfTrue: true prevents the rules after it from applying to that cell. The order of the rules in the configuration is the evaluation order; the validations extension lets the user reorder the rules and edit the flag, and the XLSX parser and render keep the flag and the Excel priority.

Examples

Rank rules, data bars and colour scales

<html>
<script src="https://jspreadsheet.com/v13/jspreadsheet.js"></script>
<script src="https://jsuites.net/v6/jsuites.js"></script>
<link rel="stylesheet" href="https://jspreadsheet.com/v13/jspreadsheet.css" type="text/css" />
<link rel="stylesheet" href="https://jsuites.net/v6/jsuites.css" type="text/css" />

<link rel="stylesheet" href="https://fonts.googleapis.com/css?family=Material+Icons" />

<div id="spreadsheet"></div>

<script>
// You can use the following license for quick testing on localhost, StackBlitz, or CodeSandbox.
// The license is valid for one day, after which the spreadsheet will become read-only.
// For a longer trial period, you can create a free account and generate a demo license with an extended expiration date.
jspreadsheet.setLicense('N2RjMzQzYzg2NGIyOTgxYThlZDljZTRmZWE2ZmI0NDQ2MGEyYTQzNDYwYzZiOTZkZDAxNmFlOTYwMDY4M2E3ODFmNzFjOTEwODMyYjM1OGNiZjA2ZGVjODI4ZmMwNTVkM2NlNTE4MTcyZWNhMzRjZjM4NDFmNDNiMTBhZDc2NWMsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94Tnprd01qTTNNVFkwTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2liRzlqWVd4b2IzTjBJbDBzSW5Cc1lXNGlPaUl6TkNJc0luTmpiM0JsSWpwYkluWTNJaXdpZGpnaUxDSjJPU0lzSW5ZeE1DSXNJbll4TVNJc0luWXhNaUlzSW5ZeE15SXNJbU5vWVhKMGN5SXNJbVp2Y20xeklpd2labTl5YlhWc1lTSXNJbkJoY25ObGNpSXNJbkpsYm1SbGNpSXNJbU52YlcxbGJuUnpJaXdpYVcxd2IzSjBaWElpTENKaVlYSWlMQ0oyWVd4cFpHRjBhVzl1Y3lJc0luTmxZWEpqYUNJc0luQnlhVzUwSWl3aWMyaGxaWFJ6SWl3aVkyeHBaVzUwSWl3aWMyVnlkbVZ5SWl3aWMyaGhjR1Z6SWl3aVptOXliV0YwSWl3aWNHbDJiM1FpWFN3aVpHVnRieUk2ZEhKMVpYMD0=');

// Create the spreadsheet
jspreadsheet(document.getElementById('spreadsheet'), {
    worksheets: [{
        worksheetName: 'Sales',
        data: [
            ['North', 120, 120, 0.42],
            ['South', 80, 80, 0.11],
            ['East', 200, 200, 0.95],
            ['West', 45, 45, 0.27],
            ['Central', 120, 120, 0.63],
            ['Islands', 15, 15, 0.08],
        ],
        columns: [
            { title: 'Region', width: 120 },
            { title: 'Top 3', width: 100, type: 'number' },
            { title: 'Data bar', width: 140, type: 'number' },
            { title: 'Color scale', width: 120, type: 'number', mask: '0%' },
        ],
    }],
    validations: [
        {
            // The three highest values in bold green
            range: 'Sales!B1:B6',
            action: 'format',
            type: 'top',
            value: [3],
            format: { color: '#2e7d32', 'font-weight': 'bold' },
        },
        {
            // Duplicated values in red, applied after the rule above
            range: 'Sales!B1:B6',
            action: 'format',
            type: 'duplicate',
            format: { color: '#c62828' },
        },
        {
            // A proportional bar
            range: 'Sales!C1:C6',
            action: 'dataBar',
            format: { color: '#90caf9' },
        },
        {
            // Red to green through yellow
            range: 'Sales!D1:D6',
            action: 'colorScale',
            format: { colors: ['#f8696b', '#ffeb84', '#63be7b'] },
        },
    ],
});
</script>
</html>

import React, { useRef } from "react";
import { Spreadsheet, Worksheet, jspreadsheet } from "@jspreadsheet/react";
import "jsuites/dist/jsuites.css";
import "jspreadsheet/dist/jspreadsheet.css";

// You can use the following license for quick testing on localhost, StackBlitz, or CodeSandbox.
// The license is valid for one day, after which the spreadsheet will become read-only.
// For a longer trial period, you can create a free account and generate a demo license with an extended expiration date.
jspreadsheet.setLicense('N2RjMzQzYzg2NGIyOTgxYThlZDljZTRmZWE2ZmI0NDQ2MGEyYTQzNDYwYzZiOTZkZDAxNmFlOTYwMDY4M2E3ODFmNzFjOTEwODMyYjM1OGNiZjA2ZGVjODI4ZmMwNTVkM2NlNTE4MTcyZWNhMzRjZjM4NDFmNDNiMTBhZDc2NWMsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94Tnprd01qTTNNVFkwTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2liRzlqWVd4b2IzTjBJbDBzSW5Cc1lXNGlPaUl6TkNJc0luTmpiM0JsSWpwYkluWTNJaXdpZGpnaUxDSjJPU0lzSW5ZeE1DSXNJbll4TVNJc0luWXhNaUlzSW5ZeE15SXNJbU5vWVhKMGN5SXNJbVp2Y20xeklpd2labTl5YlhWc1lTSXNJbkJoY25ObGNpSXNJbkpsYm1SbGNpSXNJbU52YlcxbGJuUnpJaXdpYVcxd2IzSjBaWElpTENKaVlYSWlMQ0oyWVd4cFpHRjBhVzl1Y3lJc0luTmxZWEpqYUNJc0luQnlhVzUwSWl3aWMyaGxaWFJ6SWl3aVkyeHBaVzUwSWl3aWMyVnlkbVZ5SWl3aWMyaGhjR1Z6SWl3aVptOXliV0YwSWl3aWNHbDJiM1FpWFN3aVpHVnRieUk2ZEhKMVpYMD0=');

export default function App() {
    // Spreadsheet array of worksheets
    const spreadsheet = useRef();
    // Data
    const data = [
        ['North', 120, 120, 0.42],
        ['South', 80, 80, 0.11],
        ['East', 200, 200, 0.95],
        ['West', 45, 45, 0.27],
        ['Central', 120, 120, 0.63],
        ['Islands', 15, 15, 0.08],
    ];
    // Columns
    const columns = [
        { title: 'Region', width: 120 },
        { title: 'Top 3', width: 100, type: 'number' },
        { title: 'Data bar', width: 140, type: 'number' },
        { title: 'Color scale', width: 120, type: 'number', mask: '0%' },
    ];
    // Rules
    const validations = [
        { range: 'Sales!B1:B6', action: 'format', type: 'top', value: [3], format: { color: '#2e7d32', 'font-weight': 'bold' } },
        { range: 'Sales!B1:B6', action: 'format', type: 'duplicate', format: { color: '#c62828' } },
        { range: 'Sales!C1:C6', action: 'dataBar', format: { color: '#90caf9' } },
        { range: 'Sales!D1:D6', action: 'colorScale', format: { colors: ['#f8696b', '#ffeb84', '#63be7b'] } },
    ];

    // Render component
    return (
        <Spreadsheet ref={spreadsheet} validations={validations}>
            <Worksheet data={data} columns={columns} worksheetName="Sales" />
        </Spreadsheet>
    );
}

<template>
    <Spreadsheet ref="spreadsheet" :validations="validations">
        <Worksheet :data="data" :columns="columns" worksheetName="Sales" />
    </Spreadsheet>
</template>

<script>
import { Spreadsheet, Worksheet, jspreadsheet } from "@jspreadsheet/vue";
import "jsuites/dist/jsuites.css";
import "jspreadsheet/dist/jspreadsheet.css";

// You can use the following license for quick testing on localhost, StackBlitz, or CodeSandbox.
// The license is valid for one day, after which the spreadsheet will become read-only.
// For a longer trial period, you can create a free account and generate a demo license with an extended expiration date.
jspreadsheet.setLicense('N2RjMzQzYzg2NGIyOTgxYThlZDljZTRmZWE2ZmI0NDQ2MGEyYTQzNDYwYzZiOTZkZDAxNmFlOTYwMDY4M2E3ODFmNzFjOTEwODMyYjM1OGNiZjA2ZGVjODI4ZmMwNTVkM2NlNTE4MTcyZWNhMzRjZjM4NDFmNDNiMTBhZDc2NWMsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94Tnprd01qTTNNVFkwTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2liRzlqWVd4b2IzTjBJbDBzSW5Cc1lXNGlPaUl6TkNJc0luTmpiM0JsSWpwYkluWTNJaXdpZGpnaUxDSjJPU0lzSW5ZeE1DSXNJbll4TVNJc0luWXhNaUlzSW5ZeE15SXNJbU5vWVhKMGN5SXNJbVp2Y20xeklpd2labTl5YlhWc1lTSXNJbkJoY25ObGNpSXNJbkpsYm1SbGNpSXNJbU52YlcxbGJuUnpJaXdpYVcxd2IzSjBaWElpTENKaVlYSWlMQ0oyWVd4cFpHRjBhVzl1Y3lJc0luTmxZWEpqYUNJc0luQnlhVzUwSWl3aWMyaGxaWFJ6SWl3aVkyeHBaVzUwSWl3aWMyVnlkbVZ5SWl3aWMyaGhjR1Z6SWl3aVptOXliV0YwSWl3aWNHbDJiM1FpWFN3aVpHVnRieUk2ZEhKMVpYMD0=');

export default {
    components: {
        Spreadsheet,
        Worksheet,
    },
    data() {
        return {
            data: [
                ['North', 120, 120, 0.42],
                ['South', 80, 80, 0.11],
                ['East', 200, 200, 0.95],
                ['West', 45, 45, 0.27],
                ['Central', 120, 120, 0.63],
                ['Islands', 15, 15, 0.08],
            ],
            columns: [
                { title: 'Region', width: 120 },
                { title: 'Top 3', width: 100, type: 'number' },
                { title: 'Data bar', width: 140, type: 'number' },
                { title: 'Color scale', width: 120, type: 'number', mask: '0%' },
            ],
            validations: [
                { range: 'Sales!B1:B6', action: 'format', type: 'top', value: [3], format: { color: '#2e7d32', 'font-weight': 'bold' } },
                { range: 'Sales!B1:B6', action: 'format', type: 'duplicate', format: { color: '#c62828' } },
                { range: 'Sales!C1:C6', action: 'dataBar', format: { color: '#90caf9' } },
                { range: 'Sales!D1:D6', action: 'colorScale', format: { colors: ['#f8696b', '#ffeb84', '#63be7b'] } },
            ],
        };
    }
}
</script>

import { Component, ViewChild, ElementRef } from "@angular/core";
import jspreadsheet from "jspreadsheet";
import "jsuites/dist/jsuites.css";
import "jspreadsheet/dist/jspreadsheet.css";

// You can use the following license for quick testing on localhost, StackBlitz, or CodeSandbox.
// The license is valid for one day, after which the spreadsheet will become read-only.
// For a longer trial period, you can create a free account and generate a demo license with an extended expiration date.
jspreadsheet.setLicense('N2RjMzQzYzg2NGIyOTgxYThlZDljZTRmZWE2ZmI0NDQ2MGEyYTQzNDYwYzZiOTZkZDAxNmFlOTYwMDY4M2E3ODFmNzFjOTEwODMyYjM1OGNiZjA2ZGVjODI4ZmMwNTVkM2NlNTE4MTcyZWNhMzRjZjM4NDFmNDNiMTBhZDc2NWMsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94Tnprd01qTTNNVFkwTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2liRzlqWVd4b2IzTjBJbDBzSW5Cc1lXNGlPaUl6TkNJc0luTmpiM0JsSWpwYkluWTNJaXdpZGpnaUxDSjJPU0lzSW5ZeE1DSXNJbll4TVNJc0luWXhNaUlzSW5ZeE15SXNJbU5vWVhKMGN5SXNJbVp2Y20xeklpd2labTl5YlhWc1lTSXNJbkJoY25ObGNpSXNJbkpsYm1SbGNpSXNJbU52YlcxbGJuUnpJaXdpYVcxd2IzSjBaWElpTENKaVlYSWlMQ0oyWVd4cFpHRjBhVzl1Y3lJc0luTmxZWEpqYUNJc0luQnlhVzUwSWl3aWMyaGxaWFJ6SWl3aVkyeHBaVzUwSWl3aWMyVnlkbVZ5SWl3aWMyaGhjR1Z6SWl3aVptOXliV0YwSWl3aWNHbDJiM1FpWFN3aVpHVnRieUk2ZEhKMVpYMD0=');

// Create component
@Component({
    standalone: true,
    selector: "app-root",
    template: `<div #spreadsheet></div>`,
})
export class AppComponent {
    @ViewChild("spreadsheet") spreadsheet: ElementRef;
    // Worksheets
    worksheets: jspreadsheet.worksheetInstance[];
    // Create a new data grid
    ngAfterViewInit() {
        // Create spreadsheet
        this.worksheets = jspreadsheet(this.spreadsheet.nativeElement, {
            worksheets: [{
                worksheetName: 'Sales',
                data: [
                    ['North', 120, 120, 0.42],
                    ['South', 80, 80, 0.11],
                    ['East', 200, 200, 0.95],
                    ['West', 45, 45, 0.27],
                    ['Central', 120, 120, 0.63],
                    ['Islands', 15, 15, 0.08],
                ],
                columns: [
                    { title: 'Region', width: 120 },
                    { title: 'Top 3', width: 100, type: 'number' },
                    { title: 'Data bar', width: 140, type: 'number' },
                    { title: 'Color scale', width: 120, type: 'number', mask: '0%' },
                ],
            }],
            validations: [
                { range: 'Sales!B1:B6', action: 'format', type: 'top', value: [3], format: { color: '#2e7d32', 'font-weight': 'bold' } },
                { range: 'Sales!B1:B6', action: 'format', type: 'duplicate', format: { color: '#c62828' } },
                { range: 'Sales!C1:C6', action: 'dataBar', format: { color: '#90caf9' } },
                { range: 'Sales!D1:D6', action: 'colorScale', format: { colors: ['#f8696b', '#ffeb84', '#63be7b'] } },
            ],
        });
    }
}

Stop if true

Two rules cover the same range. The first one matches the values above 100 and stops the evaluation, so the second rule only paints the remaining cells.

validations: [
    {
        range: 'Sales!B1:B6',
        action: 'format',
        type: 'number',
        criteria: '>',
        value: [100],
        format: { 'background-color': '#c8e6c9' },
        stopIfTrue: true,
    },
    {
        range: 'Sales!B1:B6',
        action: 'format',
        type: 'aboveAverage',
        format: { 'font-weight': 'bold' },
    },
]