Conditional formatting

Change text or background colour of cells based on the value

CanvasGrid accepts array of conditionalFormats prop which can be used to inject formatting rules based on the value of a cell. Formula values are supported.

import { SpreadsheetProvider, CanvasGrid } from "@rowsncolumns/spreadsheet";

const MySpreadsheet = () => {
  return (
    <CanvasGrid
      conditionalFormats={[
        {
          ranges: [
            {
              startRowIndex: 1,
              endRowIndex: 1000,
              startColumnIndex: 2,
              endColumnIndex: 2,
            },
          ],
          booleanRule: {
            condition: {
              type: "NUMBER_LESS",
              values: [
                {
                  userEnteredValue: "0",
                },
              ],
            },
            format: {
              backgroundColor: "red",
            },
          },
        },
      ]}
    />
  );
};

const App = () => (
  <SpreadsheetProvider>
    <MySpreadsheet />
  </SpreadsheetProvider>
);

Gradient scale or heatmaps

conditionalFormats: [
  {
    ranges: [
      {
        startRowIndex: 11,
        endRowIndex: 26,
        startColumnIndex: 7,
        endColumnIndex: 8,
      },
    ],
    gradientRule: {
      minpoint: {
        color: "#FFCF54",
        type: "MIN",
        // value: 10 Automatically calculated from range
      },
      midpoint: {
        color: "#FFE8A3",
        type: "MIN",
        // value: 12, Automatically calculated from range
      },
      maxpoint: {
        color: "#FFFFFF",
        type: "MAX",
        // value: 21, Automatically calculated from range
      },
    },
  },
],

Data bars

Paint a horizontal bar inside each cell, scaled by the cell's value relative to the range min/max. Round-trips XLSX + ODS.

conditionalFormats: [
  {
    ranges: [
      {
        startRowIndex: 1,
        endRowIndex: 20,
        startColumnIndex: 3,
        endColumnIndex: 3,
      },
    ],
    dataBarRule: {
      color: "#638EC6",
      minpoint: { type: "MIN" },
      maxpoint: { type: "MAX" },
      // Optional: separate color for negative values
      negativeColor: "#DC2626",
      // Optional: render direction ("leftToRight" / "rightToLeft")
      direction: "leftToRight",
      // Optional: hide the cell value, show only the bar
      showValue: true,
    },
  },
],

Icon sets

Stamp an icon to the left of each cell based on which bucket the value falls in. Many built-in icon families ship: 3TrafficLights1, 3TrafficLights2, 3Arrows, 3Symbols, 4Rating, 5Rating, 5Arrows, etc.

conditionalFormats: [
  {
    ranges: [
      {
        startRowIndex: 1,
        endRowIndex: 20,
        startColumnIndex: 4,
        endColumnIndex: 4,
      },
    ],
    iconSetRule: {
      iconSet: "3TrafficLights1",
      // Thresholds split the value range into N buckets — for a 3-icon
      // set we need 2 thresholds (low/mid/high).
      thresholds: [
        { type: "PERCENT", value: 33 },
        { type: "PERCENT", value: 66 },
      ],
      // Optional: reverse the icon order (so red lights up on high
      // values instead of low).
      reverse: false,
      // Optional: show only the icon, hide the cell value
      showValue: true,
    },
  },
],

Supported conditions

// Supported condition types
export const CONDITION_TYPES = [
  "NUMBER_GREATER",
  "NUMBER_GREATER_THAN_EQ",
  "NUMBER_LESS",
  "NUMBER_LESS_THAN_EQ",
  "NUMBER_EQ",
  "NUMBER_NOT_EQ",
  "NUMBER_BETWEEN",
  "NUMBER_NOT_BETWEEN",
  "TEXT_CONTAINS",
  "TEXT_NOT_CONTAINS",
  "TEXT_STARTS_WITH",
  "TEXT_ENDS_WITH",
  "TEXT_EQ",
  "TEXT_IS_EMAIL",
  "TEXT_IS_URL",
  "DATE_EQ",
  "DATE_BEFORE",
  "DATE_AFTER",
  "DATE_ON_OR_BEFORE",
  "DATE_ON_OR_AFTER",
  "DATE_BETWEEN",
  "DATE_NOT_BETWEEN",
  "DATE_IS_VALID",
  "ONE_OF_RANGE",
  "ONE_OF_LIST",
  "BLANK",
  "NOT_BLANK",
  "CUSTOM_FORMULA",
  "BOOLEAN",
  "TEXT_NOT_EQ",
  "DATE_NOT_EQ",
] as const;

Adding conditional format editor UI

Spreadsheet comes with a default conditional format editor.

Right click on any cell and select Conditional format editor . Or you can invoke the function onRequestConditionalFormat from useSpreadsheetState hook

Conditional format editor

import { SpreadsheetProvider, CanvasGrid, defaultSpreadsheetTheme } from "@rowsncolumns/spreadsheet";
import { ConditionalFormatDialog, ConditionalFormatEditor } from "@rowsncolumns/spreadsheet-state"

const MySpreadsheet = () => {
  const [conditionalFormats, onChangeConditionalFormats] = useState<
      ConditionalFormatRule[]
    >([]);
  const [theme, onChangeTheme] = useState<SpreadsheetTheme>(
    defaultSpreadsheetTheme
  );
  const {
      onRequestConditionalFormat,
      createHistory,
      getSheetId,
      getSheetName,
      rowCount,
      columnCount,
      activeSheetId,
      onCreateConditionalFormattingRule,
      onUpdateConditionalFormattingRule,
      onDeleteConditionalFormattingRule,
      onPreviewConditionalFormattingRule,
  } = useSpreadsheetState()
  return (
    <>
      <CanvasGrid
        conditionalFormats={conditionalFormats}
        onRequestConditionalFormat={onRequestConditionalFormat}      
      />
  
      <ConditionalFormatDialog>
        <ConditionalFormatEditor
          sheetId={activeSheetId}
          rowCount={rowCount}
          columnCount={columnCount}
          getSheetName={getSheetName}
          getSheetId={getSheetId}
          theme={theme}
          conditionalFormats={conditionalFormats}
          onCreateRule={onCreateConditionalFormattingRule}
          onDeleteRule={onDeleteConditionalFormattingRule}
          onUpdateRule={onUpdateConditionalFormattingRule}
          onPreviewRule={onPreviewConditionalFormattingRule}
        />
      </ConditionalFormatDialog>
    </>
  );
};

const App = () => (
  <SpreadsheetProvider>
    <MySpreadsheet />
  </SpreadsheetProvider>
);