Icon Rules and Icons

Icon rules are useful for visually representing data. They make trends easier to see in addition to drawing attention to the data. Icon rules display an icon when a cell value is greater than, equal to, or less than a value. Spread for Windows Forms supports built-in icon sets, individual icons, and custom icons. You can use built-in icon sets for the rule with the ConditionalFormattingIconSetStyle enumeration and the IconSetConditionalFormattingRule class. You can specify individual icons to use in the icon set with the IconRuleSet property, the IconSetConditionalFormattingRule class, and the ConditionalFormattingIcon enumeration. You can use custom icons with the AddIcon method, the IconSetConditionalFormattingRule class, and the CustomIconContainer property. This example displays red triangles for items with low sales, green triangles for items with high sales, and a yellow dash for items in the middle of the range. iconruleunits

C#

fpSpread1.Sheets[0].ColumnHeader.Cells[0, 0].Text = "Product Name"; fpSpread1.Sheets[0].Columns[0].Width = 120; fpSpread1.Sheets[0].ColumnHeader.Cells[0, 1].Text = "Total Units Sold"; fpSpread1.Sheets[0].Columns[1].Width = 120; fpSpread1.Sheets[0].Cells[0, 0].Value = "Product A"; fpSpread1.Sheets[0].Cells[1, 0].Value = "Product B"; fpSpread1.Sheets[0].Cells[2, 0].Value = "Product C"; fpSpread1.Sheets[0].Cells[3, 0].Value = "Product D"; fpSpread1.Sheets[0].Cells[4, 0].Value = "Product E"; fpSpread1.Sheets[0].Cells[5, 0].Value = "Product F"; fpSpread1.Sheets[0].Cells[6, 0].Value = "Product G"; fpSpread1.Sheets[0].Cells[0, 1].Value = 30; fpSpread1.Sheets[0].Cells[1, 1].Value = 2; fpSpread1.Sheets[0].Cells[2, 1].Value = 100; fpSpread1.Sheets[0].Cells[3, 1].Value = 1; fpSpread1.Sheets[0].Cells[4, 1].Value = 50; fpSpread1.Sheets[0].Cells[5, 1].Value = 200; fpSpread1.Sheets[0].Cells[6, 1].Value = 189; FarPoint.Win.Spread.IconSetConditionalFormattingRule c = new FarPoint.Win.Spread.IconSetConditionalFormattingRule(FarPoint.Win.Spread.ConditionalFormattingIconSetStyle.ThreeTriangles); FarPoint.Win.Spread.ConditionalFormatting IconSetCF = new FarPoint.Win.Spread.ConditionalFormatting(new FarPoint.Win.Spread.Model.CellRange(0, 1, 10, 1), c); fpSpread1.Sheets[0].Models.ConditionalFormatting.Add(IconSetCF); fpSpread1.Font = new System.Drawing.Font("Calibri", 11);

VB

FpSpread1.Sheets(0).ColumnHeader.Cells(0, 0).Text = "Product Name" FpSpread1.Sheets(0).Columns(0).Width = 120 FpSpread1.Sheets(0).ColumnHeader.Cells(0, 1).Text = "Total Units Sold" FpSpread1.Sheets(0).Columns(1).Width = 120 FpSpread1.Sheets(0).Cells(0, 0).Value = "Product A" FpSpread1.Sheets(0).Cells(1, 0).Value = "Product B" FpSpread1.Sheets(0).Cells(2, 0).Value = "Product C" FpSpread1.Sheets(0).Cells(3, 0).Value = "Product D" FpSpread1.Sheets(0).Cells(4, 0).Value = "Product E" FpSpread1.Sheets(0).Cells(5, 0).Value = "Product F" FpSpread1.Sheets(0).Cells(6, 0).Value = "Product G" FpSpread1.Sheets(0).Cells(0, 1).Value = 30 FpSpread1.Sheets(0).Cells(1, 1).Value = 2 FpSpread1.Sheets(0).Cells(2, 1).Value = 100 FpSpread1.Sheets(0).Cells(3, 1).Value = 1 FpSpread1.Sheets(0).Cells(4, 1).Value = 50 FpSpread1.Sheets(0).Cells(5, 1).Value = 200 FpSpread1.Sheets(0).Cells(6, 1).Value = 189 Dim c As New FarPoint.Win.Spread.IconSetConditionalFormattingRule(FarPoint.Win.Spread.ConditionalFormattingIconSetStyle.ThreeTriangles) Dim IconSetCF As New FarPoint.Win.Spread.ConditionalFormatting(New FarPoint.Win.Spread.Model.CellRange(0, 1, 10, 1), c) FpSpread1.Sheets(0).Models.ConditionalFormatting.Add(IconSetCF) FpSpread1.Font = New System.Drawing.Font("Calibri", 11)

