Rows n’ Columns Docs
Visit HomepagePricing
  • Introduction
  • License
  • Demos
  • Getting started
    • Installation
    • Spreadsheet state
    • Headless UI
    • Imperative Spreadsheet API
    • Examples
  • ⚙️Configuration
    • Features
      • Data validation
      • Formula evaluation
      • Real-time data
      • Cell editors
      • Cell renderer
      • Structured Cell Renderer
      • Theming
      • Styling
      • Context menu
      • Localisation
      • Undo/Redo
      • Conditional formatting
      • Named ranges
      • Structured references
        • Schema based tables and columns
        • Calculated columns
      • Basic filter or Excel AutoFilter
      • Charts
      • Embedded content
      • Calculate on-demand
      • Drag and Drop
      • Pivoting and Grouping (Coming soon)
      • Tokenizer
      • Lazy loading/Infinite scrolling
      • OpenAI/Chat GPT Integration
      • Search
      • Formula protection
      • Autofill
      • Export canvas as image
    • Components
      • Canvas Grid
      • Toolbar
      • Sheet Tabs
      • Sheet Switcher
      • Sheet Status
      • Range Selector
      • Formula Input
      • Selection Input
      • SheetSearch
      • NamedRangeEditor
      • DeleteSheetConfirmation
      • TableEditor
      • Cell Format Editor
      • Conditional Format Editor
      • Data Validation Editor
      • Insert Link Editor
      • Insert Image Editor
    • API
      • Cell Data
      • Sheets
      • SpreadsheetProvider
      • useSpreadsheet
      • Modules
      • SheetCell
    • Using Spreadsheet with NextJS
    • Keyboard shortcuts
  • Collaboration
    • Real time collaboration
    • Yjs Collaboration
    • Supabase realtime Collaboration
  • Charts
    • Charts
    • Custom charts
  • Excel and Google sheets
    • CSV
    • Excel
    • Google sheets (Coming soon)
  • Functions
    • Named functions
    • Array formulas
  • Data persistence
    • Server side data persistence
    • React Query integration
  • Specifications
    • Browser support
    • Third party licenses
  • Support
    • Contact support
    • Report bugs
    • Feature requests
Powered by GitBook
On this page
  • Gradient scale or heatmaps
  • Supported conditions
  • Adding conditional format editor UI

Was this helpful?

  1. Configuration
  2. Features

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
      },
    },
  },
],

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

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>
);
PreviousUndo/RedoNextNamed ranges

Last updated 11 months ago

Was this helpful?

⚙️
Conditional format editor