XLSX and XLS file formats are frequently used to manipulate huge data. Likewise, AutoFilters are used to organize data in Excel files. You can easily add or remove AutoFilter in Excel files with Aspose.Cells for .NET API. Filters can be used to quantify some data to extract some meaningful information. For example, an organization wants to filter the months with low sales, in order to devise and improve their strategies. In this article, we will explore data filtering using auto filters in C#:
- Add or Remove AutoFilter in Excel Files – API Installation
- Apply AutoFilter on Cells in Excel Files using C#
- Add Date AutoFilter in XLSX using C#
- Add Dynamic Date AutoFilter in XLSX using C#
- Apply Custom Number AutoFilter in XLSX with C#
- Add Custom AutoFilter with Contains using C#
- Remove or Delete AutoFilter in Excel using C#
Add or Remove AutoFilter in Excel Files – API Installation
Aspose.Cells for .NET API efficiently lets you work with Excel spreadsheets. You can create, edit, or manipulate XLS, XLSX, and other supported formats programmatically with minimal code. You can download and install the MSI or ZIP package from the Downloads section, or simply install it via the NuGet gallery in your solution. For example, you can easily install it in your environment using the following command:
> Install-Package Aspose.Cells -Version 20.9.0
After installing the API successfully, let us proceed with some of the important and useful use cases:
Apply AutoFilter on Cells in Excel Files using C#
You can apply AutoFilter on a specific range of cells in Excel files. This allows you to sort the values in ascending or descending order as well as other number filters when the cells contain numerical values. You need to follow the following steps for applying AutoFilter in Excel files using C#:
- Open an Excel file and access the Worksheet
- Create AutoFilter by specifying specific cell range
- Save output XLS/XLSX file
The code snippet shows how to apply AutoFilter on cells in Excel file using C#:
You can notice the AutoFilter added in source XLSX file in the screenshot below:
Add Date AutoFilter in XLSX using C#
Data in Excel files often contain information related to dates. Sometimes you might need to explore information based on different time periods that can be identified by the dates. For example, let us consider the case where we need to filter data related to January 2018 then you need to follow the following steps:
- Load input spreadsheet
- Accessing a worksheet
- Add Date Filter and save the output file
The following code sample follows these steps and shows how to add Date AutoFilter in Excel files using C#:
Add Dynamic Date AutoFilter in XLSX using C#
As we have already discussed the scenario of filtering data based on dates. However, the date scenario can be a little more generic as well, in some scenarios. For example, we need the data to be filtered out for January month irrespective of which year. Such requirements can easily be handled with a dynamic date filter by using the steps below:
- Load input XLSX file
- Access a worksheet
- Set DynamicFilter for January month
- Save output Excel sheet
This example is in continuation of the above example, where the code snippet below shows how to add a dynamic date AutoFilter in an Excel file using C#:
Apply Custom Number AutoFilter in XLSX with C#
You can filter data based on a custom range of numbers using Aspose.Cells for .NET API. For instance, when a company needs to access how many of its employees have achieved sales targets. Likewise, there could be a lot of possibilities where this filtering technique can help. Let us assume a case where we need to filter all data where the numbers are within the range of 5 and 10 inclusively, then you need to follow the below steps:
- Instantiate a Workbook object and load the input file
- Add custom number filter in a specific worksheet
- Save output Excel file
Based on these steps, the following code shows how to add custom number AutoFilter using C#:
Add Custom AutoFilter with Contains using C#
Moving another step further from custom number AutoFilter, let us learn how to add Custom AutoFilter which can be used to track specific text using Contains Filter Operator. It can be useful to search for words where any text is partially or fully present in the data. You should follow the following steps to achieve these requirements:
- Load input Excel file with data
- Specify AutoFilter range and the FilterOperatorType
- Save output XLSX file
The code snippet below shows how to add Custom AutoFilter with Contains using C#:
Remove or Delete AutoFilter in Excel using C#
As we have learned different scenarios of adding AutoFilters in Excel files using C#, let us check out the other way around. The use case of removing or deleting the AutoFilters from Excel is equally important and pivotal. You need to follow the steps below for removing all AutoFilters from an XLSX file:
- Load input XLSX file
- Iterate through each worksheet and remove AutoFilters
- Save output file
The following code snippet shows how to remove AutoFilters from Excel files using C#:
Conclusion
Aspose.Cells for .NET API offers you different out of the box features. Adding, removing, or deleting an AutoFilter using C# in .NET Framework is simple and easy. We have discussed several possibilities in detail. In case you have any doubts or concerns, please feel free to contact us at Free Support Forum.
