Version

Filtering Tables (Infragistics Excel Engine)

Introduction

Filtering is done by applying a filter. When the filter is applied on a column, all filters in the table will be reevaluated to determine which rows should be visible. This operation will show rows where all cells meet their table columns’ filter criteria.

If the data in the table is subsequently changed or you change the Hidden property of the rows subsequently, the filter conditions will not be reevaluated. The Filter conditions in a table are only reapplied when table column filters are added, removed, modified, or when the ReapplyFilters method is called on the table.

The following filter methods are available to apply on columns:

Filter type Description

Only cells with dates in a specific month or quarter of any year will be displayed. All other cells will be hidden.

Cells can be filtered based on whether they are above or below the average value of all cells in the column.

Cells can be filtered based on one or more custom conditions.

Only cells with a specific fill will be displayed. All other cells will be hidden.

Only cells with a specific font color will be displayed. All other cells will be hidden.

Cells which only match specific display values or which fall within a specific group of dates/times will be displayed. All other cells will be hidden.

Cells with date values can be filtered based on whether they occur within a relative time range of the date when the filter was applied, such as the next day or the previous quarter.

This filter allows for the following types of filters:

  • Only the top N values are visible

  • Only the bottom N values are visible

  • Only the top N% of values are visible

  • Only the bottom N% of the values are visible

Cells with date values can be filtered if they occur between the start of the year and the date on which the filter is applied.

Code Examples Overview

Overview

The following table lists the code examples included in this topic.

Example Description

Average Filtering

This code example shows how cells can be filtered based on whether they are above or below the average value of all cells in the column.

Custom Filtering

This code example shows how cells can be filtered according to one or more CustomFilterConditions.

Date Period Filtering

This code filters cells with dates in a specific month or quarter. All other cells will be hidden.

Fill Filtering

This code filters cells with specific fill. All other cells will be hidden.

Fixed Values Filtering

This code filters cells which have specific display values. All other cells will be hidden.

Relative Date Range Filtering

This code example shows how to filter date values based on whether they occur within a relative time range of the date on which the filter is applied.

Top or Bottom Filtering

This code example shows how to filter the top 10% of values in the column.

Year To Date Filtering

This code example shows how cells with date values can be filtered if they occur between the start of the year and the date o the filter is applied.

Code Example: Average Filtering

Description

This code shows how to filter cells above the average value of all cells in the column. Filtering below average values is done in a similar manner.

The code in this example creates a workbook with a table. After that, a filter is applied on a column of the table. In the end, the workbook is saved so the filtered table can be seen.

Prerequisites

In this example code snippet for programmatically creating a workbook, a Patients named table is used.

Code

In Visual Basic:

Dim workbook As Infragistics.Documents.Excel.Workbook = ExcelExampleTable.CreateExampleWorkbook()
' Get a table. Table is created in separate code snippet.
Dim table As Infragistics.Documents.Excel.WorksheetTable = workbook.GetTable("Patients")
' Apply average filter
table.Columns("YOB").ApplyAverageFilter(Infragistics.Documents.Excel.Filtering.AverageFilterType.AboveAverage)
workbook.Save("C:\TableFiltering-Filtered.xlsx")

In C#:

Infragistics.Documents.Excel.Workbook workbook = ExcelExampleTable.CreateExampleWorkbook();
// Get a table. Table is created in separate code snippet.
Infragistics.Documents.Excel.WorksheetTable table = workbook.GetTable("Patients");
// Apply average filter
table.Columns["YOB"].ApplyAverageFilter(Infragistics.Documents.Excel.Filtering.AverageFilterType.AboveAverage);
workbook.Save("C:\\TableFiltering-Filtered.xlsx");

Code Example: Custom Filtering

Description

This code shows how to filter cells according to the CustomFilterCondition condition

The code in this example creates a workbook with a table. After that a CustomFilterCondition is created, with a specified ExcelComparisonOperator and a match string. The custom filter is applied and in the end, the workbook is saved so the filtered table can be seen.

Prerequisites

In this example code snippet for programmatically creating a workbook, a Patients named table is used.

Code

In Visual Basic:

