CODEWITHSUNDEEP
c#.netepplus
randomguy
randomguy

Reputation: 23

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

Upvotes: 2

Views: 3027

Answers (2)

Anepicpastry
Anepicpastry

Reputation: 542

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

Upvotes: 0

VDWWD
VDWWD

Reputation: 35514

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;

Upvotes: 2

Related Questions