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 |
|---|---|---|
A filter by a list of cell values or date and time values. | ||
A custom filter that uses filter values and comparison operators to construct the filter expression. | ||
A dynamic filter that shows dates that fall within a specified period, or displays values that are above or below average. | ||
A "Top 10" filter that displays the top/bottom ranked values. | ||
A filter by cell color or font 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.
// 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.
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.
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.
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.
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.
// 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.
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.
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.
// 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();