Applying new filters to pivot tables in .NET

Aspose.Cells Product Family
1

I am using Aspose.Cells to create pivot tables. I need to filter the row headers and column headers to exclude certain values as well as any displayed as (Blank). How do I do this in VB?

I have tried adding a filter, but when I do this and open the resultant file in Excel 2010, Excel crashes.

2

Hi,

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

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


Please try the following sample code. I have provided it both in C# and VB.NET.

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

C#


string filePath = @“D:\Documents and Settings\AHome\Desktop\Des\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”);


VB.NET

Dim filePath As String = “D:\Documents and Settings\AHome\Desktop\Des\filter.xlsx”

Dim workbook As Workbook = New Workbook(filePath)

Dim table As PivotTable = workbook.Worksheets(1).PivotTables(0)

Dim index As Integer = table.PivotFilters.Add(1, PivotFilterType.Count)

Dim filter As PivotFilter = 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”)