Excel Filtering in Angular Grid

The grid Excel filtering provides an Excel like filtering UI for any Angular table like the Grid.

Angular Grid Excel Style Filtering Example

Usage

To turn on the grid excel filtering, two inputs should be set. The allowFiltering should be set to true and the filterMode should be set to excelStyleFilter.

<igx-grid [data]="data" [autoGenerate]="true" [allowFiltering]="true" [filterMode]="'excelStyleFilter'">
</igx-grid>

Interactions

In order to open the filter menu for a particular column, the Angular 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 FilteringLogic.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.

Configure Menu Features

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

<igx-grid #grid1 [data]="data" [autoGenerate]="false" height="650px" width="100%" [moving]="true" [allowFiltering]="true" [filterMode]="'excelStyleFilter'">
    <igx-column field="ProductName" header="Product Name" [sortable]="true" [dataType]="'string'">
    </igx-column>
    <igx-column field="QuantityPerUnit" header="Quantity Per Unit" [sortable]="false" [disablePinning]="true" [disableHiding]="true" [dataType]="'string'">
    </igx-column>
    <igx-column field="UnitPrice" header="Unit Price" [disablePinning]="true" [disableHiding]="true" [sortable]="true" [dataType]="'number'">
    </igx-column>
    <igx-column field="OrderDate" header="Order Date" [sortable]="false"  [dataType]="'date'" [formatter]="formatDate">
    </igx-column>
    <igx-column field="Discontinued" header="Discontinued" [sortable]="true" [dataType]="'boolean'">
    </igx-column>
</igx-grid>

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 without disabling the column features you could use custom templates. The Excel Style filter menu provides two directives for templating:

• IgxExcelStyleColumnOperationsTemplateDirective - re-templates the area with all column operations like sorting, pinning, etc.
• IgxExcelStyleFilterOperationsTemplateDirective - re-templates the area with all filter specific operations.

You could either re-template only one of those areas or both of them. You could put any custom content inside those directives or you could use any of our built-in Excel style filtering components.

The following code demonstrates how to define a custom Excel style filter menu using the ExcelStyleHeader, ExcelStyleSorting and ExcelStyleSearch components.

<igx-grid #grid1 [data]="data" [autoGenerate]="false" height="650px" width="100%" [allowFiltering]="true" [filterMode]="'excelStyleFilter'">

    <igx-grid-excel-style-filtering [minHeight]="'380px'" [maxHeight]="'500px'">
        <igx-excel-style-column-operations>
            <igx-excel-style-header
                [showPinning]="true"
                [showHiding]="true"
            >
            </igx-excel-style-header>

            <igx-excel-style-sorting></igx-excel-style-sorting>
        </igx-excel-style-column-operations>

        <igx-excel-style-filter-operations>
            <igx-excel-style-search></igx-excel-style-search>
        </igx-excel-style-filter-operations>
    </igx-grid-excel-style-filtering>
    
    ...
</igx-grid>

You could also re-template the Excel style filtering icon in the column header using the igxExcelStyleHeaderIcon directive:

<igx-grid ...>
    <ng-template igxExcelStyleHeaderIcon>
        <igx-icon>filter_alt</igx-icon>
    </ng-template>
</igx-grid>

Here is the full list of Excel style filtering components that you could use:

• ExcelStyleHeader
• ExcelStyleSorting
• ExcelStyleMoving
• ExcelStylePinning
• ExcelStyleHiding
• ExcelStyleSelecting
• ExcelStyleClearFilters
• ExcelStyleConditionalFilter
• ExcelStyleSearch

Unique Column Values Strategy

The list items inside the Excel Style Filtering dialog represent the unique values for the respective column.

These values can be provided manually and loaded on demand, which is demonstrated in the Grid Remote Data Operations topic.

Formatted Values Filtering Strategy

By default, the Grid component filters the data based on the original cell values, however in some cases you may want to filter the data based on the formatted values. In order to do that you can use the FormattedValuesFilteringStrategy.

The following sample demonstrates how to format the numeric values of a column as strings and filter the Grid based on the string values:

External Excel Style filtering

As you see at the demos above the default appearance of the Excel Style filtering dialog is inside the Grid. So this dialog is only visible when configuring the filters. There is a way to make that dialog stay always visible - it can be used outside of the grid as a standalone component. In the demo below, the Excel style filtering is declared separately of the Grid.

Demo

Usage

In order to configure the Excel style filtering component, you should set its column property to one of the Grid’s columns. In the sample above, we have bound the column property to the value of an IgxSelectComponent that displays the Grid’s columns.

<igx-select #gridColums value="ProductID">
   <label igxLabel>Columns:</label>
   <igx-select-item *ngFor="let c of grid1.columns" [value]="c.field">
       {{ c.field }}
   </igx-select-item>
</igx-select>

<igx-grid-excel-style-filtering [column]="grid1.getColumnByName(gridColums.value)">
</igx-grid-excel-style-filtering>

External Outlet

The Grid’s z-index creates separate stacking context for each grid in the DOM. This ensures that all descendant elements of the grid will render as intended, without overlapping one another. However, elements that go outside of the grid (e.g. Excel Style filter) will conflict with outside elements with the same z-index (e.g. having two grids one under another) resulting in false rendering. The solution for this issue is to set the outlet property to an external outlet directive which allows the overlay elements to always appear on top.

Demo

Styling

To get started with styling the Excel Style Filtering dialog, we need to import the index file, where all the theme functions and component mixins live:

@use "igniteui-angular/theming" as *;

// IMPORTANT: Prior to Ignite UI for Angular version 13 use:
// @import '~igniteui-angular/lib/core/styles/themes/index';

There are a couple of ways to style the Excel Style Filtering dialog. It can be styled using the grid-theme. The Excel Style Filtering dialog inherits the $background, $foreground, and $accent-color values defined in the grid-theme. It also provides dedicated parameters for customizing the dialog’s text colors.

Alternatively, you can use the dedicated excel-filtering-theme, which allows you to fully style only the Excel Style Filtering dialog.

The simplest approach is to use the grid-theme:

$background: #292826;
$foreground: #eeece1;
$accent: #ffcd0f;

$custom-grid: grid-theme(
  $background: $background,
  $foreground: $foreground,
  $accent-color: $accent,
);

The background and foreground colors of the Excel Style Filtering dialog are inherited from the grid theme. Additionally, all nested components, such as buttons and checkboxes, inherit the accent color from the grid theme.

After that, we are ready to include our newly created grid theme. If we want to make additional style changes specific to the Excel Style Filtering dialog, we can target it directly:

:host {
    @include tokens($custom-grid);

    igx-grid-excel-style-filtering {
        --ig-excel-filtering-background: #444;
    }
}

:host ::ng-deep {
    @include tokens($custom-grid);

    igx-grid-excel-style-filtering {
        --ig-excel-filtering-background: #444;
    }
}

Demo

API References

Additional Resources

• Grid overview

• Paging

• Virtualization and Performance

• Sorting

• Summaries

• Column Moving

• Column Pinning

• Column Resizing

• Selection

Our community is active and always welcoming to new ideas.

• Ignite UI for Angular Forums
• Ignite UI for Angular GitHub