IXlSheet.BeginFiltering(XlCellRange) Method

Starts filtering data in the specified cell range.

Namespace: DevExpress.Export.Xl

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

NuGet Package: DevExpress.Printing.Core

Declaration

C#

void BeginFiltering(
    XlCellRange autoFilterRange
)

VB.NET

Sub BeginFiltering(
    autoFilterRange As XlCellRange
)

Parameters

Name	Type	Description
autoFilterRange	XlCellRange	An XlCellRange object that is the header row of the cell range to be filtered.

Remarks

To filter data in a cell range, do the following:

• Use the IXlSheet.AutoFilterColumns property to get access to the worksheet’s filter collection. Create an XlFilterColumn instance and add it to the collection to apply filtering to a specific column in a cell range. XlFilterColumn.ColumnId specifies the column’s zero-based index in the cell range you wish to filter, and the XlFilterColumn.FilterCriteria property defines the column’s filter criteria.
• Call the BeginFiltering method to start filtering data using the specified filter criteria. Note that you can pass only the header row of the filter range to the method, since this range automatically extends when you generate new rows. If a row contains cells that do not meet the filter criteria, the row is hidden.
• Call the IXlSheet.EndFiltering method to finish filtering after generating all the data.

For more examples on how to filter data in a worksheet using the Excel Export Library, refer to the Filtering topic.

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

// Start filtering data in the "Product" column by a list of values.
XlValuesFilter filter = new XlValuesFilter();
filter.Values.Add("Mascarpone Fabioli");
filter.Values.Add("Mozzarella di Giovanni");
sheet.AutoFilterColumns.Add(new XlFilterColumn(1, filter));
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

' Start filtering data in the "Product" column by a list of values.
Dim filter As New XlValuesFilter()
filter.Values.Add("Mascarpone Fabioli")
filter.Values.Add("Mozzarella di Giovanni")
sheet.AutoFilterColumns.Add(New XlFilterColumn(1, filter))
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()