Conditional Formatting ‐ Order in the Worksheet

$$\color{#D29922}\textsf{\Large⚠\kern{0.2cm}\normalsize Below only applicable from Epplus 7.0 onwards}$$

Occasionally when a worksheet is loaded the order in ExcelWorksheet.ConditionalFormatting may be different from the order they were saved in. This is because of how Excel saves the xml and how we read it.

Some conditionalFormattings are a bit special. Any conditionalFormatting referring to an external worksheet, All databars, Iconsets with customIcons or including 3Stars, 3Triangles or 5Boxes. And other special cases are ExtLst conditionalFormattings.

Normally this has no larger effect for a user and they can be handled just like any other conditionalFormatting. But these special cases will always be read in last in ExcelWorksheet.ConditionalFormatting. So for example even if ExcelWorksheet.ConditionalFormatting has the order

{[Databar], [BeginsWith], [EndsWith]} it will be read in from file in order {[BeginsWith], [EndsWith], [Databar]} as long as BeginsWith and EndsWith does not refer to any external worksheets.

We therefore recommend to not rely on this order when writing code after reading files but rather use RulesByPriority, Linq expressions or some other means of reciving your preferred order of ConditionalFormattings if order of conditionalFormattings matters in your project. e.g

var sheet = pck.Workbook.Worksheets[0];
var allBeginsWith = sheet.ConditionalFormatting.ToList().Find(x => x.As.BeginsWith != null);

To get all ConditionalFormattings of the BeginsWith type.