Formatting Rule for Top or Bottom rank values

Aspose.Cells Product Family
1

Hi,

how to get Formatting Rule for formatting only top or bottom ranked values in Column using Conditional formatting.

please provide the sample code for this.

Thanks,

2

Hi Jack,


Please check the following piece of code that reads a spreadsheet and sets the conditional formatting on cell range A1:A10, ranking the top 5 in red color. Also attached are the input & output spreadsheets for your reference.

C#

var workbook = new Workbook(“D:/sample.xlsx”);
var 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 = 0;
ca.EndRow = 9;
ca.StartColumn = 0;
ca.EndColumn = 0;

fcs.AddArea(ca);

//Adds condition.
int conditionIndex = fcs.AddCondition(FormatConditionType.Top10);
FormatCondition fc = fcs[conditionIndex];
//Sets Rank value.
fc.Top10.Rank = 5;
//Sets the background color.
fc.Style.BackgroundColor = Color.Red;

//Saving the Excel file
workbook.Save(“D:/output.xlsx”);