Conditional Formatting

Facade API	Has Paid Plan	Univer Server	Univer on Node.js	Preset
✅	-	-	✅	UniverSheetsConditionalFormattingPreset

Conditional formatting is a feature that sets rules in cells to automatically set cell formats based on the value of the data.

This feature includes the following plugin packages:

@univerjs/sheets-conditional-formatting - fundamental conditional formatting plugin
@univerjs/sheets-conditional-formatting-ui - UI plugin for conditional formatting

Presets Installation


import { createUniver, defaultTheme, LocaleType, merge } from '@univerjs/presets';
import { UniverSheetsCorePreset } from '@univerjs/presets/preset-sheets-core';
import UniverPresetSheetsCoreEnUS from '@univerjs/presets/preset-sheets-core/locales/en-US';
import { UniverSheetsConditionalFormattingPreset } from '@univerjs/presets/preset-sheets-conditional-formatting';
import UniverPresetSheetsConditionalFormattingEnUS from '@univerjs/presets/preset-sheets-conditional-formatting/locales/en-US';
 
import '@univerjs/presets/lib/styles/preset-sheets-core.css'
import '@univerjs/presets/lib/styles/preset-sheets-conditional-formatting.css'
 
const { univerAPI } = createUniver({
  locale: LocaleType.EN_US,
  locales: {
    [LocaleType.EN_US]: merge(
      {},
      UniverPresetSheetsCoreEnUS,
      UniverPresetSheetsConditionalFormattingEnUS 
    ),
  },
  theme: defaultTheme,
  presets: [
    UniverSheetsCorePreset(),
    UniverSheetsConditionalFormattingPreset(),
  ],
});

Piecemeal Installation


npm install @univerjs/sheets-conditional-formatting @univerjs/sheets-conditional-formatting-ui


pnpm add @univerjs/sheets-conditional-formatting @univerjs/sheets-conditional-formatting-ui


yarn add @univerjs/sheets-conditional-formatting @univerjs/sheets-conditional-formatting-ui


bun add @univerjs/sheets-conditional-formatting @univerjs/sheets-conditional-formatting-ui


import { LocaleType, merge, Univer } from '@univerjs/core';
import { defaultTheme } from "@univerjs/design";
import { UniverSheetsConditionalFormattingPlugin } from '@univerjs/sheets-conditional-formatting';
import { UniverSheetsConditionalFormattingUIPlugin } from '@univerjs/sheets-conditional-formatting-ui';
import SheetsConditionalFormattingUIEnUS from '@univerjs/sheets-conditional-formatting-ui/locale/en-US';
 
import '@univerjs/sheets-conditional-formatting-ui/lib/index.css';
 
import '@univerjs/sheets-conditional-formatting/facade';
 
const univer = new Univer({
  theme: defaultTheme,
  locale: LocaleType.EN_US,
  locales: {
    [LocaleType.EN_US]: merge(
      SheetsConditionalFormattingUIEnUS
    ),
  },
});
 
univer.registerPlugin(UniverSheetsConditionalFormattingPlugin);
univer.registerPlugin(UniverSheetsConditionalFormattingUIPlugin);

Univer on Node.js Piecemeal Installation


npm install @univerjs/sheets-conditional-formatting


pnpm add @univerjs/sheets-conditional-formatting


yarn add @univerjs/sheets-conditional-formatting


bun add @univerjs/sheets-conditional-formatting


import { LocaleType, Univer } from '@univerjs/core';
import { defaultTheme } from "@univerjs/design";
import { UniverSheetsConditionalFormattingPlugin } from '@univerjs/sheets-conditional-formatting';
 
import '@univerjs/sheets-conditional-formatting/facade';
 
const univer = new Univer({
  theme: defaultTheme,
  locale: LocaleType.EN_US,
});
 
univer.registerPlugin(UniverSheetsConditionalFormattingPlugin);

Facade API

To get full definition of facade api, please refer to FacadeAPI

Create a Conditional Formatting Rule

FWorksheet.newConditionalFormattingRule() creates a conditional formatting builder that returns an instance of FConditionalFormattingBuilder that can be used to create conditional formatting rules through chain calls.

Here are some member methods on FConditionalFormattingBuilder:

