Hi,
Is the “Aspose.Cells for Java” package able to support the processing of pivot table data for each individual item within the pivot table filter. Please see attached image: the value in cells C34:U46 change depending on the items selected from the filter in cell C29.
pivot filters.png (38.9 KB)
FYI: the filter is set to (All) by default.
I need to extract the pivot table data for each individual filter item (DAX OIL, LUBNOR, MANGUINHOS, etc.) for developing a data parser. Could you please let me know if this is possible using "“Aspose.Cells for Java”?
Thanks,
@roger_lau,
Thanks for the screenshot and some details.
Aspose.Cells supports pivot filters, so you may apply filters on Pivot report for your needs, see the sample code segment on how to use pivot filters using relevant APIs for a PivotTable for your reference:
e.g
Sample code:
Workbook workbook = new Workbook("Book1.xlsx");
//Get Worksheet having PivotTable
Worksheet worksheet = workbook.getWorksheets().get(1);
//Get first PivotTable
PivotTable table = worksheet.getPivotTables().get(0);
//Add first PivotFilter
int index = table.getPivotFilters().add(0, PivotFilterType.VALUE_EQUAL);
//Get PivotFilter by Index
PivotFilter filter = table.getPivotFilters().get(index);
//Set various properties
filter.setValue1("Cookies");
//filter.setMeasureFldIndex(0);
//Add another PivotFilter of the type Count
index = table.getPivotFilters().add(1, PivotFilterType.COUNT);
//Get PivotFilter by Index
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(2, FilterOperatorType.GREATER_THAN, 1);
workbook.save("out1.xlsx");
Hope, this helps a bit.
Hi Amjad,
Thanks for sharing the sample code. Could you also share the excel file that was used as the basis for the sample code?
Your help is much appreciated.
@roger_lau,
Please spare us some time. We will provide you sample Excel file as soon as possible. Thanks for your patience and have a good day.
@roger_lau,
Please find attached the input and output files in the zipped archive, The sample code is just for demonstration purpose for your reference as some code segments (in the example) might be irrelevant.
files1.zip (22.3 KB)
A post was split to a new topic: Render PivotTable to Image