IXlTableColumn.FilterCriteria Property

Gets or sets the filter criteria that should be applied to the table column.

Namespace: DevExpress.Export.Xl

Assembly: DevExpress.Printing.v19.2.Core.dll

Declaration

C#

IXlFilterCriteria FilterCriteria { get; set; }

VB.NET

Property FilterCriteria As IXlFilterCriteria

Property Value

Type	Description
IXlFilterCriteria	An object implementing the IXlFilterCriteria interface.

Remarks

To filter data in a table, get access to the table column you wish to filter by its name or index in the IXlTableColumnCollection collection, and then assign the required filter criteria to the column’s FilterCriteria property before generating the table data.

The following types of filters are available:

• XlValuesFilter - a filter by a list of cell values or date and time values;
• XlCustomFilters - a custom filter that uses filter values and comparison operators to construct the filter expression;
• XlDynamicFilter - a dynamic filter that shows dates that fall within a specified time period or displays values that are above or below the average;
• XlTop10Filter - a "Top 10" filter that displays top/bottom ranked values;
• XlColorFilter - a filter by cell color or font color.

For more information on filtering in the Excel Export Library, refer to the Filtering article.

Example

This example demonstrates how to use the IXlTableColumn.FilterCriteria property to filter data in a table.

C#

IXlTable table;
// Specify an array containing column headings for a table.
string[] columnNames = new string[] { "Product", "Category", "Amount" };

// Create the first row in the worksheet from which the table starts.
using (IXlRow row = sheet.CreateRow())
{
    // Start generating the table with a header row displayed.
    table = row.BeginTable(columnNames, true);
    // Specify the total row label.
    table.Columns[0].TotalRowLabel = "Total";
    // Specify the function to calculate the total.
    table.Columns[2].TotalRowFunction = XlTotalRowFunction.Sum;
    // Specify the number format for the "Amount" column and its total cell.
    XlNumberFormat accounting = @"_([$$-409]* #,##0.00_);_([$$-409]* \(#,##0.00\);_([$$-409]* ""-""??_);_(@_)";
    table.Columns[2].DataFormatting = accounting;
    table.Columns[2].TotalRowFormatting = accounting;
    // Create a custom filter to display values in the "Amount" column that are greater than $15000.
    XlCustomFilters filter = new XlCustomFilters(new XlCustomFilterCriteria(XlFilterOperator.GreaterThan, 15000));
    table.Columns[2].FilterCriteria = filter;
}

// Generate table rows and populate them with data.
using (IXlRow row = sheet.CreateRow())
    row.BulkCells(new object[] { "Camembert Pierrot", "Dairy Products", 17000 }, null);
using (IXlRow row = sheet.CreateRow())
    row.BulkCells(new object[] { "Gnocchi di nonna Alice", "Grains/Cereals", 15500 }, null);
using (IXlRow row = sheet.CreateRow())
    row.BulkCells(new object[] { "Mascarpone Fabioli", "Dairy Products", 15000 }, null);
using (IXlRow row = sheet.CreateRow())
    row.BulkCells(new object[] { "Ravioli Angelo", "Grains/Cereals", 12500 }, null);

// Create the total row and finish the table.
using (IXlRow row = sheet.CreateRow())
    row.EndTable(table, true);

VB.NET

Dim table As IXlTable
' Specify an array containing column headings for a table.
Dim columnNames() As String = { "Product", "Category", "Amount" }

' Create the first row in the worksheet from which the table starts.
Using row As IXlRow = sheet.CreateRow()
    ' Start generating the table with a header row displayed.
    table = row.BeginTable(columnNames, True)
    ' Specify the total row label.
    table.Columns(0).TotalRowLabel = "Total"
    ' Specify the function to calculate the total.
    table.Columns(2).TotalRowFunction = XlTotalRowFunction.Sum
    ' Specify the number format for the "Amount" column and its total cell.
    Dim accounting As XlNumberFormat = "_([$$-409]* #,##0.00_);_([$$-409]* \(#,##0.00\);_([$$-409]* ""-""??_);_(@_)"
    table.Columns(2).DataFormatting = accounting
    table.Columns(2).TotalRowFormatting = accounting
    ' Create a custom filter to display values in the "Amount" column that are greater than $15000.
    Dim filter As New XlCustomFilters(New XlCustomFilterCriteria(XlFilterOperator.GreaterThan, 15000))
    table.Columns(2).FilterCriteria = filter
End Using

' Generate table rows and populate them with data.
Using row As IXlRow = sheet.CreateRow()
    row.BulkCells(New Object() { "Camembert Pierrot", "Dairy Products", 17000 }, Nothing)
End Using
Using row As IXlRow = sheet.CreateRow()
    row.BulkCells(New Object() { "Gnocchi di nonna Alice", "Grains/Cereals", 15500 }, Nothing)
End Using
Using row As IXlRow = sheet.CreateRow()
    row.BulkCells(New Object() { "Mascarpone Fabioli", "Dairy Products", 15000 }, Nothing)
End Using
Using row As IXlRow = sheet.CreateRow()
    row.BulkCells(New Object() { "Ravioli Angelo", "Grains/Cereals", 12500 }, Nothing)
End Using

' Create the total row and finish the table.
Using row As IXlRow = sheet.CreateRow()
    row.EndTable(table, True)
End Using