EPPlus Conditional Formatting

Question

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

Try first to add your conditionnal format to 4 cells, A2, A3, A4, A5, instead of doing it on a range. I got some issue when I tried to do on a range. — DanB

VDWWD VDWWD · Accepted Answer · 2018-10-10T13:28:53

For starters, there is an = missing from the formula. And I don't know what the purpose of SEARCH($A$1;C2) is, but the below code works.

//the range of cells to be searched
var cells = new ExcelAddress("A1:Z10");

//the excel formula, note that it uses the top left cell of the range
//so if the range was C5:d10, it would be =ISNUMBER(C5)
var formula = "=ISNUMBER(A1)";

var condition = worksheet.ConditionalFormatting.AddExpression(cells);
condition.Formula = formula;
condition.Style.Fill.PatternType = ExcelFillStyle.Solid;
condition.Style.Fill.BackgroundColor.Color = Color.Yellow;

EPPlus Conditional Formatting

2 Answers