IXlFilterColumns.Add(XlFilterColumn) Method

Adds a new filter column to the worksheet collection of filters.

Namespace: DevExpress.Export.Xl

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

NuGet Package: DevExpress.Printing.Core

Declaration

C#

void Add(
    XlFilterColumn column
)

VB.NET

Sub Add(
    column As XlFilterColumn
)

Parameters

Name	Type	Description
column	XlFilterColumn	An XlFilterColumn object that should be added to the collection.

Remarks

Create an XlFilterColumn instance and add it to the IXlFilterColumns collection to specify the filter criteria for a particular column in a cell range you wish to filter.

Use the IXlSheet.BeginFiltering - IXlSheet.EndFiltering method pair to filter data using the specified filter expression(s). If the generated row contains cells that do not meet the filter criteria, the row is hidden.

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

Example

Note

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

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()