CODEWITHSUNDEEP
c#excelexcel-formulaepplus
GFree
GFree

Reputation: 31

EPPlus Conditional Formatting String Length of Entire Column

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.

Upvotes: 1

Views: 2546

Answers (3)

Wimpix
Wimpix

Reputation: 11

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;

Upvotes: 1

Richard Mneyan
Richard Mneyan

Reputation: 692

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).

Upvotes: 0

chandler
chandler

Reputation: 1150

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

Upvotes: 0

Related Questions