Excel Filtering in React Grid

The React Grid exposes an Excel-style filtering feature that provides an Excel-like filtering UI. It simplifies the process of working with large datasets. The main idea is to help them filter the data that is most relevant, while eliminating irrelevant entries.

React Grid Excel Style Filtering Example

Usage

To turn on the IgrGrid component's Excel-style filtering, two inputs should be set. The allowFiltering should be set to true and the filterMode should be set to ExcelStyleFilter value.

<IgrGrid data={nwindData} autoGenerate="true" allowFiltering="true" filterMode={FilterMode.ExcelStyleFilter}>
</IgrGrid>

Interactions

In order to open the filter menu for a particular column, the React filter icon in the header should be clicked. Additionally, you can use the Ctrl + Shift + L combination on a selected header. If the column can be sorted, pinned, moved, selected or hidden along with the filtering functionality, there will be buttons available for the features that are turned on.

If no filter is applied, all the items in the list will be selected. They can be filtered from the input above the list. In order to filter the data, you can select/deselect the items in the list and either click the Apply button, or press Enter. The filtering applied through the list items creates filter expressions with equals operator and the logic operator between the expressions is OR.

If you type something in the search box and apply the filter, only the items that match the search criteria will be selected. If you want to add items to the currently filtered ones, however, you should select the option Add current selection to filter.

If you want to clear the filter, you can check the Select All option and then click the Apply button.

To apply a filter with different expressions, you can click the Text filter, which will open a sub menu with all available filter operators for the particular column. Selecting one of them will open the custom filter dialog, where you can add as many expressions as you want with different filter and logic operators. There is also a clear button, which can clear the filter.

Sorting, pinning and hiding features can be removed from the filter menu using the corresponding inputs: sortable, selected, disablePinning, disableHiding.

<IgrGrid data={nwindData} autoGenerate="false" allowFiltering="true" filterMode={FilterMode.ExcelStyleFilter}>
    <IgrColumn field="ProductName" header="Product Name" sortable="true" dataType="String">
    </IgrColumn>
    <IgrColumn field="QuantityPerUnit" header="Quantity Per Unit" sortable="false" disable-pinning="true" disable-hiding="true" data-type="String">
    </IgrColumn>
    <IgrColumn field="UnitPrice" header="Unit Price" disable-pinning="true" disable-hiding="true" sortable="true" data-type="Number">
    </IgrColumn>
    <IgrColumn field="OrderDate" header="Order Date" sortable="false"  data-type="Date">
    </IgrColumn>
    <IgrColumn field="Discontinued" header="Discontinued" sortable="true" data-type="Boolean">
    </IgrColumn>      
</IgrGrid>

In the sample below Product Name and Discontinued columns have all four features enabled, Quantity Per Unit have all three disabled, Unit Price has only sorting and Order Date has only pinning and hiding and all are selectable.

Templates

If you want to further customize the Excel style filter menu, you can use the excelStyleHeaderIconTemplate property to define a custom template for the header icon of the menu.

The following code demonstrates how to customize the Excel style filter menu using the excelStyleHeaderIconTemplate:

const webGridFilterAltIconTemplate = ({dataContext: IgrCellTemplateContext}) => {
  return (
    <img 
      height="15px" 
      width="15px" 
      src="http://static.infragistics.com/xplatform/images/grid/propeller-logo.svg" 
      title="Continued" 
      alt="Continued" 
    />
  );
}

<IgrGrid autoGenerate="true" allowFiltering="true" filterMode="excelStyleFilter" 
    excelStyleHeaderIconTemplate={webGridFilterAltIconTemplate}>
</IgrGrid>

Styling

In addition to the predefined themes, the grid could be further customized by setting some of the available CSS properties. In case you would like to change some of the colors, you need to set a class for the grid first:

<IgrGrid className="grid"></IgrGrid>

Then set the related CSS properties to this class:

.grid {
    --ig-grid-filtering-row-background: #ffcd0f;
    --ig-list-item-background: #ffcd0f;
}

Demo

API References

Additional Resources

Our community is active and always welcoming to new ideas.