Filter

Packages	`@univerjs/sheets-filter`

CORE

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`	Get the filter criteria of a column
`getFilteredOutRows`	Get the filtered out rows by this filter
`getRange`	Get the range of the filter
`remove`	Remove the filter from the worksheet
`removeColumnFilterCriteria`	Clear the filter criteria of a column
`removeFilterCriteria`	Remove the filter criteria of all columns
`setColumnFilterCriteria`	Set the filter criteria of a column

APIs

Getters & Queries

`getColumnFilterCriteria`

Get the filter criteria of a column.

Signature

getColumnFilterCriteria(column: number): Nullable<IFilterColumn>

Parameters

column number — No description

Returns

any — The filter criteria of the column.

Examples

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

getFilteredOutRows(): number[]

Returns

number[] — Filtered out rows by this filter.

Examples

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

getRange(): FRange

Returns

FRange — The range of the filter.

Examples

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

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

Parameters

column number — No description
criteria ISetSheetsFilterCriteriaCommandParams — No description

Returns

FFilter — The FFilter instance for chaining.

Examples

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

remove(): boolean

Returns

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

Examples

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

removeColumnFilterCriteria(column: number): FFilter

Parameters

column number — No description

Returns

FFilter — The FFilter instance for chaining.

Examples

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

removeFilterCriteria(): FFilter

Returns

FFilter — The FFilter instance for chaining.

Examples

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

Table of Contents