# Filter

> Language fallback: requested `zh-CN`; content is `en-US`.

- Human documentation: [https://docs.univer.ai/zh-CN/reference/facade/filter](https://docs.univer.ai/zh-CN/reference/facade/filter)

- Agent Markdown: [https://docs.univer.ai/zh-CN/reference/facade/filter.md](https://docs.univer.ai/zh-CN/reference/facade/filter.md)

- Requested language: `zh-CN`

- Content language: `en-US`

- Documentation version: `1.0.0-rc.0`

- Source: [facade/filter.mdx](https://github.com/dream-num/documentation/blob/dev/content/reference/facade/filter.mdx)

---

| Packages | `@univerjs/sheets-filter` |
| -------- | ------------------------- |

This interface class provides methods to modify the filter settings of a worksheet.

> This class should not be instantiated directly. Use factory methods on `univerAPI` instead.

## Overview

### @univerjs/sheets-filter

| Method                                                      | Description                               |
| ----------------------------------------------------------- | ----------------------------------------- |
| [`getColumnFilterCriteria`](#getcolumnfiltercriteria)       | Get the filter criteria of a column       |
| [`getFilteredOutRows`](#getfilteredoutrows)                 | Get the filtered out rows by this filter  |
| [`getRange`](#getrange)                                     | Get the range of the filter               |
| [`remove`](#remove)                                         | Remove the filter from the worksheet      |
| [`removeColumnFilterCriteria`](#removecolumnfiltercriteria) | Clear the filter criteria of a column     |
| [`removeFilterCriteria`](#removefiltercriteria)             | Remove the filter criteria of all columns |
| [`setColumnFilterCriteria`](#setcolumnfiltercriteria)       | Set the filter criteria of a column       |

## APIs

### Getters & Queries

### `getColumnFilterCriteria`

Get the filter criteria of a column.

**Signature**

```typescript
getColumnFilterCriteria(column: number): Nullable<IFilterColumn>
```

**Parameters**

* `column` `number` — *No description*

**Returns**

* `any` — The filter criteria of the column.

**Examples**

```ts
const fWorkbook = univerAPI.getActiveWorkbook();
const fWorksheet = fWorkbook.getActiveSheet();

// Set some values of the range C1:F10
const fRange = fWorksheet.getRange('C1:F10');
fRange.setValues([
  [1, 2, 3, 4],
  [2, 3, 4, 5],
  [3, 4, 5, 6],
  [4, 5, 6, 7],
  [5, 6, 7, 8],
  [6, 7, 8, 9],
  [7, 8, 9, 10],
  [8, 9, 10, 11],
  [9, 10, 11, 12],
  [10, 11, 12, 13],
]);

// Create a filter on the range C1:F10
let fFilter = fRange.createFilter();

// If the filter already exists, remove it and create a new one
if (!fFilter) {
  fRange.getFilter().remove();
  fFilter = fRange.createFilter();
}

// Set the filter criteria of the column C, filter out the rows that are not 1, 5, 9
const column = fWorksheet.getRange('C:C').getColumn();
fFilter.setColumnFilterCriteria(column, {
  colId: 0,
  filters: {
    filters: ['1', '5', '9'],
  },
});

// Print the filter criteria of the column C and D
console.log(fFilter.getColumnFilterCriteria(column)); // { colId: 0, filters: { filters: ['1', '5', '9'] } }
console.log(fFilter.getColumnFilterCriteria(column + 1)); // undefined
```

Source: 

`@univerjs/sheets-filter`

### `getFilteredOutRows`

Get the filtered out rows by this filter.

**Signature**

```typescript
getFilteredOutRows(): number[]
```

**Returns**

* `number[]` — Filtered out rows by this filter.

**Examples**

```ts
const fWorkbook = univerAPI.getActiveWorkbook();
const fWorksheet = fWorkbook.getActiveSheet();

// Set some values of the range C1:F10
const fRange = fWorksheet.getRange('C1:F10');
fRange.setValues([
  [1, 2, 3, 4],
  [2, 3, 4, 5],
  [3, 4, 5, 6],
  [4, 5, 6, 7],
  [5, 6, 7, 8],
  [6, 7, 8, 9],
  [7, 8, 9, 10],
  [8, 9, 10, 11],
  [9, 10, 11, 12],
  [10, 11, 12, 13],
]);

// Create a filter on the range C1:F10
let fFilter = fRange.createFilter();

// If the filter already exists, remove it and create a new one
if (!fFilter) {
  fRange.getFilter().remove();
  fFilter = fRange.createFilter();
}

// Set the filter criteria of the column C, filter out the rows that are not 1, 5, 9
const column = fWorksheet.getRange('C:C').getColumn();
fFilter.setColumnFilterCriteria(column, {
  colId: 0,
  filters: {
    filters: ['1', '5', '9'],
  },
});

// Get the filtered out rows
console.log(fFilter.getFilteredOutRows()); // [1, 2, 3, 5, 6, 7, 9]
```

Source: 

`@univerjs/sheets-filter`

### `getRange`

Get the range of the filter.

**Signature**

```typescript
getRange(): FRange
```

**Returns**

* `FRange` — The range of the filter.

**Examples**

```ts
const fWorkbook = univerAPI.getActiveWorkbook();
const fWorksheet = fWorkbook.getActiveSheet();
const fFilter = fWorksheet.getFilter();
console.log(fFilter?.getRange().getA1Notation());
```

Source: 

`@univerjs/sheets-filter`

### Setters & Modifiers

### `setColumnFilterCriteria`

Set the filter criteria of a column.

**Signature**

```typescript
setColumnFilterCriteria(column: number, criteria: ISetSheetsFilterCriteriaCommandParams['criteria']): FFilter
```

**Parameters**

* `column` `number` — The column index (0-based) to set the filter criteria for.
* `criteria` `ISetSheetsFilterCriteriaCommandParams['criteria']` — The filter criteria to apply.

**Returns**

* `FFilter` — The FFilter instance for chaining.

**Examples**

```ts
const fWorkbook = univerAPI.getActiveWorkbook();
const fWorksheet = fWorkbook.getActiveSheet();

// Set some values of the range C1:F10
const fRange = fWorksheet.getRange('C1:F10');
fRange.setValues([
  [1, 2, 3, 4],
  [2, 3, 4, 5],
  [3, 4, 5, 6],
  [4, 5, 6, 7],
  [5, 6, 7, 8],
  [6, 7, 8, 9],
  [7, 8, 9, 10],
  [8, 9, 10, 11],
  [9, 10, 11, 12],
  [10, 11, 12, 13],
]);

// Create a filter on the range C1:F10
let fFilter = fRange.createFilter();

// If the filter already exists, remove it and create a new one
if (!fFilter) {
  fRange.getFilter().remove();
  fFilter = fRange.createFilter();
}

// Set the filter criteria of the column C, filter out the rows that are not 1, 5, 9
const column = fWorksheet.getRange('C:C').getColumn();
fFilter.setColumnFilterCriteria(column, {
  colId: 0,
  filters: {
    filters: ['1', '5', '9'],
  },
});
```

Source: 

`@univerjs/sheets-filter`

### Actions & Operations

### `remove`

Remove the filter from the worksheet.

**Signature**

```typescript
remove(): boolean
```

**Returns**

* `boolean` — True if the filter is removed successfully; otherwise, false.

**Examples**

```ts
const fWorkbook = univerAPI.getActiveWorkbook();
const fWorksheet = fWorkbook.getActiveSheet();
const fRange = fWorksheet.getRange('A1:D14');
let fFilter = fRange.createFilter();

// If the worksheet already has a filter, remove it and create a new filter.
if (!fFilter) {
  fWorksheet.getFilter().remove();
  fFilter = fRange.createFilter();
}
console.log(fFilter);
```

Source: 

`@univerjs/sheets-filter`

### `removeColumnFilterCriteria`

Clear the filter criteria of a column.

**Signature**

```typescript
removeColumnFilterCriteria(column: number): FFilter
```

**Parameters**

* `column` `number` — *No description*

**Returns**

* `FFilter` — The FFilter instance for chaining.

**Examples**

```ts
const fWorkbook = univerAPI.getActiveWorkbook();
const fWorksheet = fWorkbook.getActiveSheet();

// Set some values of the range C1:F10
const fRange = fWorksheet.getRange('C1:F10');
fRange.setValues([
  [1, 2, 3, 4],
  [2, 3, 4, 5],
  [3, 4, 5, 6],
  [4, 5, 6, 7],
  [5, 6, 7, 8],
  [6, 7, 8, 9],
  [7, 8, 9, 10],
  [8, 9, 10, 11],
  [9, 10, 11, 12],
  [10, 11, 12, 13],
]);

// Create a filter on the range C1:F10
let fFilter = fRange.createFilter();

// If the filter already exists, remove it and create a new one
if (!fFilter) {
  fRange.getFilter().remove();
  fFilter = fRange.createFilter();
}

// Set the filter criteria of the column C, filter out the rows that are not 1, 5, 9
const column = fWorksheet.getRange('C:C').getColumn();
fFilter.setColumnFilterCriteria(column, {
  colId: 0,
  filters: {
    filters: ['1', '5', '9'],
  },
});

// Clear the filter criteria of the column C after 3 seconds
setTimeout(() => {
  fFilter.removeColumnFilterCriteria(column);
}, 3000);
```

Source: 

`@univerjs/sheets-filter`

### `removeFilterCriteria`

Remove the filter criteria of all columns.

**Signature**

```typescript
removeFilterCriteria(): FFilter
```

**Returns**

* `FFilter` — The FFilter instance for chaining.

**Examples**

```ts
const fWorkbook = univerAPI.getActiveWorkbook();
const fWorksheet = fWorkbook.getActiveSheet();

// Set some values of the range C1:F10
const fRange = fWorksheet.getRange('C1:F10');
fRange.setValues([
  [1, 2, 3, 4],
  [2, 3, 4, 5],
  [3, 4, 5, 6],
  [4, 5, 6, 7],
  [5, 6, 7, 8],
  [6, 7, 8, 9],
  [7, 8, 9, 10],
  [8, 9, 10, 11],
  [9, 10, 11, 12],
  [10, 11, 12, 13],
]);

// Create a filter on the range C1:F10
let fFilter = fRange.createFilter();

// If the filter already exists, remove it and create a new one
if (!fFilter) {
  fRange.getFilter().remove();
  fFilter = fRange.createFilter();
}

// Set the filter criteria of the column C, filter out the rows that are not 1, 5, 9
const column = fWorksheet.getRange('C:C').getColumn();
fFilter.setColumnFilterCriteria(column, {
  colId: 0,
  filters: {
    filters: ['1', '5', '9'],
  },
});

// Clear the filter criteria of all columns after 3 seconds
setTimeout(() => {
  fFilter.removeFilterCriteria();
}, 3000);
```

Source: 

`@univerjs/sheets-filter`