Conditional Formatting

GitHubEdit on GitHub
Preset Info
@univerjs/preset-sheets-conditional-formatting
Server Required
No

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:

MethodDescription
buildBuilds the conditional formatting rule
setRangesSets the range of the conditional formatting rule
setAverageSets the average value conditional formatting rule
setUniqueValuesSets the unique value conditional formatting rule
setDuplicateValuesSets the duplicate value conditional formatting rule
setRankSets the ranking conditional formatting rule
setIconSetSets the icon set conditional formatting rule
setColorScaleSets the color scale conditional formatting rule
setDataBarSets the data bar conditional formatting rule
setBackgroundSets the background color of the conditional format
setBoldSets whether the font of the conditional format is bold
setFontColorSets the font color of the conditional format
setItalicSets whether the font of the conditional format is italic
setStrikethroughSets whether the font of the conditional format has a strikethrough
setUnderlineSets whether the font of the conditional format has an underline
whenCellEmptySets the conditional format rule to trigger when the cell is empty
whenCellNotEmptySets the conditional format rule to trigger when the cell is not empty
whenDateSets the conditional format rule to trigger when the date is within a specific time period
whenFormulaSatisfiedSets the conditional format rule to trigger when the formula calculation result is true
whenNumberBetweenSets the conditional format rule to trigger when the number is between two specified values or equal to one of them
whenNumberEqualToSets the conditional format rule to trigger when the number is equal to the given value
whenNumberGreaterThanSets the conditional format rule to trigger when the number is greater than the given value
whenNumberGreaterThanOrEqualToSets the conditional format rule to trigger when the number is greater than or equal to the given value
whenNumberLessThanSets the conditional format rule to trigger when the number is less than the given value
whenNumberLessThanOrEqualToSets the conditional format rule to trigger when the number is less than or equal to the given value
whenNumberNotBetweenSets the conditional format rule to trigger when the number is not between two specified values and not equal to them
whenNumberNotEqualToSets the conditional format rule to trigger when the number is not equal to the given value
whenTextContainsSets the conditional format rule to trigger when the text contains the specified value
whenTextDoesNotContainSets the conditional format rule to trigger when the text does not contain the specified value
whenTextEndsWithSets the conditional format rule to trigger when the text ends with the specified value
whenTextEqualToSets the conditional format rule to trigger when the text equals the given value
whenTextStartsWithSets 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()

How is this guide?