We're sorry Aspose doesn't work properply without JavaScript enabled.

Free Support Forum - aspose.com

Why does attempt to color ranges in a PivotTable have no effect?

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

private void ColorizeContractItemBlocks(List 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?

Note: Even when I changed it based on the official docs here, like so:

// 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);

...it made no difference.

Hi Clay,


Thank you for contacting Aspose support.

As you have mentioned that you are trying to apply conditional formatting to the Pivot Table range therefore I would suggest you to use the PivotFormatConditionCollection class for this purpose. Please check the following piece of code as well as the input & output spreadsheets for your reference, and make the necessary adjustments to your code. In case you still face any difficulty, please share your input spreadsheet as well as your desired results, that you may create manually in Excel application. This will help us understand your requirements more precisely.

C#

var book = new Workbook(dir + “sample.xlsx”);
var sheet = book.Worksheets[0];
var pivot = sheet.PivotTables[0];

int index = pivot.PivotFormatConditions.Add();
PivotFormatCondition pfc = pivot.PivotFormatConditions[index];
pfc.ScopeType = PivotConditionFormatScopeType.Data;
FormatConditionCollection fcs = pfc.FormatConditions;
CellArea ca = pivot.DataBodyRange;

int[] t = fcs.Add(ca, FormatConditionType.ColorScale, OperatorType.Equal, “”, “”);
FormatCondition fc = fcs[t[0]];
ColorScale scale = fc.ColorScale;
scale.MidColor = Color.Red;
scale.MinCfvo.Type = FormatConditionValueType.Max;
scale.MinCfvo.Value = 10;

pivot.RefreshDataFlag = true;
pivot.RefreshData();
pivot.CalculateData();
pivot.RefreshDataFlag = false;

book.Save(dir + “output.xlsx”);

Thanks, but I’m not understanding the logic for marking certain cells as those to be formatted (colored red); I see the red cells in your output.xlsx sheet, but don’t know why some are red and others are not.


IOW, I need to know how I can color certain cells (rows/ranges, actually, in my case) based on a condition.

Using your code as-is colors nothing in my sheet. I knew it wouldn’t color the right things, but I need to know how I can provide which row numbers within the pivot need to be colorized.

Hi Clay,


Please note, the code shared in my previous response has been provided for demonstration purposes only therefore you need to amend the code to get the desired results based on your sample/Pivot Table. Regarding the ranges, there are a number of properties in PivotTable class that you can put to use. Please check following snippet and code comments for your reference. In case you still face any difficulty, please share the input spreadsheet (containing the data, preferably an un-formatted Pivot table) and your desired results (that you may create manually in Excel application). We will review them to suggest you the exact solution you are looking for.

C#

var book = new Workbook(dir + “sample.xlsx”);
var sheet = book.Worksheets[0];
var pivot = sheet.PivotTables[0];

// DataBodyRange returns CellArea that represents range between the header row & insert row
var dataBodyRange = pivot.DataBodyRange;
Console.WriteLine(dataBodyRange);
// TableRange1 returns complete Pivot Table area except page fields
var tableRange1 = pivot.TableRange1;
Console.WriteLine(tableRange1);
// TableRange2 returns complete Pivot Table area including page fields
var tableRange2 = pivot.TableRange2;
Console.WriteLine(tableRange2);
// ColumnRange returns range that represents the column area of the Pivot Table
var columnRange = pivot.ColumnRange;
Console.WriteLine(columnRange);
// RowRange returns range that represents the row area of the Pivot Table
var rowRange = pivot.RowRange;

Console.WriteLine(rowRange);

Thanks; I know how to specify a range; the problem I was having was determining how to say “this is the row I want to colorize.” I am trying the answer provided at http://stackoverflow.com/questions/40684369/why-does-attempt-to-color-ranges-in-a-pivottable-have-no-effect-aspose-cells/40732843#40732843, and if that doesn’t work, I’ll re-post.

Hi Clay.


As per my previous code snippet, you can observe that all mentioned properties return an instance of CellArea which in-turn contains the properties such as StartRow, EndRow, StartColumn & EndColumn. You have to use these properties to get the desired cell (row, column pair) and apply the formatting/cell shading.

Please feel free to contact us back in case you need any further assistance with Aspose APIs.

Sorry, I’m still confused.


By “all mentioned properties” do you mean DataBodyRange, TableRange1, TableRange2, ColumnRange, and RowRange?

If so, can you provide an example of how those instances are used to color a specific row? e.g., how would I colorize all rows which had a value of “bla” in column 1?

Hi Clay,


Please consider the example shared in this post. As you can see that the PivotTable.ColumnRange returns the CellArea which occupies the range B5:E6 (as shown in attached snapshot). If you wish to style the cells B6, C6 & D6 (with custom date format) you can point to these cells using the following logic. It is up to your application requirements how you devise similar logic for other areas of the Pivot Table or employ extra checks such as if the cell contains a particular value.

C#

CellArea columnRange = pt.ColumnRange;
for (int c = columnRange.StartColumn; c < columnRange.EndColumn; c++)
{
pt.Format(columnRange.StartRow + 1, c, columnStyle);
}

Still doesn’t work; for all the gory details, see http://stackoverflow.com/questions/40684369/why-does-attempt-to-color-ranges-in-a-pivottable-have-no-effect-aspose-cells

Hi,


Thanks for using Aspose.Cells.

<p style=“margin: 0px 0px 1em; padding: 0px; border: 0px; font-size: 15px; clear: both; color: rgb(36, 39, 41); font-family: Arial, “Helvetica Neue”, Helvetica, sans-serif; background-color: rgb(255, 255, 255);”>UPDATE 5<p style=“margin: 0px 0px 1em; padding: 0px; border: 0px; font-size: 15px; clear: both; color: rgb(36, 39, 41); font-family: Arial, “Helvetica Neue”, Helvetica, sans-serif; background-color: rgb(255, 255, 255);”>For PivotTable.Format(), you should use Style.BackgroundColor property instead of Style.ForegroundColor property. So change your line<pre style=“margin-top: 0px; margin-bottom: 1em; padding: 5px; border: 0px; font-size: 13px; width: auto; max-height: 600px; overflow: auto; font-family: Consolas, Menlo, Monaco, “Lucida Console”, “Liberation Mono”, “DejaVu Sans Mono”, “Bitstream Vera Sans Mono”, “Courier New”, monospace, sans-serif; background-color: rgb(239, 240, 241); word-wrap: normal; color: rgb(36, 39, 41);”><code style=“margin: 0px; padding: 0px; border: 0px; font-family: Consolas, Menlo, Monaco, “Lucida Console”, “Liberation Mono”, “DejaVu Sans Mono”, “Bitstream Vera Sans Mono”, “Courier New”, monospace, sans-serif; white-space: inherit;”>style.ForegroundColor = CONTRACT_ITEM_COLOR; //Color.Red;
<p style=“margin: 0px 0px 1em; padding: 0px; border: 0px; font-size: 15px; clear: both; color: rgb(36, 39, 41); font-family: Arial, “Helvetica Neue”, Helvetica, sans-serif; background-color: rgb(255, 255, 255);”>to<pre style=“margin-top: 0px; margin-bottom: 1em; padding: 5px; border: 0px; font-size: 13px; width: auto; max-height: 600px; overflow: auto; font-family: Consolas, Menlo, Monaco, “Lucida Console”, “Liberation Mono”, “DejaVu Sans Mono”, “Bitstream Vera Sans Mono”, “Courier New”, monospace, sans-serif; background-color: rgb(239, 240, 241); word-wrap: normal; color: rgb(36, 39, 41);”><code style=“margin: 0px; padding: 0px; border: 0px; font-family: Consolas, Menlo, Monaco, “Lucida Console”, “Liberation Mono”, “DejaVu Sans Mono”, “Bitstream Vera Sans Mono”, “Courier New”, monospace, sans-serif; white-space: inherit;”>style.BackgroundColor = CONTRACT_ITEM_COLOR; //Color.Red;
<p style=“margin: 0px 0px 1em; padding: 0px; border: 0px; font-size: 15px; clear: both; color: rgb(36, 39, 41); font-family: Arial, “Helvetica Neue”, Helvetica, sans-serif; background-color: rgb(255, 255, 255);”>and it should fix your issue. Thank you.

Thanks, that helped a lot.


However, there are still bits that are not getting colored. My code and a screen shot appear below:

private void ColorizeContractItemBlocks(List 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;
}
}