Dim workbook As Infragistics.Documents.Excel.Workbook = ExcelExampleTable.CreateExampleWorkbook()
' Get a table. Table is created in separate code snippet.
Dim table As Infragistics.Documents.Excel.WorksheetTable = workbook.GetTable("Patients")
' Create custom filter condition
Dim matchWordCondition As New Infragistics.Documents.Excel.Filtering.CustomFilterCondition(Infragistics.Documents.Excel.Filtering.ExcelComparisonOperator.Equals, "High")
' Apply custom filter
table.Columns("Severity").ApplyCustomFilter(matchWordCondition)
workbook.Save("C:\TableFiltering-Filtered.xlsx")

In C#:

Infragistics.Documents.Excel.Workbook workbook = ExcelExampleTable.CreateExampleWorkbook();
// Get a table. Table is created in separate code snippet.
Infragistics.Documents.Excel.WorksheetTable table = workbook.GetTable("Patients");
// Create custom filter condition
Infragistics.Documents.Excel.Filtering.CustomFilterCondition matchWordCondition = new Infragistics.Documents.Excel.Filtering.CustomFilterCondition(
    Infragistics.Documents.Excel.Filtering.ExcelComparisonOperator.Equals, "High");
// Apply custom filter
table.Columns["Severity"].ApplyCustomFilter(matchWordCondition);
workbook.Save("C:\\TableFiltering-Filtered.xlsx");

Code Example: Date Period Filtering

Description

This code filters cells with dates in a specific month or quarter.

The code in this example creates a workbook with a table. After that, a Date Period Filter is applied on a column of the table which contains dates. In the end, workbook is saved so the filtered table can be seen.

Prerequisites

In this example code snippet for programmatically creating a workbook, a Patients named table is used.

Preview

The following screenshot is a preview of the final result.

Filtering Exported Tables 1.png

Code

In Visual Basic:

Dim workbook As Infragistics.Documents.Excel.Workbook = ExcelExampleTable.CreateExampleWorkbook()
' Get a table. Table is created in separate code snippet.
Dim table As Infragistics.Documents.Excel.WorksheetTable = workbook.GetTable("Patients")
' Apply Date Period Filter
' If you choose DatePeriodFilterType.Month, second argument represents months from January, 1, to December, 12
' and can take values 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12
' If you choose DatePeriodFilterType.Quarter, second argument represents the four quaters of the year - Q1, Q2, Q3 and Q4,
' and can take values 1, 2, 3 and 4.
' Code below accepts all dates in August.
table.Columns("Acceptance Date").ApplyDatePeriodFilter(Infragistics.Documents.Excel.Filtering.DatePeriodFilterType.Month, 8)
workbook.Save("C:\TableFiltering-Filtered.xlsx")

In C#:

Infragistics.Documents.Excel.Workbook workbook = ExcelExampleTable.CreateExampleWorkbook();
// Get a table. Table is created in separate code snippet.
Infragistics.Documents.Excel.WorksheetTable table = workbook.GetTable("Patients");
// Apply Date Period Filter
// If you choose DatePeriodFilterType.Month, second argument represents months from January, 1, to December, 12
// and can take values 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12
// If you choose DatePeriodFilterType.Quarter, second argument represents the four quaters of the year - Q1, Q2, Q3 and Q4,
// and can take values 1, 2, 3 and 4.
// Code below accept all dates in August.
table.Columns["Acceptance Date"].ApplyDatePeriodFilter(Infragistics.Documents.Excel.Filtering.DatePeriodFilterType.Month, 8);
workbook.Save("C:\\TableFiltering-Filtered.xlsx");

Code Example: Fill Filtering

Description

This code shows how to filter cells with specific background fill.

The code in this example creates a workbook with a table. After that, a CellFill is created which will be used for matching. A Fill Filter is applied and in the end, the workbook is saved so the filtered table can be seen.

Prerequisites

In this example code snippet for programmatically creating a workbook, a Patients named table is used.

Preview

The following screenshot is a preview of the final result.

Filtering Exported Tables 2.png

Code

In Visual Basic:

Dim workbook As Infragistics.Documents.Excel.Workbook = ExcelExampleTable.CreateExampleWorkbook()
' Get a table. Table is created in separate code snippet.
Dim table As Infragistics.Documents.Excel.WorksheetTable = workbook.GetTable("Patients")
Dim lastRow As Integer = table.DataAreaRegion.LastRow
Dim rand As New System.Random()
' Set Red background for random cells from first column
Dim i As Integer = 1
While i < lastRow
    If rand.[Next](2) = 1 Then
        workbook.Worksheets(0).Rows(i).Cells(0).CellFormat.Fill = Infragistics.Documents.Excel.CellFill.CreateSolidFill(New Infragistics.Documents.Excel.WorkbookColorInfo(Infragistics.Documents.Excel.WorkbookThemeColorType.Accent1))
    End If
    i += 1