Method	Description
build	Builds the conditional formatting rule
setRanges	Sets the range of the conditional formatting rule
setAverage	Sets the average value conditional formatting rule
setUniqueValues	Sets the unique value conditional formatting rule
setDuplicateValues	Sets the duplicate value conditional formatting rule
setRank	Sets the ranking conditional formatting rule
setIconSet	Sets the icon set conditional formatting rule
setColorScale	Sets the color scale conditional formatting rule
setDataBar	Sets the data bar conditional formatting rule
setFontColor	Sets the font color of the conditional format
setItalic	Sets whether the font of the conditional format is italic
setBackground	Sets the background color of the conditional format
setBold	Sets whether the font of the conditional format is bold
setStrikethrough	Sets whether the font of the conditional format has a strikethrough
setUnderline	Sets whether the font of the conditional format has an underline
whenCellEmpty	Sets the conditional format rule to trigger when the cell is empty
whenCellNotEmpty	Sets the conditional format rule to trigger when the cell is not empty
whenDate	Sets the conditional format rule to trigger when the date is within a specific time period
whenFormulaSatisfied	Sets the conditional format rule to trigger when the formula calculation result is true
whenNumberBetween	Sets the conditional format rule to trigger when the number is between two specified values or equal to one of them
whenNumberEqualTo	Sets the conditional format rule to trigger when the number is equal to the given value
whenNumberGreaterThan	Sets the conditional format rule to trigger when the number is greater than the given value
whenNumberGreaterThanOrEqualTo	Sets the conditional format rule to trigger when the number is greater than or equal to the given value
whenNumberLessThan	Sets the conditional format rule to trigger when the number is less than the given value
whenNumberLessThanOrEqualTo	Sets the conditional format rule to trigger when the number is less than or equal to the given value
whenNumberNotBetween	Sets the conditional format rule to trigger when the number is not between two specified values and not equal to them
whenNumberNotEqualTo	Sets the conditional format rule to trigger when the number is not equal to the given value
whenTextContains	Sets the conditional format rule to trigger when the text contains the specified value
whenTextDoesNotContain	Sets the conditional format rule to trigger when the text does not contain the specified value
whenTextEndsWith	Sets the conditional format rule to trigger when the text ends with the specified value
whenTextEqualTo	Sets the conditional format rule to trigger when the text equals the given value
whenTextStartsWith	Sets the conditional format rule to trigger when the text starts with the specified value


const fWorkbook = univerAPI.getActiveWorkbook();
const fWorksheet = fWorkbook.getActiveSheet();
 
// Create a conditional formatting rule that sets the cell format to italic, red background, and green font color when the cell is not empty.
const fRange = fWorksheet.getRange('A1:T100');
const rule = fWorksheet.newConditionalFormattingRule()
  .whenCellNotEmpty()
  .setRanges([fRange.getRange()])
  .setItalic(true)
  .setBackground('red')
  .setFontColor('green')
  .build();
fWorksheet.addConditionalFormattingRule(rule);

Get Conditional Formatting Rules

FWorksheet.getConditionalFormattingRules() gets all conditional formatting rules of the worksheet
FRange.getConditionalFormattingRules() gets all conditional formatting rules of the range


const fWorkbook = univerAPI.getActiveWorkbook();
 
// Get all conditional formatting rules of the active sheet
const fWorksheet = fWorkbook.getActiveSheet();
const rulesOfSheet = fWorksheet.getConditionalFormattingRules();
console.log(rulesOfSheet);
 
// Get all conditional formatting rules of the range A1:T100
const fRange = fWorksheet.getRange('A1:T100');
const rulesOfRange = fRange.getConditionalFormattingRules();
console.log(rulesOfRange);

Delete Conditional Formatting Rule

FWorksheet.deleteConditionalFormattingRule(cfId) method deletes the conditional formatting rule of the worksheet, where cfId is the id of the conditional formatting rule.


const fWorkbook = univerAPI.getActiveWorkbook();
const fWorksheet = fWorkbook.getActiveSheet();
const rules = fWorksheet.getConditionalFormattingRules();
 
// Delete the first rule
fWorksheet.deleteConditionalFormattingRule(rules[0]?.cfId);

Update Conditional Formatting Rule

FWorksheet.setConditionalFormattingRule(cfId, rule) 方法可以更新工作表的条件格式规则，cfId 为条件格式规则的 id，rule 为新的条件格式规则。


const fWorkbook = univerAPI.getActiveWorkbook();
const fWorksheet = fWorkbook.getActiveSheet();
 
// Create a conditional formatting rule that sets the cell format to italic, red background, and green font color when the cell is not empty.
const fRange = fWorksheet.getRange('A1:T100');
const rule = fWorksheet.newConditionalFormattingRule()
  .whenCellNotEmpty()
  .setRanges([fRange.getRange()])
  .setItalic(true)
  .setBackground('red')
  .setFontColor('green')
  .build();
fWorksheet.addConditionalFormattingRule(rule);
 
// Modify the first rule to apply to a new range
const rules = fWorksheet.getConditionalFormattingRules();
const newRuleRange = fWorksheet.getRange('A1:D10');
fWorksheet.setConditionalFormattingRule(rules[0]?.cfId, { ...rules[0], ranges: [newRuleRange.getRange()] });

Modify the Priority of Conditional Formatting Rule

FWorksheet.moveConditionalFormattingRule(cfId, toCfId, type) method can modify the priority of the conditional formatting rule of the worksheet, where cfId is the id of the conditional formatting rule, toCfId is the id of the target conditional formatting rule, and type is the move type.


const fWorkbook = univerAPI.getActiveWorkbook();
const fWorksheet = fWorkbook.getActiveSheet();
const rules = fWorksheet.getConditionalFormattingRules();
 
// Move the third rule before the first rule
const rule = rules[2];
const targetRule = rules[0];
fWorksheet.moveConditionalFormattingRule(rule?.cfId, targetRule?.cfId, 'before');

Clear All Conditional Formatting Rules

FWorksheet.clearConditionalFormatRules() clear all conditional formatting rules of the worksheet
FRange.clearConditionalFormatRules() clear all conditional formatting rules of the range


const fWorkbook = univerAPI.getActiveWorkbook();
 
// Clear all conditional formatting rules of the active sheet
const fWorksheet = fWorkbook.getActiveSheet();
fWorksheet.clearConditionalFormatRules();
 
// Clear all conditional formatting rules of the range A1:T100
const fRange = fWorksheet.getRange('A1:T100');
fRange.clearConditionalFormatRules();