API Reference

Conditional Formatting

Packages@univerjs/sheets-conditional-formatting

This class should not be instantiated directly. Use factory methods on univerAPI instead.

Overview

@univerjs/sheets-conditional-formatting

MethodDescription
buildConstructs a conditional format rule from the settings applied to the builder
copyDeep clone a current builder
createCfIdCreate a conditional format ID
getIconMapGet the icon set mapping dictionary
getRangesGets the scope of the current conditional format
setAverageSet average rule
setBackgroundSets the background color for the conditional format rule's format
setBoldSets text bolding for the conditional format rule's format
setColorScaleSet color scale rule
setDataBarSet data bar rule
setDuplicateValuesSet duplicate values rule
setFontColorSets the font color for the conditional format rule's format
setIconSetSet up icon set conditional formatting rule
setItalicSets text italics for the conditional format rule's format
setRankSet rank rule
setStrikethroughSets text strikethrough for the conditional format rule's format
setUnderlineSets text underlining for the conditional format rule's format
setUniqueValuesSet unique values rule
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 a time period is met
whenFormulaSatisfiedSets the conditional format rule to trigger when the given formula evaluates to true
whenNumberBetweenSets the conditional format rule to trigger when a number falls between, or is either of, two specified values
whenNumberEqualToSets the conditional format rule to trigger when a number is equal to the given value
whenNumberGreaterThanSets the conditional format rule to trigger when a number is greater than the given value
whenNumberGreaterThanOrEqualToSets the conditional format rule to trigger when a number is greater than or equal to the given value
whenNumberLessThanSets the conditional format rule to trigger when a number is less than the given value
whenNumberLessThanOrEqualToSets the conditional format rule to trigger when a number is less than or equal to the given value
whenNumberNotBetweenSets the conditional format rule to trigger when a number does not fall between, and is neither of, two specified values
whenNumberNotEqualToSets the conditional format rule to trigger when a number is not equal to the given value
whenTextContainsSets the conditional format rule to trigger when the input contains the given value
whenTextDoesNotContainSets the conditional format rule to trigger when the input does not contain the given value
whenTextEndsWithSets the conditional format rule to trigger when the input ends with the given value
whenTextEqualToSets the conditional format rule to trigger when the input is equal to the given value
whenTextStartsWithSets the conditional format rule to trigger when the input starts with the given value

Enums

EnumDescription
ConditionFormatNumberOperatorEnumConditional formatting number operator
ConditionFormatTimePeriodOperatorEnumConditional formatting time period operator
ConditionFormatIconSetTypeEnumConditional formatting icon set type
ConditionFormatValueTypeEnumConditional formatting value type

APIs

Lifecycle & Creation

build

Constructs a conditional format rule from the settings applied to the builder.

Signature

TypeScript
build(): IConditionFormattingRule

Returns

  • IConditionFormattingRule — The conditional format rule.

Examples

TypeScript
const fWorkbook = univerAPI.getActiveWorkbook();const fWorksheet = fWorkbook.getActiveSheet();// Create a conditional formatting rule that highlights cells with values greater than 10 in red for the range A1:D10.const fRange = fWorksheet.getRange('A1:D10');const rule = fWorksheet.newConditionalFormattingRule()  .whenNumberGreaterThan(10)  .setBackground('#FF0000')  .setRanges([fRange.getRange()])  .build();fWorksheet.addConditionalFormattingRule(rule);
Source: @univerjs/sheets-conditional-formatting

copy

Deep clone a current builder.

Signature

TypeScript
copy(): ConditionalFormatRuleBaseBuilder

Returns

  • ConditionalFormatRuleBaseBuilder — A new builder with the same settings as the original.

Examples

TypeScript
const fWorkbook = univerAPI.getActiveWorkbook();const fWorksheet = fWorkbook.getActiveSheet();// Create a conditional formatting rule that highlights cells with no content in red for the range A1:D10.const fRange = fWorksheet.getRange('A1:D10');const builder = fWorksheet.newConditionalFormattingRule()  .whenCellEmpty()  .setBackground('#FF0000')  .setRanges([fRange.getRange()]);fWorksheet.addConditionalFormattingRule(builder.build());// Copy the rule and change the background color to green for the range A1:B2.const newRange = fWorksheet.getRange('A1:B2');const newBuilder = builder.copy()  .setBackground('#00FF00')  .setRanges([newRange.getRange()]);fWorksheet.addConditionalFormattingRule(newBuilder.build());
Source: @univerjs/sheets-conditional-formatting

