Why does attempt to color ranges in a PivotTable have no effect (Aspose Cells)?

Question

I'm trying to conditionally colorize ranges in a PivotTable like so:

private void ColorizeContractItemBlocks(List<string> contractItemDescs)
{
    int FIRST_DESCRIPTION_ROW = 7;
    int DESCRIPTION_COL = 0;
    int ROWS_BETWEEN_DESCRIPTIONS = 4;
    var pivot = pivotTableSheet.PivotTables[0];
    var dataBodyRange = pivot.DataBodyRange;
    int currentRowBeingExamined = FIRST_DESCRIPTION_ROW;
    int rowsUsed = dataBodyRange.EndRow;

    pivot.RefreshData();
    pivot.CalculateData();

    // Loop through PivotTable data, colorizing contract items
    while (currentRowBeingExamined < rowsUsed)
    {
        Cell descriptionCell = pivotTableSheet.Cells[currentRowBeingExamined, DESCRIPTION_COL];
        String desc = descriptionCell.Value.ToString();
        if (contractItemDescs.Contains(desc))
        {
            // args are firstRow, firstColumn, totalRows, totalColumns
            Range rangeToColorize = pivotTableSheet.Cells.CreateRange(
                currentRowBeingExamined, 0,
                ROWS_BETWEEN_DESCRIPTIONS, _grandTotalsColumnPivotTable + 2);
            Style style = workBook.Styles[workBook.Styles.Add()];
            style.BackgroundColor = CONTRACT_ITEM_COLOR;
            StyleFlag styleFlag = new StyleFlag();
            styleFlag.All = true;
            rangeToColorize.ApplyStyle(style, styleFlag);
        }
        currentRowBeingExamined = currentRowBeingExamined + ROWS_BETWEEN_DESCRIPTIONS;
    }
}

...but, although the if block is reached several times (such as where "rangeToColorize" spans A28:E31) it doesn't "take"; what is wrong with my styling or styleflagging or applying those?

UPDATE

Even when I changed it to this:

// Declare a style object.
Style style;

// Create/add the style object.
style = workBook.CreateStyle();

// To Set the fill color of the range, you may use ForegroundColor with
// Solid Pattern setting.
style.BackgroundColor = CONTRACT_ITEM_COLOR;
style.Pattern = BackgroundType.Solid;

// Create a StyleFlag object.
StyleFlag styleFlag = new StyleFlag();
// Make the corresponding attributes ON.
styleFlag.Font = true;
styleFlag.CellShading = true;

// Apply the style to the range.
rangeToColorize.ApplyStyle(style, styleFlag);

...based on the offical docs, it made no difference.

UPDATE 2

Even when I changed some very explicit code about one particular cell to this:

cell = pivotTableSheet.Cells[4, 0];
cell.PutValue(AnnualContractProductsLabel);
style = workBook.CreateStyle(); // cell.GetStyle();
style.HorizontalAlignment = TextAlignmentType.Center;
style.VerticalAlignment = TextAlignmentType.Center;
style.Font.IsBold = true;
pivotTableSheet.Cells.SetRowHeight(4, 25);
//style.BackgroundColor = CONTRACT_ITEM_COLOR; 
style.ForegroundColor = CONTRACT_ITEM_COLOR;
pivotTableSheet.Cells[4, 0].SetStyle(style);

...IOW, changing "style = cell.GetStyle()" to "style = workBook.CreateStyle()" and "BackgroundColor" to "ForegroundColor", it does nothing; the cell in question is not colored.

UPDATE 3

Well, the (or an) odd thing is that the only way I've been able to color anything is in a manually generated "Grand Total" column:

As you can see, certain rows have been colorized, based on a condition. But only in that column, not across the entire row, as it should (theoretically, at least) be:

private void ColorizeContractItemBlocks(List<string> contractItemDescs)
{
    int FIRST_DESCRIPTION_ROW = 7;
    int DESCRIPTION_COL = 0;
    int ROWS_BETWEEN_DESCRIPTIONS = 4;
    var pivot = pivotTableSheet.PivotTables[0];
    var dataBodyRange = pivot.DataBodyRange;
    int currentRowBeingExamined = FIRST_DESCRIPTION_ROW;
    int rowsUsed = dataBodyRange.EndRow;

    pivot.RefreshData();
    pivot.CalculateData();

    // Loop through PivotTable data, colorizing contract items
    while (currentRowBeingExamined < rowsUsed)
    {
        Cell descriptionCell = pivotTableSheet.Cells[currentRowBeingExamined, DESCRIPTION_COL];
        String desc = descriptionCell.Value.ToString();
        if (contractItemDescs.Contains(desc))
        {
            // args are firstRow, firstColumn, totalRows, totalColumns
            Range rangeToColorize = pivotTableSheet.Cells.CreateRange(
                currentRowBeingExamined, 0,
                ROWS_BETWEEN_DESCRIPTIONS, _grandTotalsColumnPivotTable + 2);

            // Declare a style object.
            Style style;

            // Create/add the style object.
            style = workBook.CreateStyle();
            style.ForegroundColor = CONTRACT_ITEM_COLOR; //Color.Red;
            style.Pattern = BackgroundType.Solid;

            // Create a StyleFlag object.
            StyleFlag styleFlag = new StyleFlag();
            // Make the corresponding attributes ON.
            styleFlag.Font = true;
            styleFlag.CellShading = true;

            // Apply the style to the range.
            rangeToColorize.ApplyStyle(style, styleFlag);
        }
        currentRowBeingExamined = currentRowBeingExamined + ROWS_BETWEEN_DESCRIPTIONS;
    }
}

