Filter in Vue Spreadsheet component

3 Jul 202420 minutes to read

Filtering helps you to view specific rows in the spreadsheet by hiding the other rows. You can use the allowFiltering property to enable or disable filtering functionality.

  • The default value for allowFiltering property is true.

By default, the Filter module is injected internally into Spreadsheet to perform filtering.

Apply filter on UI

In the active sheet, select a range of cells to filter by value of the cell. The filtering can be done by any of the following ways:

  • Select the filter item in the Ribbon toolbar.
  • Right-click the sheet, select the filter item in the context menu.
  • Use the applyFilter() method programmatically.
  • Use Ctrl + Shift + L keyboard shortcut to apply the filter.
  • Use Alt + Up/Down keyboard shortcut to open the filter dialog.

Filter by criteria

The applyFilter() method will apply the filter UI, based on the predicate and range given in the arguments.

  • The beforeFilter event will be triggered before filtering the specified range.
  • The filterComplete event will be triggered after the filter action is completed successfully.

The following code example shows filter functionality in the Spreadsheet control.

<template>
  <ejs-spreadsheet ref="spreadsheet" :allowFiltering='true' :dataBound="dataBound">
    <e-sheets>
      <e-sheet>
        <e-ranges>
          <e-range :dataSource="dataSource"></e-range>
        </e-ranges>
        <e-columns>
          <e-column :width="width1"></e-column>
          <e-column :width="width2"></e-column>
          <e-column :width="width3"></e-column>
          <e-column :width="width2"></e-column>
          <e-column :width="width2"></e-column>
          <e-column :width="width3"></e-column>
          <e-column :width="width1"></e-column>
          <e-column :width="width1"></e-column>
          <e-column :width="width4"></e-column>
          <e-column :width="width1"></e-column>
          <e-column :width="width2"></e-column>
          <e-column :width="width5"></e-column>
        </e-columns>
      </e-sheet>
    </e-sheets></ejs-spreadsheet>
</template>

<script setup>
import { ref } from "vue";
import { SpreadsheetComponent as EjsSpreadsheet, ColumnsDirective as EColumns, ColumnDirective as EColumn, RangesDirective as ERanges, RangeDirective as ERange, SheetsDirective as ESheets, SheetDirective as ESheet } from "@syncfusion/ej2-vue-spreadsheet";
import { tradeData } from './data.js';

const spreadsheet = ref(null);
const dataSource = tradeData;
const width1 = 100;
const width2 = 130;
const width3 = 96;
const width4 = 110;
const width5 = 150;

const dataBound = function () {
  if (spreadsheet.value.ej2Instances.activeSheetIndex === 0) {
    let departments = ['Sweden', 'Canada', 'UK'];
    let predicateList = []
    departments.forEach((department) => { predicateList.push({ field: 'D', predicate: 'or', operator: 'equal', value: department }); })
    spreadsheet.value.applyFilter(predicateList);
  }
}
</script>
<style>
@import '../node_modules/@syncfusion/ej2-base/styles/material.css';
@import '../node_modules/@syncfusion/ej2-buttons/styles/material.css';
@import '../node_modules/@syncfusion/ej2-dropdowns/styles/material.css';
@import '../node_modules/@syncfusion/ej2-inputs/styles/material.css';
@import '../node_modules/@syncfusion/ej2-navigations/styles/material.css';
@import '../node_modules/@syncfusion/ej2-popups/styles/material.css';
@import '../node_modules/@syncfusion/ej2-splitbuttons/styles/material.css';
@import '../node_modules/@syncfusion/ej2-grids/styles/material.css';
@import "../node_modules/@syncfusion/ej2-vue-spreadsheet/styles/material.css";
</style>
<template>
  <ejs-spreadsheet ref="spreadsheet" :allowFiltering='true' :dataBound="dataBound">
    <e-sheets>
      <e-sheet>
        <e-ranges>
          <e-range :dataSource="dataSource"></e-range>
        </e-ranges>
        <e-columns>
          <e-column :width="width1"></e-column>
          <e-column :width="width2"></e-column>
          <e-column :width="width3"></e-column>
          <e-column :width="width2"></e-column>
          <e-column :width="width2"></e-column>
          <e-column :width="width3"></e-column>
          <e-column :width="width1"></e-column>
          <e-column :width="width1"></e-column>
          <e-column :width="width4"></e-column>
          <e-column :width="width1"></e-column>
          <e-column :width="width2"></e-column>
          <e-column :width="width5"></e-column>
        </e-columns>
      </e-sheet>
    </e-sheets></ejs-spreadsheet>
</template>

<script>
import { SpreadsheetComponent, ColumnsDirective, ColumnDirective, RangesDirective, RangeDirective, SheetsDirective, SheetDirective } from "@syncfusion/ej2-vue-spreadsheet";
import { tradeData } from './data.js';