createCfId

Create a conditional format ID.

Signature

TypeScript
createCfId(): string

Returns

  • string — The conditional format ID.

Examples

TypeScript
const fWorkbook = univerAPI.getActiveWorkbook();const fWorksheet = fWorkbook.getActiveSheet();console.log(fWorksheet.newConditionalFormattingRule().createCfId());
Source: @univerjs/sheets-conditional-formatting

Getters & Queries

getIconMap

Get the icon set mapping dictionary.

Signature

TypeScript
getIconMap(): Record<string, string[]>

Returns

  • Record<string, string[]> — The icon set mapping dictionary.

Examples

TypeScript
const fWorkbook = univerAPI.getActiveWorkbook();const fWorksheet = fWorkbook.getActiveSheet();console.log(fWorksheet.newConditionalFormattingRule().getIconMap()); // icons key-value map
Source: @univerjs/sheets-conditional-formatting

getRanges

Gets the scope of the current conditional format.

Signature

TypeScript
getRanges(): IRange[]

Returns

  • IRange[] — The ranges to which the conditional format applies.

Examples

TypeScript
const fWorkbook = univerAPI.getActiveWorkbook();const fWorksheet = fWorkbook.getActiveSheet();const fRange = fWorksheet.getRange('A1:D10');const builder = fWorksheet.newConditionalFormattingRule()  .whenCellEmpty()  .setRanges([fRange.getRange()]);console.log(builder.getRanges()); // [{ startRow: 0, endRow: 9, startColumn: 0, endColumn: 3 }]
Source: @univerjs/sheets-conditional-formatting

Setters & Modifiers

setAverage

Set average rule.

Signature

TypeScript
setAverage(operator: IAverageHighlightCell['operator']): ConditionalFormatHighlightRuleBuilder

Parameters

  • operator IAverageHighlightCell — No description

Returns

  • ConditionalFormatHighlightRuleBuilder — The conditional format highlight rule builder.

Examples

TypeScript
const fWorkbook = univerAPI.getActiveWorkbook();const fWorksheet = fWorkbook.getActiveSheet();// Create a conditional formatting rule that highlights cells with greater than average values in red for the range A1:D10.const fRange = fWorksheet.getRange('A1:D10');const rule = fWorksheet.newConditionalFormattingRule()  .setAverage(univerAPI.Enum.ConditionFormatNumberOperatorEnum.greaterThan)  .setBackground('#FF0000')  .setRanges([fRange.getRange()])  .build();fWorksheet.addConditionalFormattingRule(rule);
Source: @univerjs/sheets-conditional-formatting

setBackground

Sets the background color for the conditional format rule's format.

Signature

TypeScript
setBackground(color?: string): ConditionalFormatHighlightRuleBuilder

Parameters

  • color string (optional) — No description

Returns

  • ConditionalFormatHighlightRuleBuilder — The conditional format highlight rule builder.

Examples

TypeScript
const fWorkbook = univerAPI.getActiveWorkbook();const fWorksheet = fWorkbook.getActiveSheet();// Create a conditional formatting rule that highlights cells with no content in red for the range A1:D10.const fRange = fWorksheet.getRange('A1:D10');const rule = fWorksheet.newConditionalFormattingRule()  .whenCellEmpty()  .setBackground('#FF0000')  .setRanges([fRange.getRange()])  .build();fWorksheet.addConditionalFormattingRule(rule);
Source: @univerjs/sheets-conditional-formatting

setBold

Sets text bolding for the conditional format rule's format.

Signature

TypeScript
setBold(isBold: boolean): ConditionalFormatHighlightRuleBuilder

Parameters

  • isBold boolean — No description

Returns

  • ConditionalFormatHighlightRuleBuilder — The conditional format highlight rule builder.

Examples

TypeScript
const fWorkbook = univerAPI.getActiveWorkbook();const fWorksheet = fWorkbook.getActiveSheet();// Create a conditional formatting rule that bolds the text for cells with not empty content in the range A1:D10.const fRange = fWorksheet.getRange('A1:D10');const rule = fWorksheet.newConditionalFormattingRule()  .whenCellNotEmpty()  .setBold(true)  .setRanges([fRange.getRange()])  .build();fWorksheet.addConditionalFormattingRule(rule);
Source: @univerjs/sheets-conditional-formatting

setColorScale

Set color scale rule.

Signature

