FFilter

packages	`@univerjs/sheets-filter`

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

APIs

`getColumnFilterCriteria`

Get the filter criteria of a column.

Signature

getColumnFilterCriteria(column: number): Nullable<IFilterColumn>

Parameters

column (number) — - The column index.

Returns

(Nullable<IFilterColumn>) — 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

`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]

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

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

`removeColumnFilterCriteria`

Clear the filter criteria of a column.

Signature

removeColumnFilterCriteria(column: number): FFilter

Parameters

column (number) — - The column index.

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)

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

`setColumnFilterCriteria`

Set the filter criteria of a column.

Signature

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

Parameters

column (number) — - The column index.
criteria (ISetSheetsFilterCriteriaCommandParams['criteria']) — - The new filter criteria.

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

Table of Contents