Conditional Formatting
@univerjs/preset-sheets-conditional-formatting
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 worksheetFRange.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?