Grid Excel Style Filtering

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

Demo


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>

Interaction

In order to open the filter menu for a particular column, the angular filter icon in the header should be clicked. If the column can be sorted, pinned, moved or hidden along with the filtering functionality, there will be buttons 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 click Apply button. The filtering applied through the list items creates filter expressions with equals operator and the logic operator between each expression is OR. If you want to clear the filter, you can check Select All item and then 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, moving, pinning and hiding features can be removed from the filter menu. The inputs that control them are as follows: sortable, movable, disablePinning, disableHiding.

<igx-grid #grid1 [data]="data" [autoGenerate]="false" height="650px" width="100%" [allowFiltering]="true" [filterMode]="'excelStyleFilter'">
    <igx-column field="ProductName" header="Product Name" [sortable]="true" [movable]="true" [dataType]="'string'">
    </igx-column>
    <igx-column field="QuantityPerUnit" header="Quantity Per Unit" [sortable]="false" [disablePinning]="true" [disableHiding]="true" [movable]="false" [dataType]="'string'">
    </igx-column>
    <igx-column field="UnitPrice" header="Unit Price" [disablePinning]="true" [disableHiding]="true" [sortable]="true" [movable]="true" [dataType]="'number'">
    </igx-column>
    <igx-column field="OrderDate" header="Order Date" [sortable]="false" [movable]="false" [dataType]="'date'" [formatter]="formatDate">
    </igx-column>
    <igx-column field="Discontinued" header="Discontinued" [sortable]="true" [movable]="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 four disabled, 'Unit Price' has only sorting and moving and 'Order Date' has only pinning and hiding.


If you want to keep the sorting, moving, pinning and hiding features of the columns, but remove the items from the excel style filter menu, you can add templates in the grid for each operation.

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

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.

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:

@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: igx-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: igx-button-theme(
    $flat-background: #FFCD0F,
    $flat-text-color: #292826,
    $flat-hover-background: #292826,
    $flat-hover-text-color: #FFCD0F,

    $raised-background: #FFCD0F,
    $raised-text-color: #292826,
    $raised-hover-background: #292826,
    $raised-hover-text-color: #FFCD0F
);

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

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

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

$custom-drop-down: igx-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 igx-button-theme, igx-checkbox-theme, igx-drop-down-theme, igx-input-group-theme, igx-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 igx-drop-down($custom-drop-down);
@include igx-grid($custom-grid);
.igx-excel-filter, .igx-excel-filter__secondary {
    @include igx-button($dark-button);
    @include igx-input-group($dark-input-group);
    @include igx-list($custom-list);
    @include igx-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 igx-drop-down($custom-drop-down);
        @include igx-grid($custom-grid);
        .igx-excel-filter, .igx-excel-filter__secondary {
            @include igx-button($dark-button);
            @include igx-input-group($dark-input-group);
            @include igx-list($custom-list);
            @include igx-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: igx-palette($primary: $black-color, $secondary: $yellow-color);

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

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

$dark-button: igx-button-theme(
    $flat-background: igx-color($dark-palette, "secondary", 400),
    $flat-text-color: igx-color($dark-palette, "primary", 400),
    $flat-hover-background: igx-color($dark-palette, "primary", 400),
    $flat-hover-text-color: igx-color($dark-palette, "secondary", 400),

    $raised-background: igx-color($dark-palette, "secondary", 400),
    $raised-text-color: igx-color($dark-palette, "primary", 400),
    $raised-hover-background: igx-color($dark-palette, "primary", 400),
    $raised-hover-text-color: igx-color($dark-palette, "secondary", 400)
);

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

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

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

$custom-drop-down: igx-drop-down-theme(
    $background-color: igx-color($dark-palette, "secondary", 400),
    $item-text-color: igx-color($dark-palette, "primary", 400),
    $hover-item-background: igx-color($dark-palette, "primary", 400),
    $hover-item-text-color: igx-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:(
            igx-color: ("secondary", 400)
        )
    )
);

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

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

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

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

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

$custom-drop-down-schema: extend($_light-drop-down,
    (
        background-color:(
            igx-color: ("secondary", 400)
        ),
        item-text-color:(
            igx-color: ("primary", 400)
        ),
        hover-item-background:(
            igx-color: ("primary", 400)
        ),
        hover-item-text-color:(
            igx-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,(
    igx-grid: $custom-grid-schema,
    igx-button: $custom-button-schema,
    igx-input-group: $custom-input-group-schema,
    igx-list: $custom-list-schema,
    igx-checkbox: $custom-checkbox-schema,
    igx-drop-down: $custom-drop-down-schema
));

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

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

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

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

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

$custom-drop-down: igx-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


API References

Additional Resources

Our community is active and always welcoming to new ideas.