To view the large amount of Excel data's easily, Spire.XLS for .NET supports create Pivot table and add excel table with filters. Spire.XLS also offers a method of pivotTable.ReportFilters.Add(); to enable developers to add the report filter to the pivot table.
This article will show you how to add a report filter to the Excel Pivot table in C#.
Note: Before Start, please download the latest version of Spire.XLS and add Spire.xls.dll in the bin folder as the reference of Visual Studio.
Firstly, please check the original screenshot of excel pivot table.
Step 1: Create a new workbook and load from file.
Workbook workbook = new Workbook();
workbook.LoadFromFile("Sample.xlsx");
Step 2: Get the PivotTable from the Excel worksheet.
Spire.Xls.Core.Spreadsheet.PivotTables.XlsPivotTable pivotTable = workbook.Worksheets["Pivot Table"].PivotTables[0] as Spire.Xls.Core.Spreadsheet.PivotTables.XlsPivotTable;
Step 3: Add a filter to the pivot table.
PivotReportFilter filter = new PivotReportFilter("JAN", true);
pivotTable.ReportFilters.Add(filter);
Step 4: Save the document to file and launch to preview it.
workbook.SaveToFile("Result.xlsx", ExcelVersion.Version2010);
System.Diagnostics.Process.Start("result.xlsx");
Effective screenshot after add a filter to the pivot table:
Full codes:
using Spire.Xls;
using Spire.Xls.Core.Spreadsheet.PivotTables;
namespace AddReportFilter
{
class Program
{
static void Main(string[] args)
{
Workbook workbook = new Workbook();
workbook.LoadFromFile("Sample.xlsx");
Spire.Xls.Core.Spreadsheet.PivotTables.XlsPivotTable pivotTable = workbook.Worksheets["Pivot Table"].PivotTables[0] as Spire.Xls.Core.Spreadsheet.PivotTables.XlsPivotTable;
PivotReportFilter filter = new PivotReportFilter("JAN", true);
pivotTable.ReportFilters.Add(filter);
workbook.SaveToFile("Result.xlsx", ExcelVersion.Version2010);
System.Diagnostics.Process.Start("result.xlsx");
}
}
}
