Excel-like Filtering in React Gantt Chart Component
18 Nov 201824 minutes to read
The Excel-like filter in React Gantt Chart component enables column-level filtering similar to Microsoft Excel. It supports sorting, clearing filters, and applying advanced conditions through a submenu available in each column header. This feature is highly effective for working with large datasets and applying multiple filter criteria.
To enable this feature, configure filterSettings.type as Excel and set allowFiltering to true.
import * as React from 'react';
import * as ReactDOM from 'react-dom';
import { GanttComponent, Inject, Filter } from '@syncfusion/ej2-react-gantt';
import { data } from './datasource';
function App() {
const taskFields = {
id: 'TaskID',
name: 'TaskName',
startDate: 'StartDate',
duration: 'Duration',
progress: 'Progress',
parentID: 'ParentID'
};
const FilterOptions = {
type: 'Excel'
};
return <GanttComponent dataSource={data} taskFields={taskFields} allowFiltering={true} height='450px' filterSettings={FilterOptions}>
<Inject services={[Filter]} />
</GanttComponent>
};
ReactDOM.render(<App />, document.getElementById('root'));import * as React from 'react';
import * as ReactDOM from 'react-dom';
import { GanttComponent, Inject, Filter, TaskFieldsModel } from '@syncfusion/ej2-react-gantt';
import { data } from './datasource';
function App() {
const taskFields: TaskFieldsModel = {
id: 'TaskID',
name: 'TaskName',
startDate: 'StartDate',
duration: 'Duration',
progress: 'Progress',
parentID: 'ParentID'
};
const FilterOptions = {
type: 'Excel'
};
return <GanttComponent dataSource={data} taskFields={taskFields} allowFiltering={true} height='450px' filterSettings={FilterOptions}>
<Inject services={[Filter]} />
</GanttComponent>
};
ReactDOM.render(<App />, document.getElementById('root'));<!DOCTYPE html>
<html lang="en">
<head>
<title>Syncfusion React Gantt</title>
<meta charset="utf-8" />
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<meta name="description" content="Essential JS 2 for React Components" />
<meta name="author" content="Syncfusion" />
<link href="https://cdn.syncfusion.com/ej2/34.1.29/tailwind3.css" rel="stylesheet" type="text/css"/>
<link href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" rel="stylesheet" />
<script src="https://cdnjs.cloudflare.com/ajax/libs/systemjs/0.19.38/system.js"></script>
<script src="systemjs.config.js"></script>
<style>
#loader {
color: #008cff;
height: 40px;
left: 45%;
position: absolute;
top: 45%;
width: 30%;
}
</style>
</head>
<body>
<div id='root'>
<div id='loader'>Loading....</div>
</div>
</body>
</html>Show customized text in checkbox list data
To customize the text displayed in a checkbox list, use filterItemTemplate and assign it to the desired column. This allows rendering custom content for each item in the filter list.
import * as React from 'react';
import * as ReactDOM from 'react-dom';
import { GanttComponent, ColumnsDirective, ColumnDirective, Inject, Filter, Sort } from '@syncfusion/ej2-react-gantt';
import { data } from './datasource';
function App() {
const taskSettings = {
id: 'TaskID',
name: 'TaskName',
startDate: 'StartDate',
endDate: 'EndDate',
duration: 'Duration',
progress: 'Progress',
parentID: 'ParentID'
};
const filterSettings = { type: 'Excel' };
const splitterSettings = { columnIndex: 3 };
const customFilter = (props) => {
return props.IsCompleted ? 'Completed' : 'In Progress';
}
const columnFilterSettings = {
type: 'Excel',
itemTemplate: customFilter
}
return (
<GanttComponent
height="450px"
width="700px"
dataSource={data}
taskFields={taskSettings}
allowFiltering={true}
filterSettings={filterSettings}
splitterSettings={splitterSettings}
>
<ColumnsDirective>
<ColumnDirective field="TaskID" headerText="Task ID" width="100" isPrimaryKey={true} />
<ColumnDirective field="TaskName" headerText="Task Name" width="150" />
<ColumnDirective field="StartDate" headerText="Start Date" width="150" />
<ColumnDirective field="EndDate" headerText="End Date" width="150" />
<ColumnDirective field="Duration" headerText="Duration" width="150" />
<ColumnDirective field="Progress" headerText="Progress" width="150" />
<ColumnDirective field="IsCompleted" headerText="Status" width="150" displayAsCheckBox={true} filter={columnFilterSettings} type="boolean" />
</ColumnsDirective>
<Inject services={[Filter, Sort]} />
</GanttComponent>
);
}
ReactDOM.render(<App />, document.getElementById('root'));import * as React from 'react';
import * as ReactDOM from 'react-dom';
import { GanttComponent, ColumnsDirective, ColumnDirective, Inject, Filter, Sort, FilterSettingsModel, TaskFieldsModel } from '@syncfusion/ej2-react-gantt';
import { data } from './datasource';
function App() {
const taskSettings: TaskFieldsModel = {
id: 'TaskID',
name: 'TaskName',
startDate: 'StartDate',
endDate: 'EndDate',
duration: 'Duration',
progress: 'Progress',
parentID: 'ParentID'
};
const filterSettings: FilterSettingsModel = { type: 'Excel' };
const splitterSettings = { columnIndex: 3 };
const customFilter = (props: any) => {
return props.IsCompleted ? 'Completed' : 'In Progress';
}
const columnFilterSettings = {
type: 'Excel',
itemTemplate: customFilter
}
return (
<GanttComponent
height="450px"
width="700px"
dataSource={data}
taskFields={taskSettings}
allowFiltering={true}
filterSettings={filterSettings}
splitterSettings={splitterSettings}
>
<ColumnsDirective>
<ColumnDirective field="TaskID" headerText="Task ID" width="100" isPrimaryKey={true} />
<ColumnDirective field="TaskName" headerText="Task Name" width="150" />
<ColumnDirective field="StartDate" headerText="Start Date" width="150" />
<ColumnDirective field="EndDate" headerText="End Date" width="150" />
<ColumnDirective field="Duration" headerText="Duration" width="150" />
<ColumnDirective field="Progress" headerText="Progress" width="150" />
<ColumnDirective field="IsCompleted" headerText="Status" width="150" displayAsCheckBox={true} filter={columnFilterSettings} type="boolean" />
</ColumnsDirective>
<Inject services={[Filter, Sort]} />
</GanttComponent>
);
}
ReactDOM.render(<App />, document.getElementById('root'));<!DOCTYPE html>
<html lang="en">
<head>
<title>Syncfusion React Gantt</title>
<meta charset="utf-8" />
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<meta name="description" content="Essential JS 2 for React Components" />
<meta name="author" content="Syncfusion" />
<link href="https://cdn.syncfusion.com/ej2/34.1.29/tailwind3.css" rel="stylesheet" type="text/css"/>
<link href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" rel="stylesheet" />
<script src="https://cdnjs.cloudflare.com/ajax/libs/systemjs/0.19.38/system.js"></script>
<script src="systemjs.config.js"></script>
<style>
#loader {
color: #008cff;
height: 40px;
left: 45%;
position: absolute;
top: 45%;
width: 30%;
}
</style>
</head>
<body>
<div id='root'>
<div id='loader'>Loading....</div>
</div>
</body>
</html>Hide Excel filter dialog elements
You can hide elements such as the context menu, search box, sorting options, and checkbox list in the Gantt Excel filter dialog using the following CSS:
.e-gantt .e-excelfilter .e-contextmenu-wrapper {
display: none;
}If you want to hide only the built-in sorting options (ascending, descending, and separator), apply the following CSS:
.e-excel-ascending,
.e-excel-descending,
.e-separator.e-excel-separator {
display: none;
}import * as React from 'react';
import * as ReactDOM from 'react-dom';
import { GanttComponent, Inject, Filter, Sort } from '@syncfusion/ej2-react-gantt';
import { data } from './datasource';
function App() {
const taskSettings = {
id: 'TaskID',
name: 'TaskName',
startDate: 'StartDate',
duration: 'Duration',
progress: 'Progress',
parentID: 'ParentID'
};
const columns = [
{ field: 'TaskID', headerText: 'Task ID', textAlign: 'Left', width: '120' },
{ field: 'TaskName', headerText: 'Task Name', width: '250' },
{ field: 'StartDate', headerText: 'Start Date', width: '150' },
{ field: 'Duration', headerText: 'Duration', width: '150' },
{ field: 'Progress', headerText: 'Progress', width: '150' }
];
const splitterSettings = {
columnIndex: 3
};
const filterSettings = {
type: 'Excel'
};
return (
<GanttComponent
height="370px"
allowFiltering={true}
allowSorting={true}
dataSource={data}
taskFields={taskSettings}
columns={columns}
splitterSettings={splitterSettings}
filterSettings={filterSettings}
>
<Inject services={[Filter, Sort]} />
</GanttComponent>
);
}
ReactDOM.render(<App />, document.getElementById('root'));import * as React from 'react';
import * as ReactDOM from 'react-dom';
import { GanttComponent, Inject, Filter, Sort } from '@syncfusion/ej2-react-gantt';
import { data } from './datasource';
import { TaskFieldsModel, ColumnModel, SplitterSettingsModel, FilterSettingsModel } from '@syncfusion/ej2-react-gantt';
function App() {
const taskSettings: TaskFieldsModel = {
id: 'TaskID',
name: 'TaskName',
startDate: 'StartDate',
duration: 'Duration',
progress: 'Progress',
parentID: 'ParentID'
};
const columns: ColumnModel[] = [
{ field: 'TaskID', headerText: 'Task ID', textAlign: 'Left', width: '120' },
{ field: 'TaskName', headerText: 'Task Name', width: '250' },
{ field: 'StartDate', headerText: 'Start Date', width: '150' },
{ field: 'Duration', headerText: 'Duration', width: '150' },
{ field: 'Progress', headerText: 'Progress', width: '150' }
];
const splitterSettings: SplitterSettingsModel = {
columnIndex: 3
};
const filterSettings: FilterSettingsModel = {
type: 'Excel'
};
return (
<GanttComponent
height="370px"
allowFiltering={true}
allowSorting={true}
dataSource={data}
taskFields={taskSettings}
columns={columns}
splitterSettings={splitterSettings}
filterSettings={filterSettings}
>
<Inject services={[Filter, Sort]} />
</GanttComponent>
);
}
ReactDOM.render(<App />, document.getElementById('root'));<!DOCTYPE html>
<html lang="en">
<head>
<title>Syncfusion React Gantt</title>
<meta charset="utf-8" />
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<meta name="description" content="Essential JS 2 for React Components" />
<meta name="author" content="Syncfusion" />
<link href="https://cdn.syncfusion.com/ej2/34.1.29/tailwind3.css" rel="stylesheet" type="text/css" />
<link href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" rel="stylesheet" />
<script src="https://cdnjs.cloudflare.com/ajax/libs/systemjs/0.19.38/system.js"></script>
<script src="systemjs.config.js"></script>
<style>
#loader {
color: #008cff;
height: 40px;
left: 45%;
position: absolute;
top: 45%;
width: 30%;
}
.e-gantt .e-excelfilter .e-contextmenu-wrapper {
display: none;
}
</style>
</head>
<body>
<div id='root'>
<div id='loader'>Loading....</div>
</div>
</body>
</html>Customize filter choice count
The React Gantt Chart component displays up to 1000 distinct values per column in the filter dialog by default. These values are taken from the first 1000 records bound to the component and shown as checkbox list items to maintain optimal performance. Additional values can be accessed using the search option within the filter dialog.
To customize this behavior, the filterChoiceCount property can be adjusted to increase or decrease the number of distinct values displayed, depending on the dataset size and filtering requirements.
import * as React from 'react';
import * as ReactDOM from 'react-dom';
import { GanttComponent, Inject, Filter, ColumnsDirective, ColumnDirective } from '@syncfusion/ej2-react-gantt';
import { data } from './datasource';
function App() {
const taskSettings = {
id: 'TaskID',
name: 'TaskName',
startDate: 'StartDate',
endDate: 'EndDate',
duration: 'Duration',
progress: 'Progress',
parentID: 'ParentID',
dependency: 'Predecessor'
};
const splitterSettings = {
columnIndex: 3
};
const filterSettings = {
type: 'Excel'
};
function actionBegin(args) {
if (args.requestType === "filterchoicerequest" || args.requestType === "filtersearchbegin") {
args.filterChoiceCount = 1000;
}
}
return (
<GanttComponent
height="370px"
allowFiltering={true}
dataSource={data}
taskFields={taskSettings}
splitterSettings={splitterSettings}
filterSettings={filterSettings}
actionBegin={actionBegin}
>
<ColumnsDirective>
<ColumnDirective field="TaskID" headerText="Task ID" width="120" />
<ColumnDirective field="TaskName" headerText="Project Activity" width="250" clipMode="EllipsisWithTooltip" />
<ColumnDirective field="StartDate" headerText="Planned Start Date" width={200} />
<ColumnDirective field="Duration" headerText="Duration" width={160} />
<ColumnDirective field="Progress" headerText="Completion (%)" width={200} />
</ColumnsDirective>
<Inject services={[Filter]} />
</GanttComponent>
);
}
ReactDOM.render(<App />, document.getElementById('root'));import * as React from 'react';
import * as ReactDOM from 'react-dom';
import { GanttComponent, Inject, Filter, ColumnsDirective, ColumnDirective } from '@syncfusion/ej2-react-gantt';
import { TaskFieldsModel, SplitterSettingsModel, FilterSettingsModel, ActionBeginArgs } from '@syncfusion/ej2-react-gantt';
import { data } from './datasource';
function App() {
const taskSettings: TaskFieldsModel = {
id: 'TaskID',
name: 'TaskName',
startDate: 'StartDate',
endDate: 'EndDate',
duration: 'Duration',
progress: 'Progress',
parentID: 'ParentID',
dependency: 'Predecessor'
};
const splitterSettings: SplitterSettingsModel = {
columnIndex: 3
};
const filterSettings: FilterSettingsModel = {
type: 'Excel'
};
function actionBegin(args: ActionBeginArgs): void {
if (args.requestType === "filterchoicerequest" || args.requestType === "filtersearchbegin") {
(args as any).filterChoiceCount = 1000;
}
}
return (
<GanttComponent
height="370px"
allowFiltering={true}
dataSource={data}
taskFields={taskSettings}
splitterSettings={splitterSettings}
filterSettings={filterSettings}
actionBegin={actionBegin}
>
<ColumnsDirective>
<ColumnDirective field="TaskID" headerText="Task ID" width="120" />
<ColumnDirective field="TaskName" headerText="Project Activity" width="250" clipMode="EllipsisWithTooltip" />
<ColumnDirective field="StartDate" headerText="Planned Start Date" width={200} />
<ColumnDirective field="Duration" headerText="Duration" width={160} />
<ColumnDirective field="Progress" headerText="Completion (%)" width={200} />
</ColumnsDirective>
<Inject services={[Filter]} />
</GanttComponent>
);
}
ReactDOM.render(<App />, document.getElementById('root'));<!DOCTYPE html>
<html lang="en">
<head>
<title>Syncfusion React Gantt</title>
<meta charset="utf-8" />
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<meta name="description" content="Essential JS 2 for React Components" />
<meta name="author" content="Syncfusion" />
<link href="https://cdn.syncfusion.com/ej2/34.1.29/tailwind3.css" rel="stylesheet" type="text/css" />
<link href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" rel="stylesheet" />
<script src="https://cdnjs.cloudflare.com/ajax/libs/systemjs/0.19.38/system.js"></script>
<script src="systemjs.config.js"></script>
<style>
#loader {
color: #008cff;
height: 40px;
left: 45%;
position: absolute;
top: 45%;
width: 30%;
}
.e-gantt .e-excelfilter .e-contextmenu-wrapper {
display: none;
}
</style>
</head>
<body>
<div id='root'>
<div id='loader'>Loading....</div>
</div>
</body>
</html>Bind custom remote data source for Excel-like filtering
You can dynamically bind a custom remote data source to the Excel filter in the Gantt Chart component by using a DataManager with WebApiAdaptor. This can be done by assigning the data source directly or storing fetched data in a global variable. Then, bind it to the filter module’s dataSource within the actionBegin event when requestType is filterBeforeOpen.
import * as React from 'react';
import * as ReactDOM from 'react-dom';
import { GanttComponent, ColumnsDirective, ColumnDirective, Inject, Filter } from '@syncfusion/ej2-react-gantt';
import { DataManager, WebApiAdaptor } from '@syncfusion/ej2-data';
function App() {
const localData = [
{ TaskID: 1, TaskName: 'Project Initiation', StartDate: new Date('04/02/2019'), EndDate: new Date('04/21/2019') },
{ TaskID: 2, TaskName: 'Identify Site location', StartDate: new Date('04/02/2019'), Duration: 4, ParentID: 1, Progress: 50 },
{ TaskID: 3, TaskName: 'Perform Soil test', StartDate: new Date('04/02/2019'), Duration: 4, ParentID: 1, Progress: 50 },
{ TaskID: 4, TaskName: 'Soil test approval', StartDate: new Date('04/02/2019'), Duration: 4, ParentID: 1, Progress: 50 },
{ TaskID: 5, TaskName: 'Project Estimation', StartDate: new Date('04/02/2019'), EndDate: new Date('04/21/2019') },
{ TaskID: 6, TaskName: 'Develop floor plan for estimation', StartDate: new Date('04/04/2019'), Duration: 3, ParentID: 5, Progress: 50 },
{ TaskID: 7, TaskName: 'List materials', StartDate: new Date('04/04/2019'), Duration: 3, ParentID: 5, Progress: 50 },
{ TaskID: 8, TaskName: 'Estimation approval', StartDate: new Date('04/04/2019'), Duration: 3, ParentID: 5, Progress: 50 }
];
const taskSettings = {
id: 'TaskID',
name: 'TaskName',
startDate: 'StartDate',
endDate: 'EndDate',
duration: 'Duration',
progress: 'Progress',
parentID: 'ParentID',
dependency: 'Predecessor'
};
const splitterSettings = {
columnIndex: 3
};
const filterSettings = {
type: 'Excel'
};
function actionBegin(args) {
if (args.requestType === 'filterBeforeOpen') {
const hostUrl = 'https://ej2services.syncfusion.com/angular/development/api/GanttWebApiRemoteData';
args.filterModel.options.dataSource.json = new DataManager({
url: hostUrl,
adaptor: new WebApiAdaptor(),
crossDomain: true
});
}
}
return (
<GanttComponent
height="370px"
allowFiltering={true}
dataSource={localData}
taskFields={taskSettings}
splitterSettings={splitterSettings}
filterSettings={filterSettings}
actionBegin={actionBegin}
>
<ColumnsDirective>
<ColumnDirective field="TaskID" headerText="Task ID" width="120" />
<ColumnDirective field="TaskName" headerText="Project Activity" width="250" clipMode="EllipsisWithTooltip" />
<ColumnDirective field="StartDate" headerText="Planned Start Date" width={200} />
<ColumnDirective field="Duration" headerText="Duration" width={160} />
<ColumnDirective field="Progress" headerText="Completion (%)" width={200} />
</ColumnsDirective>
<Inject services={[Filter]} />
</GanttComponent>
);
}
ReactDOM.render(<App />, document.getElementById('root'));import * as React from 'react';
import * as ReactDOM from 'react-dom';
import { GanttComponent, ColumnsDirective, ColumnDirective, Inject, Filter } from '@syncfusion/ej2-react-gantt';
import { TaskFieldsModel, SplitterSettingsModel, FilterSettingsModel } from '@syncfusion/ej2-react-gantt';
import { DataManager, WebApiAdaptor } from '@syncfusion/ej2-data';
function App() {
const localData: object[] = [
{ TaskID: 1, TaskName: 'Project Initiation', StartDate: new Date('04/02/2019'), EndDate: new Date('04/21/2019') },
{ TaskID: 2, TaskName: 'Identify Site location', StartDate: new Date('04/02/2019'), Duration: 4, ParentID: 1, Progress: 50 },
{ TaskID: 3, TaskName: 'Perform Soil test', StartDate: new Date('04/02/2019'), Duration: 4, ParentID: 1, Progress: 50 },
{ TaskID: 4, TaskName: 'Soil test approval', StartDate: new Date('04/02/2019'), Duration: 4, ParentID: 1, Progress: 50 },
{ TaskID: 5, TaskName: 'Project Estimation', StartDate: new Date('04/02/2019'), EndDate: new Date('04/21/2019') },
{ TaskID: 6, TaskName: 'Develop floor plan for estimation', StartDate: new Date('04/04/2019'), Duration: 3, ParentID: 5, Progress: 50 },
{ TaskID: 7, TaskName: 'List materials', StartDate: new Date('04/04/2019'), Duration: 3, ParentID: 5, Progress: 50 },
{ TaskID: 8, TaskName: 'Estimation approval', StartDate: new Date('04/04/2019'), Duration: 3, ParentID: 5, Progress: 50 }
];
const taskSettings: TaskFieldsModel = {
id: 'TaskID',
name: 'TaskName',
startDate: 'StartDate',
endDate: 'EndDate',
duration: 'Duration',
progress: 'Progress',
parentID: 'ParentID',
dependency: 'Predecessor'
};
const splitterSettings: SplitterSettingsModel = {
columnIndex: 3
};
const filterSettings: FilterSettingsModel = {
type: 'Excel'
};
function actionBegin(args: any): void {
if (args.requestType === 'filterBeforeOpen') {
const hostUrl = 'https://ej2services.syncfusion.com/angular/development/api/GanttWebApiRemoteData';
args.filterModel.options.dataSource.json = new DataManager({
url: hostUrl,
adaptor: new WebApiAdaptor(),
crossDomain: true
});
}
}
return (
<GanttComponent
height="370px"
allowFiltering={true}
dataSource={localData}
taskFields={taskSettings}
splitterSettings={splitterSettings}
filterSettings={filterSettings}
actionBegin={actionBegin}
>
<ColumnsDirective>
<ColumnDirective field="TaskID" headerText="Task ID" width="120" />
<ColumnDirective field="TaskName" headerText="Project Activity" width="250" clipMode="EllipsisWithTooltip" />
<ColumnDirective field="StartDate" headerText="Planned Start Date" width={200} />
<ColumnDirective field="Duration" headerText="Duration" width={160} />
<ColumnDirective field="Progress" headerText="Completion (%)" width={200} />
</ColumnsDirective>
<Inject services={[Filter]} />
</GanttComponent>
);
}
ReactDOM.render(<App />, document.getElementById('root'));<!DOCTYPE html>
<html lang="en">
<head>
<title>Syncfusion React Gantt</title>
<meta charset="utf-8" />
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<meta name="description" content="Essential JS 2 for React Components" />
<meta name="author" content="Syncfusion" />
<link href="https://cdn.syncfusion.com/ej2/34.1.29/tailwind3.css" rel="stylesheet" type="text/css" />
<link href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" rel="stylesheet" />
<script src="https://cdnjs.cloudflare.com/ajax/libs/systemjs/0.19.38/system.js"></script>
<script src="systemjs.config.js"></script>
<style>
#loader {
color: #008cff;
height: 40px;
left: 45%;
position: absolute;
top: 45%;
width: 30%;
}
.e-gantt .e-excelfilter .e-contextmenu-wrapper {
display: none;
}
</style>
</head>
<body>
<div id='root'>
<div id='loader'>Loading....</div>
</div>
</body>
</html>