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 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.

<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 igx-excel-style-header, igx-excel-style-sorting and igx-excel-style-search 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:

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:

Note

The formatted values filtering strategy won't work correctly if you have more than one column bound to the same field from your data and one of the columns has a formatter.

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';

The excel style filtering dialog takes its background color from the grid's theme, using the filtering-row-background parameter. In order to change the background we need to create a custom theme:

$custom-grid: grid-theme(
    $filtering-row-background: #FFCD0F
);

We obviously have a lot more components inside the excel like filtering dialog, such as buttons, checkboxes, a list and even a drop-down. In order to style them, we need to create a separate theme for each one:

$dark-button: button-theme(
    $background: #FFCD0F,
    $foreground: #292826,
    $hover-background: #292826,
    $hover-foreground: #FFCD0F
);

$dark-input-group: input-group-theme(
    $box-background: #FFCD0F,
    $idle-text-color: #292826,
    $focused-text-color: #292826,
    $filled-text-color: #292826
);

$custom-list: list-theme(
    $background: #FFCD0F
);

$custom-checkbox: checkbox-theme(
    $empty-color: #292826,
    $fill-color: #292826,
    $tick-color: #FFCD0F,
    $label-color: #292826
);

$custom-drop-down: drop-down-theme(
    $background-color: #FFCD0F,
    $item-text-color: #292826,
    $hover-item-background: #292826,
    $hover-item-text-color: #FFCD0F
);

In this example we only changed some of the parameters for the listed components, but the button-theme, checkbox-theme, drop-down-theme, input-group-theme, list-theme themes provide way more parameters to control their respective styling.

The last step is to include the component mixins, each with its respective theme. We will also set the color property for the input's placeholder.

@include drop-down($custom-drop-down);
@include grid($custom-grid);
.igx-excel-filter, .igx-excel-filter__secondary {
    @include button($dark-button);
    @include input-group($dark-input-group);
    @include list($custom-list);
    @include checkbox($custom-checkbox);
    .igx-input-group__input::placeholder {
        color: #FFCD0F;
    }
}

Note

We scope most of the components' mixins within .igx-excel-filter and .igx-excel-filter__secondary, so that these custom themes will affect only components nested in the excel style filtering dialog and all of its sub-dialogs. Otherwise other buttons, checkboxes, input-groups and lists would be affected too.

Note

If the component is using an Emulated ViewEncapsulation, it is necessary to penetrate this encapsulation using ::ng-deep:

:host {
    ::ng-deep {
        @include drop-down($custom-drop-down);
        @include grid($custom-grid);
        .igx-excel-filter, .igx-excel-filter__secondary {
            @include button($dark-button);
            @include input-group($dark-input-group);
            @include list($custom-list);
            @include checkbox($custom-checkbox);
            .igx-input-group__input::placeholder {
                color: #FFCD0F;
            }
        }
    }
}

Defining a color palette

Instead of hardcoding the color values like we just did, we can achieve greater flexibility in terms of colors by using the igx-palette and igx-color functions.

igx-palette generates a color palette based on the primary and secondary colors that are passed:

$yellow-color: #FFCD0F;
$black-color: #292826;

$dark-palette: palette($primary: $black-color, $secondary: $yellow-color);

And then with igx-color we can easily retrieve color from the palette.

$custom-grid: grid-theme(
    $filtering-row-background: #FFCD0F
);

$dark-button: button-theme(
    $background: color($dark-palette, "secondary", 400),
    $foreground: color($dark-palette, "primary", 400),
    $hover-background: color($dark-palette, "primary", 400),
    $hover-foreground: color($dark-palette, "secondary", 400)
);

$dark-input-group: input-group-theme(
    $box-background: color($dark-palette, "secondary", 400),
    $idle-text-color: color($dark-palette, "primary", 400),
    $focused-text-color: color($dark-palette, "primary", 400),
    $filled-text-color: color($dark-palette, "primary", 400)
);

$custom-list: list-theme(
    $background: color($dark-palette, "secondary", 400)
);

$custom-checkbox: checkbox-theme(
    $empty-color: color($dark-palette, "primary", 400),
    $fill-color: color($dark-palette, "primary", 400),
    $tick-color: color($dark-palette, "secondary", 400),
    $label-color: color($dark-palette, "primary", 400)
);

