EPPlus Conditional Formatting String Length of Entire Column

Question

I am using EPPlus to generate Excel documents with validation and conditional formatting. I want to check the length of text in a cell and fill it with a color if it is greater than a specified length. I want this to be done for an entire column.

var address = new ExcelAddress("$A:$A");
var condition = workSheet.ConditionalFormatting.AddExpression(address);

condition.Formula = "=IF(LEN(A1)>25, TRUE, FALSE)";
condition.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
condition.Style.Fill.BackgroundColor.Color = Color.Green;

When I open the generated excel spreadsheet it displays an error asking to recover data.

score 1 · Answer 1 · answered Apr 28 '20 at 08:21

For someone looking at this thread like I was and getting an error when opening the excel, note that you can't use "=" to start your conditional expression.

Also, my Excel formula regional formatting dictates that I should use ";" to indicate multiple parameters, but in this case the formula seems to like "," as a separator. The following snippet should work:

var address = new ExcelAddress("A2");
var condition = workSheet.ConditionalFormatting.AddExpression(address);

condition.Formula = "IF(LEN(A1)>25, TRUE, FALSE)";
condition.Style.Fill.BackgroundColor.Color = Color.Green;

score 0 · Answer 2 · answered Jun 15 '16 at 15:29

when I tested this

using (var app = new ExcelPackage())
{
   var workSheet = app.Workbook.Worksheets.Add("asdf");
   var address = new ExcelAddress("$A:$A");
   var condition = workSheet.ConditionalFormatting.AddExpression(address);
   workSheet.Cells["A1"].Value = "asdfasdfasdfasdfasdfasfdasd";
   condition.Formula = "=IF(LEN(A1)>25, TRUE, FALSE)";
   condition.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
   condition.Style.Fill.BackgroundColor.Color = System.Drawing.Color.Green;
   var destinationPath = @"../../GeneratedExcelFile.xlsx";
   File.WriteAllBytes(destinationPath, app.GetAsByteArray());
}

it didn't cause any error so I think the issue is caused by something other than the code you provided

Have you tried opening the excel sheet being generated? It doesn't throw an error server side. I ran the snippet you provided and it generates everything ok and will throw the error when opened. — GFree, Jun 15 '16 at 20:26

score 0 · Answer 3 · answered Feb 28 '18 at 05:43

Here is a whole new approach to conditional formatting: you can use LINQ to retrieve cell addresses based on your condition. Just make sure to add in your list additional property for storing Excel Row numbers (iRow in below).

string sRng = string.Join(",", YourModel.Where(l => l.YourColumn.Length > 25)
    .Select(a => "A" + a.iRow)); // this address could be many pages and it works

if (sRng.Length > 0) {
    ws.Cells[sRng].Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.Green); 
}

This approach is lightning fast, more flexible, and unlike conditional formatting doesn't sacrifice Excel performance. Here is the full article:

https://www.codeproject.com/Tips/1231992/Conditional-Formatting-in-Excel-with-LINQ-and-EPPl

What's good about EPPlus I didn't see restriction in range addresses - in a single string you can pass addresses of about 15,000 - 20,000 cells and format all of them instantaneously. The only disadvantage that it won't be dynamic for user playing with data and wanting to see how formats are changing (like in Excel Conditional Formatting).

EPPlus Conditional Formatting String Length of Entire Column

3 Answers3

Linked