…the second, third, and fourth rows of the Data column are not being colored, as seen in the screen shot.

Why not? How can I fix it?

Hi,


Thanks for your posting and using Aspose.Cells.

Please check if you could directly format those cells. Suppose your target cell in pivot table is H5, then try this code and see if H5 gets affected or not.

C#
Cell cell = pivotTableSheet.Cells[“H5”];
pt.Format(cell.Row, cell.Column, style);

PS

In order to look into this issue further, please provide us your original file. Thank you.

I tried that with this code (see the end):

private void ColorizeContractItemBlocks(List 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();

PivotTable pt = pivotTableSheet.PivotTables[0];
var style = workBook.CreateStyle();
// 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.BackgroundColor = CONTRACT_ITEM_COLOR;
style.Pattern = BackgroundType.Solid;

//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;
}
// try to force some
Cell cell = pivotTableSheet.Cells["B33"];
pt.Format(cell.Row, cell.Column, style);
cell = pivotTableSheet.Cells["B34"];
pt.Format(cell.Row, cell.Column, style);
cell = pivotTableSheet.Cells["B35"];
pt.Format(cell.Row, cell.Column, style);
}

...but it makes no difference. Here is what it looks like (still the same as before):
BTW: This is my last remaining issue.

Still trying to get the elements of column B to color up, I experimted by 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 casued columns A/0 and B/1 to not color up at all (see screenshot below).

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?