TypeScript
setColorScale(config: IColorScale['config']): ConditionalFormatColorScaleRuleBuilder

Parameters

  • config IColorScale — No description

Returns

  • ConditionalFormatColorScaleRuleBuilder — The conditional format color scale rule builder.

Examples

TypeScript
const fWorkbook = univerAPI.getActiveWorkbook();const fWorksheet = fWorkbook.getActiveSheet();// Create a conditional formatting rule that adds a color scale to cells with values between 0 and 100 in the range A1:D10.// The color scale is green for 0, yellow for 50, and red for 100.const fRange = fWorksheet.getRange('A1:D10');const rule = fWorksheet.newConditionalFormattingRule()  .setColorScale([    { index: 0, color: '#00FF00', value: { type: 'num', value: 0 } },    { index: 1, color: '#FFFF00', value: { type: 'num', value: 50 } },    { index: 2, color: '#FF0000', value: { type: 'num', value: 100 } }  ])  .setRanges([fRange.getRange()])  .build();fWorksheet.addConditionalFormattingRule(rule);
Source: @univerjs/sheets-conditional-formatting

setDataBar

Set data bar rule.

Signature

TypeScript
setDataBar(config: {        min: IValueConfig;        max: IValueConfig;        isGradient?: boolean;        positiveColor: string;        nativeColor: string;        isShowValue?: boolean;    }): ConditionalFormatDataBarRuleBuilder

Parameters

  • config { min: IValueConfig; max: IValueConfig; isGradient?: boolean; positiveColor: string; nativeColor: string; isShowValue?: boolean; } — No description

Returns

  • ConditionalFormatDataBarRuleBuilder — The conditional format data bar rule builder.

Examples

TypeScript
const fWorkbook = univerAPI.getActiveWorkbook();const fWorksheet = fWorkbook.getActiveSheet();// Create a conditional formatting rule that adds a data bar to cells with values between -100 and 100 in the range A1:D10.// positive values are green and negative values are red.const fRange = fWorksheet.getRange('A1:D10');const rule = fWorksheet.newConditionalFormattingRule()  .setDataBar({    min: { type: 'num', value: -100 },    max: { type: 'num', value: 100 },    positiveColor: '#00FF00',    nativeColor: '#FF0000',    isShowValue: true  }) .setRanges([fRange.getRange()]).build();fWorksheet.addConditionalFormattingRule(rule);
Source: @univerjs/sheets-conditional-formatting

setDuplicateValues

Set duplicate values rule.

Signature

TypeScript
setDuplicateValues(): ConditionalFormatHighlightRuleBuilder

Returns

  • ConditionalFormatHighlightRuleBuilder — The conditional format highlight rule builder.

Examples

TypeScript
const fWorkbook = univerAPI.getActiveWorkbook();const fWorksheet = fWorkbook.getActiveSheet();// Create a conditional formatting rule that highlights cells with duplicate values in red for the range A1:D10.const fRange = fWorksheet.getRange('A1:D10');const rule = fWorksheet.newConditionalFormattingRule()  .setDuplicateValues()  .setBackground('#FF0000')  .setRanges([fRange.getRange()])  .build();fWorksheet.addConditionalFormattingRule(rule);
Source: @univerjs/sheets-conditional-formatting

setFontColor

Sets the font color for the conditional format rule's format.

Signature

TypeScript
setFontColor(color?: string): ConditionalFormatHighlightRuleBuilder

Parameters

  • color string (optional) — No description

Returns

  • ConditionalFormatHighlightRuleBuilder — The conditional format highlight rule builder.

Examples

TypeScript
const fWorkbook = univerAPI.getActiveWorkbook();const fWorksheet = fWorkbook.getActiveSheet();// Create a conditional formatting rule that changes the font color to red for cells with not empty content in the range A1:D10.const fRange = fWorksheet.getRange('A1:D10');const rule = fWorksheet.newConditionalFormattingRule()  .whenCellNotEmpty()  .setFontColor('#FF0000')  .setRanges([fRange.getRange()])  .build();fWorksheet.addConditionalFormattingRule(rule);
Source: @univerjs/sheets-conditional-formatting

setIconSet

Set up icon set conditional formatting rule.

Signature

TypeScript
setIconSet(config: { iconConfigs: IIconSet['config']; isShowValue: boolean }): ConditionalFormatIconSetRuleBuilder

Parameters

  • config { iconConfigs: IIconSet['config']; isShowValue: boolean; } — No description

