How to keep % format for Pivot table

Aspose.Cells Product Family
1

Sample File.zip (11.0 KB)

Hello Team,
I have an issue where one of my fields which is storing % values is getting lost while creating Pivot table out of the same data.

Can you help how this can be done using Aspose.cells java.

Attaching sample excel as well. Basically I want Marks column as % in Pivot table and not the values that are being shown.

Same can be done manually in excel using Value Field Settings -> Show Values As -> Number Format and select % option.

2

@sourav24,
You can use the following code to achieve the goal. Please refer to the attachment (12.2 KB).

Workbook book = new Workbook("Sample File.xlsx");
PivotTable pivot = book.getWorksheets().get(0).getPivotTables().get(0);
PivotField secondData =  pivot.getDataFields().get(1);
secondData.setNumber(10);
pivot.setRefreshDataFlag(true);
pivot.refreshData();
pivot.calculateData();
pivot.setRefreshDataFlag(false);

book.save("out_java.xlsx");

Hope helps a bit.

3

Thanks @John.He for your response. I have few other questions related to the same Pivot table.

First, I am keeping a blank Pivot as template and then putting in data and doing a refresh on it. Now even after refresh with data, a blank row is remaining in the Pivot.

How can this be removed?

Secondly, how can I apply autoFitColumns() on a Pivot table?

And lastly, is it possible to format all numeric data fields of the Pivot to say 2 decimal places? (basically round off)

4

If you are talking about blank row in your template Excel file (you shared), this is due to data source set for the pivot table which also includes blank records. See the screenshot (attached) which demonstrates that the source range is “Sheet1!$E$1:$G$7” instead of “Sheet1!$E$1:$G$6”, so you got to make sure no blank records are included when specifying the source data for pivot table.
sc_shot1.png (108.6 KB)

I think you may try using Worksheet.autoFitColumns after you have refreshed and calculated pivot table data in code.

You may try to use PivotField.setNumberFormat(“0.00”) or PivotField.setNumber(2) for data pivot field(s).