Data Filtering in the Excel Export Library

  • 16 minutes to read

The Excel Export Library allows you to use the filtering functionality to filter large amounts of data in a worksheet and display only rows that meet the filtering criteria.

  • Filter a Cell Range

    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 filtered cell range. The XlFilterColumn.FilterCriteria property defines the column’s filter criteria.

    Call the IXlSheet.BeginFiltering method to start filtering data using the specified filter expression(s). The method’s autoFilterRange parameter specifies the header row of the cell range you wish to filter. 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.

  • Filter a Table

    To filter data in a table column, assign the required filter criteria to the column’s IXlTableColumn.FilterCriteria property before generating the table data.

The following types of filters are available:

Filter Type

Description

Examples

XlValuesFilter

A filter by a list of cell values or date and time values.

Filter by Cell Values

Filter by Date Values

XlCustomFilters

A custom filter that uses filter values and comparison operators to construct the filter expression.

Apply a Custom Filter

XlDynamicFilter

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

Apply a Dynamic Date Filter

Filter Values that are Above or Below Average

XlTop10Filter

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

Filter Top or Bottom Ranked Values

XlColorFilter

A filter by cell color or font color.

Filter by Cell Color

Use the IXlSheet.AutoFilterRange property to enable filtering for a worksheet range without applying the filter criteria.

Filter by Cell Values

The example below demonstrates how to filter data in a column by a list of values.

XlExport_Examples_ValuesFilter

Create an XlValuesFilter object to specify the filter criteria. Use the Add method of the XlValuesFilter.Values collection to specify cell values that should be included in the filtering results. You can include blank cells in a filter by setting the XlValuesFilter.FilterByBlank property to true.

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

Filter by Date Values

This example demonstrates how to filter date and time values in a column.

XlExport_Examples_DateValuesFilter

Create an XlValuesFilter object to specify the filter criteria. Use the XlValuesFilter.DateGroups collection’s Add method to specify date and time values which should be used in the filter criteria. An instance of the XlDateGroupItem class defines each date and time filter value. When you create a new XlDateGroupItem object, you should specify the base date or time value to filter by and which part of this value to be used in the filter criteria.

The following example uses the XlDateGroupItem instance with the XlDateGroupItem.Value set to the current date and time, and XlDateGroupItem.GroupingType set to XlDateTimeGroupingType.Year to display the current year’s dates:

// Generate the header row.
using (IXlRow row = sheet.CreateRow())
    row.BulkCells(new string[] { "Date", "Customer", "Total" }, headerRowFormatting);

// Create a date filter to display sales data for the current year.
XlValuesFilter filter = new XlValuesFilter();
filter.DateGroups.Add(new XlDateGroupItem(DateTime.Today, XlDateTimeGroupingType.Year));
sheet.AutoFilterColumns.Add(new XlFilterColumn(0, filter));
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 = (i < 4) ? new DateTime(DateTime.Today.AddYears(-1).Year, 9 + i, 2 * i + 7) : new DateTime(DateTime.Today.Year, i - 3, 2 * i + 7);
            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();

Apply a Custom Filter

The example below demonstrates how to specify the custom filter criteria to filter numbers in a column.

XlExport_Examples_CustomFilter

Create an XlCustomFilters object to construct the filter expression. An instance of the XlCustomFilterCriteria class defines a filter criterion for a custom filter. It includes a filter value (XlCustomFilterCriteria.Value) and a comparison operator (XlCustomFilterCriteria.FilterOperator). If you create a complex filter expression containing two filter criteria, specify the logical operator (“AND” or “OR”) used to combine these criteria (XlCustomFilters.And).

Tip

You can use the wildcard characters when performing text filtering. The asterisk * matches any number of characters, while the question mark ? represents a single character. If you need to filter values containing a specific character, such as the asterisk, question mark or tilde, put the tilde (~) before it.

// Generate the header row.
using (IXlRow row = sheet.CreateRow())
    row.BulkCells(new string[] { "Region", "Product", "Sales" }, headerRowFormatting);

// Create a custom filter to display values in the "Sales" column that are greater than $4500.
XlCustomFilters filter = new XlCustomFilters(new XlCustomFilterCriteria(XlFilterOperator.GreaterThanOrEqual, 4500));
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();

Apply a Dynamic Date Filter

The following example demonstrates how to use a dynamic filter to show dates that occurred this month.

XlExport_Examples_DateFilter

Create an XlDynamicFilter object to construct the filter criteria. Use the appropriate XlDynamicFilterType enumeration member to specify the dynamic filter type you wish to apply.

Note

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

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

Filter Values that are Above or Below Average

The example below demonstrates how to use a dynamic filter to display sales values that are above average.

XlExport_Examples_AboveAverageFilter

Create an XlDynamicFilter class instance to specify the filter criteria. Its constructor requires the following parameters:

// Generate the header row.
using (IXlRow row = sheet.CreateRow())
    row.BulkCells(new string[] { "Region", "Product", "Sales" }, headerRowFormatting);

// Apply a dynamic filter to the "Sales" column to display only values that are above the average.
XlDynamicFilter filter = new XlDynamicFilter(XlDynamicFilterType.AboveAverage, 5045);
sheet.AutoFilterColumns.Add(new XlFilterColumn(2, 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();

Filter Top or Bottom Ranked Values

This example demonstrates how to display three products with the highest sales using the “Top 10” filter.

XlExport_Examples_Top10Filter

Create an XlTop10Filter instance to specify the filter criteria. This filter type requires the following parameters:

  • A number or percentage of column items to display (XlTop10Filter.Value);
  • A filter value based on which to perform the comparison for the filter (XlTop10Filter.FilterValue);

    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.

  • A value indicating whether to filter column values by top or bottom order (XlTop10Filter.Top);
  • A value indicating whether to show values that fall in the top/bottom N percent of the column (XlTop10Filter.Percent).
// 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();

Filter by Cell Color

The following example demonstrates how to filter cells in a column by a fill color.

XlExport_Examples_ColorFilter

Create an XlColorFilter class instance and use its properties to specify the filter criteria.

You can also filter a column by a pattern fill applied to its cells by additionally specifying the XlColorFilter.PatternType and XlColorFilter.PatternColor properties of the pattern fill you wish to use as a criterion.

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