Programmatically Apply Filtering to Excel XLSX Files in C# .NET

What You Will Need

• Visual Studio
• NuGet
• .NET Framework & .NET Core

Controls Referenced

• Document Solutions for Excel, .NET Edition (DsExcel) - C# .NET Excel Library
• Documentation | Online Demo Explorer

Tutorial Concept

Learn to apply advanced data filtering with a C# .NET Excel library. Leverage effortless data management and enhanced filtering options for better Excel data handling.

Have you ever wondered what makes big data manageable? It's all about effective filtering.

In the era of big data, the ability to efficiently sift through vast datasets to extract meaningful insights is crucial. Whether you're a data analyst, a software developer, or someone else who works with a lot of data, efficient data handling can significantly enhance productivity and accuracy. With the right filtering techniques, you can focus on the important part of your data and generate a precise report.

MESCIUS offers the Document Solutions for Excel (DsExcel) API for managing the Excel file programmatically and generating advanced Excel reports through .NET and Java apps. DsExcel provides multiple filtering techniques for various scenarios, like generating data, creating pivots, or using templates to create reports. In this blog post, we will take a closer look at the advanced filtering techniques available in DsExcel.

Data Filtering Techniques Using .NET and Java Excel API Libraries

• General Excel Filtering
• Pivot Table Filtering
• Template Data Filtering

General Excel Filtering

When generating Excel data, there can be situations where you need to show the filtered result. This can be done using the Excel filtering feature. To apply this filtering via code, you can use the AutoFilter method of the DsExcel API with the appropriate FilterOperator and desired filter value.

DsExcel supports the following types of auto filters:

• Number Filters
• Multi Select Filters
• Text Filters
• Date Filters
• Dynamic Date Filters
• Filters by Cell Color
• Filters by No Fill
• Filters by Icon
• Filters by No Icon

Let’s quickly explore how to apply some of them in DsExcel on the following Excel data:

Here, we apply the icon and number filters to filter out the rows with a green round icon in the weight column and a height value of less than 180. Below is the DsExcel code to achieve this:

    IIconSetCondition iconset = worksheet.Range["E2:E7"].FormatConditions.AddIconSetCondition();
    iconset.IconSet = workbook.IconSets[IconSetType.Icon3TrafficLights1];
    //filter in the rows which icon is green light.
    worksheet.Range["A1:F7"].AutoFilter(4, workbook.IconSets[IconSetType.Icon3TrafficLights1][2], AutoFilterOperator.Icon);
    //Height less than 80.
    worksheet.Range["A1:F7"].AutoFilter(5, "<180");

The result after applying this filtering is shown below:

You can also apply the AutoFilter method without any parameter, enabling the filter header in the top row of the exported Excel, where the end user can apply the filtering at their convenience.

MESCIUS offers another product line, SpreadJS, that provides JavaScript spreadsheet solutions for browsers. This product supports filtering directly in the column header. To apply this type of filtering using DsExcel, you can set the isFirstRowData parameter of the AutoFilter method to True. The effect is only visible in a SpreadJS(SJS) exported file, as shown below:

Pivot Table Filtering

Pivot tables offer a powerful and efficient way to summarize, analyze, and explore large datasets. Their ability to dynamically rearrange data enables users to view information from different perspectives, making it easier to uncover patterns and trends. With smart filtering, you can refine your analyses by isolating data points based on criteria such as date ranges, geographic regions, product categories, or any other relevant factors.

DsExcel provides support for both label and value filters in the pivot table. You can apply these filters by adding the IPivotFilter object in the PivotFilters collection. You can use value and label filters in the same field by setting the AllowMultipleFilters property of the IPivotTable interface to True.

Let’s apply these filters to the following pivot data.

Below is the DsExcel code for filtering the Product data field where its name should contain “mi” and the amount should be greater than $7000.

    //access pivot table
    var pivottable = worksheet.PivotTables[0];
    //access pivot table's fields
    var field_Product = pivottable.PivotFields["Product"];
    //Allows adding label filter and value filter to a field at the same time.
    pivottable.AllowMultipleFilters = true;
    // Filter for products with sales volume greater than 7000.
    field_Product.PivotFilters.Add(PivotFilterType.ValueGreaterThan, 7000, options: new PivotFilterOptions { AppliedDataField = 0 });
    // Filter for products where the product name contains 'mi'.
    field_Product.PivotFilters.Add(PivotFilterType.CaptionContains, "mi");