$custom-drop-down:drop-down-theme(
    $background-color: color($dark-palette, "secondary", 400),
    $item-text-color: color($dark-palette, "primary", 400),
    $hover-item-background: color($dark-palette, "primary", 400),
    $hover-item-text-color: color($dark-palette, "secondary", 400)
);

Note

The igx-color and igx-palette are powerful functions for generating and retrieving colors. Please refer to Palettes topic for detailed guidance on how to use them.

Using Schemas

Going further with the theming engine, you can build a robust and flexible structure that benefits from schemas. A schema is a recipe of a theme.

Extend one of the two predefined schemas, that are provided for every component, in this case - light-grid, light-input-group, light-button, light-list, light-checkbox and light-drop-down schemas:

$custom-grid-schema: extend($_light-grid,
    (
        filtering-row-background:(
           color: ("secondary", 400)
        )
    )
);

$custom-button-schema: extend($_light-button,
    (
        flat-background:(
           color: ("secondary", 400)
        ),
        flat-text-color:(
           color: ("primary", 400)
        ),
        flat-hover-background:(
           color: ("primary", 400)
        ),
        flat-hover-text-color:(
           color: ("secondary", 400)
        ),

        contained-background:(
           color: ("secondary", 400)
        ),
        contained-text-color:(
           color: ("primary", 400)
        ),
        contained-hover-background:(
           color: ("primary", 400)
        ),
        contained-hover-text-color:(
           color: ("secondary", 400)
        )
    )
);

$custom-input-group-schema: extend($_light-input-group,
    (
        box-background:(
           color: ("secondary", 400)
        ),
        idle-text-color:(
           color: ("primary", 400)
        ),
        focused-text-color:(
           color: ("primary", 400)
        ),
        filled-text-color:(
           color: ("primary", 400)
        )
    )
);

$custom-list-schema: extend($_light-list,
    (
        background:(
           color: ("secondary", 400)
        )
    )
);

$custom-checkbox-schema: extend($_light-checkbox,
    (
        empty-color:(
           color: ("primary", 400)
        ),
        fill-color:(
           color: ("primary", 400)
        ),
        tick-color:(
           color: ("secondary", 400)
        ),
        label-color:(
           color: ("primary", 400)
        )
    )
);

$custom-drop-down-schema: extend($_light-drop-down,
    (
        background-color:(
           color: ("secondary", 400)
        ),
        item-text-color:(
           color: ("primary", 400)
        ),
        hover-item-background:(
           color: ("primary", 400)
        ),
        hover-item-text-color:(
           color: ("secondary", 400)
        )
    )
);

In order to apply our custom schemas we have to extend one of the globals (light or dark), which is basically pointing out the components with a custom schema, and after that add it to the respective component themes:

$custom-light-schema: extend($light-schema,(
   grid: $custom-grid-schema,
   button: $custom-button-schema,
   input-group: $custom-input-group-schema,
   list: $custom-list-schema,
   checkbox: $custom-checkbox-schema,
   drop-down: $custom-drop-down-schema
));

$custom-grid: grid-theme(
    $palette: $dark-palette,
    $schema: $custom-light-schema
);

$custom-button: button-theme(
    $palette: $dark-palette,
    $schema: $custom-light-schema
);

$custom-input-group: input-group-theme(
    $palette: $dark-palette,
    $schema: $custom-light-schema
);

$custom-list: list-theme(
    $palette: $dark-palette,
    $schema: $custom-light-schema
);

$custom-checkbox: checkbox-theme(
    $palette: $dark-palette,
    $schema: $custom-light-schema
);

$custom-drop-down: drop-down-theme(
    $palette: $dark-palette,
    $schema: $custom-light-schema
);

Don't forget to include the themes in the same way as it was demonstrated above.

Demo

Note

The sample will not be affected by the selected global theme from Change Theme.

API References

Additional Resources

Our community is active and always welcoming to new ideas.

Excel Filtering in Angular Grid

Angular Grid Excel Style Filtering Example

Usage

Interactions

Configure Menu Features

Templates

Unique Column Values Strategy

Formatted Values Filtering Strategy

Note

External Excel Style filtering

Demo

Usage

External Outlet

Demo

Styling

Note

Note

Defining a color palette

Note

Using Schemas

Demo

Note

API References

Additional Resources

Theming