End While
' Create cell fill argument
Dim cellFill As Infragistics.Documents.Excel.CellFill = Infragistics.Documents.Excel.CellFill.CreateSolidFill(New Infragistics.Documents.Excel.WorkbookColorInfo(Infragistics.Documents.Excel.WorkbookThemeColorType.Accent1))
' Apply FillFilter
table.Columns("First Name").ApplyFillFilter(cellFill)
workbook.Save("C:\TableFiltering-Filtered.xlsx")

In C#:

Infragistics.Documents.Excel.Workbook workbook = ExcelExampleTable.CreateExampleWorkbook();
// Get a table. Table is created in separate code snippet.
Infragistics.Documents.Excel.WorksheetTable table = workbook.GetTable("Patients");
int lastRow = table.DataAreaRegion.LastRow;
System.Random rand = new System.Random();
// Set Red background for random cells from first column
for (int i = 1; i < lastRow; i++)
{
    if (rand.Next(2) == 1)
    {
        workbook.Worksheets[0].Rows[i].Cells[0].CellFormat.Fill = Infragistics.Documents.Excel.CellFill.CreateSolidFill(
        new Infragistics.Documents.Excel.WorkbookColorInfo(Infragistics.Documents.Excel.WorkbookThemeColorType.Accent1));
    }
}
// Create cell fill argument
Infragistics.Documents.Excel.CellFill cellFill = Infragistics.Documents.Excel.CellFill.CreateSolidFill(
    new Infragistics.Documents.Excel.WorkbookColorInfo(Infragistics.Documents.Excel.WorkbookThemeColorType.Accent1));
// Apply FillFilter
table.Columns["First Name"].ApplyFillFilter(cellFill);
workbook.Save("C:\\TableFiltering-Filtered.xlsx");

Code Example: Fixed Values Filtering

Description

This code filters cells which have specific display values.

The code in this example creates a workbook with a table. After that, a string array is created which holds all fixed values for filtering and the Fixed Values Filter is applied. The cells from the column on which filter is applied have different string values. In the end, the workbook is saved so the filtered table can be seen.

Prerequisites

In this example code snippet for programmatically creating a workbook, a Patients named table is used.

Code

In Visual Basic:

Dim workbook As Infragistics.Documents.Excel.Workbook = ExcelExampleTable.CreateExampleWorkbook()
' Get a table. Table is created in separate code snippet.
Dim table As Infragistics.Documents.Excel.WorksheetTable = workbook.GetTable("Patients")
' Create a category array of values, to be used in filtering
Dim categories As String() = New String() {"High", "Medium"}
' Apply FixedValuesFilter
table.Columns("Severity").ApplyFixedValuesFilter(False, categories)
workbook.Save("C:\TableFiltering-Filtered.xlsx")

In C#:

Infragistics.Documents.Excel.Workbook workbook = ExcelExampleTable.CreateExampleWorkbook();
// Get a table. Table is created in separate code snippet.
Infragistics.Documents.Excel.WorksheetTable table = workbook.GetTable("Patients");
// Create a category array of values, to be used in filtering
string[] categories = new string[] {"High", "Medium"};
// Apply FixedValuesFilter
table.Columns["Severity"].ApplyFixedValuesFilter(false, categories);
workbook.Save("C:\\TableFiltering-Filtered.xlsx");

Code Example: Relative Date Range Filtering

Description

This code example shows how to filter date values based on whether they occur within a relative time range of the date.

The code in this example creates a workbook with a table. After that, a Relative Date Values Filter is applied. In the end, the workbook is saved so the filtered table can be seen.

Prerequisites

In this example code snippet for programmatically creating a workbook, a Patients named table is used.

Code

In Visual Basic:

