XlColorFilter Class

A filter by cell fill or font color.

Namespace: DevExpress.Export.Xl

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

Declaration

C#

public class XlColorFilter :
    IXlFilter,
    IXlFilterCriteria

VB.NET

Public Class XlColorFilter
    Implements IXlFilter,
               IXlFilterCriteria

Remarks

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

The XlColorFilter.Color property defines the color to filter by, and the XlColorFilter.FilterByCellColor property specifies whether the filter should use the cell color or font color as a filter criterion.

To filter a column by a pattern fill applied to its cells, you should additionally specify the XlColorFilter.PatternType and XlColorFilter.PatternColor properties of the pattern fill you wish to use in the filter.

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

// Start filtering data in the "Product" column by the specified fill color.
XlColorFilter filter = new XlColorFilter();
filter.Color = XlColor.FromArgb(0x00ffcc99);
filter.FilterByCellColor = true;
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);
            if (i % 4 == 0)
                cell.ApplyFormatting(XlFill.SolidFill(XlColor.FromArgb(0xffcc99)));
        }
        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 the specified fill color.
Dim filter As New XlColorFilter()
filter.Color = XlColor.FromArgb(&Hffcc99)
filter.FilterByCellColor = True
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)
            If i Mod 4 = 0 Then
                cell.ApplyFormatting(XlFill.SolidFill(XlColor.FromArgb(&Hffcc99)))
            End If
        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

XlColorFilter