One of the best uses of conditional formatting is to quickly highlight top or bottom ranked values in a large set of data. In this article, we’re going to show you how to highlight top and bottom ranked values in an Excel worksheet using Spire.XLS and conditional formatting.
The input.xlsx file we used for demonstration:
Detail steps:
Step 1: Initialize an object of Workbook class and load the Excel file.
Workbook workbook = new Workbook(); workbook.LoadFromFile("Input.xlsx");
Step 2: Get the first worksheet.
Worksheet sheet = workbook.Worksheets[0];
Step 3: Apply conditional formatting to range "C2:C5" to highlight the top 2 ranked values.
ConditionalFormatWrapper format1 = sheet.Range["C2:C5"].ConditionalFormats.AddCondition(); format1.FormatType = ConditionalFormatType.TopBottom; format1.TopBottom.Type = TopBottomType.Top; format1.TopBottom.Rank = 2; format1.BackColor = Color.Red;
Step 4: Apply conditional formatting to range "D2:D5" to highlight the bottom 2 ranked values.
ConditionalFormatWrapper format2 = sheet.Range["D2:D5"].ConditionalFormats.AddCondition(); format2.FormatType = ConditionalFormatType.TopBottom; format2.TopBottom.Type = TopBottomType.Bottom; format2.TopBottom.Rank = 2; format2.BackColor = Color.ForestGreen;
Step 5: Save the file.
workbook.SaveToFile("TopBottomValues.xlsx", ExcelVersion.Version2013);
Screenshot:
Full code:
using Spire.Xls; using System.Drawing; namespace HighlightValues { class Program { static void Main(string[] args) { { //Initialize an object of Workbook class Workbook workbook = new Workbook(); //Load the Excel file workbook.LoadFromFile("Input.xlsx"); //Get the first worksheet Worksheet sheet = workbook.Worksheets[0]; //Apply conditional formatting to range “C2:C5” to highlight the top 2 values ConditionalFormatWrapper format1 = sheet.Range["C2:C5"].ConditionalFormats.AddCondition(); format1.FormatType = ConditionalFormatType.TopBottom; format1.TopBottom.Type = TopBottomType.Top; format1.TopBottom.Rank = 2; format1.BackColor = Color.Red; //Apply conditional formatting to range “D2:D5” to highlight the bottom 2 values ConditionalFormatWrapper format2 = sheet.Range["D2:D5"].ConditionalFormats.AddCondition(); format2.FormatType = ConditionalFormatType.TopBottom; format2.TopBottom.Type = TopBottomType.Bottom; format2.TopBottom.Rank = 2; format2.BackColor = Color.ForestGreen; //Save the file workbook.SaveToFile("TopBottomValues.xlsx", ExcelVersion.Version2013); } } } }