How can I replace automatically-generated grand total values with different ones?

Aspose.Cells Product Family
1
My spreadsheet automatically generates a "Grand Totals" column as the rightmost column (see screenshot):

This is nice, in general. But in specific, I've got a couple of problems with it: The last two values (with the unfortunate labels "Sum of Avg Price" and "Sum of Percentage") provide just that - a sum of the previous columns. In those cases, I don't want a simple sum, but an average in the first case and a percentage in the second case.

For the AvgPrice, what I need is a calculation of "Sum of Total Price" / "Sum of Total Quty" in the Grand Total column. For instance, the first AvgPrice Grand Total value should be "33.14" rather than "66.26"

For the Percentage, I need the percentage of Total Price for the item/Description (such as "25151.75" seen in the first item above) as compared to the "Total Price" value in the "Total Sum of Total Price" grand total row/column ("1529802.82"). That value is seen in one of the screenshots added.

So the "Percentage" value for that first item ("ASPARAGUS, LARGE 11/1#") should be approximately 1.6 (as 25151.75 is about 1/60th of 1529802.82), rather than 1.36.

Is there a way to set this up to automatically generate those values in the Grand Total Column, or do I need to prevent the Grand Total column from being generated like so:

pivotTable.ColumnGrand = false;

...and then add that column to the sheet manually, doing the calculations in code, and adding those values that way?
2

Hi,

Thanks for your posting and using Aspose.Cells.

In order to investigate this issue, please download the attached source excel file and modify the pivot table via MS-Excel as per your expected pivot table. Then provide us your expected pivot table file. We will then look into it via Aspose.Cells code and provide you an equivalent code which you will then try in your original report code and it should fix the issue hopefully. Thanks for your cooperation in this regard and have a good day.

3

Thanks; I don’t know how to do it in Excel proper; I do it all in code. I am now just generating the Grand Total column manually, since there seems to be no easy way to override bits and pieces of the auto-generated column.

4

Hi,


Thanks for your posting and using Aspose.Cells.

Please provide us the excel file you have generated by your code as you have shown in the images above in your first post and the then change it manually via Microsoft Excel to create your expected excel file. How can you say, that you do not know how to create your expected excel file manually via Microsoft Excel, if this is so, it means such a feature is not available in Microsoft Excel. If something is not support with Microsoft Excel, then it will also not be supported by Aspose.Cells automatically. Because Aspose.Cells provides the subset of Microsoft Excel functionalities. Thanks for your understanding and cooperation. Have a good day.

5

I didn’t say it’s not possible to do it directly in Excel; I said I don’t know how to do it that way. Anyway, rather than allow the grand totals to be generated and then overwrite certain values, I ended up creating my own manually and then “tacking it on” to the right of the pivot table; the code for how I did that is here: excel - How can I replace automatically-generated grand total values with different ones (Aspose Cells)? - Stack Overflow

6

Hi,


Thanks for your posting and using Aspose.Cells.

It is good to know that you managed to sort out this issue and provided the sample code on stackoverflow for other’s help. However, if you could try these things manually in Microsoft Excel, then you could provide us expected excel files. So if you have managed to do it via code, then you will definitely manage to do it via Microsoft Excel because it is easier than code.

7

Hi,


Thanks for using Aspose.Cells.

We have looked into this issue further, so please check the following code, it should fulfill your needs.

C#
var book = new Workbook(filePath + “AsposeTest32.xlsx”);
var sheet = book.Worksheets[“PivotTableSheet”];
PivotTable pivot = sheet.PivotTables[0];

pivot.DataFields[1].DataDisplayFormat = PivotFieldDataDisplayFormat.PercentageOfColumn;
pivot.DataFields[2].Function = ConsolidationFunction.Average;

pivot.RefreshDataFlag = true;
pivot.RefreshData();
pivot.CalculateData();
pivot.RefreshDataFlag = false;
book.Save(filePath + “out.xlsx”);