Telerik blogs
Industry NewsT2 Dark_1200x303

Let’s get an overview of the new Conditional Formatting functionality in SpreadProcessing.

With the latest R1 2022, we introduced two of the most-demanded features in our SpreadProcessing libraryNotes and Conditional Formatting. Today I would like to show you a brief overview of the conditional formatting support and how you can use this feature with SpreadProcessing.

What Is Conditional Formatting?

Conditional formatting comes as great help when you need to apply styling on cells according to the data they contain. With this functionality, you can ensure that the data will be always easy to read and the end users will be able to focus on important points inside. Furthermore, the styling of the cells will dynamically change if that is needed when the value of a cell is modified.

You can apply various formatting options like changing fill and font and even create more complex rules with data bars, color scales and icon sets. You can see some examples in the screenshot below.

Conditional formattings

To give you a better understanding of the API SpreadProcessing exposes, let’s first take a look of the building elements of the conditional formatting:

  • Rule – defines the condition that the values should be evaluated against. If the condition is evaluated to a value bigger than 0, the cell range is formatted; otherwise, if the condition results in 0, the cell range is not formatted.
  • Formatting – defines the formatting that should be applied to all the cells whose value falls within the condition constraints.

There is an extensive set of predefined rules that you can use in SpreadProcessing and creating custom ones through formulas is also supported. Since I don’t want to bore you with long lists, if you are eager to check all of the built-in rules, they are available in the dedicated Conditional Formatting help topic.

Conditional Formatting in Action

After introducing you the basics of the feature, I suggest it’s time to look at some examples of how it works.

Let’s assume that you are keeping an eye on some companies you bought shares from. With the conditional formatting, you can easily find the companies that are not profitable enough or such that can bring you more earnings. Here is the data we have:

Sample data

There are several things we can make to better visualize this data and make it easy to read and make decisions from. The first one is to highlight the distribution of the shares so you can immediately see which are the companies you bought mostly from. This can be done using the DataBar rule:

// Create the context to set the minimum and maximum values the rule should use.
DataBarValueContext dataBarValueContext = new DataBarValueContext();
dataBarValueContext.MinimumValue = new MinimumValue();
dataBarValueContext.MaximumValue = new MaximumValue();
// Create the rule and apply the desired properties to it.
DataBarRule dataBarRule = new DataBarRule(dataBarValueContext);
dataBarRule.ShowBorder = false;
dataBarRule.FillColor = new ThemableColor(Colors.Green);
// Create ConditionalFormatting object and apply it on the desired range.
ConditionalFormatting conditionalFormatting = new ConditionalFormatting(dataBarRule);
worksheet.Cells[1, 1, 6, 1].AddConditionalFormatting(conditionalFormatting);

Now you can see the distribution of the shares from the first sight:

Data Bar Rule

Anyone would be also interested in the investments that turn out to be dropping on yearly basis. Let’s emphasize on them using the Formula rule. When using this rule, the cell values that evaluate to true will be highlighted by the rule formatting. The specific formula used in the snippet below will pick up all the shares whose price has decreased, or the increase is with less than $10.

FormulaRule formulaRule = new FormulaRule("=($D2-$C2)<10");
// Create and apply the rule formatting
DifferentialFormatting formulaRuleFormatting = new DifferentialFormatting();
formulaRuleFormatting.Fill = new PatternFill(PatternType.Solid, Colors.OrangeRed, Colors.OrangeRed);
formulaRule.Formatting = formulaRuleFormatting;
// Create ConditionalFormatting object and apply it on the desired range.
ConditionalFormatting conditionalFormattingFormula = new ConditionalFormatting(formulaRule);
worksheet.Cells[1, 3, 6, 3].AddConditionalFormatting(conditionalFormattingFormula);

Now you can see which shares are dangerous for your savings.

Data Bar and Formula Rules

The Change column can help you even more to make the most appropriate decision. I would suggest adding icons to it that show how the shares perform:

// Create the rule using a predefined set of icons.
IconSetRule iconSetRule = new IconSetRule(PresetIconSet.ThreeSymbolsUnrimmed);
// Create ConditionalFormatting object and apply it on the desired range.
ConditionalFormatting conditionalFormattingIcons = new ConditionalFormatting(iconSetRule);
worksheet.Cells[1, 4, 6, 4].AddConditionalFormatting(conditionalFormattingIcons);

I believe you will agree with me that it is a matter of seconds to notice which shares would be better to get out of your portfolio and which one you should be keeping an eye on.

Data Bar, Formula and Icon Set Rules

Of course, this is a pretty simple example showing a small part of all the fancy formattings you can apply and the rules you can use for them. I am sure that you are dealing with much more diverse data and there are many points of improving its visualization. Thus, don’t wait to surprise your customers with new shiny documents.

Try It and Share Your Feedback

No matter if you are already familiar with Telerik Document Processing or will meet the libraries for the first time, hurry up and get the latest bits so you can explore all the possibilities they provide:

Download a Free Trial

And I am sure I have told you many times that your input is valuable. We do listen. So, do not be shy—drop us a line to share your feedback in the comments section below or directly in our Document Processing Libraries Feedback Portal.

Happy formatting!

About the Author

Tanya Dimitrova

Tanya Dimitrova is a Tech Support Engineer in the Telerik XAML Team. In her work her main responsibility is to assist clients to implement different scenarios using the document processing libraries and editors. She is passionate in finding new adventures and in her free time enjoys travelling, reading, swimming, dancing or just spending time with friends.

Related Posts


Comments are disabled in preview mode.