Search code examples
c#.netepplus

EPPlus Conditional Formatting

I've tried to follow this one: Conditional Formatting by Expression using EPPlus

But in my case, the excel file was corrupted and give me option to recover with rule removed.

I want to achieve this (simplified): screenshot

Here's my codes (for column A):

ExcelWorksheet ew = ep.Workbook.Worksheets.Add("Sheet1");

var cells = new ExcelAddress("A2:A5");
string formula = "ISNUMBER(SEARCH($A$1;C2))";
var condition = ew.ConditionalFormatting.AddExpression(cells);
condition.Formula = formula;
condition.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
condition.Style.Fill.BackgroundColor.Color = System.Drawing.Color.Yellow;

Thanks in advance

Solution

  • The reason you're getting the corruption error is due to the semi colon in the formula. A semi-colon is not a valid operator in this formula.

    In response to VDWWD - I don't think the equal sign is a problem, I get the corruption error if the equal sign is used in the formula.

    From the EPPlus Documentation

    • Don't use localized function names. Only english names (such as SUM, IF, VLOOKUP, etc) are supported.
    • Don't use semicolon as a separator between function arguments. Only comma is supported.
    • Don't add the leading = sign in your formula. "=SUM(A1:A2)" is wrong, "SUM(A1:A2)" is correct.

    Epplus Formula Calculation