XlCustomFilters Class

A custom filter that uses filter values and comparison operators to construct the filter expression.

Namespace: DevExpress.Export.Xl

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

Declaration

C#

public class XlCustomFilters :
    IXlFilter,
    IXlFilterCriteria

VB.NET

Public Class XlCustomFilters
    Implements IXlFilter,
               IXlFilterCriteria

Remarks

Create an instance of the XlCustomFilters class and assign it to the XlFilterColumn.FilterCriteria or IXlTableColumn.FilterCriteria property to define the filter criteria for a worksheet or table column, respectively.

An instance of the XlCustomFilterCriteria class defines a filter criterion for a custom filter. It includes a filter value (XlCustomFilterCriteria.Value) and a comparison operator (XlCustomFilterCriteria.FilterOperator). If you create a complex filter expression containing two filter criteria, specify the logical operator (“AND” or “OR”) that should be used to combine these criteria (XlCustomFilters.And).

Refer to the Filtering article to learn more about filtering in the Excel Export Library.

Example

Note

A complete sample project is available at https://github.com/DevExpress-Examples/xl-export-api-examples-t253492

DataActions.cs

// Generate the header row.
using (IXlRow row = sheet.CreateRow())
    row.BulkCells(new string[] { "Region", "Product", "Sales" }, headerRowFormatting);

// Create a custom filter to display values in the "Sales" column that are greater than $4500.
XlCustomFilters filter = new XlCustomFilters(new XlCustomFilterCriteria(XlFilterOperator.GreaterThanOrEqual, 4500));
sheet.AutoFilterColumns.Add(new XlFilterColumn(2, filter));
// Start filtering data.
sheet.BeginFiltering(sheet.DataRange);

// Generate data for the document.
string[] products = new string[] { "Camembert Pierrot", "Gorgonzola Telino", "Mascarpone Fabioli", "Mozzarella di Giovanni" };
int[] amount = new int[] { 6750, 4500, 3550, 4250, 5500, 6250, 5325, 4235 };
for (int i = 0; i < 8; i++)
{
    using (IXlRow row = sheet.CreateRow())
    {
        using (IXlCell cell = row.CreateCell())
        {
            cell.Value = (i < 4) ? "East" : "West";
            cell.ApplyFormatting(rowFormatting);
        }
        using (IXlCell cell = row.CreateCell())
        {
            cell.Value = products[i % 4];
            cell.ApplyFormatting(rowFormatting);
        }
        using (IXlCell cell = row.CreateCell())
        {
            cell.Value = amount[i];
            cell.ApplyFormatting(rowFormatting);
        }
    }
}

// Finish filtering.
sheet.EndFiltering();

DataActions.vb

' Generate the header row.
Using row As IXlRow = sheet.CreateRow()
    row.BulkCells(New String() { "Region", "Product", "Sales" }, headerRowFormatting)
End Using

' Create a custom filter to display values in the "Sales" column that are greater than $4500.
Dim filter As New XlCustomFilters(New XlCustomFilterCriteria(XlFilterOperator.GreaterThanOrEqual, 4500))
sheet.AutoFilterColumns.Add(New XlFilterColumn(2, filter))
' Start filtering data.
sheet.BeginFiltering(sheet.DataRange)

' Generate data for the document.
Dim products() As String = { "Camembert Pierrot", "Gorgonzola Telino", "Mascarpone Fabioli", "Mozzarella di Giovanni" }
Dim amount() As Integer = { 6750, 4500, 3550, 4250, 5500, 6250, 5325, 4235 }
For i As Integer = 0 To 7
    Using row As IXlRow = sheet.CreateRow()
        Using cell As IXlCell = row.CreateCell()
            cell.Value = If(i < 4, "East", "West")
            cell.ApplyFormatting(rowFormatting)
        End Using
        Using cell As IXlCell = row.CreateCell()
            cell.Value = products(i Mod 4)
            cell.ApplyFormatting(rowFormatting)
        End Using
        Using cell As IXlCell = row.CreateCell()
            cell.Value = amount(i)
            cell.ApplyFormatting(rowFormatting)
        End Using
    End Using
Next i

' Finish filtering.
sheet.EndFiltering()

Related GitHub Examples

The following code snippet (auto-collected from DevExpress Examples) contains a reference to the XlCustomFilters class.

Note

The algorithm used to collect these code examples remains a work in progress. Accordingly, the links and snippets below may produce inaccurate results. If you encounter an issue with code examples below, please use the feedback form on this page to report the issue.

C#

excel-export-api-examples-t253492/CS/XLExportExamples/SpreadsheetActions/DataActions.cs#L164

// Create a custom filter to display values in the "Sales" column that are greater than $4500.
XlCustomFilters filter = new XlCustomFilters(new XlCustomFilterCriteria(XlFilterOperator.GreaterThanOrEqual, 4500));
sheet.AutoFilterColumns.Add(new XlFilterColumn(2, filter));

VB.NET

excel-export-api-examples-t253492/VB/XLExportExamples/SpreadsheetActions/DataActions.vb#L150

' Create a custom filter to display values in the "Sales" column that are greater than $4500.
Dim filter As New XlCustomFilters(New XlCustomFilterCriteria(XlFilterOperator.GreaterThanOrEqual, 4500))
sheet.AutoFilterColumns.Add(New XlFilterColumn(2, filter))

Implements

IXlFilterCriteria

Inheritance

Object

XlCustomFilters