Conditional Formatting in Excel Spreadsheets with ExpertXls |
Conditional formatting changes the look of cells by their values when the workbook is displayed in Excel, so the formatting follows the values when they are changed. The ConditionalFormats property of a range returns an ExcelConditionalFormatsCollection with methods to add the rules; each method returns an ExcelConditionalFormat.
Cell value rules compare the cell value with one or two values, for example greater than 1000 or between 10 and 20, and set the font color, the background color, bold, italic, underline or strikethrough.
Formula rules format the cells for which a formula evaluates to TRUE.
Color scales, data bars and icon sets visualize the values of the range.
All the rule types are stored in the Excel 97-2003 (.xls) and Excel 2007 or later (.xlsx) formats. In the Excel 97-2003 format the font and fill colors of a rule are replaced by the nearest colors of the workbook palette. A range can have several rules, and they can be removed one by one or all at once (see Removing Rules).
The values are Excel formulas without the leading equal sign: a number like "1000", a cell reference like "$H$2" or a text in double quotes.
// quarters greater than 1000: green fill, dark green bold font ExcelConditionalFormat high = worksheet["C7:F18"].ConditionalFormats.AddCellValueRule( ExcelConditionalFormatOperator.GreaterThan, "1000"); high.BackColor = Color.FromArgb(198, 239, 206); high.FontColor = Color.FromArgb(0, 97, 0); high.Bold = true; // totals between 2000 and 3000: yellow fill, italic font ExcelConditionalFormat middle = worksheet["G7:G18"].ConditionalFormats.AddCellValueRule( ExcelConditionalFormatOperator.Between, "2000", "3000"); middle.BackColor = Color.FromArgb(255, 235, 156); middle.Italic = true;
The relative references of the formula are relative to the top left cell of the range: in the example below each product name is compared with the total of its own row.
A color scale fills the cells with a two or three color gradient from the lowest to the highest value. A data bar draws a bar proportional to the value in each cell. An icon set displays an icon chosen by the value (ExcelConditionalFormatIconSet); the range of values is divided in equal percent intervals, one for each icon.
// red for the lowest values, yellow in the middle, green for the highest worksheet["C7:F18"].ConditionalFormats.AddColorScale( Color.FromArgb(248, 105, 107), Color.FromArgb(255, 235, 132), Color.FromArgb(99, 190, 123)); // bars proportional to the totals worksheet["G7:G18"].ConditionalFormats.AddDataBar(Color.FromArgb(99, 142, 198)); // three arrows; ShowIconOnly hides the values ExcelConditionalFormat arrows = worksheet["H7:H18"].ConditionalFormats.AddIconSet(ExcelConditionalFormatIconSet.ThreeArrows); arrows.ShowIconOnly = false;
The ConditionalFormatting sample of the download (console, Windows Forms, ASP.NET and Blazor) creates a workbook with all these rules.
RemoveAt(Int32) removes one rule of a range by its index in the collection, and Remove(ExcelConditionalFormat) removes a rule object returned by the collection or by the method that added it. The other rules of the range and the rules of the other ranges are kept. When the range is a part of a larger range that has the rule, only the cells of the range lose it. Clear removes all the rules of a range. This works the same in workbooks created in code and in workbooks opened from a file, in both formats.
// remove the color scales of the quarters, keep their other rules ExcelConditionalFormatsCollection rules = worksheet["C7:F18"].ConditionalFormats; for (int i = rules.Count - 1; i >= 0; i--) { if (rules[i].Type == ExcelConditionalFormatType.ColorScale) rules.RemoveAt(i); } // remove a rule by the object returned when it was added ExcelConditionalFormat bars = worksheet["G7:G18"].ConditionalFormats.AddDataBar(Color.FromArgb(99, 142, 198)); worksheet["G7:G18"].ConditionalFormats.Remove(bars); // remove all the rules of a range worksheet["H7:H18"].ConditionalFormats.Clear();