Class: ConditionalFormatHighlightRuleBuilder

Extends

ConditionalFormatRuleBaseBuilder

Methods

copy()


copy(): ConditionalFormatHighlightRuleBuilder

Deep clone a current builder.

Returns

ConditionalFormatHighlightRuleBuilder

A new builder with the same settings as the original.

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

Overrides


ConditionalFormatRuleBaseBuilder.copy

setAverage()


setAverage(operator): ConditionalFormatHighlightRuleBuilder

Set average rule.

Parameters

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

Returns

ConditionalFormatHighlightRuleBuilder

This builder for chaining.

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

This builder for chaining.

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

This builder for chaining.

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

setDuplicateValues()


setDuplicateValues(): ConditionalFormatHighlightRuleBuilder

Set duplicate values rule.

Returns

ConditionalFormatHighlightRuleBuilder

This builder for chaining.

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

This builder for chaining.

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

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

This builder for chaining.

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

This builder for chaining.

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

This builder for chaining.

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

This builder for chaining.

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

This builder for chaining.

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

This builder for chaining.

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

This builder for chaining.

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

This builder for chaining.

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

This builder for chaining.

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

This builder for chaining.

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

This builder for chaining.

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

This builder for chaining.

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

This builder for chaining.

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

This builder for chaining.

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

This builder for chaining.

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

This builder for chaining.

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

This builder for chaining.

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

This builder for chaining.

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

This builder for chaining.

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

This builder for chaining.

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

This builder for chaining.

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

This builder for chaining.

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