export default {
  name: "App",
  components: {
    "ejs-spreadsheet": SpreadsheetComponent,
    "e-sheets": SheetsDirective,
    "e-sheet": SheetDirective,
    "e-ranges": RangesDirective,
    "e-range": RangeDirective,
    "e-columns": ColumnsDirective,
    "e-column": ColumnDirective
  },
  data: () => {
    return {
      dataSource: tradeData,
      width1: 100,
      width2: 130,
      width3: 96,
      width4: 110,
      width5: 150,
    }
  },
  methods: {
    dataBound: function () {
      let spreadsheet = this.$refs.spreadsheet;
      if (spreadsheet.ej2Instances.activeSheetIndex === 0) {
        let departments = ['Sweden', 'Canada', 'UK'];
        let predicateList = []
        departments.forEach((department) => { predicateList.push({ field: 'D', predicate: 'or', operator: 'equal', value: department }); })
        spreadsheet.applyFilter(predicateList);
      }
    }
  }
}
</script>
<style>
@import '../node_modules/@syncfusion/ej2-base/styles/material.css';
@import '../node_modules/@syncfusion/ej2-buttons/styles/material.css';
@import '../node_modules/@syncfusion/ej2-dropdowns/styles/material.css';
@import '../node_modules/@syncfusion/ej2-inputs/styles/material.css';
@import '../node_modules/@syncfusion/ej2-navigations/styles/material.css';
@import '../node_modules/@syncfusion/ej2-popups/styles/material.css';
@import '../node_modules/@syncfusion/ej2-splitbuttons/styles/material.css';
@import '../node_modules/@syncfusion/ej2-grids/styles/material.css';
@import "../node_modules/@syncfusion/ej2-vue-spreadsheet/styles/material.css";
</style>

Filter by cell value

To apply a filter for a cell value, right-click the cell and choose filter -> Filter By Selected Cell's Value option from the menu. It applies the filter based on the value of the selected cell in the current sheet.

Clear filter

After applying filter to a certain column, you may want to clear it to make all filtered rows visible again. It can be done in the following ways,

  • Choose Clear option in ribbon toolbar under Filter and Sort. It clears the filters applied in the spreadsheet for all fields.

  • Use the clearFilter() method programmatically, to clear the applied filters in spreadsheet for all fields.

Clear filter on a field

After filtering, you can clear/reset the filter for a field alone. It can be done in the following ways,

  • Click filter icon in the column’s header and then choose Clear Filter option from the filter dialog.
  • You can right-click on a filtered column cell and choose Clear Filter from <Column Name>. option from the context menu.
  • Use the clearFilter(field) method programmatically, to clear the filter in a particular column.

Reapply filter

When you want to reapply the filter after some changes happened in the rows. It can be done in the following ways,

  • You can choose Reapply option in ribbon toolbar under Filter and Sort to reapply the filtered columns again.
  • You can right-click on a filtered cell and choose Reapply option from the context menu. It reapplies the filters again in the Spreadsheet for all the fields.

Known error validations

The following errors have been handled for filtering,

  • Out of range validation: When the selected range is not a used range of the active sheet, it is considered as invalid and the out of range alert with the message Select a cell or range inside the used range and try again will be displayed. No filter will be performed if the range is invalid.

Get data from filtered rows

Filtering allows you to view specific rows in a spreadsheet while hiding the others. The allowFiltering property allows you to enable or disable filtering functionality through the UI. You can also use the allowFiltering property and applyFilter method combination to filter data via code behind. The filtered rows can be identified by iterating through the row collection on the sheet and using the isFiltered property available in each row object.

The following code example shows how to get the filtered rows.

<template>
  <div>
    <ejs-button class="e-btn custom-btn" @click="getFilterData">Get Filtered Data</ejs-button>
    <ejs-spreadsheet ref="spreadsheet" :created="created">
      <e-sheets>
        <e-sheet>
          <e-ranges>
            <e-range :dataSource="dataSource"></e-range>
          </e-ranges>
          <e-columns>
            <e-column :width="width1"></e-column>
            <e-column :width="width2"></e-column>
            <e-column :width="width2"></e-column>
            <e-column :width="width1"></e-column>
            <e-column :width="width2"></e-column>
            <e-column :width="width3"></e-column>
          </e-columns>
        </e-sheet>
      </e-sheets></ejs-spreadsheet>
  </div>
</template>

<script setup>
import { ref } from "vue";
import { SpreadsheetComponent as EjsSpreadsheet, ColumnsDirective as EColumns, ColumnDirective as EColumn, RangesDirective as ERanges, RangeDirective as ERange, SheetsDirective as ESheets, SheetDirective as ESheet } from "@syncfusion/ej2-vue-spreadsheet";
import { ButtonComponent as EjsButton } from "@syncfusion/ej2-vue-buttons";
import { defaultData } from './data.js';

const spreadsheet = ref(null);
const dataSource = defaultData;
const width1 = 180;
const width2 = 130;
const width3 = 120;

