Conditional Formatting

Packages	`@univerjs/sheets-conditional-formatting`

CORE

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

Overview

@univerjs/sheets-conditional-formatting

Method	Description
`build`	Constructs a conditional format rule from the settings applied to the builder
`getIconMap`	Get the icon set mapping dictionary
`setAverage`	Set average rule
`setBackground`	Sets the background color for the conditional format rule's format
`setBold`	Sets text bolding for the conditional format rule's format
`setColorScale`	Set color scale rule
`setDataBar`	Set data bar rule
`setDuplicateValues`	Set duplicate values rule
`setFontColor`	Sets the font color for the conditional format rule's format
`setIconSet`	Set up icon set conditional formatting rule
`setItalic`	Sets text italics for the conditional format rule's format
`setRank`	Set rank rule
`setStrikethrough`	Sets text strikethrough for the conditional format rule's format
`setUnderline`	Sets text underlining for the conditional format rule's format
`setUniqueValues`	Set unique values rule
`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 a time period is met
`whenFormulaSatisfied`	Sets the conditional format rule to trigger when that the given formula evaluates to `true`
`whenNumberBetween`	Sets the conditional format rule to trigger when a number falls between, or is either of, two specified values
`whenNumberEqualTo`	Sets the conditional format rule to trigger when a number is equal to the given value
`whenNumberGreaterThan`	Sets the conditional format rule to trigger when a number is greater than the given value
`whenNumberGreaterThanOrEqualTo`	Sets the conditional format rule to trigger when a number is greater than or equal to the given value
`whenNumberLessThan`	Sets the conditional conditional format rule to trigger when a number less than the given value
`whenNumberLessThanOrEqualTo`	Sets the conditional format rule to trigger when a number less than or equal to the given value
`whenNumberNotBetween`	Sets the conditional format rule to trigger when a number does not fall between, and is neither of, two specified values
`whenNumberNotEqualTo`	Sets the conditional format rule to trigger when a number is not equal to the given value
`whenTextContains`	Sets the conditional format rule to trigger when that the input contains the given value
`whenTextDoesNotContain`	Sets the conditional format rule to trigger when that the input does not contain the given value
`whenTextEndsWith`	Sets the conditional format rule to trigger when that the input ends with the given value
`whenTextEqualTo`	Sets the conditional format rule to trigger when that the input is equal to the given value
`whenTextStartsWith`	Sets the conditional format rule to trigger when that the input starts with the given value

APIs

Lifecycle & Creation

`build`

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

Signature

build(): IConditionFormattingRule

Returns

IConditionFormattingRule — The conditional format rule.

Examples

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

Getters & Queries

`getIconMap`

Get the icon set mapping dictionary.

Signature

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

Returns

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

Examples

const fWorkbook = univerAPI.getActiveWorkbook();
const fWorksheet = fWorkbook.getActiveSheet();
console.log(fWorksheet.newConditionalFormattingRule().getIconMap()); // icons key-value map

Source: @univerjs/sheets-conditional-formatting

Setters & Modifiers

`setAverage`

Set average rule.

Signature

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

Parameters

operator IAverageHighlightCell — No description

Returns

ConditionalFormatHighlightRuleBuilder — The conditional format highlight rule builder.

Examples

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

setBackground(color?: string): ConditionalFormatHighlightRuleBuilder

Parameters

color string (optional) — No description

Returns

ConditionalFormatHighlightRuleBuilder — The conditional format highlight rule builder.

Examples

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

setBold(isBold: boolean): ConditionalFormatHighlightRuleBuilder

Parameters

isBold boolean — No description

Returns

ConditionalFormatHighlightRuleBuilder — The conditional format highlight rule builder.

Examples

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

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

Parameters

config IColorScale — No description

Returns

ConditionalFormatColorScaleRuleBuilder — The conditional format color scale rule builder.

Examples

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

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

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

setDuplicateValues(): ConditionalFormatHighlightRuleBuilder

Returns

ConditionalFormatHighlightRuleBuilder — The conditional format highlight rule builder.

Examples

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

setFontColor(color?: string): ConditionalFormatHighlightRuleBuilder

Parameters

color string (optional) — No description

Returns

ConditionalFormatHighlightRuleBuilder — The conditional format highlight rule builder.

Examples

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

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

Parameters

config { iconConfigs: IIconSet; isShowValue: boolean; } — No description

Returns

ConditionalFormatIconSetRuleBuilder — The conditional format icon set rule builder.

Examples

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 map
const rule = builder.setIconSet({
    iconConfigs: [
      { iconType: '3Arrows', iconId: '0', operator: univerAPI.Enum.ConditionFormatNumberOperatorEnum.greaterThan, value: { type: 'num', value: 20 } },
      { iconType: '3Arrows', iconId: '1', operator: univerAPI.Enum.ConditionFormatNumberOperatorEnum.greaterThan, value: { type: 'num', value: 10 } },
      { iconType: '3Arrows', iconId: '2', operator: univerAPI.Enum.ConditionFormatNumberOperatorEnum.lessThanOrEqual, value: { type: '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

setItalic(isItalic: boolean): ConditionalFormatHighlightRuleBuilder

Parameters

isItalic boolean — No description

Returns

ConditionalFormatHighlightRuleBuilder — The conditional format highlight rule builder.

Examples

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

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

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

setStrikethrough(isStrikethrough: boolean): ConditionalFormatHighlightRuleBuilder

Parameters

isStrikethrough boolean — No description

Returns

ConditionalFormatHighlightRuleBuilder — The conditional format highlight rule builder.

Examples

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

setUnderline(isUnderline: boolean): ConditionalFormatHighlightRuleBuilder

Parameters

isUnderline boolean — No description

Returns

ConditionalFormatHighlightRuleBuilder — The conditional format highlight rule builder.

Examples

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

setUniqueValues(): ConditionalFormatHighlightRuleBuilder

Returns

ConditionalFormatHighlightRuleBuilder — The conditional format highlight rule builder.

Examples

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

whenCellEmpty(): ConditionalFormatHighlightRuleBuilder

Returns

ConditionalFormatHighlightRuleBuilder — The conditional format highlight rule builder.

Examples

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

whenCellNotEmpty(): ConditionalFormatHighlightRuleBuilder

Returns

ConditionalFormatHighlightRuleBuilder — The conditional format highlight rule builder.

Examples

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

whenDate(date: CFTimePeriodOperator): ConditionalFormatHighlightRuleBuilder

Parameters

date CFTimePeriodOperator — No description

Returns

ConditionalFormatHighlightRuleBuilder — The conditional format highlight rule builder.

Examples

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 that the given formula evaluates to true.

Signature

whenFormulaSatisfied(formulaString: string): ConditionalFormatHighlightRuleBuilder

Parameters

formulaString string — No description

Returns

ConditionalFormatHighlightRuleBuilder — The conditional format highlight rule builder.

Examples

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

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

Parameters

start number — No description
end number — No description

Returns

ConditionalFormatHighlightRuleBuilder — The conditional format highlight rule builder.

Examples

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

whenNumberEqualTo(value: number): ConditionalFormatHighlightRuleBuilder

Parameters

value number — No description

Returns

ConditionalFormatHighlightRuleBuilder — The conditional format highlight rule builder.

Examples

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

whenNumberGreaterThan(value: number): ConditionalFormatHighlightRuleBuilder

Parameters

value number — No description

Returns

ConditionalFormatHighlightRuleBuilder — The conditional format highlight rule builder.

Examples

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

whenNumberGreaterThanOrEqualTo(value: number): ConditionalFormatHighlightRuleBuilder

Parameters

value number — No description

Returns

ConditionalFormatHighlightRuleBuilder — The conditional format highlight rule builder.

Examples

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 conditional format rule to trigger when a number less than the given value.

Signature

whenNumberLessThan(value: number): ConditionalFormatHighlightRuleBuilder

Parameters

value number — No description

Returns

ConditionalFormatHighlightRuleBuilder — The conditional format highlight rule builder.

Examples

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 less than or equal to the given value.

Signature

whenNumberLessThanOrEqualTo(value: number): ConditionalFormatHighlightRuleBuilder

Parameters

value number — No description

Returns

ConditionalFormatHighlightRuleBuilder — The conditional format highlight rule builder.

Examples

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

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

Parameters

start number — No description
end number — No description

Returns

ConditionalFormatHighlightRuleBuilder — The conditional format highlight rule builder.

Examples

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

whenNumberNotEqualTo(value: number): ConditionalFormatHighlightRuleBuilder

Parameters

value number — No description

Returns

ConditionalFormatHighlightRuleBuilder — The conditional format highlight rule builder.

Examples

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 that the input contains the given value.

Signature

whenTextContains(text: string): ConditionalFormatHighlightRuleBuilder

Parameters

text string — No description

Returns

ConditionalFormatHighlightRuleBuilder — The conditional format highlight rule builder.

Examples

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 that the input does not contain the given value.

Signature

whenTextDoesNotContain(text: string): ConditionalFormatHighlightRuleBuilder

Parameters

text string — No description

Returns

ConditionalFormatHighlightRuleBuilder — The conditional format highlight rule builder.

Examples

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 that the input ends with the given value.

Signature

whenTextEndsWith(text: string): ConditionalFormatHighlightRuleBuilder

Parameters

text string — No description

Returns

ConditionalFormatHighlightRuleBuilder — The conditional format highlight rule builder.

Examples

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 that the input is equal to the given value.

Signature

whenTextEqualTo(text: string): ConditionalFormatHighlightRuleBuilder

Parameters

text string — No description

Returns

ConditionalFormatHighlightRuleBuilder — The conditional format highlight rule builder.

Examples

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 that the input starts with the given value.

Signature

whenTextStartsWith(text: string): ConditionalFormatHighlightRuleBuilder

Parameters

text string — No description

Returns

ConditionalFormatHighlightRuleBuilder — The conditional format highlight rule builder.

Examples

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

Table of Contents