Excel like filter in Vue Grid component

20 Jan 20247 minutes to read

You can enable Excel like filter by defining. filterSettings.type as Excel.The excel menu contains an option such as Sorting, Clear filter, Sub menu for advanced filtering.

<template>
  <div id="app">
    <ejs-grid ref="Grid" :dataSource="data" :allowFiltering="true" :filterSettings="filterOptions" height="273px">
      <e-columns>
        <e-column field="OrderID" headerText="ID" width="80" isPrimaryKey="true"></e-column>
        <e-column field="CustomerID" headerText="CustomerID" width="90"></e-column>
        <e-column field="OrderDate" headerText="OrderDate" width="120" format="yMd"><e-column>
        <e-column field="ShipName" headerText="ShipName" width="120"></e-column>
        <e-column field="ShipCity" headerText="ShipCity" width="120"></e-column>
      </e-columns>
    </ejs-grid>
  </div>
</template>
<script>
import Vue from "vue";
import { GridPlugin, Filter } from "@syncfusion/ej2-vue-grids";
import { data } from './datasource.js'
Vue.use(GridPlugin);

export default {
  data() {
    return {
      data: data,
     filterOptions: { type: "Excel" },
    };
  },
  methods: {
  },
  provide: {
    grid: [Filter],
  },
};
</script>

<style>
@import "https://cdn.syncfusion.com/ej2/material.css";
</style>

  • By default, while opening the excel/checkbox filter in the Grid, the filter dialog will get and display the distinct data from the first 1000 records bound to the Grid to optimize performance. The remaining records will be returned as a result of the search option of the filter dialog.
  • However, we can increase the excel/checkbox filter count by modifying the filterChoiceCount argument value(as per our need) in the actionBegin event when the requestType is filterchoicerequest or filtersearchbegin. This is demonstrated in the below code snippet,
  methods: {
    actionBegin(args) {
      if (args.requestType === "filterchoicerequest" || args.requestType ==="filtersearchbegin" ) {
        args.filterChoiceCount = 3000;
      }
    },
  },

Render checkbox list data in on-demand for excel/checkbox filtering

The Excel/Checkbox filter type of Grid has a restriction where only the first 1000 unique sorted items are accessible to view in the filter dialog checkbox list content by scrolling. This limitation is in place to avoid any rendering delays when opening the filter dialog. However, the searching and filtering processes consider all unique items in that particular column.

The Excel/Checkbox filter in the Grid provides an option to load large data sets on-demand during scrolling to improve scrolling limitation functionality. This is achieved by setting the filterSettings.enableInfiniteScrolling property to true. This feature proves especially beneficial for managing extensive datasets, enhancing data loading performance in the checkbox list, and allowing interactive checkbox selection with persistence for the selection based on filtering criteria.

The Excel/Checkbox filter retrieves distinct data in ascending order, governed by its filterSettings.itemsCount property, with a default value of 50. As the checkbox list data scroller reaches its end, the next dataset is fetched and displayed, with the notable advantage that this process only requests new checkbox list data without redundantly fetching the existing loaded dataset.

Customize the items count for initial rendering

Based on the items count value, the Excel/Checkbox filter gets unique data and displayed in Excel/Checkbox filter content dialog. You can customize the count of on-demand data rendering for Excel/Checkbox filter by adjusting the filterSettings.itemsCount property. The default value is 50.

grid.filterSettings = { enableInfiniteScrolling = true, itemsCount = 40 };

It is recommended to keep the itemsCount below 300. Higher values will result in unwanted whitespace because of DOM maintenance performance degradation.

Customize the loading animation effect

A loading effect is presented to signify that loading is in progress when the checkbox list data scroller reaches the end, and there is a delay in receiving the data response from the server. The loading effect during on-demand data retrieval for Excel/Checkbox filter can be customized using the filterSettings.loadingIndicator property. The default value is Shimmer.

grid.filterSettings = { enableInfiniteScrolling = true, loadingIndicator = 'Spinner' };

In the provided example, On-Demand Excel filter has been enabled for the Vue Grid

<template>
  <div id="app">
    <ejs-grid ref='grid' :dataSource="getTradeData" :query="query" :allowPaging='true' :allowFiltering='true'
      :allowSorting='true' :pageSettings='pageSettings' :filterSettings='filterSettings'>
      <e-columns>
        <e-column field='EmployeeID' headerText='Employee ID' width='120' textAlign='Right'></e-column>
        <e-column field='Employees' headerText='Employee Name' width='150'></e-column>
        <e-column field='Designation' headerText='Designation' width='130' textAlign='Right'></e-column>
        <e-column field='CurrentSalary' headerText='CurrentSalary' width='120' format='C2' textAlign='Right'></e-column>
      </e-columns>
    </ejs-grid>
  </div>
</template>
<script>
import Vue from "vue";
import { GridPlugin, Filter, Page, Sort  } from "@syncfusion/ej2-vue-grids";
import { DataManager, Query, UrlAdaptor } from "@syncfusion/ej2-data";
Vue.use(GridPlugin);

export default {
  data() {
    return {
      pageSettings: { pageCount: 5 },
      filterSettings: { type: "Excel", enableInfiniteScrolling: true },
    };
  },
  methods: {
  },
  computed: {
    getTradeData: function () {
        let SERVICE_URI = "https://services.syncfusion.com/vue/production/";
        let getTradeData = new DataManager({
            url: SERVICE_URI + 'api/UrlDataSource',
            adaptor: new UrlAdaptor()
        });
        return getTradeData;
    },
    query: function () {
        let query = new Query().addParams('dataCount', '10000');
        return query;
    }
  },
  provide: {
    grid: [Filter, Page, Sort],
  },
};
</script>

See also