C#: Add Filters to Pivot Tables in Excel

2024-10-14 01:02:04 Written by  support iceblue
Rate this item
(0 votes)

Filters in pivot tables enable users to narrow down the displayed data based on specific criteria. By adding filters, users can focus on subsets of data that are most relevant to their analysis, allowing for a more targeted and efficient data exploration. In this article, we will demonstrate how to add filters to pivot tables in Excel in C# using Spire.XLS for .NET.

Install Spire.XLS for .NET

To begin with, you need to add the DLL files included in the Spire.XLS for.NET package as references in your .NET project. The DLL files can be either downloaded from this link or installed via NuGet.

PM> Install-Package Spire.XLS

Add Report Filter to Pivot Table in Excel in C#

Spire.XLS for .NET offers the XlsPivotTable.ReportFilters.Add() method to add report filters to a pivot table. The detailed steps are as follows.

  • Create an object of the Workbook class.
  • Load an Excel file using Workbook.LoadFromFile() method.
  • Get a specific worksheet in the file using Workbook.Worksheets[index] property.
  • Get a specific pivot table in the worksheet using Worksheet.PivotTables[index] property.
  • Create a report filter using PovotReportFilter class.
  • Add the report filter to the pivot table using XlsPivotTable.ReportFilters.Add() method.
  • Save the resulting file using Workbook.SaveToFile() method.
  • C#
using Spire.Xls;
using Spire.Xls.Core.Spreadsheet.PivotTables;

namespace AddReportFilter
{
    internal class Program
    {
        static void Main(string[] args)
        {
            // Create an object of the Workbook class
            Workbook workbook = new Workbook();
            // Load an Excel file
            workbook.LoadFromFile("Sample.xlsx");

            // Get the first worksheet
            Worksheet sheet = workbook.Worksheets[0];

            // Get the first pivot table
            XlsPivotTable pt = sheet.PivotTables[0] as XlsPivotTable;

            // Create a report filter
            PivotReportFilter reportFilter = new PivotReportFilter("Product", true);

            // Add the report filter to the pivot table
            pt.ReportFilters.Add(reportFilter);

            // Save the resulting file
            workbook.SaveToFile("AddReportFilter.xlsx", FileFormat.Version2016);
            workbook.Dispose();
        }
    }
}

C#: Add Filters to Pivot Tables in Excel

Add Filter to a Row Field of Pivot Table in Excel in C#

You can add a value filter or label filter to a specific row field in a pivot table using the XlsPivotTable.RowFields[index].AddValueFilter() or XlsPivotTable.RowFields[index].AddLabelFilter() method. The detailed steps are as follows.

  • Create an object of the Workbook class.
  • Load an Excel file using Workbook.LoadFromFile() method.
  • Get a specific worksheet in the file using Workbook.Worksheets[index] property.
  • Get a specific pivot table in the worksheet using Worksheet.PivotTables[index] property.
  • Add a value filter or label filter to a specific row field in the pivot table using XlsPivotTable.RowFields[index].AddValueFilter() or XlsPivotTable.RowFields[index].AddLabelFilter() method.
  • Calculate the data in the pivot table using XlsPivotTable.CalculateData() method.
  • Save the resulting file using Workbook.SaveToFile() method.
  • C#
using Spire.Xls;
using Spire.Xls.Core.Spreadsheet.PivotTables;

namespace AddRowFilter
{
    internal class Program
    {
        static void Main(string[] args)
        {
            // Create an object of the Workbook class
            Workbook workbook = new Workbook();
            // Load an Excel file
            workbook.LoadFromFile("Sample.xlsx");

            // Get the first worksheet
            Worksheet sheet = workbook.Worksheets[0];

            // Get the first pivot table
            XlsPivotTable pt = sheet.PivotTables[0] as XlsPivotTable;


            // Add a value filter to the first row field in the pivot table
            pt.RowFields[0].AddValueFilter(PivotValueFilterType.GreaterThan, pt.DataFields[0], 5000, null);
            // Or add a label filter to the first row field in the pivot table
            //pt.RowFields[0].AddLabelFilter(PivotLabelFilterType.Equal, "Mike", null);

            // Calculate the pivot table data
            pt.CalculateData();

            // Save the resulting file
            workbook.SaveToFile("AddRowFilter.xlsx", FileFormat.Version2016);
            workbook.Dispose();
        }
    }
}

C#: Add Filters to Pivot Tables in Excel

Add Filter to a Column Field of Pivot Table in Excel in C#

To add a value filter or label filter to a specific column field in a pivot table, you can use the XlsPivotTable.ColumnFields[index].AddValueFilter() or XlsPivotTable.ColumnFields[index].AddLabelFilter() method. The detailed steps are as follows.

  • Create an object of the Workbook class.
  • Load an Excel file using Workbook.LoadFromFile() method.
  • Get a specific worksheet in the file using Workbook.Worksheets[index] property.
  • Get a specific pivot table in the worksheet using Worksheet.PivotTables[index] property.
  • Add a value filter or label filter to a specific column field in the pivot table using XlsPivotTable.ColumnFields[index].AddValueFilter() or XlsPivotTable.ColumnFields[index].AddLabelFilter() method.
  • Calculate the data in the pivot table using XlsPivotTable.CalculateData() method.
  • Save the resulting file using Workbook.SaveToFile() method.
  • C#
using Spire.Xls;
using Spire.Xls.Core.Spreadsheet.PivotTables;

namespace AddColumnFilter
{
    internal class Program
    {
        static void Main(string[] args)
        {
            // Create an object of the Workbook class
            Workbook workbook = new Workbook();
            // Load an Excel file
            workbook.LoadFromFile("Sample.xlsx");

            // Get the first worksheet
            Worksheet sheet = workbook.Worksheets[0];

            // Get the first pivot table
            XlsPivotTable pt = sheet.PivotTables[0] as XlsPivotTable;

            // Add a label filter to the first column field in the pivot table
            pt.ColumnFields[0].AddLabelFilter(PivotLabelFilterType.Equal, "Laptop", null);
            // Or add a value filter to the first column field in the pivot table
            // pt.ColumnFields[0].AddValueFilter(PivotValueFilterType.Between, pt.DataFields[0], 5000, 10000);


            // Calculate the pivot table data
            pt.CalculateData();

            // Save the resulting file
            workbook.SaveToFile("AddColumnFilter.xlsx", FileFormat.Version2016);
            workbook.Dispose();
        }
    }
}

C#: Add Filters to Pivot Tables in Excel

Apply for a Temporary License

If you'd like to remove the evaluation message from the generated documents, or to get rid of the function limitations, please request a 30-day trial license for yourself.