How to set the traffic lights icons in C# by Spire.XLS

By using Spire.XLS, developers can easily set the IconSetType of conditional formatting. This article will demonstrate how to set the traffic lights icons in C# with the help of Spire.XLS.

Note: Before Start, please download the latest version of Spire.XLS and add Spire.xls.dll in the bin folder as the reference of Visual Studio.

Here comes to the code snippets:

Step 1: Create a new excel document instance and get the first worksheet.

Workbook wb = new Workbook();
Worksheet sheet = book.Worksheets[0];

Step 2: Add some data to the Excel sheet cell range and set the format for them.

sheet.Range["A1"].Text = "Traffic Lights";
sheet.Range["A2"].NumberValue = 0.95;
sheet.Range["A2"].NumberFormat = "0%";
sheet.Range["A3"].NumberValue = 0.5;
sheet.Range["A3"].NumberFormat = "0%";
sheet.Range["A4"].NumberValue = 0.1;
sheet.Range["A4"].NumberFormat = "0%";
sheet.Range["A5"].NumberValue = 0.9;
sheet.Range["A5"].NumberFormat = "0%";
sheet.Range["A6"].NumberValue = 0.7;
sheet.Range["A6"].NumberFormat = "0%";
sheet.Range["A7"].NumberValue = 0.6;
sheet.Range["A7"].NumberFormat = "0%";

Step 3: Set the height of row and width of column for Excel cell range.

sheet.AllocatedRange.RowHeight = 20;
sheet.AllocatedRange.ColumnWidth = 25;

Step 4: Add a conditional formatting of cell range and set its type to CellValue.

ConditionalFormatWrapper format1 = sheet.Range.ConditionalFormats.AddCondition(); 
format1.FormatType = ConditionalFormatType.CellValue;
format1.FirstFormula = "300";
format1.Operator = ComparisonOperatorType.Less;
format1.FontColor = Color.Black;
format1.BackColor = Color.LightSkyBlue;

Step 5: Add a conditional formatting of cell range and set its type to IconSet.

ConditionalFormatWrapper format = sheet.AllocatedRange.ConditionalFormats.AddCondition();
format.FormatType = ConditionalFormatType.IconSet;
format.IconSet.IconSetType = IconSetType.ThreeTrafficLights1;

Step 6: Save the document to file.

wb.SaveToFile("Light.xlsx", ExcelVersion.Version2010);

Effective screenshots of the traffic lights icons set by Spire.XLS.

How to set the traffic lights icons in C# by Spire.XLS