I have an Excel file that is created via Aspose Cells for Java. I added a data sheet then a pivot table, column fields, row fields and data fields. All is well until I attempt to add a PivotFilter to the table. Nothing shows up and the doc is lacking a bit. I have the following code. I am using cells-7.3.1.jar.
private PivotFilter setUpPivotFieldFilter(PivotTable pivotTable, ColumnType columnType) {
PivotFilterCollection pivotFilterCollection = pivotTable.getPivotFilters();
int fieldId = pivotFilterCollection.add(columnType.getExcelColumnNumber(), PivotFilterType.VALUE_EQUAL);
PivotFilter pivotFilter = pivotFilterCollection.get(fieldId);
return pivotFilter;
What am I missing here? Are there any example of adding a report filter to the pivot table?
Or can i somehow add a Slicer?
Thanks,
Marc
Hi,
Please see the sample code below with the attached template Excel file for your reference. I have also attached the output file here.
Sample code:
String filePath = “f:\files\filter.xlsx”; ;
Workbook workbook = new Workbook(filePath);
//Get Worksheet having PivotTable
Worksheet worksheet = workbook.getWorksheets().get(1);
//Get PivotTable
PivotTable table = worksheet.getPivotTables().get(0);
//Add first PivotFilter of Type Count
int index = table.getPivotFilters().add(1, PivotFilterType.COUNT);
//Get PivotFilter by Index
PivotFilter filter = table.getPivotFilters().get(index);
//Set Filter Top
filter.getAutoFilter().filterTop10(0, true, false, 1);
//Set Measuring Index Field
filter.setMeasureFldIndex(3);
//Add another PivotFilter
index = table.getPivotFilters().add(1, PivotFilterType.CAPTION_GREATER_THAN);
//Set various properties
filter = table.getPivotFilters().get(index);
filter.setValue1(“1”);
//Set AutoFilter
filter.getAutoFilter().custom(0, FilterOperatorType.GREATER_THAN, 1);
workbook.save(“f:\files\outfilters.xlsx”);
If you still have any problem, kindly provide us your sample code (runnable) and template Excel file(s), we will check your issue soon.
Thank you.