XlDynamicFilter Class

A dynamic filter that shows dates that fall within a specified time period or displays values that are above or below the average.

Namespace: DevExpress.Export.Xl

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

Declaration

C#

public class XlDynamicFilter :
    IXlFilter,
    IXlFilterCriteria

VB.NET

Public Class XlDynamicFilter
    Implements IXlFilter,
               IXlFilterCriteria

Remarks

Create an instance of the XlDynamicFilter 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 appropriate XlDynamicFilterType enumeration member to specify the dynamic filter type you wish to apply.

If you use a dynamic filter of the XlDynamicFilterType.AboveAverage or XlDynamicFilterType.BelowAverage type, you should assign the arithmetic mean of values in the filtered column to the XlDynamicFilter.Value property to perform filtering, since an exporter cannot calculate this value automatically.

Note

The dynamic filter criteria can change when the data to which the filter is applied or the current system date changes.

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[] { "Date", "Customer", "Total" }, headerRowFormatting);

// Create a dynamic filter to display dates that occur this month.
XlDynamicFilter filter = new XlDynamicFilter(XlDynamicFilterType.ThisMonth);
sheet.AutoFilterColumns.Add(new XlFilterColumn(0, filter));
// Start filtering data.
sheet.BeginFiltering(sheet.DataRange);

// Generate data for the document.
string[] customers = new string[] { "Tom's Club", "E-Mart", "K&S Music", "Walters" };
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 = DateTime.Now.AddDays(-7 * (7 - i));
            cell.ApplyFormatting(rowFormatting);
        }
        using (IXlCell cell = row.CreateCell())
        {
            cell.Value = customers[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() {"Date", "Customer", "Total"}, headerRowFormatting)
End Using

' Create a dynamic filter to display dates that occur this month.
Dim filter As New XlDynamicFilter(XlDynamicFilterType.ThisMonth)
sheet.AutoFilterColumns.Add(New XlFilterColumn(0, filter))
' Start filtering data.
sheet.BeginFiltering(sheet.DataRange)

' Generate data for the document.
Dim customers() As String = {"Tom's Club", "E-Mart", "K&S Music", "Walters"}
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 = Date.Now.AddDays(-7 * (7 - i))
            cell.ApplyFormatting(rowFormatting)
        End Using
        Using cell As IXlCell = row.CreateCell()
            cell.Value = customers(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

XlDynamicFilter