Pivot Table Collapse

Aspose.Cells Product Family
1

Hi,


Is there a way to automatically collapse all fields of a pivot table?

thanks
2

Hi,


You can do so by hiding the details of RowFields of pivot table. You also need to set the RefreshDataOnOpeningFile to true to get it to working. I have achieved this using following lines of code with Aspose.Cells for Java v7.2.1.7 as the latest fix:

//Create a new Workbook
Workbook workbook = new Workbook("E://Pt.xlsx");
//Get the worksheet containing the Pivot Table
Worksheet sheet = workbook.getWorksheets().get(1);
//Hide the details
sheet.getPivotTables().get(0).getRowFields().get(0).hideDetail(true);
//Set the RefreshDataOnOpeningFile flag
sheet.getPivotTables().get(0).setRefreshDataOnOpeningFile(true);
//Save the workbook
workbook.save("E://PivotCollapse.xlsx");

You can see the output in the attached screenshot.

3

Thanks for the reply.


I tried this, but it doesn’t collapse the entire field but rather hides the first member of the first row label.
4

Hi,


Please see my edited reply in the previous post. I have modified it and also attached a screenshot as well for your reference. If this is not what you want, please send us a screenshot of your excel file what you want to achieve and we will look into it.

5

Great! It worked! Is this only available in v7.x.x and not in 2.5.4?

6

Hi,

I have checked the code in the older version and found that it is available in the older version too.

Below is a code with little modifications that will run on the older version

Java


//Create a new Workbook

Workbook workbook = new Workbook();

workbook.open(“E:\Pt.xlsx”);


//Get the worksheet containing the Pivot Table

Worksheet sheet = workbook.getWorksheets().getSheet(1);

//Hide the details

sheet.getPivotTables().get(0).getRowFields().get(0).hideDetail(true);

//Set the RefreshDataOnOpeningFile flag

sheet.getPivotTables().get(0).setRefreshDataOnOpeningFile(true);

//Save the workbook

workbook.save(“E://PivotCollapse.xlsx”);

7
I am using 2.5.4 and there's no hideDetail method in there. Do you have a patch for 2.5.4?
8

Hi,


We recommend you to upgrade your product to the latest version of Aspose.Cells for Java. This will save you time in your development as it has a number of issues fixed, that have been reported by our valued customers from time to time, and additional features added as well.

Moreover, all the new versions are based on our latest fixes. In addition, we are normally not able to provide support with previous/old versions. You can download and try the latest fix here: Aspose.Cells for Java v7.2.1.7

9

Hi,

Thanks for letting me know.

I tested the code on Aspose.Cells for Java v2.5.4.20.

But since you do not have this patch, so you will have to upgrade your code to latest version.

10

I understand that we really need to upgrade to the latest version. But it takes time for us because we need to follow the company process. Is it possible that you give me the 2.5.4.20 version?


thanks!
11

Hi,

I am afraid, this is against our policy to provide versions older than one year.