Conditional Formatting

The ConditionalFormattingPlugin applies visual formatting rules to cells based on their values. It supports four condition types: value-based styling, gradient color scales, data bars, and icon sets.

Live Demo

Gradient scales on Math (red→yellow→green), data bars on Science (blue), and icon sets on English (arrows).

View source code

import { useRef, useEffect, useState } from 'react';
import { WitTable } from '@witqq/spreadsheet-react';
import type { WitTableRef } from '@witqq/spreadsheet-react';
import type { ColumnDef } from '@witqq/spreadsheet';
import { ConditionalFormattingPlugin } from '@witqq/spreadsheet-plugins';
import { DemoWrapper } from './DemoWrapper';
import { useSiteTheme } from './useSiteTheme';

interface StudentRow {
  name: string;
  math: number;
  science: number;
  english: number;
  average: number;
}

const columns: ColumnDef[] = [
  { key: 'name', title: 'Student', width: 140 },
  { key: 'math', title: 'Math', width: 100, type: 'number' },
  { key: 'science', title: 'Science', width: 100, type: 'number' },
  { key: 'english', title: 'English', width: 100, type: 'number' },
  { key: 'average', title: 'Average', width: 100, type: 'number' },
];

function makeStudent(name: string, math: number, science: number, english: number): StudentRow {
  return { name, math, science, english, average: Math.round((math + science + english) / 3) };
}

const data: StudentRow[] = [
  makeStudent('Alice Johnson', 92, 88, 95),
  makeStudent('Bob Smith', 45, 72, 38),
  makeStudent('Carol Davis', 78, 91, 82),
  makeStudent('Dan Wilson', 31, 55, 67),
  makeStudent('Eve Martinez', 88, 64, 71),
  makeStudent('Frank Lee', 56, 83, 49),
  makeStudent('Grace Kim', 97, 95, 99),
  makeStudent('Hank Brown', 63, 41, 58),
  makeStudent('Ivy Chen', 74, 78, 86),
  makeStudent('Jake Taylor', 85, 69, 44),
];

export function ConditionalFormatDemo() {
  const { witTheme } = useSiteTheme();
  const tableRef = useRef<WitTableRef>(null);
  const [status, setStatus] = useState('Loading...');

  useEffect(() => {
    const engine = tableRef.current?.getInstance();
    if (!engine) return;

    const plugin = new ConditionalFormattingPlugin();
    engine.installPlugin(plugin);

    plugin.addRule(ConditionalFormattingPlugin.createGradientScale(
      { startRow: 0, startCol: 1, endRow: 9, endCol: 1 },
      [
        { value: 0, color: '#ef4444' },
        { value: 50, color: '#eab308' },
        { value: 100, color: '#22c55e' },
      ]
    ));

    plugin.addRule(ConditionalFormattingPlugin.createDataBar(
      { startRow: 0, startCol: 2, endRow: 9, endCol: 2 },
      '#3b82f6'
    ));

    plugin.addRule(ConditionalFormattingPlugin.createIconSet(
      { startRow: 0, startCol: 3, endRow: 9, endCol: 3 },
      'arrows'
    ));

    engine.requestRender();
    setStatus('Gradient: Math | Data Bars: Science | Icons: English');
  }, []);

  return (
    <DemoWrapper title="Live Demo" description="Gradient scales on Math (red→yellow→green), data bars on Science (blue), and icon sets on English (arrows)." height={420}>
      <div style={{ display: 'flex', flexDirection: 'column', height: '100%' }}>
        <div style={{ padding: '0.5rem 0.75rem', borderBottom: '1px solid #e2e8f0', flexShrink: 0, display: 'flex', gap: '0.5rem', alignItems: 'center' }}>
          <span style={{ fontSize: '0.8rem', color: '#64748b' }}>{status}</span>
        </div>
        <div style={{ flex: 1 }}>
          <WitTable
            theme={witTheme}
            ref={tableRef}
            columns={columns}
            data={data}
            showRowNumbers
            editable={false}
            style={{ width: '100%', height: '100%' }}
          />
        </div>
      </div>
    </DemoWrapper>
  );
}

Installation

import { ConditionalFormattingPlugin } from '@witqq/spreadsheet-plugins';

const cfPlugin = new ConditionalFormattingPlugin();
engine.installPlugin(cfPlugin);

Condition Types

Value Condition

Apply a style when a cell value matches a comparison:

interface ValueCondition {
  type: 'value';
  operator: ComparisonOperator;
  value: number;
  value2?: number; // Required for 'between' / 'notBetween'
}

Gradient Scale

Interpolate colors across a numeric range:

interface GradientScaleCondition {
  type: 'gradientScale';
  stops: readonly { value: number; color: string }[];
}