Returns

  • ConditionalFormatIconSetRuleBuilder — The conditional format icon set rule builder.

Examples

TypeScript
const fWorkbook = univerAPI.getActiveWorkbook();const fWorksheet = fWorkbook.getActiveSheet();// Create a 3-arrow icon set conditional formatting rule in the range A1:D10.// The first arrow is green for values greater than 20.// The second arrow is yellow for values greater than 10.// The third arrow is red for values less than or equal to 10.const fRange = fWorksheet.getRange('A1:D10');const builder = fWorksheet.newConditionalFormattingRule();console.log(builder.getIconMap()); // icons key-value mapconst rule = builder.setIconSet({    iconConfigs: [      { iconType: univerAPI.Enum.ConditionFormatIconSetTypeEnum.threeArrows, iconId: '0', operator: univerAPI.Enum.ConditionFormatNumberOperatorEnum.greaterThan, value: { type: univerAPI.Enum.ConditionFormatValueTypeEnum.num, value: 20 } },      { iconType: univerAPI.Enum.ConditionFormatIconSetTypeEnum.threeArrows, iconId: '1', operator: univerAPI.Enum.ConditionFormatNumberOperatorEnum.greaterThan, value: { type: univerAPI.Enum.ConditionFormatValueTypeEnum.num, value: 10 } },      { iconType: univerAPI.Enum.ConditionFormatIconSetTypeEnum.threeArrows, iconId: '2', operator: univerAPI.Enum.ConditionFormatNumberOperatorEnum.lessThanOrEqual, value: { type: univerAPI.Enum.ConditionFormatValueTypeEnum.num, value: 10 } }    ],    isShowValue: true,  })  .setRanges([fRange.getRange()])  .build();fWorksheet.addConditionalFormattingRule(rule);
Source: @univerjs/sheets-conditional-formatting

setItalic

Sets text italics for the conditional format rule's format.

Signature

TypeScript
setItalic(isItalic: boolean): ConditionalFormatHighlightRuleBuilder

Parameters

  • isItalic boolean — No description

Returns

  • ConditionalFormatHighlightRuleBuilder — The conditional format highlight rule builder.

Examples

TypeScript
const fWorkbook = univerAPI.getActiveWorkbook();const fWorksheet = fWorkbook.getActiveSheet();// Create a conditional formatting rule that italicizes the text for cells with not empty content in the range A1:D10.const fRange = fWorksheet.getRange('A1:D10');const rule = fWorksheet.newConditionalFormattingRule()  .whenCellNotEmpty()  .setItalic(true)  .setRanges([fRange.getRange()])  .build();fWorksheet.addConditionalFormattingRule(rule);
Source: @univerjs/sheets-conditional-formatting

setRank

Set rank rule.

Signature

TypeScript
setRank(config: { isBottom: boolean; isPercent: boolean; value: number }): ConditionalFormatHighlightRuleBuilder

Parameters

  • config { isBottom: boolean; isPercent: boolean; value: number; } — No description

Returns

  • ConditionalFormatHighlightRuleBuilder — The conditional format highlight rule builder.

Examples

TypeScript
const fWorkbook = univerAPI.getActiveWorkbook();const fWorksheet = fWorkbook.getActiveSheet();// Create a conditional formatting rule that highlights the bottom 10% of values in red for the range A1:D10.const fRange = fWorksheet.getRange('A1:D10');const rule = fWorksheet.newConditionalFormattingRule()  .setRank({ isBottom: true, isPercent: true, value: 10 })  .setBackground('#FF0000')  .setRanges([fRange.getRange()])  .build();fWorksheet.addConditionalFormattingRule(rule);
Source: @univerjs/sheets-conditional-formatting

setStrikethrough

Sets text strikethrough for the conditional format rule's format.

Signature

TypeScript
setStrikethrough(isStrikethrough: boolean): ConditionalFormatHighlightRuleBuilder

Parameters

  • isStrikethrough boolean — No description

Returns

  • ConditionalFormatHighlightRuleBuilder — The conditional format highlight rule builder.

Examples

TypeScript
const fWorkbook = univerAPI.getActiveWorkbook();const fWorksheet = fWorkbook.getActiveSheet();// Create a conditional formatting rule that set text strikethrough for cells with not empty content in the range A1:D10.const fRange = fWorksheet.getRange('A1:D10');const rule = fWorksheet.newConditionalFormattingRule()  .whenCellNotEmpty()  .setStrikethrough(true)  .setRanges([fRange.getRange()])  .build();fWorksheet.addConditionalFormattingRule(rule);
Source: @univerjs/sheets-conditional-formatting

