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 EPPlus reads it.

Some conditional formattings types might not be read in the same order as they were saved. This includes (but not limited to) conditional formats referring to an external worksheet, databars, iconsets with custom icons or including 3Stars, 3Triangles or 5Boxes.

Normally this has no larger effect for a user and they can be handled just like any other conditional formatting. 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 } when the file is saved. 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 ExcelConditionFormattingCollection.RulesByPriority(int priority), Linq expressions or some other means of reading in your preferred order of conditional formattings if this order matters in your code.

Here is an example on how you can read a specific type of conditional formats without being dependant on the order.

var sheet = pck.Workbook.Worksheets[0];
var allBeginsWith = sheet.ConditionalFormatting
                        .Where(x => x.Type == eExcelConditionalFormattingRuleType.BeginsWith)
                        .Select(x => x.As.BeginsWith);

See also

• Conditional formatting
• Conditional formatting - priority