2

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

randomguy
  • 23
  • 3
  • 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 Oct 10 '18 at 16:11

2 Answers2

2

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;
VDWWD
  • 32,238
  • 19
  • 56
  • 70
0

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

Anepicpastry
  • 422
  • 2
  • 9
  • Ah, thanks for pointing out: only comma is supported. My Excel's default separator is semicolon though. – randomguy Oct 11 '18 at 02:27
  • Ah, I didn't know that was possible until now. Not sure if you want to but it looks like you can change that through language settings (assuming you're on windows): https://lockone.wordpress.com/2015/08/06/excel-now-using-semicolons-instead-of-commas-in-separating-formulas-fix-it-now/ – Anepicpastry Oct 11 '18 at 13:19