Conditional Formatting

Preset ModePlugin Mode

Preset Info

@univerjs/preset-sheets-conditional-formatting

Server Required

Conditional formatting allows users to automatically change the format of cells based on specific conditions, highlighting important data or trends. It supports various conditions and formatting options, helping users to understand data more intuitively.

Preset Mode

Installation

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

Usage

import { UniverSheetsConditionalFormattingPreset } from '@univerjs/preset-sheets-conditional-formatting'
import UniverPresetSheetsConditionalFormattingEnUS from '@univerjs/preset-sheets-conditional-formatting/locales/en-US'
import { UniverSheetsCorePreset } from '@univerjs/preset-sheets-core'
import UniverPresetSheetsCoreEnUS from '@univerjs/preset-sheets-core/locales/en-US'
import { createUniver, LocaleType, merge } from '@univerjs/presets'

import '@univerjs/preset-sheets-core/lib/index.css'
import '@univerjs/preset-sheets-conditional-formatting/lib/index.css'

const { univerAPI } = createUniver({
  locale: LocaleType.En_US,
  locales: {
    [LocaleType.En_US]: merge(
      {},
      UniverPresetSheetsCoreEnUS,
      UniverPresetSheetsConditionalFormattingEnUS, 
    ),
  },
  presets: [
    UniverSheetsCorePreset(),
    UniverSheetsConditionalFormattingPreset(), 
  ],
})

Plugin Mode

Installation

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

Usage

import { LocaleType, merge, Univer } from '@univerjs/core'
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({
  locale: LocaleType.En_US,
  locales: {
    [LocaleType.En_US]: merge(
      {},
      SheetsConditionalFormattingUIEnUS, 
    ),
  },
})

univer.registerPlugin(UniverSheetsConditionalFormattingPlugin) 
univer.registerPlugin(UniverSheetsConditionalFormattingUIPlugin)

Facade API

Complete Facade API type definitions can be found in the FacadeAPI.

Create Conditional Formatting Rules

FWorksheet.newConditionalFormattingRule() creates a conditional formatting builder, returning an instance of FConditionalFormattingBuilder, which can be used to generate conditional formatting rules through method chaining.

Here are some member methods of 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
setBackground	Sets the background color of the conditional format
setBold	Sets whether the font of the conditional format is bold
setFontColor	Sets the font color of the conditional format
setItalic	Sets whether the font of the conditional format is italic
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 worksheet
const fWorksheet = fWorkbook.getActiveSheet()
const rulesOfSheet = fWorksheet.getConditionalFormattingRules()

// Get all conditional formatting rules of the range A1:T100
const fRange = fWorksheet.getRange('A1:T100')
const rulesOfRange = fRange.getConditionalFormattingRules()

Remove Conditional Formatting Rules

FWorksheet.deleteConditionalFormattingRule(cfId) removes a conditional formatting rule from the worksheet, where cfId is the ID of the conditional formatting rule.

const fWorkbook = univerAPI.getActiveWorkbook()
const fWorksheet = fWorkbook.getActiveSheet()
const rules = fWorksheet.getConditionalFormattingRules()

// Remove the first conditional formatting rule
fWorksheet.deleteConditionalFormattingRule(rules[0]?.cfId)

Update Conditional Formatting Rules

FWorksheet.setConditionalFormattingRule(cfId, rule) updates a conditional formatting rule in the worksheet, where cfId is the ID of the conditional formatting rule and rule is the new conditional formatting 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 Rules

FWorksheet.moveConditionalFormattingRule(cfId, toCfId, type) modifies the priority of a conditional formatting rule in the worksheet, where cfId is the ID of the conditional formatting rule, toCfId is the ID of the target conditional formatting rule, and type specifies the move type (e.g., 'before', 'after').

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() clears all conditional formatting rules of the worksheet.
FRange.clearConditionalFormatRules() clears all conditional formatting rules of the range.

const fWorkbook = univerAPI.getActiveWorkbook()

// Clear all conditional formatting rules of the active worksheet
const fWorksheet = fWorkbook.getActiveSheet()
fWorksheet.clearConditionalFormatRules()

// Clear all conditional formatting rules of the range A1:T100
const fRange = fWorksheet.getRange('A1:T100')
fRange.clearConditionalFormatRules()

On this page