So that makes me think that a PivotTable cannot be colorized. Yet, even when I try to color a "plain old" cell like so:

cell = pivotTableSheet.Cells[4, 0];
cell.PutValue(AnnualContractProductsLabel);
style = workBook.CreateStyle();
style.HorizontalAlignment = TextAlignmentType.Center;
style.VerticalAlignment = TextAlignmentType.Center;
style.Font.IsBold = true;
pivotTableSheet.Cells.SetRowHeight(4, 25);
style.ForegroundColor = CONTRACT_ITEM_COLOR;

// Create a StyleFlag object.
StyleFlag styleFlag = new StyleFlag();
// Make the corresponding attributes ON.
styleFlag.Font = true;
styleFlag.CellShading = true;

// Apply the style to the cell
pivotTableSheet.Cells[4, 0].SetStyle(style, styleFlag);

...it doesn't work - the color is not added. Why does colorizing only work in the one circumstance, but not otherwise?

UPDATE 4

With this:

PivotTableCollection pivotTables = pivotTableSheet.PivotTables;
PivotTable pivotTable = pivotTables[0];
pivotTable.Format();

...I get, "No overload for method 'Format' takes 0 arguments."

...and also with simply this:

PivotTable.Format();

(capital "P", no "pivotTable" assigned), I get the same err msg.

UPDATE 5

Even with the following, based on the suggestion of another Aspose support person, it does nothing:

private void ColorizeContractItemBlocks(List<string> contractItemDescs)
{
    int FIRST_DESCRIPTION_ROW = 7;
    int DESCRIPTION_COL = 0;
    int ROWS_BETWEEN_DESCRIPTIONS = 4;
    var pivot = pivotTableSheet.PivotTables[0];
    var dataBodyRange = pivot.DataBodyRange;
    int currentRowBeingExamined = FIRST_DESCRIPTION_ROW;
    int rowsUsed = dataBodyRange.EndRow;

    pivot.RefreshData();
    pivot.CalculateData();

    // Loop through PivotTable data, colorizing contract items
    while (currentRowBeingExamined < rowsUsed)
    {
        Cell descriptionCell = pivotTableSheet.Cells[currentRowBeingExamined, DESCRIPTION_COL];
        String desc = descriptionCell.Value.ToString();
        if (contractItemDescs.Contains(desc))
        {
            Style style;
            style = workBook.CreateStyle();
            style.ForegroundColor = CONTRACT_ITEM_COLOR; //Color.Red;
            style.Pattern = BackgroundType.Solid;

            StyleFlag styleFlag = new StyleFlag();
            styleFlag.Font = true;
            styleFlag.CellShading = true;

            PivotTable pt = pivotTableSheet.PivotTables[0];
            pt.Format(currentRowBeingExamined, 0, style); // test - does not work, either
            pt.Format(currentRowBeingExamined, 1, style); // " "
            //CellArea columnRange = pt.ColumnRange;
            //for (int c = columnRange.StartColumn; c < columnRange.EndColumn; c++)
            //{
            //    //    pt.Format(columnRange.StartRow + 1, c, style);
            //    pt.Format(currentRowBeingExamined, c, style);
            //}
        }
        currentRowBeingExamined = currentRowBeingExamined + ROWS_BETWEEN_DESCRIPTIONS;
    }
}

UPDATE 6

I've got it pretty much working, now, with the following code:

