Monday, 15 February 2016 08:26

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

Written by  support iceblue
Rate this item
(0 votes)

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

Additional Info

  • tutorial_title: Set the traffic lights icons in C#
Last modified on Friday, 07 July 2017 09:05