Styling specific values of Pivot Table

Aspose.Cells Product Family
,
1

Hello Team,

How can we style (color, bold) specific cells matching a condition in Pivot Table.

For example, I want to color “Kiwi” and “Mango” fruit name with foreground as “RED” and all kg values having count >=2 as “GREEN”

Attaching my sample excel file and desired output.

Attachments.zip (228.2 KB)

2

@sourav24,
You may try following code with conditional formatting and let us know your feedback.

Workbook workbook = new Workbook(dataDir + "Test.xlsx");
Worksheet sheet = workbook.Worksheets[0];

// Adds an empty conditional formatting
int index = sheet.ConditionalFormattings.Add();
FormatConditionCollection fcs = sheet.ConditionalFormattings[index];

// Sets the conditional format range.
CellArea ca = new CellArea();
ca.StartRow = 4;
ca.EndRow = 10;
ca.StartColumn = 0;
ca.EndColumn = 0;
fcs.AddArea(ca);

// Adds condition.
int conditionIndex = fcs.AddCondition(FormatConditionType.CellValue, OperatorType.Equal, "Kiwi", "");

// Sets the text color.
FormatCondition fc = fcs[conditionIndex];
fc.Style.Font.Color = Color.Red;

// Adds condition.
int conditionIndex2 = fcs.AddCondition(FormatConditionType.CellValue, OperatorType.Equal, "Mango", "");
fc = fcs[conditionIndex2];
fc.Style.Font.Color = Color.Red;

index = sheet.ConditionalFormattings.Add();
FormatConditionCollection fcs1 = sheet.ConditionalFormattings[index];

CellArea carea = new CellArea();
carea.StartRow = 4;
carea.EndRow = 9;
carea.StartColumn = 1;
carea.EndColumn = 6;
fcs1.AddArea(carea);

int conditionIndex3 = fcs1.AddCondition(FormatConditionType.CellValue, OperatorType.GreaterOrEqual, "2", "");
fc = fcs1[conditionIndex3];
fc.Style.Font.Color = Color.Green;
fc.Style.Font.IsBold = true;

// Saving the Excel file
workbook.Save(dataDir + "output.xlsx");
1 Like
3

Thanks it works as expected.

4

@sourav24,

Good to know that the suggested code segment works for your needs. Feel free to write us back if you have further comments or questions.

5

@Amjad_Sahi I need another help. Based on values of an additional non-Pivot column I need color formatting for my cells.

Requirement : For all rows where the Comments column is blank, the cells should be marked as Red.

Attaching sample excel and desired output.

Attachments.zip (168.4 KB)

6

@sourav24,

You may write your own code to accomplish the task easily. You may first find your commented cells using Find/Search options provided by Aspose.Cells in J column. Now get the row indices of the found cells, so you should not iterate those rows and set or apply cells formatting (regarding font color) to other rows’ cell values only.

1 Like
7

Thanks, let me try it out.