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.
- Add Report Filter to Pivot Table in Excel in C#
- Add Filter to a Row Field of Pivot Table in Excel in C#
- Add Filter to a Column Field of Pivot Table in Excel in C#
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();
}
}
}
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();
}
}
}
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();
}
}
}
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.