Dim workbook As Infragistics.Documents.Excel.Workbook = ExcelExampleTable.CreateExampleWorkbook()
' Get a table. Table is created in separate code snippet.
Dim table As Infragistics.Documents.Excel.WorksheetTable = workbook.GetTable("Patients")
' Apply RelativeDateRangeFilter
table.Columns("Acceptance Date").ApplyRelativeDateRangeFilter(Infragistics.Documents.Excel.Filtering.RelativeDateRangeOffset.Current, Infragistics.Documents.Excel.Filtering.RelativeDateRangeDuration.Month)
workbook.Save("C:\TableFiltering-Filtered.xlsx")

In C#:

Infragistics.Documents.Excel.Workbook workbook = ExcelExampleTable.CreateExampleWorkbook();
// Get a table. Table is created in separate code snippet.
Infragistics.Documents.Excel.WorksheetTable table = workbook.GetTable("Patients");
// Apply RelativeDateRangeFilter
table.Columns["Acceptance Date"].ApplyRelativeDateRangeFilter(
    Infragistics.Documents.Excel.Filtering.RelativeDateRangeOffset.Current,
    Infragistics.Documents.Excel.Filtering.RelativeDateRangeDuration.Month);
workbook.Save("C:\\TableFiltering-Filtered.xlsx");

Code Example: Top or Bottom Filtering

Description

This code example shows how to filter the top 10% of values from a column.

The code in this example creates a workbook with a table. After that, a filter is applied which filters the top 10% of values. In the end, the workbook is saved so the filtered table can be seen.

Prerequisites

In this example code snippet for programmatically creating a workbook, a Patients named table is used.

Code

In Visual Basic:

Dim workbook As Infragistics.Documents.Excel.Workbook = ExcelExampleTable.CreateExampleWorkbook()
' Get a table. Table is created in separate code snippet.
Dim table As Infragistics.Documents.Excel.WorksheetTable = workbook.GetTable("Patients")
table.Columns("YOB").ApplyTopOrBottomFilter(Infragistics.Documents.Excel.Filtering.TopOrBottomFilterType.TopPercentage, 20)
workbook.Save("C:\TableFiltering-Filtered.xlsx")

In C#:

Infragistics.Documents.Excel.Workbook workbook = ExcelExampleTable.CreateExampleWorkbook();
// Get a table. Table is created in separate code snippet.
Infragistics.Documents.Excel.WorksheetTable table = workbook.GetTable("Patients");
table.Columns["YOB"].ApplyTopOrBottomFilter(Infragistics.Documents.Excel.Filtering.TopOrBottomFilterType.TopPercentage, 20);
workbook.Save("C:\\TableFiltering-Filtered.xlsx");

Code Example: Year to Date Filtering

Description

This code example shows how cells with date values can be filtered if they occur between the start of the year and the date on which the filter is applied.

The code in this example creates a workbook with a table. After that, the Year-To-Date Filter is applied. In the end, the workbook is saved so the filtered table can be seen.

Prerequisites

In this example code snippet for programmatically creating a workbook, a Patients named table is used.

Code

In Visual Basic:

Dim workbook As Infragistics.Documents.Excel.Workbook = ExcelExampleTable.CreateExampleWorkbook()
' Get a table. Table is created in separate code snippet.
Dim table As Infragistics.Documents.Excel.WorksheetTable = workbook.GetTable("Patients")
table.Columns("Acceptance Date").ApplyYearToDateFilter()
workbook.Save("C:\TableFiltering-Filtered.xlsx")

In C#:

Infragistics.Documents.Excel.Workbook workbook = ExcelExampleTable.CreateExampleWorkbook();
// Get a table. Table is created in separate code snippet.
Infragistics.Documents.Excel.WorksheetTable table = workbook.GetTable("Patients");
table.Columns["Acceptance Date"].ApplyYearToDateFilter();
workbook.Save("C:\\TableFiltering-Filtered.xlsx");

Related Content

Topics

The following topics provide additional information related to this topic.

Topic Purpose

This topic describes table support in Infragistics Excel Engine. A table in Microsoft Excel® is a named rectangular region of cells which contain related data organized into columns.

This topic describes how to sort columns in tables. Columns in a table can be sorted by applying a sort condition to the WorksheetTableColumn.SortCondition property.

This topic describes how to determine the text that would be displayed in Microsoft Excel® cell given the cell’s value, format string, column width, and worksheet options. You can determine the cell text using the WorksheetCell.GetText method.

In this section you’ll find information that will help you to better understand the object model and the functionalities of the Infragistics Excel Engine.