Skip to main content

IXlTableColumn.FilterCriteria Property

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

Namespace: DevExpress.Export.Xl

Assembly: DevExpress.Printing.v24.1.Core.dll

NuGet Package: DevExpress.Printing.Core

Declaration

IXlFilterCriteria FilterCriteria { get; set; }

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.

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);
See Also