FWorksheetConditionalFormattingMixin

packages	`@univerjs/sheets-conditional-formatting`

APIs

`addConditionalFormattingRule`

Add a new conditional format

Signature

addConditionalFormattingRule(rule: IConditionFormattingRule): FWorksheet

Parameters

rule (IConditionFormattingRule) — - The conditional formatting rule to add

Returns

(FWorksheet) — Returns the current worksheet instance for method chaining

Tags

@memberof — IFWorksheetConditionalFormattingMixin

Examples

const fWorkbook = univerAPI.getActiveWorkbook()
const fWorksheet = fWorkbook.getActiveSheet()

// Create a conditional formatting rule that sets the cell format to italic, red background, and green font color when the cell is not empty.
const fRange = fWorksheet.getRange('A1:T100')
const rule = fWorksheet.newConditionalFormattingRule()
  .whenCellNotEmpty()
  .setRanges([fRange.getRange()])
  .setItalic(true)
  .setBackground('red')
  .setFontColor('green')
  .build()
fWorksheet.addConditionalFormattingRule(rule)

`clearConditionalFormatRules`

Removes all conditional format rules from the sheet.

Signature

clearConditionalFormatRules(): FWorksheet

Returns

(FWorksheet) — Returns the current worksheet instance for method chaining

Tags

@memberof — IFWorksheetConditionalFormattingMixin

Examples

const fWorkbook = univerAPI.getActiveWorkbook()
const fWorksheet = fWorkbook.getActiveSheet()
fWorksheet.clearConditionalFormatRules()
console.log(fWorksheet.getConditionalFormattingRules()) // []

`createConditionalFormattingRule`

Deprecated use newConditionalFormattingRule instead. Creates a constructor for conditional formatting

Signature

createConditionalFormattingRule(): FConditionalFormattingBuilder

Returns

(FConditionalFormattingBuilder) — The conditional formatting builder

Tags

@memberof — IFWorksheetConditionalFormattingMixin

`deleteConditionalFormattingRule`

Delete conditional format according to cfId

Signature

deleteConditionalFormattingRule(cfId: string): FWorksheet

Parameters

cfId (string) — - The conditional formatting rule id to delete

Returns

(FWorksheet) — Returns the current worksheet instance for method chaining

Tags

@memberof — IFWorksheetConditionalFormattingMixin

Examples

const fWorkbook = univerAPI.getActiveWorkbook()
const fWorksheet = fWorkbook.getActiveSheet()
const rules = fWorksheet.getConditionalFormattingRules()

// Delete the first rule
fWorksheet.deleteConditionalFormattingRule(rules[0]?.cfId)

`getConditionalFormattingRules`

Gets all the conditional formatting for the current sheet

Signature

getConditionalFormattingRules(): IConditionFormattingRule[]

Returns

(IConditionFormattingRule[]) — conditional formatting rules for the current sheet

Tags

@memberof — IFWorksheetConditionalFormattingMixin

Examples

const fWorkbook = univerAPI.getActiveWorkbook()
const fWorksheet = fWorkbook.getActiveSheet()
const rules = fWorksheet.getConditionalFormattingRules()
console.log(rules)

`moveConditionalFormattingRule`

Modify the priority of the conditional format

Signature

moveConditionalFormattingRule(cfId: string, toCfId: string, type?: IAnchor['type'] = 'after'): FWorksheet

Parameters

cfId (string) — - The conditional formatting rule id to move
toCfId (string) — Target rule
type (IAnchor['type']) — After the default move to the destination rule, if type = before moves to the front, the default value is after

Returns

(FWorksheet) — Returns the current worksheet instance for method chaining

Tags

@memberof — FWorksheetConditionalFormattingMixin

Examples

const fWorkbook = univerAPI.getActiveWorkbook()
const fWorksheet = fWorkbook.getActiveSheet()
const rules = fWorksheet.getConditionalFormattingRules()

// Move the third rule before the first rule
const rule = rules[2]
const targetRule = rules[0]
fWorksheet.moveConditionalFormattingRule(rule?.cfId, targetRule?.cfId, 'before')

`newConditionalFormattingRule`

Creates a constructor for conditional formatting

Signature

newConditionalFormattingRule(): FConditionalFormattingBuilder

Returns

(FConditionalFormattingBuilder) — The conditional formatting builder

Tags

@memberof — IFWorksheetConditionalFormattingMixin

Examples

const fWorkbook = univerAPI.getActiveWorkbook()
const fWorksheet = fWorkbook.getActiveSheet()

// Create a conditional formatting rule that sets the cell format to italic, red background, and green font color when the cell is not empty.
const fRange = fWorksheet.getRange('A1:T100')
const rule = fWorksheet.newConditionalFormattingRule()
  .whenCellNotEmpty()
  .setRanges([fRange.getRange()])
  .setItalic(true)
  .setBackground('red')
  .setFontColor('green')
  .build()
fWorksheet.addConditionalFormattingRule(rule)

`setConditionalFormattingRule`

Set the conditional format according to cfId

Signature

setConditionalFormattingRule(cfId: string, rule: IConditionFormattingRule): FWorksheet

Parameters

cfId (string) — - The conditional formatting rule id to set
rule (IConditionFormattingRule) — - The conditional formatting rule to set

Returns

(FWorksheet) — Returns the current worksheet instance for method chaining

Tags

@memberof — IFWorksheetConditionalFormattingMixin

Examples

const fWorkbook = univerAPI.getActiveWorkbook()
const fWorksheet = fWorkbook.getActiveSheet()

// Create a conditional formatting rule that sets the cell format to italic, red background, and green font color when the cell is not empty.
const fRange = fWorksheet.getRange('A1:T100')
const rule = fWorksheet.newConditionalFormattingRule()
  .whenCellNotEmpty()
  .setRanges([fRange.getRange()])
  .setItalic(true)
  .setBackground('red')
  .setFontColor('green')
  .build()
fWorksheet.addConditionalFormattingRule(rule)

// Modify the first rule to apply to a new range
const rules = fWorksheet.getConditionalFormattingRules()
const newRuleRange = fWorksheet.getRange('A1:D10')
fWorksheet.setConditionalFormattingRule(rules[0]?.cfId, { ...rules[0], ranges: [newRuleRange.getRange()] })

Table of Contents