We're sorry Aspose doesn't work properply without JavaScript enabled.

Free Support Forum - aspose.com

Aspose Excel ReportFilter

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

Attached an image for example.

Ty.

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.

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");