The updated pivot table after applying the filtering is shown below:

The general Excel and pivot table filters temporarily hide the irrelevant information in the output. In the upcoming section, we will discover how to apply filtering via the DsExcel template to retrieve only the filtered data from the data source.

Template Data Filtering

Report generation for business data is crucial in every domain. By using the template functionality of DsExcel, you can automate the process of populating spreadsheets with data from various sources, making it ideal for generating consistent and professional reports. DsExcel has introduced filtering functionality in the template language in the v7.2 release, where you can filter out the result from the data source to show only relevant information in the generated output. You can apply two types of filtering using a template:

• Conditional filters
• Slice filters

Conditional Filters

Conditional filters offer an effective way to refine data by applying specific criteria using operators and keywords like AND, OR, NOT, and LIKE. Whether you're dealing with large datasets or need to find records that match multiple criteria, conditional filters are indispensable. The syntax of this filter is as follows:

F/Filter = (field1 > 1 AND field2 = 2 OR field3 <> 3) 

Let’s populate the following data table via template to generate the report:

    var datasource = new DataTable();
        datasource.Columns.Add(new DataColumn("oid", typeof(string)));
        datasource.Columns.Add(new DataColumn("cid", typeof(string)));
        datasource.Columns.Add(new DataColumn("pid", typeof(string)));
        datasource.Columns.Add(new DataColumn("count", typeof(double)));
        datasource.Rows.Add("TF00001", "C001", "R001", 1);
        datasource.Rows.Add("TF00002", "C001", "T002", 1);
        datasource.Rows.Add("TF00003", "C001", "W003", 1);
        datasource.Rows.Add("TF00004", "C001", "C004", 1);
        datasource.Rows.Add("TF00005", "C001", "O005", 1);
        datasource.Rows.Add("TF00006", "C002", "R001", 1);
        datasource.Rows.Add("TF00007", "C002", "T002", 1);
        datasource.Rows.Add("TF00008", "C002", "W003", 1);
        datasource.Rows.Add("TF00009", "C002", "C004", 1);
        datasource.Rows.Add("TF00010", "C002", "O005", 1);
        datasource.Rows.Add("TF00011", "C003", "W009", 5);
        datasource.Rows.Add("TF00012", "C003", "R001", 2);
        datasource.Rows.Add("TF00013", "C003", "T002", 1);
        datasource.Rows.Add("TF00014", "C003", "W003", 3);
        datasource.Rows.Add("TF00015", "C004", "L010", 10);
        datasource.Rows.Add("TF00016", "C005", "B008", 999);
        datasource.Rows.Add("TF00017", "C006", "C007", 23);
        datasource.Rows.Add("TF00018", "C007", "N011", 1);
        datasource.Rows.Add("TF00019", "C007", "G012", 10);
        datasource.Rows.Add("TF00020", "C008", "P013", 10);
        datasource.Rows.Add("TF00021", "C008", "P014", 15);
        datasource.Rows.Add("TF00022", "C008", "S015", 2);
        workbook.AddDataSource("order", datasource);

Now, let’s look at the following template structure with the conditional filtering on the Order Id column to show the records where the order cid is ‘C002’ and the pid is ‘W003.’

After populating the data in this template, the output appears as follows:

Slice Filters

The slice filter feature allows you to extract a specific range of data between two indices. This type of filter is ideal for situations where you need to analyze a subset of your data, such as pulling the top 10 results or examining a specific segment within a dataset. The syntax for applying a Slice Filter is straightforward where: the start index marks the beginning of the filter, the stop index defines its end, and the step shows the value by which the index increases with each step.

F/Filter = [start:stop:step]

Let’s look at the following template structure with a slice filter to fetch all items at even indexes from the previously provided data table:

After populating the data in this template, the output appears as below:

To learn more about template filtering, you can check out this documentation.

Use this sample to see the implementation of all the above-discussed filtering techniques.

Conclusion

In this blog, we reviewed different filtering techniques that DsExcel offers to analyze data more effectively. When we use these filters wisely, we can make better decisions based on our data. Explore our demos to see how DsExcel can improve your Excel experience with easy coding.

If you have any questions, feel free to ask in the comments below!

More References:

• Product Demo .NET
• Documentation .NET
• Product Demo JAVA
• Documentation JAVA