How can I replace automatically-generated grand total values with different ones (Aspose Cells)?

Question

My spreadsheet automatically generates a "Grand Totals" column as the rightmost column:

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

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?

Answer 1

It's easiest, I think, to just add that column manually, calculating the necessary values; here's how I'm doing it now (basically the same idea as in Excel Interop - manually adding the Grand Total column):

After the PivotTable code, I call AddManualGrandTotalColumn(), which is:

private void AddManualGrandTotalColumn()
{
    var pivot = pivotTableSheet.PivotTables[0];
    var dataBodyRange = pivot.DataBodyRange;
    int rowsUsed = dataBodyRange.EndRow;
    int FIRST_DATA_ROW = 7;
    int currentQtyRow = FIRST_DATA_ROW;
    int ROWS_IN_A_RANGE = 4;

    // Needed?
    pivot.RefreshData();
    pivot.CalculateData();

    // Get Total Sales value, which will be needed for computing the % val
    Cell totalTotalPurchasesCell = pivotTableSheet.Cells[rowsUsed - 2, _grandTotalsColumnPivotTable + 1];
    decimal totalTotalPurchases = Convert.ToDecimal(totalTotalPurchasesCell.Value);

    Cell gtLabelCell = pivotTableSheet.Cells[6, _grandTotalsColumnPivotTable + 2];
    gtLabelCell.Value = "Grand Total";

    Cell QtyCell = null;
    Cell PriceCell = null;
    Cell AvgPriceCell = null;
    Cell PercentageCell = null;
    while (currentQtyRow < rowsUsed)
    {
        // SumTotalQty
        int qty = GetSumTotalQty(currentQtyRow);
        QtyCell = pivotTableSheet.Cells[currentQtyRow, _grandTotalsColumnPivotTable + 2];
        QtyCell.Value = qty;
        // SumTotalPrice
        decimal price = GetSumTotalPrice(currentQtyRow+1);
        PriceCell = pivotTableSheet.Cells[currentQtyRow+1, _grandTotalsColumnPivotTable + 2];
        PriceCell.Value = price;
        // Calculate Avg Price (SumTotalPrice / SumTotalQty)
        decimal avg = 0.0M;
        if ((price > 0) && (qty > 0))
        {
            avg = price / qty;
        }
        AvgPriceCell = pivotTableSheet.Cells[currentQtyRow+2, _grandTotalsColumnPivotTable + 2];
        AvgPriceCell.Value = avg;
        // Calculate Percentage (totalTotalPurchases / SumTotalPrice?)
        decimal prcntg = 0.0M;
        if ((totalTotalPurchases > 0) && (price > 0)) // ? Right calculation?
        {
            prcntg = totalTotalPurchases / price;
        }
        PercentageCell = pivotTableSheet.Cells[currentQtyRow+3, _grandTotalsColumnPivotTable + 2];
        PercentageCell.Value = prcntg;

        currentQtyRow = currentQtyRow + ROWS_IN_A_RANGE;
    }
}

private int GetSumTotalQty(int currentQtyRow)
{
    int FIRST_MONTH_COL = 2;
    int LAST_MONTH_COL = _grandTotalsColumnPivotTable; // - 1;
    int cumulativeQty = 0;
    Cell qtyCell = null;
    for (int i = FIRST_MONTH_COL; i <= LAST_MONTH_COL; i++)
    {
        qtyCell = pivotTableSheet.Cells[currentQtyRow, i];
        cumulativeQty = cumulativeQty + Convert.ToInt32(qtyCell.Value);
    }
    return cumulativeQty;
}

. . . (etc. for GetSumTotalPrice())