In data processing and analysis, Excel's AutoFilter feature is a powerful tool for boosting efficiency—it quickly locates the desired subset from massive datasets without requiring complex formulas. If you are a .NET developer looking to programmatically add filters to Excel files, Free Spire.XLS for .NET is a lightweight, free, and feature-rich choice. This article will walk you through three common AutoFilter scenarios using C# and this component: basic range filtering, date-group filtering, and custom text matching. All code is standardized and ready for reuse.
Preparation: Installing Free Spire.XLS
First, create a Console Application in Visual Studio (supporting .NET Framework, .NET Core, or .NET 5+). Install Free Spire.XLS via the NuGet Package Manager:
Install-Package FreeSpire.XLS
Or via the .NET CLI:
dotnet add package FreeSpire.XLS
This library provides full read/write support for Excel 97–2016 formats and does not require Microsoft Office to be installed. The examples in this article use a test file named Data.xlsx; you can adjust the path as needed.
1. Basic AutoFilter: Setting the Filter Range
The simplest scenario is to enable the AutoFilter on a specified column (or row) so that users can manually choose criteria later in Excel. The following code loads a workbook, gets the first worksheet, and sets the AutoFilter range to the header row (e.g., A1:C1), thereby adding drop‑down arrows for each column in that region.
using Spire.Xls;
namespace AddAutoFilterDemo
{
class Program
{
static void Main(string[] args)
{
// 1. Initialize Workbook and load the Excel file
using (Workbook workbook = new Workbook())
{
workbook.LoadFromFile(@"C:\Data\Data.xlsx");
// 2. Get the first worksheet
Worksheet sheet = workbook.Worksheets[0];
// 3. Set the AutoFilter range (typically the header row)
sheet.AutoFilters.Range = sheet.Range["A1:C1"];
// 4. Save the result (Excel 2016 version can be specified)
workbook.SaveToFile("AutoFilter_Base.xlsx", ExcelVersion.Version2016);
}
}
}
}
Note : The
AutoFilters.Rangeproperty determines the filtering scope. Generally, you only need to select the header cells, and the filter will automatically cover all data rows in those columns. When you open the saved file, you will see filter buttons on each column header, allowing you to filter as needed.
2. Built‑in Date Filtering: Grouping by Year/Month/Day
A more common requirement is to filter date fields by specific months, quarters, or years. Free Spire.XLS provides the AddDateFilter method, which supports grouping by year, month, day, hour, minute, etc. For example, the following code filters all records for February 2022 :
using Spire.Xls;
using Spire.Xls.Core.Spreadsheet.AutoFilter;
namespace AddAutoFilterDemo
{
class Program
{
static void Main(string[] args)
{
using (Workbook workbook = new Workbook())
{
workbook.LoadFromFile(@"C:\Data\Data.xlsx");
Worksheet sheet = workbook.Worksheets[0];
// Set the filter range to A1:A12 (assuming column A contains dates)
sheet.AutoFilters.Range = sheet.Range["A1:A12"];
// Get the column to filter (index 0 means the first column)
IAutoFilter filterColumn = sheet.AutoFilters[0];
// Add date grouping: February 2022
sheet.AutoFilters.AddDateFilter(
filterColumn,
DateTimeGroupingType.Month, // group by month
2022, // year
2, // month
0, 0, 0, 0 // day, hour, minute, second (not used here)
);
// Apply the filter
sheet.AutoFilters.Filter();
workbook.SaveToFile("AutoFilter_Date.xlsx", ExcelVersion.Version2016);
}
}
}
}
Key Point : The
DateTimeGroupingTypeenum supportsYear,Quarter,Month,Day,Hour,Minute,Second, etc., which you can combine as needed. For example, to filter the first quarter of 2022, set the type toQuarter, year to 2022, and quarter parameter to 1.
Additional Note : The example above demonstrates date grouping (AddDateFilter), but the AutoFilters object provides several other built‑in filtering methods, allowing flexible use across different business scenarios:
-
Filter by fill color – Use the
AddFillColorFiltermethod to filter rows whose cells have a specific background color. -
Filter by font color – Use the
AddFontColorFiltermethod to filter by cell font color. -
Filter by icon – Use the
AddIconFiltermethod to filter rows that contain a specific icon from conditional formatting (e.g., traffic lights, arrows).
These methods work similarly to AddDateFilter: you specify the filter column, pass the color or icon parameters, and finally call Filter() to apply. You can freely combine them based on your actual data types to achieve highly customized filtering logic. This article uses date filtering only as an illustration; in practice, do not limit yourself to date grouping.
3. Custom Filtering: Matching Specific Text or Numeric Values
Sometimes we need to filter rows containing a specified string (or equal to a numeric value). In such cases, we can use the CustomFilter method. The following example filters all rows in column G where the value is "Grocery" (for exact text matching):
using Spire.Xls;
using Spire.Xls.Core.Spreadsheet.AutoFilter;
namespace AddAutoFilterDemo
{
class Program
{
static void Main(string[] args)
{
using (Workbook workbook = new Workbook())
{
workbook.LoadFromFile(@"C:\Data\Data.xlsx");
Worksheet sheet = workbook.Worksheets[0];
// Set the filter range to G1:G12
sheet.AutoFilters.Range = sheet.Range["G1:G12"];
// Get the column object (explicitly cast to FilterColumn)
FilterColumn filterColumn = (FilterColumn)sheet.AutoFilters[0];
// Add a custom filter condition: equal to "Grocery"
sheet.AutoFilters.CustomFilter(
filterColumn,
FilterOperatorType.Equal, // equal operator
"Grocery" // comparison value
);
// Apply the filter
sheet.AutoFilters.Filter();
workbook.SaveToFile("AutoFilter_Custom.xlsx", ExcelVersion.Version2016);
}
}
}
}
Besides Equal, FilterOperatorType also offers LessThan, GreaterThan, Contains, BeginsWith, EndsWith, and many other options, covering most fuzzy‑matching needs. To combine multiple conditions (e.g., "contains A or B"), you can call CustomFilter multiple times and combine them with logical operators.
Advanced Tips and Precautions
-
Path Handling : The examples use absolute paths; in real projects, it is recommended to use relative paths or read from configuration, e.g.,
Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Data.xlsx"). -
Performance Optimization : For large Excel files, consider using
Workbook.LoadFromFile(fileName, ExcelOpenType.Automatic)to optimize memory usage during loading. -
Working with Filtered Data : After applying the filter, you can iterate over
sheet.Rowsand check theIsHiddenproperty to further process visible rows. -
Multi‑column Filtering : To filter multiple columns simultaneously, simply add conditions for each column in order, then call
Filter()once at the end. -
Clearing Filters : To remove all filters, call
sheet.AutoFilters.Clear()and save again.
Summary
With Free Spire.XLS for .NET, just a few lines of C# code can empower Excel with robust AutoFilter capabilities. Whether enabling basic column filters, using built‑in date grouping, or applying custom text matching, the component offers intuitive APIs and stable performance. The three examples in this article cover the most common filtering scenarios, which you can flexibly combine based on your actual data characteristics. Finally, don’t forget to share the generated Excel files with colleagues or downstream systems to make data insights more efficient.
If you wish to explore further, you can also investigate other methods under AutoFilters, such as AddDynamicFilter (for top N dynamic filtering) or AddColorFilter (for filtering by cell color). Mastering these techniques will make your Excel automation toolbox even more complete.
Top comments (0)