Data Filtering in the Excel Export Library

Sep 19, 2023
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
Filter a Table

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.

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.

View Example

DataActions.cs
DataActions.vb

csharp

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

' 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 a list of values.
Dim filter As 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.
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()

Filter by Date Values

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

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:

View Example

DataActions.cs
DataActions.vb

csharp

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

' Generate the header row.
Using row As IXlRow = sheet.CreateRow()
    row.BulkCells(New String() {"Date", "Customer", "Total"}, headerRowFormatting)
End Using

' Create a date filter to display sales data for the current year.
Dim filter As New XlValuesFilter()
filter.DateGroups.Add(New XlDateGroupItem(Date.Today, XlDateTimeGroupingType.Year))
sheet.AutoFilterColumns.Add(New XlFilterColumn(0, filter))
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 = If(i < 4, New Date(Date.Today.AddYears(-1).Year, 9 + i, 2 * i + 7), New Date(Date.Today.Year, i - 3, 2 * i + 7))
            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()

Apply a Custom Filter

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

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.

View Example

DataActions.cs
DataActions.vb

csharp

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

' Generate the header row.
Using row As IXlRow = sheet.CreateRow()
    row.BulkCells(New String() { "Region", "Product", "Sales" }, headerRowFormatting)
End Using

' Create a custom filter to display values in the "Sales" column that are greater than $4500.
Dim filter As 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.
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()

Apply a Dynamic Date Filter

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

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.

View Example

DataActions.cs
DataActions.vb

csharp

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

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

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.

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

The dynamic filter type (XlDynamicFilterType.AboveAverage or XlDynamicFilterType.BelowAverage);
The arithmetic mean of values in the filtered column that should be used to perform the comparison for the filter, since an exporter cannot calculate this value automatically.
C#
VB.NET

csharp

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

' Generate the header row.
Using row As IXlRow = sheet.CreateRow()
    row.BulkCells(New String() { "Region", "Product", "Sales" }, headerRowFormatting)
End Using

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

Filter Top or Bottom Ranked Values

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

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

View Example

DataActions.cs
DataActions.vb

csharp

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

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

Filter by Cell Color

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

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

XlColorFilter.Color - specifies the color to filter by;
XlColorFilter.FilterByCellColor - specifies whether the filter should use the cell color or font color as a criterion.

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.

View Example

DataActions.cs
DataActions.vb

csharp

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

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