Pivot Table report filter

Aspose.Cells Product Family
1

Hello,


I have the attached source document “Source.xlsx” with a Picot table. I would like to add a report filter using the Site column as shown in “Desired Output.xlsx”. How can I achieve this? I have attempted to use the following code:

Aspose.Cells.Pivot.PivotTableCollection pivotTables = ws.PivotTables;
Aspose.Cells.Pivot.PivotTable table = pivotTables[0];
int index = table.PivotFilters.Add(3, Aspose.Cells.Pivot.PivotFilterType.Count);
Aspose.Cells.Pivot.PivotFilter filter = table.PivotFilters[index];
filter.AutoFilter.AddFilter(3, “1”);

However this throws an exception stating that the pivot index is out of range

Thanks
2

Hi,


See the sample code below for your reference:
e.g
Sample code:

string filePath = “e:\test2\” + “source.xlsx”;
Workbook workbook = new Workbook(filePath);
Worksheet ws = workbook.Worksheets[0];
PivotTableCollection ptc = ws.PivotTables;
int index = ptc.Add("=Sheet1!A1:C5", “A22”, “PivotTable2”);
PivotTable table = ptc[index];

table.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Page,“Site”);
table.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Row, “Name”);
table.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Data, “Value”);

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


Hope, this helps a bit.

Thank you.

3

Yes, that worked, thanks.

4

Hi,


Good to know that it figures out your issue now. Feel free to contact us any time if you need further help or have some other issue or queries, we will be happy to assist you soon.

Thank you.