This example code creates an icon set rule and uses built-in icons. builtinicon

C#

fpSpread1.Sheets[0].Cells[0, 0].Value = 8; fpSpread1.Sheets[0].Cells[1, 0].Value = 5; fpSpread1.Sheets[0].Cells[2, 0].Value = 10; fpSpread1.Sheets[0].Cells[3, 0].Value = 1; FarPoint.Win.Spread.Model.CellRange celRange1 = new FarPoint.Win.Spread.Model.CellRange(0, 0, 4, 1); FarPoint.Win.Spread.IconSetConditionalFormattingRule rule = new FarPoint.Win.Spread.IconSetConditionalFormattingRule(FarPoint.Win.Spread.ConditionalFormattingIconSetStyle.ThreeRimmedTrafficLights); fpSpread1.Sheets[0].SetConditionalFormatting(new FarPoint.Win.Spread.Model.CellRange[] { celRange1 }, rule); fpSpread1.Font = new System.Drawing.Font("Calibri", 11);

VB

FpSpread1.Sheets(0).Cells(0, 0).Value = 8 FpSpread1.Sheets(0).Cells(1, 0).Value = 5 FpSpread1.Sheets(0).Cells(2, 0).Value = 10 FpSpread1.Sheets(0).Cells(3, 0).Value = 1 Dim celRange1 As New FarPoint.Win.Spread.Model.CellRange(0, 0, 4, 1) Dim rule As New FarPoint.Win.Spread.IconSetConditionalFormattingRule(FarPoint.Win.Spread.ConditionalFormattingIconSetStyle.ThreeRimmedTrafficLights) FpSpread1.Sheets(0).SetConditionalFormatting(New FarPoint.Win.Spread.Model.CellRange() {celRange1}, rule) FpSpread1.Font = New System.Drawing.Font("Calibri", 11)

This example changes a single icon. customicon

C#

fpSpread1.Sheets[0].Cells[0, 0].Value = 3; fpSpread1.Sheets[0].Cells[1, 0].Value = 2; fpSpread1.Sheets[0].Cells[1, 1].Value = 10; fpSpread1.Sheets[0].Cells[0, 1].Value = 1; //IconSetConditionalFormatting FarPoint.Win.Spread.IconSetConditionalFormattingRule c = new FarPoint.Win.Spread.IconSetConditionalFormattingRule(FarPoint.Win.Spread.ConditionalFormattingIconSetStyle.ThreeTriangles); c.IconRuleSet.Insert(0, new FarPoint.Win.Spread.ConditionalFormattingIconValue(10, FarPoint.Win.Spread.ConditionalFormattingValueType.Number, true, FarPoint.Win.Spread.ConditionalFormattingIcon.BlackCircle)); FarPoint.Win.Spread.ConditionalFormatting IconSetCF = new FarPoint.Win.Spread.ConditionalFormatting(new FarPoint.Win.Spread.Model.CellRange(0, 0, 2, 2), c); fpSpread1.Sheets[0].Models.ConditionalFormatting.Add(IconSetCF); fpSpread1.Font = new System.Drawing.Font("Calibri", 11);

VB

