EPPLUS multiple columns configuration and conditio

Question

In my tool, user can choose one configuration (through combobox->multiple datatable) and the respective table will reflect in the excel sheet as per below.Columns (data in rows will differ) that will remain the same for all configuration are Product Name, Serial Name and Length 1 and Total Length. Different configuration will have added columns such as Length 2, Length 3,Length 4 (user will add the data in these rows)etc.

I want to add conditional formatting formula in Total Length column where background cell will turn green if it is in range (minval to maxval) and red when it is out of range. I am stuck with my code without solution.It did not change any color when the user add the data in the excel. Help. Thanks!

Table

     private void ManualFormatExcelandAddRules(ExcelWorksheet WS, DataTable DTtoFormat, int ColStartAddOfDT, int RowStartAddOfDT)
        {
            int colCountofDT = DTtoFormat.Columns.Count;
            int rowCountofDT = DTtoFormat.Rows.Count;
            double minval = 0;
            double maxval = 0;
            int flag = 0; 
            for (int Colno = ColStartAddOfDT; Colno < ColStartAddOfDT + colCountofDT; Colno++)
            {
                WS.Cells[RowStartAddOfDT, Colno].Style.Border.BorderAround(ExcelBorderStyle.Thin);
                for (int RowNo = RowStartAddOfDT + 1; RowNo <= RowStartAddOfDT + rowCountofDT; RowNo++)
                { if (WS.Cells[RowNo, Colno].Text.Contains("to") && WS.Cells[RowNo, ColStartAddOfDT].Text.Contains("DRAM"))
                        {
                            string[] GuidelineVal = WS.Cells[RowNo, Colno].Text.Split("to".ToArray(), StringSplitOptions.RemoveEmptyEntries).ToArray();
                            if (GuidelineVal[0].Trim() != "NA" && GuidelineVal[1].Trim() != "NA")
                            {
                                minval = Convert.ToDouble(GuidelineVal[0].Trim());
                                maxval = Convert.ToDouble(GuidelineVal[1].Trim());
                                flag = 0;
                            }
                            else
                                flag = 1;
                        }

                        else if (WS.Cells[RowNo, Colno].Text == "" && WS.Cells[RowStartAddOfDT + 1, Colno].Text.Contains("to"))
                        {


                            if (flag == 0)
                            {

                                string _statement = "AND(Convert.ToDouble(WS.Cells[RowNo, Colno].Text) >= minval,Convert.ToDouble(WS.Cells[RowNo, Colno].Text) <= maxval)";
                                var _cond = WS.ConditionalFormatting.AddExpression(WS.Cells[RowNo, Colno]);
                                _cond.Formula = _statement;
                                _cond.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
                                _cond.Style.Fill.BackgroundColor.Color = Color.Green;                              

                            }
                            else
                                WS.Cells[RowNo, Colno].Style.Fill.BackgroundColor.SetColor(Color.Red);
                                WS.Cells[RowNo, Colno].Style.Border.BorderAround(ExcelBorderStyle.Thin);
                    }
   }
}

Answer 1

The conditional formatting expression you use is wrong/contains syntax errors/uses functions that don't exist and that makes that Excel will ignore it as it doesn't understand what it needs to do.

Looking at your code you have 4 variables that make up that expression:

• RowNo and ColNo to indicate the cell to apply the conditional formattig to
• minval and maxval to be the lower and upper bound of the condition

The following code uses those variables to build up the correct expression:

string _statement = string.Format(
    CultureInfo.InvariantCulture,
    "AND({0}>={1},{0}<={2})",
    new OfficeOpenXml.ExcelCellAddress(RowNo, ColNo).Address, 
    minval, 
    maxval );
var _cond = WS.ConditionalFormatting.AddExpression(WS.Cells[RowNo, ColNo]);
_cond.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
_cond.Style.Fill.BackgroundColor.Color = Color.Green;

_cond.Formula = _statement;

Notice that the expression uses only valid Excel functions. You can't mixin .Net statements like Convert.ToDouble. It is also important to use the InvariantCulture for the number conversion, otherwise the separators might get interpreted as an extra parameter in your function.

When you debug this _statement will contain this: AND(A2>=40.2,A2<=44.5) and when applied to the A2 cell, that works as advertised.