Class: FConditionalFormattingBuilder

Methods

build()


build(): IConditionFormattingRule

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

Returns

IConditionFormattingRule

The conditional format rule.

Example


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


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

Get the icon set mapping dictionary.

Returns

Record<string, …[]>

The icon set mapping dictionary.

Example


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

setAverage()


setAverage(operator): ConditionalFormatHighlightRuleBuilder

Set average rule.

Parameters

Parameter	Type	Description
`operator`	`IAverageHighlightCell`	The operator to use for the average rule.

Returns

ConditionalFormatHighlightRuleBuilder

The conditional format highlight rule builder.

Example


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


setBackground(color?): ConditionalFormatHighlightRuleBuilder

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

Parameters

Parameter	Type	Description
`color`?	`string`	The background color to set. If not provided, the background color is removed.

Returns

ConditionalFormatHighlightRuleBuilder

The conditional format highlight rule builder.

Example


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


setBold(isBold): ConditionalFormatHighlightRuleBuilder

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

Parameters

Parameter	Type	Description
`isBold`	`boolean`	Whether to bold the text.

Returns

ConditionalFormatHighlightRuleBuilder

The conditional format highlight rule builder.

Example


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


setColorScale(config): ConditionalFormatColorScaleRuleBuilder

Set color scale rule.

Parameters

Parameter	Type	Description
`config`	`IColorScale`	The color scale rule settings.

Returns

ConditionalFormatColorScaleRuleBuilder

The conditional format color scale rule builder.

Example


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


setDataBar(config): ConditionalFormatDataBarRuleBuilder

Set data bar rule.

Parameters

Parameter	Type	Description
`config`	{ `isGradient`: `boolean`; `isShowValue`: `boolean`; `max`: `IValueConfig`; `min`: `IValueConfig`; `nativeColor`: `string`; `positiveColor`: `string`; }	The data bar rule settings.
`config.isGradient`?	`boolean`	Whether the data bar is gradient.
`config.isShowValue`?	`boolean`	Whether to show the value in the cell.
`config.max`	`IValueConfig`	The maximum value for the data bar.
`config.min`	`IValueConfig`	The minimum value for the data bar.
`config.nativeColor`	`string`	The color for negative values.
`config.positiveColor`	`string`	The color for positive values.

Returns

ConditionalFormatDataBarRuleBuilder

The conditional format data bar rule builder.

Example


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


setDuplicateValues(): ConditionalFormatHighlightRuleBuilder

Set duplicate values rule.

Returns

ConditionalFormatHighlightRuleBuilder

The conditional format highlight rule builder.

Example


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


setFontColor(color?): ConditionalFormatHighlightRuleBuilder

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

Parameters

Parameter	Type	Description
`color`?	`string`	The font color to set. If not provided, the font color is removed.

Returns

ConditionalFormatHighlightRuleBuilder

The conditional format highlight rule builder.

Example


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


setIconSet(config): ConditionalFormatIconSetRuleBuilder

Set up icon set conditional formatting rule.

Parameters

Parameter	Type	Description
`config`	{ `iconConfigs`: `IIconSet`; `isShowValue`: `boolean`; }	The icon set conditional formatting rule settings.
`config.iconConfigs`	`IIconSet`	The icon configurations. iconId property is a string indexing of a group icons.
`config.isShowValue`	`boolean`	Whether to show the value in the cell.

Returns

ConditionalFormatIconSetRuleBuilder

The conditional format icon set rule builder.

Example


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


setItalic(isItalic): ConditionalFormatHighlightRuleBuilder

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

Parameters

Parameter	Type	Description
`isItalic`	`boolean`	Whether to italicize the text.

Returns

ConditionalFormatHighlightRuleBuilder

The conditional format highlight rule builder.

Example


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


setRank(config): ConditionalFormatHighlightRuleBuilder

Set rank rule.

Parameters

Parameter	Type	Description
`config`	{ `isBottom`: `boolean`; `isPercent`: `boolean`; `value`: `number`; }	The rank rule settings.
`config.isBottom`	`boolean`	Whether to highlight the bottom rank.
`config.isPercent`	`boolean`	Whether to use a percentage rank.
`config.value`	`number`	The rank value.

Returns

ConditionalFormatHighlightRuleBuilder

The conditional format highlight rule builder.

Example


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


setStrikethrough(isStrikethrough): ConditionalFormatHighlightRuleBuilder

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

Parameters

Parameter	Type	Description
`isStrikethrough`	`boolean`	Whether is strikethrough the text.

Returns

ConditionalFormatHighlightRuleBuilder

The conditional format highlight rule builder.

Example


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


setUnderline(isUnderline): ConditionalFormatHighlightRuleBuilder

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

Parameters

Parameter	Type	Description
`isUnderline`	`boolean`	Whether to underline the text.

Returns

ConditionalFormatHighlightRuleBuilder

The conditional format highlight rule builder.