setUnderline

Sets text underlining for the conditional format rule's format.

Signature

TypeScript
setUnderline(isUnderline: boolean): ConditionalFormatHighlightRuleBuilder

Parameters

  • isUnderline boolean — No description

Returns

  • ConditionalFormatHighlightRuleBuilder — The conditional format highlight rule builder.

Examples

TypeScript
const fWorkbook = univerAPI.getActiveWorkbook();const fWorksheet = fWorkbook.getActiveSheet();// Create a conditional formatting rule that underlines the text for cells with not empty content in the range A1:D10.const fRange = fWorksheet.getRange('A1:D10');const rule = fWorksheet.newConditionalFormattingRule()  .whenCellNotEmpty()  .setUnderline(true)  .setRanges([fRange.getRange()])  .build();fWorksheet.addConditionalFormattingRule(rule);
Source: @univerjs/sheets-conditional-formatting

setUniqueValues

Set unique values rule.

Signature

TypeScript
setUniqueValues(): ConditionalFormatHighlightRuleBuilder

Returns

  • ConditionalFormatHighlightRuleBuilder — The conditional format highlight rule builder.

Examples

TypeScript
const fWorkbook = univerAPI.getActiveWorkbook();const fWorksheet = fWorkbook.getActiveSheet();// Create a conditional formatting rule that highlights cells with unique values in red for the range A1:D10.const fRange = fWorksheet.getRange('A1:D10');const rule = fWorksheet.newConditionalFormattingRule()  .setUniqueValues()  .setBackground('#FF0000')  .setRanges([fRange.getRange()])  .build();fWorksheet.addConditionalFormattingRule(rule);
Source: @univerjs/sheets-conditional-formatting

Miscellaneous

whenCellEmpty

Sets the conditional format rule to trigger when the cell is empty.

Signature

TypeScript
whenCellEmpty(): ConditionalFormatHighlightRuleBuilder

Returns

  • ConditionalFormatHighlightRuleBuilder — The conditional format highlight rule builder.

Examples

TypeScript
const fWorkbook = univerAPI.getActiveWorkbook();const fWorksheet = fWorkbook.getActiveSheet();// Create a conditional formatting rule that highlights cells with no content in red for the range A1:D10.const fRange = fWorksheet.getRange('A1:D10');const rule = fWorksheet.newConditionalFormattingRule()  .whenCellEmpty()  .setBackground('#FF0000')  .setRanges([fRange.getRange()])  .build();fWorksheet.addConditionalFormattingRule(rule);
Source: @univerjs/sheets-conditional-formatting

whenCellNotEmpty

Sets the conditional format rule to trigger when the cell is not empty.

Signature

TypeScript
whenCellNotEmpty(): ConditionalFormatHighlightRuleBuilder

Returns

  • ConditionalFormatHighlightRuleBuilder — The conditional format highlight rule builder.

Examples

TypeScript
const fWorkbook = univerAPI.getActiveWorkbook();const fWorksheet = fWorkbook.getActiveSheet();// Create a conditional formatting rule that changes the font color to red for cells with not empty content in the range A1:D10.const fRange = fWorksheet.getRange('A1:D10');const rule = fWorksheet.newConditionalFormattingRule()  .whenCellNotEmpty()  .setFontColor('#FF0000')  .setRanges([fRange.getRange()])  .build();fWorksheet.addConditionalFormattingRule(rule);
Source: @univerjs/sheets-conditional-formatting

whenDate

Sets the conditional format rule to trigger when a time period is met.

Signature

TypeScript
whenDate(date: CFTimePeriodOperator): ConditionalFormatHighlightRuleBuilder

Parameters

  • date CFTimePeriodOperator — No description

Returns

  • ConditionalFormatHighlightRuleBuilder — The conditional format highlight rule builder.

Examples

TypeScript
const fWorkbook = univerAPI.getActiveWorkbook();const fWorksheet = fWorkbook.getActiveSheet();// Create a conditional formatting rule that highlights cells with dates in the last 7 days in red for the range A1:D10.const fRange = fWorksheet.getRange('A1:D10');const rule = fWorksheet.newConditionalFormattingRule()  .whenDate(univerAPI.Enum.ConditionFormatTimePeriodOperatorEnum.last7Days)  .setBackground('#FF0000')  .setRanges([fRange.getRange()])  .build();fWorksheet.addConditionalFormattingRule(rule);
Source: @univerjs/sheets-conditional-formatting

