Search code examples
formattingpivot-tablenumber-formattingasposeaspose-cells

How can I coerce generated values in a PivotTable to adopt a specific format (Aspose Cells)?

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 so:

enter image description here

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?

Solution

  • Please follow this thread. I have provided the sample code, source and output excel files for your reference. Let us know your feedback.

    Thread Link

    Note: I am working as Developer Evangelist at Aspose