How can I coerce generated values in a PivotTable to adopt a specific format?

Aspose.Cells Product Family
1
I have code to create data fields (and then give their labels user-friendly strings) like so:

pivotTable.AddFieldToArea(PivotFieldType.Data, TOTALQTY_COLUMN);
pivotTable.AddFieldToArea(PivotFieldType.Data, TOTALPRICE_COLUMN);
pivotTable.AddFieldToArea(PivotFieldType.Data, AVGPRICE_COLUMN);
pivotTable.AddFieldToArea(PivotFieldType.Data, PERCENTOFTOTAL_COLUMN);
pivotTable.DataFields[0].DisplayName = "Total Packages";
pivotTable.DataFields[1].DisplayName = "Total Purchases";
pivotTable.DataFields[2].DisplayName = "Avg Purchase";
pivotTable.DataFields[3].DisplayName = "% of Total";

With this, I end up with values like those seen in the screenshot below.

I want commas added to "Total Packages" so that for any value over 999, a comma will appear ("1,000" instead of "1000")

I also want dollar signs prepended to the "Total Purchases" values (so that, for instance, "14042.56" becomes "$14,042.56")

Also, I want "Avg Purchases" values such as "33.2" to instead be "33.20" (always two and exactly two values following the decimal point)

Finally, I want a percent sign appended to the "% of Total" values, so that "0.76" becomes "0.76%"

I thought the following might work:

pivotTable.DataFields[3].DataDisplayFormat = PivotFieldDataDisplayFormat.

...but there doesn't seem to be the right type of options for that to accomplish what I want.

What code is needed to make this work?

2

Hi,


Thanks for your posting and considering Aspose.Cells.

Please spare us some time. We will look into it and update you asap.

3

Hi,


Thanks for your posting and using Aspose.Cells.

Please see the following sample code. I have attached its source excel and output excel file for your reference. It should fix your issue. Let us know your feedback.

C#
var book = new Workbook(“AsposeTest32.xlsx”);
var sheet = book.Worksheets[“PivotTableSheet”];
PivotTable pivot = sheet.PivotTables[0];
pivot.DataFields[1].Number = 7;
pivot.DataFields[2].Number = 4;
pivot.DataFields[3].Number = 9;

pivot.RefreshDataFlag = true;
pivot.RefreshData();
pivot.CalculateData();
pivot.RefreshDataFlag = false;

book.Save(“AsposeTest32_out.xlsx”);


4

Thanks; that worked great!

5

Hi,


Good to know that your issue is sorted out now. Feel free to contact us if you need further help or have some other issue or queries, we will be happy to assist you soon.

Thank you.