Filter

Preset ModePlugin Mode

Preset Info

@univerjs/preset-sheets-filter

Server Required

The Filter feature allows users to filter data in spreadsheets to quickly find and analyze specific information. It supports various filtering conditions and operations, helping users process data more efficiently.

Preset Mode

Installation

npm install @univerjs/preset-sheets-filter

Usage

import { UniverSheetsCorePreset } from '@univerjs/preset-sheets-core'
import UniverPresetSheetsCoreEnUS from '@univerjs/preset-sheets-core/locales/en-US'
import { UniverSheetsFilterPreset } from '@univerjs/preset-sheets-filter'
import UniverPresetSheetsFilterEnUS from '@univerjs/preset-sheets-filter/locales/en-US'
import { createUniver, LocaleType, merge } from '@univerjs/presets'

import '@univerjs/preset-sheets-core/lib/index.css'
import '@univerjs/preset-sheets-filter/lib/index.css'

const { univerAPI } = createUniver({
  locale: LocaleType.En_US,
  locales: {
    [LocaleType.En_US]: merge(
      {},
      UniverPresetSheetsCoreEnUS,
      UniverPresetSheetsFilterEnUS, 
    ),
  },
  presets: [
    UniverSheetsCorePreset(),
    UniverSheetsFilterPreset(), 
  ],
})

Plugin Mode

Installation

npm install @univerjs/sheets-filter @univerjs/sheets-filter-ui

Usage

import { LocaleType, merge, Univer } from '@univerjs/core'
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({
  locale: LocaleType.En_US,
  locales: {
    [LocaleType.En_US]: merge(
      {},
      SheetsFilterUIEnUS, 
    ),
  },
})

univer.registerPlugin(UniverSheetsFilterPlugin) 
univer.registerPlugin(UniverSheetsFilterUIPlugin)

Facade API

Complete Facade API type definitions can be found in the FacadeAPI.。

Get Filter

Returns an FFilter object. If the sheet does not have a filter, it returns null.

On the FWorksheet object, use getFilter()
On the FRange object, use getFilter()

const fWorkbook = univerAPI.getActiveWorkbook()

// Get filter from FWorksheet
const fWorksheet = fWorkbook.getActiveSheet()
const fFilter = fWorksheet.getFilter()
fFilter?.getRange().getA1Notation()

// Get filter from FRange
const fRange = fWorksheet.getRange('A1:D14')
const fFilter2 = fRange.getFilter()
fFilter2?.getRange().getA1Notation()

Create Filter

Creates an FFilter object. If the sheet already has a filter, it returns null.

On the FRange object, use createFilter()

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

Remove Filter

FFilter.remove() 方法移除筛选器。

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

Get Column Filter Criteria

FFilter.getColumnFilterCriteria(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],
])

// Set some values of 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
fFilter.getColumnFilterCriteria(column) // { colId: 0, filters: { filters: ['1', '5', '9'] } }
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

FFilter.removeColumnFilterCriteria(column) method removes the filter criteria of the specified column.
FFilter.removeFilterCriteria() method removes the filter criteria of all columns.

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
fFilter.getFilteredOutRows() // [1, 2, 3, 5, 6, 7, 9]

Event Listeners

Complete event type definitions can be found in the Events.

SheetRangeFiltered event is triggered when the column filter criteria changes.

const disposable = univerAPI.addEvent(univerAPI.Event.SheetRangeFiltered, (params) => {
  const { workbook, worksheet, col, criteria } = params

  // your custom logic
})

// Remove the event listener, use `disposable.dispose()`

SheetBeforeRangeFilter event is triggered before the column filter criteria changes.

const disposable = univerAPI.addEvent(univerAPI.Event.SheetBeforeRangeFilter, (params) => {
  const { workbook, worksheet, col, criteria } = params

  // your custom logic

  // Cancel the filter criteria change operation
  params.cancel = true
})

// Remove the event listener, use `disposable.dispose()`

SheetRangeFilterCleared event is triggered when the criteria is cleared.

const disposable = univerAPI.addEvent(univerAPI.Event.SheetRangeFilterCleared, (params) => {
  const { workbook, worksheet } = params

  // your custom logic
})

// Remove the event listener, use `disposable.dispose()`

SheetBeforeRangeFilterClear event is triggered before the criteria is cleared.

const disposable = univerAPI.addEvent(univerAPI.Event.SheetBeforeRangeFilterClear, (params) => {
  const { workbook, worksheet } = params

  // your custom logic

  // Cancel the filter clear operation
  params.cancel = true
})

// Remove the event listener, use `disposable.dispose()`

On this page