Skip to Content
🎉 Univer 0.6.7 is released.Read more →
GuidesUniver SheetsFeaturesFilter

Filter

Facade APIHas Paid PlanUniver ServerUniver on Node.jsPreset
✅--✅UniverSheetsFilterPreset

The filter plugin provides filtering functionality for cells.

This feature includes the following plugin packages:

Presets Installation

import { createUniver, defaultTheme, LocaleType, merge } from '@univerjs/presets';
import { UniverSheetsCorePreset } from '@univerjs/presets/preset-sheets-core';
import UniverPresetSheetsCoreEnUS from '@univerjs/presets/preset-sheets-core/locales/en-US';
import { UniverSheetsFilterPreset } from '@univerjs/presets/preset-sheets-filter';
import UniverPresetSheetsFilterEnUS from '@univerjs/presets/preset-sheets-filter/locales/en-US';
 
import '@univerjs/presets/lib/styles/preset-sheets-core.css'
import '@univerjs/presets/lib/styles/preset-sheets-filter.css'
 
const { univerAPI } = createUniver({
  locale: LocaleType.EN_US,
  locales: {
    [LocaleType.EN_US]: merge(
      {},
      UniverPresetSheetsCoreEnUS,
      UniverPresetSheetsFilterEnUS
    ),
  },
  theme: defaultTheme,
  presets: [
    UniverSheetsCorePreset(),
    UniverSheetsFilterPreset(),
  ],
});

Piecemeal Installation

npm install @univerjs/sheets-filter @univerjs/sheets-filter-ui
import { LocaleType, merge, Univer } from '@univerjs/core';
import { defaultTheme } from "@univerjs/design";
import { UniverSheetsFilterPlugin } from '@univerjs/sheets-filter';
import { UniverSheetsFilterUIPlugin } from '@univerjs/sheets-filter-ui';
import SheetsFilterUIEnUS from '@univerjs/sheets-filter-ui/locale/en-US';
 
import '@univerjs/sheets-filter-ui/lib/index.css';
 
import '@univerjs/sheets-filter/facade';
 
const univer = new Univer({
  theme: defaultTheme,
  locale: LocaleType.en_US,
  locales: {
    [LocaleType.en_US]: merge(
      SheetsFilterUIEnUS
    ),
  },
});
 
univer.registerPlugin(UniverSheetsFilterPlugin);
univer.registerPlugin(UniverSheetsFilterUIPlugin);

Univer on Node.js Piecemeal Installation

npm install @univerjs/sheets-filter
import { LocaleType, Univer } from '@univerjs/core';
import { defaultTheme } from "@univerjs/design";
import { UniverSheetsFilterPlugin } from '@univerjs/sheets-filter';
 
import '@univerjs/sheets-filter/facade';
 
const univer = new Univer({
  theme: defaultTheme,
  locale: LocaleType.ZH_CN,
});
 
univer.registerPlugin(UniverSheetsFilterPlugin);

Facade API

To get full defination of facade api, please refer to FacadeAPI .

Get Filter

Returns an FFilter object, or null if the sheet has no filter.

const fWorkbook = univerAPI.getActiveWorkbook();
 
// Get filter from FWorksheet
const fWorksheet = fWorkbook.getActiveSheet();
const fFilter = fWorksheet.getFilter();
console.log(fFilter, fFilter?.getRange().getA1Notation());
 
// Get filter from FRange
const fRange = fWorksheet.getRange('A1:D14');
const fFilter2 = fRange.getFilter();
console.log(fFilter2, fFilter2?.getRange().getA1Notation());

Create Filter

Create an FFilter object, and return null if the sheet already has a filter.

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, fFilter.getRange().getA1Notation());

Remove Filter

FFilter.remove() method removes the filter.

const fWorkbook = univerAPI.getActiveWorkbook();
const fWorksheet = fWorkbook.getActiveSheet();
fWorksheet.getFilter()?.remove();

Get Column Filter Criteria

FFilter.getColumnFilterCriteria(column) method returns the filter criteria of the specified column.

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

Set Column Filter Criteria

FFilter.setColumnFilterCriteria(column, criteria) method sets the filter criteria of the specified column.

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'],
  },
});

Remove Column Filter Criteria

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);
  // Or use fFilter.removeFilterCriteria() to remove all column filter criteria
}, 3000);

Get Filtered Out Rows

FFilter.getFilteredOutRows() method returns an array containing the indexes of the rows that are filtered out.

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 filtered out rows
console.log(fFilter.getFilteredOutRows()); // [1, 2, 3, 5, 6, 7, 9]

Event Listening

Full event type definitions, please refer to Events .

SheetRangeFiltered event is triggered when the column filter criteria changes.

const callbackDisposable = univerAPI.addEvent(univerAPI.Event.SheetRangeFiltered, (params) => {
  console.log(params);
  const { workbook, worksheet, col, criteria } = params;
 
  // your custom logic
});
 
// Remove the event listener, use `callbackDisposable.dispose()`

SheetBeforeRangeFilter event is triggered before the column filter criteria changes.

const callbackDisposable = univerAPI.addEvent(univerAPI.Event.SheetBeforeRangeFilter, (params) => {
  console.log(params);
  const { workbook, worksheet, col, criteria } = params;
 
  // your custom logic
 
  // Cancel the filter criteria change operation
  params.cancel = true;
});
 
// Remove the event listener, use `callbackDisposable.dispose()`

SheetRangeFilterCleared event is triggered when the criteria is cleared.

const callbackDisposable = univerAPI.addEvent(univerAPI.Event.SheetRangeFilterCleared, (params) => {
  console.log(params);
  const { workbook, worksheet } = params;
 
  // your custom logic
});
 
// Remove the event listener, use `callbackDisposable.dispose()`

SheetBeforeRangeFilterClear event is triggered before the criteria is cleared.

const callbackDisposable = univerAPI.addEvent(univerAPI.Event.SheetBeforeRangeFilterClear, (params) => {
  console.log(params);
  const { workbook, worksheet } = params;
 
  // your custom logic
 
  // Cancel the filter clear operation
  params.cancel = true;
});
 
// Remove the event listener, use `callbackDisposable.dispose()`
Was this page helpful?
Last updated on
WorkerSort