whenFormulaSatisfied

Sets the conditional format rule to trigger when the given formula evaluates to true.

Signature

TypeScript
whenFormulaSatisfied(formulaString: string): ConditionalFormatHighlightRuleBuilder

Parameters

  • formulaString string — No description

Returns

  • ConditionalFormatHighlightRuleBuilder — The conditional format highlight rule builder.

Examples

TypeScript
const fWorkbook = univerAPI.getActiveWorkbook();const fWorksheet = fWorkbook.getActiveSheet();// Create a conditional formatting rule that highlights cells with values greater than 10 in red for the range A1:D10.const fRange = fWorksheet.getRange('A1:D10');const rule = fWorksheet.newConditionalFormattingRule()  .whenFormulaSatisfied('=A1>10')  .setBackground('#FF0000')  .setRanges([fRange.getRange()])  .build();fWorksheet.addConditionalFormattingRule(rule);
Source: @univerjs/sheets-conditional-formatting

whenNumberBetween

Sets the conditional format rule to trigger when a number falls between, or is either of, two specified values.

Signature

TypeScript
whenNumberBetween(start: number, end: number): ConditionalFormatHighlightRuleBuilder

Parameters

  • start number — No description
  • end number — No description

Returns

  • ConditionalFormatHighlightRuleBuilder — The conditional format highlight rule builder.

Examples

TypeScript
const fWorkbook = univerAPI.getActiveWorkbook();const fWorksheet = fWorkbook.getActiveSheet();// Create a conditional formatting rule that highlights cells with values between 10 and 20 in red for the range A1:D10.const fRange = fWorksheet.getRange('A1:D10');const rule = fWorksheet.newConditionalFormattingRule()  .whenNumberBetween(10, 20)  .setBackground('#FF0000')  .setRanges([fRange.getRange()])  .build();fWorksheet.addConditionalFormattingRule(rule);
Source: @univerjs/sheets-conditional-formatting

whenNumberEqualTo

Sets the conditional format rule to trigger when a number is equal to the given value.

Signature

TypeScript
whenNumberEqualTo(value: number): ConditionalFormatHighlightRuleBuilder

Parameters

  • value number — No description

Returns

  • ConditionalFormatHighlightRuleBuilder — The conditional format highlight rule builder.

Examples

TypeScript
const fWorkbook = univerAPI.getActiveWorkbook();const fWorksheet = fWorkbook.getActiveSheet();// Create a conditional formatting rule that highlights cells with values equal to 10 in red for the range A1:D10.const fRange = fWorksheet.getRange('A1:D10');const rule = fWorksheet.newConditionalFormattingRule()  .whenNumberEqualTo(10)  .setBackground('#FF0000')  .setRanges([fRange.getRange()])  .build();fWorksheet.addConditionalFormattingRule(rule);
Source: @univerjs/sheets-conditional-formatting

whenNumberGreaterThan

Sets the conditional format rule to trigger when a number is greater than the given value.

Signature

TypeScript
whenNumberGreaterThan(value: number): ConditionalFormatHighlightRuleBuilder

Parameters

  • value number — No description

Returns

  • ConditionalFormatHighlightRuleBuilder — The conditional format highlight rule builder.

Examples

TypeScript
const fWorkbook = univerAPI.getActiveWorkbook();const fWorksheet = fWorkbook.getActiveSheet();// Create a conditional formatting rule that highlights cells with values greater than 10 in red for the range A1:D10.const fRange = fWorksheet.getRange('A1:D10');const rule = fWorksheet.newConditionalFormattingRule()  .whenNumberGreaterThan(10)  .setBackground('#FF0000')  .setRanges([fRange.getRange()])  .build();fWorksheet.addConditionalFormattingRule(rule);
Source: @univerjs/sheets-conditional-formatting

whenNumberGreaterThanOrEqualTo

Sets the conditional format rule to trigger when a number is greater than or equal to the given value.

Signature

TypeScript
whenNumberGreaterThanOrEqualTo(value: number): ConditionalFormatHighlightRuleBuilder

Parameters

  • value number — No description

Returns

  • ConditionalFormatHighlightRuleBuilder — The conditional format highlight rule builder.

Examples

