# Conditional Formatting

- Human documentation: [https://docs.univer.ai/guides/sheets/features/conditional-formatting](https://docs.univer.ai/guides/sheets/features/conditional-formatting)

- Agent Markdown: [https://docs.univer.ai/guides/sheets/features/conditional-formatting.md](https://docs.univer.ai/guides/sheets/features/conditional-formatting.md)

- Requested language: `en-US`

- Content language: `en-US`

- Documentation version: `1.0.0-rc.0`

- Source: [sheets/features/conditional-formatting.mdx](https://github.com/dream-num/documentation/blob/dev/content/guides/sheets/features/conditional-formatting.mdx)

---

#### Package metadata

```json
{
  "preset": [
    {
      "client": "@univerjs/preset-sheets-conditional-formatting",
      "locale": "@univerjs/preset-sheets-conditional-formatting/locales/en-US",
      "style": "@univerjs/preset-sheets-conditional-formatting/lib/index.css"
    }
  ],
  "plugins": [
    {
      "client": "@univerjs/sheets-conditional-formatting",
      "facade": "@univerjs/sheets-conditional-formatting/facade"
    },
    {
      "client": "@univerjs/sheets-conditional-formatting-ui",
      "locale": "@univerjs/sheets-conditional-formatting-ui/locale/en-US",
      "style": "@univerjs/sheets-conditional-formatting-ui/lib/index.css"
    }
  ],
  "mobile": "Supported",
  "server": false
}
```

Conditional formatting allows users to automatically change the format of cells based on specific conditions, highlighting important data or trends. It supports various conditions and formatting options, helping users understand data more intuitively.

## Preset Mode

### Installation

#### npm

```bash
npm install @univerjs/preset-sheets-conditional-formatting
```

#### pnpm

```bash
pnpm add @univerjs/preset-sheets-conditional-formatting
```

#### yarn

```bash
yarn add @univerjs/preset-sheets-conditional-formatting
```

#### bun

```bash
bun add @univerjs/preset-sheets-conditional-formatting
```

### Usage

```typescript
import { UniverSheetsConditionalFormattingPreset } from '@univerjs/preset-sheets-conditional-formatting' // [!code ++]
import UniverPresetSheetsConditionalFormattingEnUS from '@univerjs/preset-sheets-conditional-formatting/locales/en-US' // [!code ++]
import { UniverSheetsCorePreset } from '@univerjs/preset-sheets-core'
import UniverPresetSheetsCoreEnUS from '@univerjs/preset-sheets-core/locales/en-US'
import { createUniver, LocaleType, mergeLocales } from '@univerjs/presets'

import '@univerjs/preset-sheets-core/lib/index.css'
import '@univerjs/preset-sheets-conditional-formatting/lib/index.css' // [!code ++]

const { univerAPI } = createUniver({
  locale: LocaleType.EN_US,
  locales: {
    [LocaleType.EN_US]: mergeLocales(
      UniverPresetSheetsCoreEnUS,
      UniverPresetSheetsConditionalFormattingEnUS, // [!code ++]
    ),
  },
  presets: [
    UniverSheetsCorePreset(),
    UniverSheetsConditionalFormattingPreset(), // [!code ++]
  ],
})
```

## Plugin Mode

### Installation

#### npm

```bash
npm install @univerjs/sheets-conditional-formatting @univerjs/sheets-conditional-formatting-ui
```

#### pnpm

```bash
pnpm add @univerjs/sheets-conditional-formatting @univerjs/sheets-conditional-formatting-ui
```

#### yarn

```bash
yarn add @univerjs/sheets-conditional-formatting @univerjs/sheets-conditional-formatting-ui
```

#### bun

```bash
bun add @univerjs/sheets-conditional-formatting @univerjs/sheets-conditional-formatting-ui
```

### Usage

```typescript
import { LocaleType, mergeLocales, Univer } from '@univerjs/core'
import { UniverSheetsConditionalFormattingPlugin } from '@univerjs/sheets-conditional-formatting' // [!code ++]
import { UniverSheetsConditionalFormattingUIPlugin } from '@univerjs/sheets-conditional-formatting-ui' // [!code ++]
import SheetsConditionalFormattingUIEnUS from '@univerjs/sheets-conditional-formatting-ui/locale/en-US' // [!code ++]

import '@univerjs/sheets-conditional-formatting-ui/lib/index.css' // [!code ++]

import '@univerjs/sheets-conditional-formatting/facade' // [!code ++]

const univer = new Univer({
  locale: LocaleType.EN_US,
  locales: {
    [LocaleType.EN_US]: mergeLocales(
      SheetsConditionalFormattingUIEnUS, // [!code ++]
    ),
  },
})

univer.registerPlugin(UniverSheetsConditionalFormattingPlugin) // [!code ++]
univer.registerPlugin(UniverSheetsConditionalFormattingUIPlugin) // [!code ++]
```

## Facade API

Complete Facade API type definitions can be found in the [FacadeAPI](https://reference.univer.ai/en-US).

### Importing

> [!INFO: Plugin mode note]
> Only plugin mode requires manually importing the Facade package. Preset mode already includes the corresponding Facade package, so no extra import is needed.

```typescript
import '@univerjs/sheets-conditional-formatting/facade'
```

### Create Conditional Formatting Rules

[`FWorksheet.newConditionalFormattingRule()`](https://docs.univer.ai/reference/facade/worksheet.md#newconditionalformattingrule) creates a conditional formatting builder, returning an instance of `FConditionalFormattingBuilder`, which can be used to generate conditional formatting rules through method chaining.

Here are some member methods of [`FConditionalFormattingBuilder`](https://docs.univer.ai/reference/facade/conditional-formatting.md):

| Method                         | Description                                                                                                           |
| ------------------------------ | --------------------------------------------------------------------------------------------------------------------- |
| build                          | Builds the conditional formatting rule                                                                                |
| copy                           | Deep clone a current builder                                                                                          |
| createCfId                     | Create a conditional format ID                                                                                        |
| getRanges                      | Gets the scope of the current conditional format                                                                      |
| setRanges                      | Sets the range of the conditional formatting rule                                                                     |
| setAverage                     | Sets the average value conditional formatting rule                                                                    |
| setUniqueValues                | Sets the unique value conditional formatting rule                                                                     |
| setDuplicateValues             | Sets the duplicate value conditional formatting rule                                                                  |
| setRank                        | Sets the ranking conditional formatting rule                                                                          |
| setIconSet                     | Sets the icon set conditional formatting rule                                                                         |
| setColorScale                  | Sets the color scale conditional formatting rule                                                                      |
| setDataBar                     | Sets the data bar conditional formatting rule                                                                         |
| setBackground                  | Sets the background color of the conditional format                                                                   |
| setBold                        | Sets whether the font of the conditional format is bold                                                               |
| setFontColor                   | Sets the font color of the conditional format                                                                         |
| setItalic                      | Sets whether the font of the conditional format is italic                                                             |
| setStrikethrough               | Sets whether the font of the conditional format has a strikethrough                                                   |
| setUnderline                   | Sets whether the font of the conditional format has an underline                                                      |
| whenCellEmpty                  | Sets the conditional format rule to trigger when the cell is empty                                                    |
| whenCellNotEmpty               | Sets the conditional format rule to trigger when the cell is not empty                                                |
| whenDate                       | Sets the conditional format rule to trigger when the date is within a specific time period                            |
| whenFormulaSatisfied           | Sets the conditional format rule to trigger when the formula calculation result is true                               |
| whenNumberBetween              | Sets the conditional format rule to trigger when the number is between two specified values or equal to one of them   |
| whenNumberEqualTo              | Sets the conditional format rule to trigger when the number is equal to the given value                               |
| whenNumberGreaterThan          | Sets the conditional format rule to trigger when the number is greater than the given value                           |
| whenNumberGreaterThanOrEqualTo | Sets the conditional format rule to trigger when the number is greater than or equal to the given value               |
| whenNumberLessThan             | Sets the conditional format rule to trigger when the number is less than the given value                              |
| whenNumberLessThanOrEqualTo    | Sets the conditional format rule to trigger when the number is less than or equal to the given value                  |
| whenNumberNotBetween           | Sets the conditional format rule to trigger when the number is not between two specified values and not equal to them |
| whenNumberNotEqualTo           | Sets the conditional format rule to trigger when the number is not equal to the given value                           |
| whenTextContains               | Sets the conditional format rule to trigger when the text contains the specified value                                |
| whenTextDoesNotContain         | Sets the conditional format rule to trigger when the text does not contain the specified value                        |
| whenTextEndsWith               | Sets the conditional format rule to trigger when the text ends with the specified value                               |
| whenTextEqualTo                | Sets the conditional format rule to trigger when the text equals the given value                                      |
| whenTextStartsWith             | Sets the conditional format rule to trigger when the text starts with the specified value                             |

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

### Get Conditional Formatting Rules

* `FWorksheet.getConditionalFormattingRules()` gets all conditional formatting rules of the worksheet
* `FRange.getConditionalFormattingRules()` gets all conditional formatting rules of the range

```typescript
const fWorkbook = univerAPI.getActiveWorkbook()

// Get all conditional formatting rules of the active worksheet
const fWorksheet = fWorkbook.getActiveSheet()
const rulesOfSheet = fWorksheet.getConditionalFormattingRules()

// Get all conditional formatting rules of the range A1:T100
const fRange = fWorksheet.getRange('A1:T100')
const rulesOfRange = fRange.getConditionalFormattingRules()
```

### Remove Conditional Formatting Rules

`FWorksheet.deleteConditionalFormattingRule(cfId)` removes a conditional formatting rule from the worksheet, where `cfId` is the ID of the conditional formatting rule.

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

// Remove the first conditional formatting rule
fWorksheet.deleteConditionalFormattingRule(rules[0]?.cfId)
```

### Update Conditional Formatting Rules

`FWorksheet.setConditionalFormattingRule(cfId, rule)` updates a conditional formatting rule in the worksheet, where `cfId` is the ID of the conditional formatting rule and `rule` is the new conditional formatting rule.

```typescript
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()] })
```

### Modify the Priority of Conditional Formatting Rules

`FWorksheet.moveConditionalFormattingRule(cfId, toCfId, type)` modifies the priority of a conditional formatting rule in the worksheet, where `cfId` is the ID of the conditional formatting rule, `toCfId` is the ID of the target conditional formatting rule, and `type` specifies the move type (e.g., 'before', 'after').

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

### Clear All Conditional Formatting Rules

* `FWorksheet.clearConditionalFormatRules()` clears all conditional formatting rules of the worksheet.
* `FRange.clearConditionalFormatRules()` clears all conditional formatting rules of the range.

```typescript
const fWorkbook = univerAPI.getActiveWorkbook()

// Clear all conditional formatting rules of the active worksheet
const fWorksheet = fWorkbook.getActiveSheet()
fWorksheet.clearConditionalFormatRules()

// Clear all conditional formatting rules of the range A1:T100
const fRange = fWorksheet.getRange('A1:T100')
fRange.clearConditionalFormatRules()
```