XlTop10Filter Class

A Top 10 filter that displays top/bottom ranked values.

Namespace: DevExpress.Export.Xl

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

Declaration

C#

public class XlTop10Filter :
    IXlFilter,
    IXlFilterCriteria

VB.NET

Public Class XlTop10Filter
    Implements IXlFilter,
               IXlFilterCriteria

Remarks

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

Use the following properties of the XlTop10Filter class to define the filter settings:

• XlTop10Filter.Value - specifies the number or percentage of column items to display;
• XlTop10Filter.FilterValue - specifies the actual cell value used by the filter to perform the comparison (if you filter for the top/bottom items, all column values that are greater than/less than or equal to the specified filter value are shown);
• XlTop10Filter.Top - specifies whether to filter column values by top or bottom order;
• XlTop10Filter.Percent - specifies whether to show values that fall in the top/bottom N percent of the column.

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 Top 10 filter to display three products with the highest sales.
XlTop10Filter filter = new XlTop10Filter(3, 5500, true, false);
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 Top 10 filter to display three products with the highest sales.
Dim filter As New XlTop10Filter(3, 5500, True, False)
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()

Implements

IXlFilterCriteria

Inheritance

Object

XlTop10Filter