How to add conditional formatting with EPPlus for Excel 2010

Question

I am using Epplus library to add conditional formatting to an existing Excel spreadsheet with the following code:

var conditionalFormatting = worksheet.Cells[address].ConditionalFormatting.AddExpression();
conditionalFormatting.Formula = $"=IF(EXACT(A1, \"\"), IF(EXACT(B1, \"\"), TRUE, FALSE), FALSE)";
conditionalFormatting.Style.Fill.BackgroundColor = errorColor;

This code seems to be working fine since when I open the spreadsheet with Excel 2016 I can see the appropriate behaviour.

My Question

On opening the same spreadsheet with Excel 2010, I get this message:

Excel found unreadable content in 'Spreadsheet.xlsx'. Do you want to recover the contents of this Workbook?

Recovering the contents: Excel prompts with:

Removed Feature: Conditional formatting from /xl/worksheets/sheet1.xml part

Did this ever happen to anyone else? I think EPPlus library should work fine with Excel 2007+

Does anyone know of any workaround?

singularhum singularhum · Accepted Answer · 2017-08-19T00:39:15

Try removing the = sign from the Formula.

conditionalFormatting.Formula = $"IF(EXACT(A1, \"\"), IF(EXACT(B1, \"\"), TRUE, FALSE), FALSE)";

By adding the = it would result in the formula to be shown as ==IF(EXACT... which is invalid. Formulas for conditional formatting are not stored with the beginning = sign. If this still works in Excel 2016, maybe it accepts/ignores the extra =?

How to add conditional formatting with EPPlus for Excel 2010

1 Answers