Excel C＃中的EPPLUS多列配置和条件格式 [英] EPPLUS multiple columns configuration and conditional formatting in Excel C#

问题描述

在我的工具中，用户可以选择一种配置（通过combobox-> multiple datatable），相应的表将如下所示反映在excel工作表中。是产品名称，序列名称，长度1和总长度。不同的配置将添加长度2，长度3，长度4等列（用户将在这些行中添加数据）等。

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.

我想添加条件格式公式在总长度列中，如果背景单元格在范围内（从最小到最大），背景单元格将变为绿色；而在范围外时，背景单元格将变为红色。我陷入了没有解决方案的代码中。当用户在excel中添加数据时，它没有改变任何颜色。帮帮我。谢谢！

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!

表

     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);
                    }
   }
}

推荐答案

您使用的条件格式表达式是错误的/包含语法错误/使用了不存在的函数，这使Excel由于不了解需要做什么而将其忽略。

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.

查看您的代码，您有4个变量组成该表达式：

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

• RowNo 和 ColNo 表示要对


• minval 和 maxval 作为条件的上下限

• 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;

请注意，该表达式仅使用有效的Excel函数。您不能混入 Convert.ToDouble 之类的.Net语句。使用InvariantCulture进行数字转换也很重要，否则分隔符可能会在函数中解释为额外的参数。

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.

调试时，此_statement将包含此内容： AND（A2> = 40.2，A2< = 44.5），当应用于A2单元时，其效果与广告中的相同。

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.

这篇关于Excel C＃中的EPPLUS多列配置和条件格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！