TypeScript
const fWorkbook = univerAPI.getActiveWorkbook();const fWorksheet = fWorkbook.getActiveSheet();// Create a conditional formatting rule that highlights cells with values greater than or equal to 10 in red for the range A1:D10.const fRange = fWorksheet.getRange('A1:D10');const rule = fWorksheet.newConditionalFormattingRule()  .whenNumberGreaterThanOrEqualTo(10)  .setBackground('#FF0000')  .setRanges([fRange.getRange()])  .build();fWorksheet.addConditionalFormattingRule(rule);
Source: @univerjs/sheets-conditional-formatting

whenNumberLessThan

Sets the conditional format rule to trigger when a number is less than the given value.

Signature

TypeScript
whenNumberLessThan(value: number): ConditionalFormatHighlightRuleBuilder

Parameters

  • value number — No description

Returns

  • ConditionalFormatHighlightRuleBuilder — The conditional format highlight rule builder.

Examples

TypeScript
const fWorkbook = univerAPI.getActiveWorkbook();const fWorksheet = fWorkbook.getActiveSheet();// Create a conditional formatting rule that highlights cells with values less than 10 in red for the range A1:D10.const fRange = fWorksheet.getRange('A1:D10');const rule = fWorksheet.newConditionalFormattingRule()  .whenNumberLessThan(10)  .setBackground('#FF0000')  .setRanges([fRange.getRange()])  .build();fWorksheet.addConditionalFormattingRule(rule);
Source: @univerjs/sheets-conditional-formatting

whenNumberLessThanOrEqualTo

Sets the conditional format rule to trigger when a number is less than or equal to the given value.

Signature

TypeScript
whenNumberLessThanOrEqualTo(value: number): ConditionalFormatHighlightRuleBuilder

Parameters

  • value number — No description

Returns

  • ConditionalFormatHighlightRuleBuilder — The conditional format highlight rule builder.

Examples

TypeScript
const fWorkbook = univerAPI.getActiveWorkbook();const fWorksheet = fWorkbook.getActiveSheet();// Create a conditional formatting rule that highlights cells with values less than or equal to 10 in red for the range A1:D10.const fRange = fWorksheet.getRange('A1:D10');const rule = fWorksheet.newConditionalFormattingRule()  .whenNumberLessThanOrEqualTo(10)  .setBackground('#FF0000')  .setRanges([fRange.getRange()])  .build();fWorksheet.addConditionalFormattingRule(rule);
Source: @univerjs/sheets-conditional-formatting

whenNumberNotBetween

Sets the conditional format rule to trigger when a number does not fall between, and is neither of, two specified values.

Signature

TypeScript
whenNumberNotBetween(start: number, end: number): ConditionalFormatHighlightRuleBuilder

Parameters

  • start number — No description
  • end number — No description

Returns

  • ConditionalFormatHighlightRuleBuilder — The conditional format highlight rule builder.

Examples

TypeScript
const fWorkbook = univerAPI.getActiveWorkbook();const fWorksheet = fWorkbook.getActiveSheet();// Create a conditional formatting rule that highlights cells with values not between 10 and 20 in red for the range A1:D10.const fRange = fWorksheet.getRange('A1:D10');const rule = fWorksheet.newConditionalFormattingRule()  .whenNumberNotBetween(10, 20)  .setBackground('#FF0000')  .setRanges([fRange.getRange()])  .build();fWorksheet.addConditionalFormattingRule(rule);
Source: @univerjs/sheets-conditional-formatting

whenNumberNotEqualTo

Sets the conditional format rule to trigger when a number is not equal to the given value.

Signature

TypeScript
whenNumberNotEqualTo(value: number): ConditionalFormatHighlightRuleBuilder

Parameters

  • value number — No description

Returns

  • ConditionalFormatHighlightRuleBuilder — The conditional format highlight rule builder.

Examples

TypeScript
const fWorkbook = univerAPI.getActiveWorkbook();const fWorksheet = fWorkbook.getActiveSheet();// Create a conditional formatting rule that highlights cells with values not equal to 10 in red for the range A1:D10.const fRange = fWorksheet.getRange('A1:D10');const rule = fWorksheet.newConditionalFormattingRule()  .whenNumberNotEqualTo(10)  .setBackground('#FF0000')  .setRanges([fRange.getRange()])  .build();fWorksheet.addConditionalFormattingRule(rule);
Source: @univerjs/sheets-conditional-formatting

whenTextContains

Sets the conditional format rule to trigger when the input contains the given value.

Signature

TypeScript
whenTextContains(text: string): ConditionalFormatHighlightRuleBuilder

Parameters

  • text string — No description

