Master Excel Conditional Formatting Techniques with DsExcel in C#

Conditionally formatting data turns numbers into narratives.

When you look at a large amount of data in Excel, understanding the significance of each record is difficult. But what if you could customize the cells to grab your attention and make your analysis more compelling?

This is where Excel's conditional formatting feature comes into play, which allows you to highlight cells based on specific criteria. In this blog, we will see how to use the Document Solutions for Excel (DsExcel) API to apply conditional formatting to Excel in .NET app. DsExcel offers various formatting rules, like the cell value rule, the top-bottom rule, the data bar rule, and so on. 

Let’s see how we can use some of these rules to format the following healthcare data to make it visually more attractive.

Ready to Master Excel Conditional Formatting? Download Document Solutions for Excel, .NET Edition Today!

Here is the list of conditional formatting rules that we are going to cover in this blog:

• Cell Value Rule
• Data Bar Rule
• Icon Sets Rule
• Three Color Scale Rule

Cell Value Rule

The cell value rule styles the cells based on their values that fall within a certain range or match specific conditions like greater than, less than, equal to, and not equal. To apply this rule using DsExcel, just create a new FormatCondition object with FormatConditionType CellValue and add it to the FormatConditions collection.

Let’s understand this with an example to highlight all the patient's ages who are greater than or equal to 60 years. For this, you need to set the FormatConditionOperator property of your FormatCondition object to GreaterEqual.

After setting all the above-said properties, the final DsExcel code and Excel output look like below: 

//Age greater than 60, set its interior color
IFormatCondition? ageFormatCondition = worksheet.Range["B2:B11"].FormatConditions.Add(FormatConditionType.CellValue, FormatConditionOperator.GreaterEqual, 60) as IFormatCondition;
ageFormatCondition!.Interior.Color = Color.FromArgb(255,251, 226, 213);

Let’s try another example to mark patients who need a follow-up. Since the FollowUpRequired column uses "Yes" and "No" answers only, so just add separate rules for each answer using Equal as FormatConditionOperator.

Use the following DsExcel code to change all the "Yes" entries to green and all the "No" entries to red:

//Cell Value rule on FollowUpRequired column
IFormatCondition? followUpRequiredCellValueRule = worksheet.Range["H2:H11"].FormatConditions.Add(FormatConditionType.CellValue, FormatConditionOperator.Equal, "Yes") as IFormatCondition;
followUpRequiredCellValueRule!.Font.Color = Color.DarkGreen;
followUpRequiredCellValueRule = worksheet.Range["H2:H11"].FormatConditions.Add(FormatConditionType.CellValue, FormatConditionOperator.Equal, "No") as IFormatCondition;
followUpRequiredCellValueRule!.Font.Color = Color.DarkRed;

Data Bar Rule

The data bar rule adds color bars to the cells based on their values. These bars make it simpler to determine the highest and lowest values in your data. To implement this rule in DsExcel, create a new IDataBar object and add it to the FormatConditions collection. Once added, you have the flexibility to customize the bar color, type, border, and maximum/minimum values.

Let’s apply this rule to the Treatment Cost column to easily identify which patient has the highest treatment cost. Use the AddDatabar() function to add a new IDataBar object to the FormatConditions collection. To enhance the look of these bars, we're painting them a gradient blue and adjusting their max/min length based on their range values.

Below is the DsExcel code to show data bars in the "Treatment Cost" column:

//Data bar rule on Treatment Cost column
IDataBar treatmentCostDataBarCondition = worksheet.Range["F2:F11"].FormatConditions.AddDatabar();
treatmentCostDataBarCondition.MinPoint.Type = ConditionValueTypes.AutomaticMin;
treatmentCostDataBarCondition.MaxPoint.Type = ConditionValueTypes.AutomaticMax;
treatmentCostDataBarCondition.Direction = DataBarDirection.Context;
treatmentCostDataBarCondition.BarFillType = DataBarFillType.Gradient;
treatmentCostDataBarCondition.BarColor.Color = Color.FromArgb(255,166,201,236);
treatmentCostDataBarCondition.BarBorder.Type = DataBarBorderType.Solid;
treatmentCostDataBarCondition.BarBorder.Color.Color = Color.FromArgb(255,77,147,217);
treatmentCostDataBarCondition.ShowValue = true;

Icon Sets Rule

The icon sets rule displays graphical icons in the cells if they meet certain conditions. In this rule, you can define criteria for each icon and display it in the cell based on its value. This rule is designed for numerical data only. You can choose from 20 different sets of icons, including arrows, traffic lights, stars, and so on.

To use this rule in DsExcel, add the IIconSetCondition object to the FormatConditions collection. Once you've done that, set up different criteria for each icon within your IIconSetCondition object using its IconCriteria collection.

Let’s see how to show icons in the Severity column to identify the patients that are critical and need urgent inspection. This column contains text values, and this rule applies to numbers only, so we first must convert this into numbers. You can use the VLOOKUP Excel function and dummy data to convert the text to a number. Then, with a custom format, you can make these numbers appear just like the original text.

Here is the code given below for implementing the same:

