Adding Filter to the Pivot Table in Excel worksheet in .NET

Aspose.Cells Product Family
1

I have an Excel workbook with Pivot table created in MS Excel and would like to add filters in “Row”, “Filter” and “Column” area using Aspose.Cell. Please can you suggest if it is possible using Aspose component?


Thank you,
Suresh
2

Hi,

Thanks for your posting and using Aspose.Cells for .NET

Please download and try the latest version:
Aspose.Cells for .NET v7.5.0.5

Please try the following sample code.

I have also attached the source and output xlsx file used in and generated by this code.

C#


string filePath = @“F:\Shak-Data-RW\Downloads\filter.xlsx”;;


Workbook workbook = new Workbook(filePath);


PivotTable table = workbook.Worksheets[1].PivotTables[0];

int index = table.PivotFilters.Add(1, PivotFilterType.Count);

PivotFilter filter = table.PivotFilters[index];

filter.AutoFilter.FilterTop10(0, true, false, 1);

filter.MeasureFldIndex = 3;

index = table.PivotFilters.Add(1, PivotFilterType.CaptionGreaterThan);

filter = table.PivotFilters[index];

filter.Value1 = “1”;

filter.AutoFilter.Custom(0, FilterOperatorType.GreaterThan, 1);


index = table.PivotFilters.Add(0, PivotFilterType.Count);

filter = table.PivotFilters[index];

filter.AutoFilter.FilterTop10(0, true, false, 10);

filter.MeasureFldIndex = 0;


workbook.Save(filePath + “.out.xlsx”);

3

Hi Shakeel,


Thanks for the quick response!

Thank you,
Suresh