FpSpread1.Sheets(0).Cells(0, 0).Value = 3 FpSpread1.Sheets(0).Cells(1, 0).Value = 2 FpSpread1.Sheets(0).Cells(1, 1).Value = 10 FpSpread1.Sheets(0).Cells(0, 1).Value = 1 'IconSetConditionalFormatting Dim c As New FarPoint.Win.Spread.IconSetConditionalFormattingRule(FarPoint.Win.Spread.ConditionalFormattingIconSetStyle.ThreeTriangles) c.IconRuleSet.Insert(0, New FarPoint.Win.Spread.ConditionalFormattingIconValue(10, FarPoint.Win.Spread.ConditionalFormattingValueType.Number, True, FarPoint.Win.Spread.ConditionalFormattingIcon.BlackCircle)) Dim IconSetCF As New FarPoint.Win.Spread.ConditionalFormatting(New FarPoint.Win.Spread.Model.CellRange(0, 0, 2, 2), c) FpSpread1.Sheets(0).Models.ConditionalFormatting.Add(IconSetCF) FpSpread1.Font = New System.Drawing.Font("Calibri", 11)

This example uses a custom icon in a rule.

C#

System.Drawing.Icon resource1 = new System.Drawing.Icon("C:\\SpreadStudio8\\fpicon.ico"); System.Drawing.Icon resource2 = new System.Drawing.Icon("C:\\SpreadStudio8\\cat.ico"); fpSpread1.CustomIconContainer.AddIcon("fpicon", resource1); fpSpread1.CustomIconContainer.AddIcon("cat", resource2); FarPoint.Win.Spread.IconSetConditionalFormattingRule iconRule = new FarPoint.Win.Spread.IconSetConditionalFormattingRule(FarPoint.Win.Spread.ConditionalFormattingIconSetStyle.Custom); iconRule.IconRuleSet.Add(new FarPoint.Win.Spread.ConditionalFormattingIconValue(0, FarPoint.Win.Spread.ConditionalFormattingValueType.Number, false, new FarPoint.Win.Spread.CustomImageInfo("fpicon"))); iconRule.IconRuleSet.Add(new FarPoint.Win.Spread.ConditionalFormattingIconValue(1, FarPoint.Win.Spread.ConditionalFormattingValueType.Number, false, new FarPoint.Win.Spread.CustomImageInfo("cat"))); fpSpread1_Sheet1.SetValue(0, 0, 0); fpSpread1_Sheet1.SetValue(1, 0, 1); fpSpread1_Sheet1.SetValue(2, 0, 2); fpSpread1_Sheet1.SetConditionalFormatting(0, 0, 3, 1, new FarPoint.Win.Spread.IConditionalFormattingRule[] { iconRule });

VB

Dim resource1 As New System.Drawing.Icon("C:\SpreadStudio8\fpicon.ico") Dim resource2 As New System.Drawing.Icon("C:\SpreadStudio8\cat.ico") FpSpread1.CustomIconContainer.AddIcon("fpicon", resource1) FpSpread1.CustomIconContainer.AddIcon("cat", resource2) Dim iconRule As New FarPoint.Win.Spread.IconSetConditionalFormattingRule(FarPoint.Win.Spread.ConditionalFormattingIconSetStyle.Custom) iconRule.IconRuleSet.Add(New FarPoint.Win.Spread.ConditionalFormattingIconValue(0, FarPoint.Win.Spread.ConditionalFormattingValueType.Number, False, New FarPoint.Win.Spread.CustomImageInfo("fpicon"))) iconRule.IconRuleSet.Add(New FarPoint.Win.Spread.ConditionalFormattingIconValue(1, FarPoint.Win.Spread.ConditionalFormattingValueType.Number, False, New FarPoint.Win.Spread.CustomImageInfo("cat"))) FpSpread1_Sheet1.SetValue(0, 0, 0) FpSpread1_Sheet1.SetValue(1, 0, 1) FpSpread1_Sheet1.SetValue(2, 0, 2) FpSpread1_Sheet1.SetConditionalFormatting(0, 0, 3, 1, New FarPoint.Win.Spread.IConditionalFormattingRule() {iconRule})

MESCIUS inc.

comments powered by Disqus