Hi Clay,


Thank you for writing back.

I have evaluated the presented scenario while using the simplest code as provided below, and I suspect that there is a problem in the API regarding the Pivot Table area formatting. If you check the attached output.xlsx (generated with following piece of code) you will notice that the formatting has been applied correctly, however, in next few seconds the Excel application resets the formatting to the original state. I have logged this incident as CELLSNET-44955 in our bug tracking for further investigation.

By the way, as per your problem description, you wish to apply the formatting to all columns of the Pivot Table therefore the following code snippet tries to achieve the same. If you wish to skip a few cells, please highlight them in a screenshot so we could amend the code accordingly or you can do it yourself as well by changing the logic in for loops and incorporating extra checks such as verify the cell value etc.

C#

var book = new Workbook(dir + “HILTON±+Produce+Usage±+from+Oct+2015_PROCESSED_1323.xlsx”);
var sheet = book.Worksheets[“PivotTableSheet”];
var pivot = sheet.PivotTables[0];
var rowRange = pivot.TableRange1;

for (int r = rowRange.StartRow + 2; r < rowRange.EndRow; r++)
{
for (int c = rowRange.StartColumn; c < rowRange.EndColumn + 1; c++)
{
var style = sheet.Cells[r, c].GetStyle();
style.ForegroundColor = Color.Beige;
style.Pattern = BackgroundType.Solid;
pivot.Format(r, c, style);
}
}

book.Save(dir + “output.xlsx”);

There seems to be an uncivil civil war going on between olde Excel and Aspose Cells - when I used your code and opened the resulting spreadsheet, it had indeed worked - the cells were indeed colored - BUT! the sheet’s cells then reverted back to their prior state - the colors washed away and left naught but a memory of their fleeting grandeur.

Hi Clay,


Thank you for the confirmation on said behaviour. We have already logged this incident in our bug tracking system, and we will keep you posted with any updates we get from the product team in this regard.

Hi,


Regarding issue CELLSNET-44955:
Please try using the sample code as follows:
e.g
Sample code:

var book = new Workbook(dir + “HILTON±+Produce+Usage±+from+Oct+2015_PROCESSED_1323.xlsx”);
var sheet = book.Worksheets[“PivotTableSheet”];
var pivot = sheet.PivotTables[0];
var rowRange = pivot.TableRange1;

for (int r = rowRange.StartRow + 2; r < rowRange.EndRow; r++)
{
for (int c = rowRange.StartColumn; c < rowRange.EndColumn + 1; c++)
{
var style = sheet.Cells[r, c].GetStyle();

//style.ForegroundColor = Color.Beige;
//If you want to format the background color of the cell in the PivotTable, please use the Style.BackgroundColor attribute,
//not Style.ForegroundColor attribute. It is different from formatting the background color of ordinary cell.
style.BackgroundColor = Color.Beige;
style.Pattern = BackgroundType.Solid;
pivot.Format(r, c, style);
}
}

book.Save(dir + “output.xlsx”);

Let us know if you still have this issue.

Thank you.

I tried that before, but I’ll try it one more time.

Hi,


Sure, take your time. By the way, I have tested it with the sample code (pasted in my previous reply) using your template file and it works fine. Please find the output file for your reference.

Thank you.