Example


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


setUniqueValues(): ConditionalFormatHighlightRuleBuilder

Set unique values rule.

Returns

ConditionalFormatHighlightRuleBuilder

The conditional format highlight rule builder.

Example


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


whenCellEmpty(): ConditionalFormatHighlightRuleBuilder

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

Returns

ConditionalFormatHighlightRuleBuilder

The conditional format highlight rule builder.

Example


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


whenCellNotEmpty(): ConditionalFormatHighlightRuleBuilder

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

Returns

ConditionalFormatHighlightRuleBuilder

The conditional format highlight rule builder.

Example


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


whenDate(date): ConditionalFormatHighlightRuleBuilder

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

Parameters

Parameter	Type	Description
`date`	`CFTimePeriodOperator`	The time period to check for.

Returns

ConditionalFormatHighlightRuleBuilder

The conditional format highlight rule builder.

Example


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


whenFormulaSatisfied(formulaString): ConditionalFormatHighlightRuleBuilder

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

Parameters

Parameter	Type	Description
`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.

Example


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


whenNumberBetween(start, end): ConditionalFormatHighlightRuleBuilder

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

Parameters

Parameter	Type	Description
`start`	`number`	The lowest acceptable value.
`end`	`number`	The highest acceptable value.

Returns

ConditionalFormatHighlightRuleBuilder

The conditional format highlight rule builder.

Example


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


whenNumberEqualTo(value): ConditionalFormatHighlightRuleBuilder

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

Parameters

Parameter	Type	Description
`value`	`number`	The sole acceptable value.

Returns

ConditionalFormatHighlightRuleBuilder

The conditional format highlight rule builder.

Example


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


whenNumberGreaterThan(value): ConditionalFormatHighlightRuleBuilder

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

Parameters

Parameter	Type	Description
`value`	`number`	The highest unacceptable value.

Returns

ConditionalFormatHighlightRuleBuilder

The conditional format highlight rule builder.

Example


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


whenNumberGreaterThanOrEqualTo(value): ConditionalFormatHighlightRuleBuilder

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

Parameters

Parameter	Type	Description
`value`	`number`	The lowest acceptable value.

Returns

ConditionalFormatHighlightRuleBuilder

The conditional format highlight rule builder.

Example


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


whenNumberLessThan(value): ConditionalFormatHighlightRuleBuilder

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

Parameters

Parameter	Type	Description
`value`	`number`	The lowest unacceptable value.

Returns

ConditionalFormatHighlightRuleBuilder

The conditional format highlight rule builder.

Example


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


whenNumberLessThanOrEqualTo(value): ConditionalFormatHighlightRuleBuilder

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

Parameters

Parameter	Type	Description
`value`	`number`	The highest acceptable value.

Returns

ConditionalFormatHighlightRuleBuilder

The conditional format highlight rule builder.

Example


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


whenNumberNotBetween(start, end): ConditionalFormatHighlightRuleBuilder

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

Parameters

Parameter	Type	Description
`start`	`number`	The lowest unacceptable value.
`end`	`number`	The highest unacceptable value.

Returns

ConditionalFormatHighlightRuleBuilder

The conditional format highlight rule builder.

Example


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


whenNumberNotEqualTo(value): ConditionalFormatHighlightRuleBuilder

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

Parameters

Parameter	Type	Description
`value`	`number`	The sole unacceptable value.

Returns

ConditionalFormatHighlightRuleBuilder

The conditional format highlight rule builder.

Example


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


whenTextContains(text): ConditionalFormatHighlightRuleBuilder

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

Parameters

Parameter	Type	Description
`text`	`string`	The value that the input must contain.

Returns

ConditionalFormatHighlightRuleBuilder

The conditional format highlight rule builder.

Example


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


whenTextDoesNotContain(text): ConditionalFormatHighlightRuleBuilder

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

Parameters

Parameter	Type	Description
`text`	`string`	The value that the input must not contain.

Returns

ConditionalFormatHighlightRuleBuilder

The conditional format highlight rule builder.

Example


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


whenTextEndsWith(text): ConditionalFormatHighlightRuleBuilder

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

Parameters

Parameter	Type	Description
`text`	`string`	Text to compare against the end of the string.

Returns

ConditionalFormatHighlightRuleBuilder

The conditional format highlight rule builder.

Example


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


whenTextEqualTo(text): ConditionalFormatHighlightRuleBuilder

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

Parameters

Parameter	Type	Description
`text`	`string`	The sole acceptable value.

Returns

ConditionalFormatHighlightRuleBuilder

The conditional format highlight rule builder.

Example


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


whenTextStartsWith(text): ConditionalFormatHighlightRuleBuilder

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

Parameters

Parameter	Type	Description
`text`	`string`	Text to compare against the beginning of the string.

Returns

ConditionalFormatHighlightRuleBuilder

The conditional format highlight rule builder.

Example


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