Aspose Excel ReportFilter

[shilpa.mehta]

We want to add a column to the ReportFilter Area with multiselect option enabled and preselected some values.

Attached an image for example.

Ty.

[Amjad_Sahi]

Hi,

Thanks for your query.

Please see the sample line of code to accomplish your task (add field to Report Filter area) for your reference:

e.g

Sample code:

…

pivotTable.addFieldToArea(PivotFieldType.PAGE, 1);

Hope, this helps a bit.

Thank you.

[babar.raza]

Hi Shilpa,

Thank you for contacting Aspose support.

In order to drag a field to the report filter area, you have to add it as Page. Moreover, in order to enable the multiple selection, please use the PivotField.setMultipleItemSelectionAllowed method whereas for selection/de-section of a filter item, please use the PivotField.hideItem method.

Please check the following piece of code which creates a Pivot Table from scratch and add a field to report filter. It also demonstrates how to deselect an item from the filter. Please feel free to amend the code as per your application requirements.

Java

//Instantiating an Workbook object
Workbook workbook = new Workbook(dir + "data.xlsx");
//Adding a new sheet
int sheetIndex = workbook.getWorksheets().add();
Worksheet sheet2 = workbook.getWorksheets().get(sheetIndex);
//Naming the sheet
sheet2.setName("PivotTable");
//Getting the pivottables collection in the sheet
PivotTableCollection pivotTables = sheet2.getPivotTables();
//Adding a PivotTable to the worksheet
int index = pivotTables.add("=Data!A1:F30", "B3", "PivotTable1");
//Accessing the instance of the newly added PivotTable
PivotTable pivotTable = pivotTables.get(index);
//Showing the grand totals
pivotTable.setRowGrand(true);
pivotTable.setColumnGrand(true);
//Setting the PivotTable report is automatically formatted
pivotTable.setAutoFormat(true);
//Setting the PivotTable autoformat type.
pivotTable.setAutoFormatType(PivotTableAutoFormatType.REPORT_6);

//Draging the third field to the row area.
pivotTable.addFieldToArea(PivotFieldType.ROW, 2);
//Draging the second field to the row area.
pivotTable.addFieldToArea(PivotFieldType.ROW, 1);
//Draging the fourth field to the column area.
pivotTable.addFieldToArea(PivotFieldType.COLUMN, 3);
//Draging the fifth field to the data area.
pivotTable.addFieldToArea(PivotFieldType.DATA, 5);
//Draging the 1st field to filter area
index = pivotTable.addFieldToArea(PivotFieldType.PAGE, 0);

PivotField pf = pivotTable.getPageFields().get(index);
//Enabling multiple selection
pf.setMultipleItemSelectionAllowed(true);
//Deselecting first options
pf.hideItem("David", true);

//Setting the number format of the first data field
pivotTable.getDataFields().get(0).setNumber(7);
//Saving the Excel file
workbook.save(dir + "pivot.xlsx");