Data Bar

Fill a portion of the cell background proportional to the value:

interface DataBarCondition {
  type: 'dataBar';
  minValue?: number;
  maxValue?: number;
  color: string;
  showValue?: boolean;
}

Icon Set

Display an icon based on value thresholds:

interface IconSetCondition {
  type: 'iconSet';
  iconSet: IconSetName; // 'arrows' | 'circles' | 'flags' | 'stars'
  thresholds: readonly { value: number; icon: string }[];
  showValue?: boolean;
}

Built-in icon sets:

arrows — ▲ ▶ ▼
circles — colored circles
flags — flag indicators
stars — star ratings

Comparison Operators

type ComparisonOperator =
  | 'greaterThan' | 'lessThan'
  | 'greaterThanOrEqual' | 'lessThanOrEqual'
  | 'equal' | 'notEqual'
  | 'between' | 'notBetween';

Static Factory Methods

ConditionalFormattingPlugin provides convenience factories to create rules:

createValueRule

const rule = ConditionalFormattingPlugin.createValueRule(
  { startRow: 0, startCol: 2, endRow: 99, endCol: 2 }, // range
  'greaterThan',   // operator
  1000,            // threshold
  '#c6efce',       // bgColor
  { textColor: '#006100', priority: 1 }
);
cfPlugin.addRule(rule);

createGradientScale

const rule = ConditionalFormattingPlugin.createGradientScale(
  { startRow: 0, startCol: 3, endRow: 99, endCol: 3 },
  [
    { value: 0, color: '#f8696b' },    // red (low)
    { value: 50, color: '#ffeb84' },   // yellow (mid)
    { value: 100, color: '#63be7b' },  // green (high)
  ]
);
cfPlugin.addRule(rule);

createDataBar

const rule = ConditionalFormattingPlugin.createDataBar(
  { startRow: 0, startCol: 4, endRow: 99, endCol: 4 },
  '#638ec6',
  { minValue: 0, maxValue: 100, showValue: true }
);
cfPlugin.addRule(rule);

createIconSet

const rule = ConditionalFormattingPlugin.createIconSet(
  { startRow: 0, startCol: 5, endRow: 99, endCol: 5 },
  'arrows',
  { showValue: true }
);
cfPlugin.addRule(rule);

Managing Rules

cfPlugin.addRule(rule);                  // Add a rule
cfPlugin.removeRule(rule.id);            // Remove by ID
cfPlugin.clearRules();                   // Remove all rules
const rules = cfPlugin.getRules();       // Get all rules (readonly)

Rules are evaluated in priority order (lower number = higher priority). Set stopIfTrue: true to prevent further rule evaluation after a match.

Color Scale by Value

Highlight high revenue cells in green, low in red:

import { ConditionalFormattingPlugin } from '@witqq/spreadsheet-plugins';
import { WitTable, WitTableRef } from '@witqq/spreadsheet-react';

function App() {
  const ref = useRef<WitTableRef>(null);

  useEffect(() => {
    const cf = new ConditionalFormattingPlugin();
    ref.current?.installPlugin(cf);

    cf.addRule(
      ConditionalFormattingPlugin.createValueRule(
        { startRow: 0, startCol: 2, endRow: 999, endCol: 2 },
        'greaterThan',
        5000,
        '#c6efce',
        { textColor: '#006100' }
      )
    );

    cf.addRule(
      ConditionalFormattingPlugin.createValueRule(
        { startRow: 0, startCol: 2, endRow: 999, endCol: 2 },
        'lessThan',
        1000,
        '#ffc7ce',
        { textColor: '#9c0006' }
      )
    );
  }, []);

  return <WitTable ref={ref} columns={columns} data={data} />;
}

Data Bars for Progress

const progressRule = ConditionalFormattingPlugin.createDataBar(
  { startRow: 0, startCol: 3, endRow: 49, endCol: 3 },
  '#5b9bd5',
  { minValue: 0, maxValue: 100, showValue: true }
);
cfPlugin.addRule(progressRule);

Icon Sets for Status

const statusRule = ConditionalFormattingPlugin.createIconSet(
  { startRow: 0, startCol: 4, endRow: 49, endCol: 4 },
  'arrows',
  { showValue: false }
);
cfPlugin.addRule(statusRule);

Internal Utilities

The conditional formatting layer uses several internal utilities:

Function	Description
`toNumber(value)`	Convert cell value to number for comparison
`evaluateComparison(value, operator, threshold, threshold2?)`	Evaluate a comparison condition
`interpolateColor(value, stops)`	Interpolate between color stops for gradient scales
`getLayer()`	Access the `ConditionalFormatLayer` render layer instance