Returns

  • ConditionalFormatHighlightRuleBuilder — The conditional format highlight rule builder.

Examples

TypeScript
const fWorkbook = univerAPI.getActiveWorkbook();const fWorksheet = fWorkbook.getActiveSheet();// Create a conditional formatting rule that highlights cells with text containing 'apple' in red for the range A1:D10.const fRange = fWorksheet.getRange('A1:D10');const rule = fWorksheet.newConditionalFormattingRule()  .whenTextContains('apple')  .setBackground('#FF0000')  .setRanges([fRange.getRange()])  .build();fWorksheet.addConditionalFormattingRule(rule);
Source: @univerjs/sheets-conditional-formatting

whenTextDoesNotContain

Sets the conditional format rule to trigger when the input does not contain the given value.

Signature

TypeScript
whenTextDoesNotContain(text: string): ConditionalFormatHighlightRuleBuilder

Parameters

  • text string — No description

Returns

  • ConditionalFormatHighlightRuleBuilder — The conditional format highlight rule builder.

Examples

TypeScript
const fWorkbook = univerAPI.getActiveWorkbook();const fWorksheet = fWorkbook.getActiveSheet();// Create a conditional formatting rule that highlights cells with text not containing 'apple' in red for the range A1:D10.const fRange = fWorksheet.getRange('A1:D10');const rule = fWorksheet.newConditionalFormattingRule()  .whenTextDoesNotContain('apple')  .setBackground('#FF0000')  .setRanges([fRange.getRange()])  .build();fWorksheet.addConditionalFormattingRule(rule);
Source: @univerjs/sheets-conditional-formatting

whenTextEndsWith

Sets the conditional format rule to trigger when the input ends with the given value.

Signature

TypeScript
whenTextEndsWith(text: string): ConditionalFormatHighlightRuleBuilder

Parameters

  • text string — No description

Returns

  • ConditionalFormatHighlightRuleBuilder — The conditional format highlight rule builder.

Examples

TypeScript
const fWorkbook = univerAPI.getActiveWorkbook();const fWorksheet = fWorkbook.getActiveSheet();// Create a conditional formatting rule that highlights cells with text ending with '.ai' in red for the range A1:D10.const fRange = fWorksheet.getRange('A1:D10');const rule = fWorksheet.newConditionalFormattingRule()  .whenTextEndsWith('.ai')  .setBackground('#FF0000')  .setRanges([fRange.getRange()])  .build();fWorksheet.addConditionalFormattingRule(rule);
Source: @univerjs/sheets-conditional-formatting

whenTextEqualTo

Sets the conditional format rule to trigger when the input is equal to the given value.

Signature

TypeScript
whenTextEqualTo(text: string): ConditionalFormatHighlightRuleBuilder

Parameters

  • text string — No description

Returns

  • ConditionalFormatHighlightRuleBuilder — The conditional format highlight rule builder.

Examples

TypeScript
const fWorkbook = univerAPI.getActiveWorkbook();const fWorksheet = fWorkbook.getActiveSheet();// Create a conditional formatting rule that highlights cells with text equal to 'apple' in red for the range A1:D10.const fRange = fWorksheet.getRange('A1:D10');const rule = fWorksheet.newConditionalFormattingRule()  .whenTextEqualTo('apple')  .setBackground('#FF0000')  .setRanges([fRange.getRange()])  .build();fWorksheet.addConditionalFormattingRule(rule);
Source: @univerjs/sheets-conditional-formatting

whenTextStartsWith

Sets the conditional format rule to trigger when the input starts with the given value.

Signature

TypeScript
whenTextStartsWith(text: string): ConditionalFormatHighlightRuleBuilder

Parameters

  • text string — No description

Returns

  • ConditionalFormatHighlightRuleBuilder — The conditional format highlight rule builder.

Examples

TypeScript
const fWorkbook = univerAPI.getActiveWorkbook();const fWorksheet = fWorkbook.getActiveSheet();// Create a conditional formatting rule that highlights cells with text starting with 'https://' in red for the range A1:D10.const fRange = fWorksheet.getRange('A1:D10');const rule = fWorksheet.newConditionalFormattingRule()  .whenTextStartsWith('https://')  .setBackground('#FF0000')  .setRanges([fRange.getRange()])  .build();fWorksheet.addConditionalFormattingRule(rule);
Source: @univerjs/sheets-conditional-formatting

How is this guide?

© 2026 DreamNum Co., Ltd.