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