Extract pivot table data within filters

[roger_lau]

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,

[Amjad_Sahi]

@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.

[roger_lau]

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.

[shakeel.faiz]

@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.

[Amjad_Sahi]

@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)