private void ColorizeContractItemBlocks(List<string> contractItemDescs)
{
    int FIRST_DESCRIPTION_ROW = 7;
    int DESCRIPTION_COL = 0;
    int ROWS_BETWEEN_DESCRIPTIONS = 4;
    var pivot = pivotTableSheet.PivotTables[0];
    var dataBodyRange = pivot.DataBodyRange;
    int currentRowBeingExamined = FIRST_DESCRIPTION_ROW;
    int rowsUsed = dataBodyRange.EndRow;

    pivot.RefreshData();
    pivot.CalculateData();

    // Loop through PivotTable data, colorizing contract items
    while (currentRowBeingExamined < rowsUsed)
    {
        Cell descriptionCell = pivotTableSheet.Cells[currentRowBeingExamined, DESCRIPTION_COL];
        String desc = descriptionCell.Value.ToString();

        if (contractItemDescs.Contains(desc))
        {
            Style style;
            style = workBook.CreateStyle();
            style.BackgroundColor = CONTRACT_ITEM_COLOR;
            style.Pattern = BackgroundType.Solid;

            StyleFlag styleFlag = new StyleFlag();
            styleFlag.Font = true;
            styleFlag.CellShading = true;

            PivotTable pt = pivotTableSheet.PivotTables[0];
            pt.Format(currentRowBeingExamined, 0, style); 
            pt.Format(currentRowBeingExamined, 1, style);
            CellArea columnRange = pt.ColumnRange;
            for (int c = columnRange.StartColumn; c <= columnRange.EndColumn; c++)
            {
                pt.Format(currentRowBeingExamined, c, style);
                pt.Format(currentRowBeingExamined+1, c, style);
                pt.Format(currentRowBeingExamined+2, c, style);
                pt.Format(currentRowBeingExamined+3, c, style);
            }
        }
        currentRowBeingExamined = currentRowBeingExamined + ROWS_BETWEEN_DESCRIPTIONS;
    }
}

...but the second, third, and fourth rows of the Data column are not being colored:

Why not? How can I fix it?

UPDATE 7

I tried to get the elements of column B to color up, and tried changing this:

pt.Format(currentRowBeingExamined, 0, style); 
pt.Format(currentRowBeingExamined, 1, style);

...to this:

pt.Format(currentRowBeingExamined, 0, style); 
pt.Format(currentRowBeingExamined, 1, style);
pt.Format(currentRowBeingExamined, 2, style); // <= made no difference
pt.Format(currentRowBeingExamined, 3, style); // " "

...but it made no difference.

So then I wondered if the first two-line snippet above was necessary, but commenting those lines out caused columns A/0 and B/1 to not color up at all:

Here is the current code in context:

PivotTable pt = pivotTableSheet.PivotTables[0];
var style = workBook.CreateStyle();

while (currentRowBeingExamined < rowsUsed)
{
    Cell descriptionCell = pivotTableSheet.Cells[currentRowBeingExamined, DESCRIPTION_COL];
    String desc = descriptionCell.Value.ToString();

    if (contractItemDescs.Contains(desc))
    {
        style.BackgroundColor = CONTRACT_ITEM_COLOR;
        style.Pattern = BackgroundType.Solid;

        pt.Format(currentRowBeingExamined, 0, style); 
        pt.Format(currentRowBeingExamined, 1, style);
        //pt.Format(currentRowBeingExamined, 2, style); <= made no difference
        //pt.Format(currentRowBeingExamined, 3, style);
        CellArea columnRange = pt.ColumnRange;
        for (int c = columnRange.StartColumn; c <= columnRange.EndColumn; c++)
        {
            pt.Format(currentRowBeingExamined, c, style);
            pt.Format(currentRowBeingExamined+1, c, style);
            pt.Format(currentRowBeingExamined+2, c, style);
            pt.Format(currentRowBeingExamined+3, c, style);
        }
    }
    currentRowBeingExamined = currentRowBeingExamined + ROWS_BETWEEN_DESCRIPTIONS;
}

So how can I get the colorization to span all the columns, including the "Total Purchases", "Sum of Average Price", and "Percentage of Total" cells?

shakeel · Accepted Answer · 2016-11-26T10:03:53.693

1

Please change the following two lines

Style style = workBook.CreateStyle();
style.BackgroundColor = CONTRACT_ITEM_COLOR;

into

Style style = workBook.CreateStyle();
style.ForegroundColor= CONTRACT_ITEM_COLOR;

It should fix your issue. Let us know your feedback.

UPDATE 2

Please try one of the following two methods that are for PivotTable specifically

PivotTable.Format()
PivotTable.FormatAll()

UPDATE 5

For PivotTable.Format(), you should use Style.BackgroundColor property instead of Style.ForegroundColor property. So change your line

style.ForegroundColor = CONTRACT_ITEM_COLOR; //Color.Red;

to

style.BackgroundColor = CONTRACT_ITEM_COLOR; //Color.Red;

and it should fix your issue. Thank you.

Note: I am working as Developer Evangelist at Aspose

edited Nov 26 '16 at 10:03

answered Nov 22 '16 at 02:24

shakeel

1,717
10
14

Please see my Update 4 – B. Clay Shannon-B. Crow Raven Nov 25 '16 at 16:01
You need to specify relevant parameters when using PivotTable.Format() and PivotTable.FormatAll() methods, see your forum thread reply here for your reference: https://www.aspose.com/community/forums/permalink/811248/810468/showthread.aspx#810468 – Amjad Sahi Nov 26 '16 at 09:06
Please see my Update-5. Thank you. – shakeel Nov 26 '16 at 10:05
Thanks; pretty much working, but please see Update 6. – B. Clay Shannon-B. Crow Raven Nov 28 '16 at 16:54
FormatAll() does exactly that, so, although it does color up those cells, it also colors up all the others, too. – B. Clay Shannon-B. Crow Raven Nov 29 '16 at 18:51