This project has moved and is read-only. For the latest updates, please go here.

Format condition by Expression

Nov 2, 2012 at 2:57 PM

Hi experts,

I'm trying to format some range by using conditionalFormatting feature of EPPlus. I read many document but there is nowhere mentions about Conditional Formatting Expression.

I'm very confusing. Don't know how to use that feature. Here are my some questions:

  1. Can we use multiple range to put into parameter ExcelAddress (like "H1:H17,L1:L17,"AA1:AA17")
  2. The formual is put into Formual property is somehow like Interop Excel or not? (like we use "A1" to represent for the current cell for formatting in interop excel)
  3. Can you give me a small demo code leg that use Conditional Formatting Expression.

Thank you!

(Sorry for bad English I wrote)

Nov 5, 2012 at 10:15 AM
Edited Nov 5, 2012 at 10:16 AM

I have found out solution by myself. Please take an example code:

ExcelAddress _formatRangeAddress = new ExcelAddress("B3:B10,D3:D10,F3:F10,H3:H10:J3:J10"); 
// fill WHITE color if one of previous cell or current cell is BLANK: 
string _statement = "IF(AND(ISBLANK(OFFSET(B3,0,-1)),ISBLANK(B3)),1,0)"; 
var _cond4 = sheet.ConditionalFormatting.AddExpression(_formatRangeAddress);
 _cond4.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
 _cond4.Style.Fill.BackgroundColor.Color = Color.White; _cond4.Formula = _statement; 
// fill GREEN color if value of the current cell is greater than 
// or equals to value of the previous cell 
_statement = "IF(OFFSET(B3,0,-1)-B3<=0,1,0)"; 
var _cond1 = sheet.ConditionalFormatting.AddExpression(_formatRangeAddress);
_cond1.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
_cond1.Style.Fill.BackgroundColor.Color = Color.Green;
_cond1.Formula = _statement; // fill RED color if value of the current cell is less than // value of the previous cell _statement = "IF(OFFSET(B3,0,-1)-B3>0,1,0)";
var
_cond3 = sheet.ConditionalFormatting.AddExpression(_formatRangeAddress);
_cond3.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
_cond3.Style.Fill.BackgroundColor.Color = Color.Red;
_cond3.Formula = _statement;

In the above example, _formatRangeAddress is the range that will be apply for the conditional formatting by the expression. _statement is the formula used to calculate the condition, this string doesn't start with equal sign (=) (difference point from MS Excel), the cell which is used to make expression is the first cell in the _formatRangeAddress. (B3).

Hope this is helpful to others who need. -Han-

Marked as answer by hanmai on 10/26/2014 at 6:45 AM
Sep 2, 2014 at 11:54 AM
Hello Hanmai,

I am using similar code to do conditional formattiong but itys not working, pleae check my below code and let me know where I am wrong:
            ExcelPackage pck = new ExcelPackage();
            var ws = pck.Workbook.Worksheets.Add("Sample1");

            var _formatRangeAddress = new ExcelAddress("H16:K31,H33:K44,H46:K57,H59:K69,H71:K73");
            string _statement = "=AND(COUNTA(H16:H16)<2,COUNTA(H16:K16)>0)";
            var _cond4 = ws.ConditionalFormatting.AddExpression(_formatRangeAddress);
            _cond4.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
            _cond4.Style.Fill.BackgroundColor.Color = Color.Green;
            _cond4.Formula = _statement;          

            pck.SaveAs(Response.OutputStream);
            Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
            Response.AddHeader("content-disposition", "attachment;  filename=Sample1.xlsx");
Thanks in advance
Oct 26, 2014 at 2:44 PM
Sorry that I don't visit this page as well as check my outlook frequently. Your statement contains equal sign (=) while it should not.
I have no more other comment than all experiences that I've shared in my above example. -Han-
Marked as answer by hanmai on 10/26/2014 at 6:45 AM