This topic describes how to filter columns in tables.
This topic contains the following sections:
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 types are available to set on columns:
The following table lists the code examples included in this topic.
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.
In this example code snippet for programmatically creating a workbook, a Patients named table is used. The code snippet can be found here: Creating Excel Named Table Code Example
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");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.
In this example code snippet for programmatically creating a workbook, a Patients named table is used. The code snippet can be found here: Creating Excel Named Table Code Example
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");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.
In this example code snippet for programmatically creating a workbook, a Patients named table is used. The code snippet can be found here: Creating Excel Named Table Code Example
The following screenshot is a preview of the final result.
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");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.
In this example code snippet for programmatically creating a workbook, a Patients named table is used. The code snippet can be found here: Creating Excel Named Table Code Example
The following screenshot is a preview of the final result.
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");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.
In this example code snippet for programmatically creating a workbook, a Patients named table is used. The code snippet can be found here: Creating Excel Named Table Code Example
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");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.
In this example code snippet for programmatically creating a workbook, a Patients named table is used. The code snippet can be found here: Creating Excel Named Table Code Example
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");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.
In this example code snippet for programmatically creating a workbook, a Patients named table is used. The code snippet can be found here: Creating Excel Named Table Code Example
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");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.
In this example code snippet for programmatically creating a workbook, a Patients named table is used. The code snippet can be found here: Creating Excel Named Table Code Example
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");The following topics provide additional information related to this topic.
The following samples provide additional information related to this topic.