worksheet.Range["J7:K9"].Value = new object[3, 2]
{
     {"High",1 },
     {"Medium",0 },
     {"Low",-1}
};
worksheet.Range["I2:I11"].Formula2 = "=VLOOKUP(D2,$J$7:$K$9,2,0)";
worksheet.Range["I2:I11"].Copy(worksheet.Range["I2:I11"], new PasteOption() { PasteType = PasteType.Values });
worksheet.Range["I2:I11"].Copy(worksheet.Range["D2:D11"]);
worksheet.Range["D2:D11"].NumberFormat = "\"High\";\"Low\";\"Medium\"";
worksheet.Range["J7:K9, I2:I11"].Delete();

Now that we have converted the text data into numbers use the AddIconSetCondition() method to add the IIconSetCondition object. You can use the Icon3Symbols enum type to show high, low, and medium severity items.

The DsExcel code below shows different icons for each Severity:

//Icon set rule on Severity
IIconSetCondition severityIconSetCondition = worksheet.Range["D2:D11"].FormatConditions.AddIconSetCondition();
severityIconSetCondition.IconSet = workbook.IconSets[IconSetType.Icon3Symbols];
severityIconSetCondition.IconCriteria[2].Value = 0;
severityIconSetCondition.IconCriteria[2].Type = ConditionValueTypes.Number;
severityIconSetCondition.IconCriteria[2].Operator = FormatConditionOperator.Greater;
severityIconSetCondition.IconCriteria[1].Operator = FormatConditionOperator.GreaterEqual;
severityIconSetCondition.IconCriteria[1].Value = 0;
severityIconSetCondition.IconCriteria[1].Type = ConditionValueTypes.Number;

Three Color Scale Rule

The three color scale rule shades the cell with different colors according to its value. The cells get colored with a mix of three colors, showing the lowest, middle, and highest values. You can pick your own colors and decide what numbers should be the low, middle, and high points.

To use the three color scale rule via DsExcel, you need to add the IColorScale object in the FormatConditions collection. After adding it, you can create specific rules for each color by working with the ColorScaleCriteria collection within your IColorScale object.

Imagine we want to use different colors to indicate a patient's condition in the TreatmentOutcome column. Like the icon set rule, the three color scale rule also works with numbers. So, we first need to change our data into numeric form.

Here's how you do it with DsExcel code:

//Change the String Data into Numerical values to apply conditional formatting
worksheet.Range["J7:K9"].Value = new object[3, 2]
{
     {"Recovered",1 },
     {"Recovering",0 },
     {"Deteriorating",-1}
};
worksheet.Range["I2:I11"].Formula2 = "=VLOOKUP(G2,$J$7:$K$9,2,0)";
worksheet.Range["I2:I11"].Copy(worksheet.Range["I2:I11"],new PasteOption() { PasteType = PasteType.Values});
worksheet.Range["I2:I11"].Copy(worksheet.Range["G2:G11"]);
worksheet.Range["G2:G11"].NumberFormat = "\"Recovered\";\"Deteriorating\";\"Recovering\"";
worksheet.Range["J7:K9, I2:I11"].Delete();

Now that we have converted the text data into numbers, use the AddColorScale() method with ColorScaleType.ThreeColorScale as a parameter to add the IColorScale object. Then, adjust the colors and conditions to make it look how you want.

The DsExcel code given below shows different colors for each treatment result:

//Three color scale rule on Treatment Outcome column
IColorScale treatmentOutcomeThreeColorScaleRule = worksheet.Range["G2:G11"].FormatConditions.AddColorScale(ColorScaleType.ThreeColorScale);
treatmentOutcomeThreeColorScaleRule.ColorScaleCriteria[0].Type = ConditionValueTypes.Number;
treatmentOutcomeThreeColorScaleRule.ColorScaleCriteria[0].Value = -1;
treatmentOutcomeThreeColorScaleRule.ColorScaleCriteria[0].FormatColor.Color = Color.FromArgb(255,251,226,213);
treatmentOutcomeThreeColorScaleRule.ColorScaleCriteria[1].Type = ConditionValueTypes.Number;
treatmentOutcomeThreeColorScaleRule.ColorScaleCriteria[1].Value = 0;
treatmentOutcomeThreeColorScaleRule.ColorScaleCriteria[1].FormatColor.Color = Color.FromArgb(255, 192, 230, 245);
treatmentOutcomeThreeColorScaleRule.ColorScaleCriteria[2].Type = ConditionValueTypes.Number;
treatmentOutcomeThreeColorScaleRule.ColorScaleCriteria[2].Value = 1;
treatmentOutcomeThreeColorScaleRule.ColorScaleCriteria[2].FormatColor.Color = Color.FromArgb(255, 218, 242, 208);

You can check out this sample implementing all the above said rules. 

Conclusion

In this blog, you’ve seen how to make your Excel data easier to read, analyze, and present using conditional formatting in a .NET app. With Document Solutions for Excel, you can take your Excel skills to the next level by adding your own code.
Check out our demos to discover the cool features of DsExcel that can improve your Excel experience.

Have any questions about this tutorial? Drop them in the comments below!

More References:

• Documentation .NET
• Product Demo JAVA
• Documentation JAVA

Ready to Master Excel Conditional Formatting? Download Document Solutions for Excel, .NET Edition Today!