Hello,
We are using Aspose Cells 7.7.1.0.
I would like to create a pivot table with report filter, like the example in Excel (see attachment).
To achieve this, I’ve used the code below:
Worksheet pivotSheet = workbook.Worksheets[pivotSheet1];
//Getting the pivottables collection in the sheet
Aspose.Cells.Pivot.PivotTableCollection pivotTables = pivotSheet.PivotTables;
//Adding a PivotTable to the worksheet
int pivotIdx = pivotTables.Add(string.Format("={0}!A1:AY100", dataSheet), “A3”, “PivotTable1”);
//Accessing the instance of the newly added PivotTable
Aspose.Cells.Pivot.PivotTable pivotTable = pivotTables[pivotIdx];
//Showing the grand totals
pivotTable.RowGrand = true;
pivotTable.ColumnGrand = true;
//Setting the PivotTable report is automatically formatted
pivotTable.IsAutoFormat = true;
//Setting the PivotTable autoformat type.
pivotTable.AutoFormatType = Aspose.Cells.Pivot.PivotTableAutoFormatType.Report9;
//Columns
pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Column, 4); //TYPE ONGEVAL
pivotTable.ColumnFields[0].IsAutoSort = pivotTable.ColumnFields[0].IsAscendSort = true;
//Page / Report filter
pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Page, 6); //INTERN/EXTERN
//Data
pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Data, 2); //NAAM/VOORNAAM
//Rows
pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Row, 12); //BU
pivotTable.RowFields[0].IsAutoSort = pivotTable.RowFields[0].IsAscendSort = true;
pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Row, 14); //TEAM
pivotTable.RowFields[1].IsAutoSort = pivotTable.RowFields[1].IsAscendSort = true;
The line with ‘PivotFieldType.Page’ has no effect. How can I add one or more report filters?
I’ve read about PivotFilters, but I can’t find an example, with an explanation of what it actually does.
Please provide some assistance.
Regards,
Wim.