FConditionalFormattingBuilder

packages	`@univerjs/sheets-conditional-formatting`

APIs

`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)

`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

`setAverage`

Set average rule.

Signature

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

Parameters

operator (IAverageHighlightCell['operator']) — - The operator to use for the average rule.

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)

`setBackground`

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

Signature

setBackground(color?: string): ConditionalFormatHighlightRuleBuilder

Parameters

color (string) — - The background color to set. If not provided, the background color is removed.

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)

`setBold`

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

Signature

setBold(isBold: boolean): ConditionalFormatHighlightRuleBuilder

Parameters

isBold (boolean) — - Whether to bold the text.

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)

`setColorScale`

Set color scale rule.

Signature

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

Parameters

config (IColorScale['config']) — - The color scale rule settings.
config.index — - The index of the color scale.
config.color — - The color for the color scale.
config.value — - The value for the color scale.

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)

`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; }) — - The data bar rule settings.
config.min — - The minimum value for the data bar.
config.max — - The maximum value for the data bar.
config.isGradient — - Whether the data bar is gradient.
config.positiveColor — - The color for positive values.
config.nativeColor — - The color for negative values.
config.isShowValue — - Whether to show the value in the cell.

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)

`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)

`setFontColor`

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

Signature

setFontColor(color?: string): ConditionalFormatHighlightRuleBuilder

Parameters

color (string) — - The font color to set. If not provided, the font color is removed.

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)

`setIconSet`

Set up icon set conditional formatting rule.

Signature

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

Parameters

config ({ iconConfigs: IIconSet['config']; isShowValue: boolean }) — - The icon set conditional formatting rule settings.
config.iconConfigs — - The icon configurations. iconId property is a string indexing of a group icons.
config.isShowValue — - Whether to show the value in the cell.

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)

`setItalic`

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

Signature

setItalic(isItalic: boolean): ConditionalFormatHighlightRuleBuilder

Parameters

isItalic (boolean) — - Whether to italicize the text.

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)

`setRank`

Set rank rule.

Signature

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

Parameters

config ({ isBottom: boolean; isPercent: boolean; value: number }) — - The rank rule settings.
config.isBottom — - Whether to highlight the bottom rank.
config.isPercent — - Whether to use a percentage rank.
config.value — - The rank value.

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)

`setStrikethrough`

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

Signature

setStrikethrough(isStrikethrough: boolean): ConditionalFormatHighlightRuleBuilder

Parameters

isStrikethrough (boolean) — - Whether is strikethrough the text.

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)

`setUnderline`

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

Signature

setUnderline(isUnderline: boolean): ConditionalFormatHighlightRuleBuilder

Parameters

isUnderline (boolean) — - Whether to underline the text.

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)

`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)

`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)

`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)

`whenDate`

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

Signature

whenDate(date: CFTimePeriodOperator): ConditionalFormatHighlightRuleBuilder

Parameters

date (CFTimePeriodOperator) — - The time period to check for.

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)

`whenFormulaSatisfied`

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

Signature

whenFormulaSatisfied(formulaString: string): ConditionalFormatHighlightRuleBuilder

Parameters

formulaString (string) — - A custom formula that evaluates to true if the input is valid. formulaString start with '='.

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)

`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) — - The lowest acceptable value.
end (number) — - The highest acceptable value.

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)

`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) — - The sole acceptable value.

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)

`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) — - The highest unacceptable value.

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)

`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) — - The lowest acceptable value.

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)

`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) — - The lowest unacceptable value.

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)

`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) — - The highest acceptable value.

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)

`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) — - The lowest unacceptable value.
end (number) — - The highest unacceptable value.

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)

`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) — - The sole unacceptable value.

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)

`whenTextContains`

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

Signature

whenTextContains(text: string): ConditionalFormatHighlightRuleBuilder

Parameters

text (string) — - The value that the input must contain.

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)

`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) — - The value that the input must not contain.

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)

`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) — - Text to compare against the end of the string.

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)

`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) — - The sole acceptable value.

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)

`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) — - Text to compare against the beginning of the string.

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)

Table of Contents