XlTop10Filter Class

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

Namespace: DevExpress.Export.Xl

Assembly: DevExpress.Printing.v18.1.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()

Related GitHub Examples

The following code snippet (auto-collected from DevExpress Examples) contains a reference to the XlTop10Filter 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#L502

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

VB.NET

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

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

Implements

IXlFilterCriteria

Inheritance

Object

XlTop10Filter