Hi Clay,
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.
Hi Clay,
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 excel - Why does attempt to color ranges in a PivotTable have no effect (Aspose Cells)? - Stack Overflow, and if that doesn’t work, I’ll re-post.
Hi Clay.
Sorry, I’m still confused.
Hi Clay,
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 excel - Why does attempt to color ranges in a PivotTable have no effect (Aspose Cells)? - Stack Overflow
Hi,
<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.
Hi,
PSIn order to look into this issue further, please provide us your original file. Thank you.
Hi Clay,
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,
Hi,
I tried that before, but I’ll try it one more time.
Hi,