Conditional Formatting with Spread WPF-Silverlight

Conditional Formatting in Spread is already supported in the Windows and Web versions. With Spread WPF-Silverlight, this feature has been enhanced to match the level of Excel 2013. You can now set icons, bars, background, foreground, color gradient, etc. within a cell for certain conditions which is really cool for data visualization. Spread WorkSheet's ConditionalFormats property allows you to set a conditional formatting rule for a particular CellRange. The example below sets an Icon Rule for a cell range:

   gcSpreadSheet1.Sheet[0].ConditionalFormats.AddIconSetRule(IconSetType.FiveArrowsColored, new CellRange(2, 1, 7, 10));  

It produces the output as follows: ConditionalFormatArrow1 To add Bar rule to conditional formatting we may use the code as follows:

   gcSpreadSheet1.Sheet[0].ConditionalFormats.AddDataBarRule(ScaleValueType.Number, 0, ScaleValueType.Number, 700, Colors.Brown, new CellRange(2, 1, 7, 10));  

It produces the formatting as shown in the image below: ConditionalFormatBars1 We can also add our own formatting rule. For example:

   var rule = GrapeCity.Windows.SpreadSheet.Data.ThreeColorScaleRule.Create(GrapeCity.Windows.SpreadSheet.Data.ScaleValueType.Number, 1, Colors.Yellow, GrapeCity.Windows.SpreadSheet.Data.ScaleValueType.Number, 50, Colors.Blue, GrapeCity.Windows.SpreadSheet.Data.ScaleValueType.Number, 100, Colors.Red);  
   rule.Ranges = new GrapeCity.Windows.SpreadSheet.Data.CellRange[] { new GrapeCity.Windows.SpreadSheet.Data.CellRange(0, 0, 20, 1) };  

It scales the value with the color as per the set number. The final outcome of this is: You can also create your own style for some formatting rules. For example you can set the style for cells with values more than 300 and 600 with the help of CellValueRule. Take a look at this piece of code which can accomplish this :

   GrapeCity.Windows.SpreadSheet.Data.StyleInfo myStyle = new StyleInfo();  
   myStyle.Background = new SolidColorBrush(Colors.LightGray);  
   myStyle.Foreground = new SolidColorBrush(Colors.Red);  
   gcSpreadSheet1.Sheet[0].ConditionalFormats.AddCellValueRule(ComparisonOperator.Between, 300, 500, myStyle, new CellRange(2, 1, 7, 10));  

It shows the cells with value more than 300 and 600 with Backcolor as LightGray and ForeColor as Red. Something like: ConditionalFormatCellValue1 To remove a conditional formatting rule you can always call the ClearRule method. For example:


Conditional formatting rules that are supported by default with Spread are listed below:

  • AverageRule Class
  • CellValueRule Class
  • DataBarRule Class
  • DateOccurringRule Class
  • DuplicateRule Class
  • FormulaRule Class
  • IconSetRule Class
  • ScaleRule Class
  • SpecificTextRule Class
  • Top10Rule Class
  • UniqueRule Class

Formatting is a very interesting and useful feature of Spread WPF-Silverlight which improves data presentation.You can download and dig the samples attached with this blog to know more about Conditional Formatting. Download C# Sample(SL) Download VB Sample(WPF)


GrapeCity Developer Tools
comments powered by Disqus