excelpivot-tableaspose-cells

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


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

enter image description here

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:

enter image description 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?


Solution

  • 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())