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.