const created = function () {
  spreadsheet.value.cellFormat({ fontWeight: 'bold', textAlign: 'center', verticalAlign: 'middle' }, 'A1:F1');
  let predicates = [{
    field: 'C',
    operator: 'equal',
    value: 'Pink',
    matchCase: false
  }];
  spreadsheet.value.applyFilter(predicates, 'A1:C7');
}
const getFilterData = function () {
  let activeSheet = spreadsheet.value.ej2Instances.getActiveSheet();
  let usedRange = activeSheet.usedRange;
  for (let i = 0; i <= usedRange.rowIndex; i++) {
    // Get the filtered row using isFiltered property.
    let filteredRow = (activeSheet.rows[i]).isFiltered;
    if (!filteredRow) {
      let rowData = spreadsheet.value.getRowData(i);
      console.log("Row:", i + 1, "Cells", rowData);
    }
  }
}
</script>
<style>
@import '../node_modules/@syncfusion/ej2-base/styles/material.css';
@import '../node_modules/@syncfusion/ej2-buttons/styles/material.css';
@import '../node_modules/@syncfusion/ej2-dropdowns/styles/material.css';
@import '../node_modules/@syncfusion/ej2-inputs/styles/material.css';
@import '../node_modules/@syncfusion/ej2-navigations/styles/material.css';
@import '../node_modules/@syncfusion/ej2-popups/styles/material.css';
@import '../node_modules/@syncfusion/ej2-splitbuttons/styles/material.css';
@import '../node_modules/@syncfusion/ej2-grids/styles/material.css';
@import "../node_modules/@syncfusion/ej2-vue-spreadsheet/styles/material.css";

.custom-btn {
  margin: 0 0 10px 0;
}
</style>
<template>
  <div>
    <ejs-button class="e-btn custom-btn" @click="getFilterData">Get Filtered Data</ejs-button>
    <ejs-spreadsheet ref="spreadsheet" :created="created">
      <e-sheets>
        <e-sheet>
          <e-ranges>
            <e-range :dataSource="dataSource"></e-range>
          </e-ranges>
          <e-columns>
            <e-column :width="width1"></e-column>
            <e-column :width="width2"></e-column>
            <e-column :width="width2"></e-column>
            <e-column :width="width1"></e-column>
            <e-column :width="width2"></e-column>
            <e-column :width="width3"></e-column>
          </e-columns>
        </e-sheet>
      </e-sheets></ejs-spreadsheet>
  </div>
</template>

<script>
import { SpreadsheetComponent, ColumnsDirective, ColumnDirective, RangesDirective, RangeDirective, SheetsDirective, SheetDirective } from "@syncfusion/ej2-vue-spreadsheet";
import { ButtonComponent } from "@syncfusion/ej2-vue-buttons";
import { defaultData } from './data.js';

export default {
  name: "App",
  components: {
    "ejs-button": ButtonComponent,
    "ejs-spreadsheet": SpreadsheetComponent,
    "e-sheets": SheetsDirective,
    "e-sheet": SheetDirective,
    "e-ranges": RangesDirective,
    "e-range": RangeDirective,
    "e-columns": ColumnsDirective,
    "e-column": ColumnDirective
  },
  data: () => {
    return {
      dataSource: defaultData,
      width1: 180,
      width2: 130,
      width3: 120,
    }
  },
  methods: {
    created: function () {
      let spreadsheet = this.$refs.spreadsheet;
      spreadsheet.cellFormat({ fontWeight: 'bold', textAlign: 'center', verticalAlign: 'middle' }, 'A1:F1');
      let predicates = [{
        field: 'C',
        operator: 'equal',
        value: 'Pink',
        matchCase: false
      }];
      spreadsheet.applyFilter(predicates, 'A1:C7');
    },
    getFilterData: function () {
      let spreadsheet = this.$refs.spreadsheet;
      let activeSheet = spreadsheet.ej2Instances.getActiveSheet();
      let usedRange = activeSheet.usedRange;
      for (let i = 0; i <= usedRange.rowIndex; i++) {
        // Get the filtered row using isFiltered property.
        let filteredRow = (activeSheet.rows[i]).isFiltered;
        if (!filteredRow) {
          let rowData = spreadsheet.getRowData(i);
          console.log("Row:", i + 1, "Cells", rowData);
        }
      }
    },
  }
}
</script>
<style>
@import '../node_modules/@syncfusion/ej2-base/styles/material.css';
@import '../node_modules/@syncfusion/ej2-buttons/styles/material.css';
@import '../node_modules/@syncfusion/ej2-dropdowns/styles/material.css';
@import '../node_modules/@syncfusion/ej2-inputs/styles/material.css';
@import '../node_modules/@syncfusion/ej2-navigations/styles/material.css';
@import '../node_modules/@syncfusion/ej2-popups/styles/material.css';
@import '../node_modules/@syncfusion/ej2-splitbuttons/styles/material.css';
@import '../node_modules/@syncfusion/ej2-grids/styles/material.css';
@import "../node_modules/@syncfusion/ej2-vue-spreadsheet/styles/material.css";

.custom-btn {
  margin: 0 0 10px 0;
}
</style>

Limitations

The following features have some limitations in Filter:

  • Insert/delete row/column between the filter applied cells.
  • Merge cells with filter.
  • Copy/cut paste the filter applied cells.

Note

You can refer to our Vue Spreadsheet feature tour page for its groundbreaking feature representations. You can also explore our Vue Spreadsheet example to knows how to present and manipulate data.

See Also