使用EPPlus的表达式的条件格式。

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

我正在尝试使用EPPlus的条件格式特性来格式化一些范围。我读了很多文档，但是没有提到条件格式表达式。

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

我很困惑。不知道如何使用这个特性。以下是我的一些问题:

Can we use multiple range to put into parameter ExcelAddress (like "H1:H17,L1:L17,"AA1:AA17")
我们是否可以使用多个range将参数设置为ExcelAddress(比如“H1:H17,L1:L17，”AA1:AA17”)
The formula is put into Formula property is somehow like Interop Excel or not? (like we use "A1" to represent for the current cell for formatting in interop excel)
公式被放到公式属性中，是不是有点像Interop Excel ?(就像我们在interop excel中使用“A1”表示当前单元格的格式一样)
Can you give me a small demo code leg that use Conditional Formatting Expression.
你能给我一个使用条件格式表达式的小演示代码吗?

Thank you!

谢谢你！

(Sorry for bad English I wrote)

(不好意思，我写得不好)

3 个解决方案

#1

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 previous cell or current cell is BLANK:
// B3 is the current cell because the range _formatRangeAddress starts from B3.
// OFFSET(B3,0,-1) returns the previous cell's value. It's excel function.
string _statement = "IF(OR(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 applied for the conditional formatting by the expression. The first cell in this range will be used in the condition formula. (B3).
_formatRangeAddress是表达式为条件格式应用的范围。这个范围内的第一个单元格将用于条件公式。(B3)。
_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).
_statement是用于计算条件的公式，该字符串不以等号(=)开头(与MS Excel不同)，用于生成表达式的单元格是_formatRangeAddress中的第一个单元格。(B3)。

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

希望这对需要帮助的人有帮助。汉,

#2

There is support for conditional formatting in the 3.1 beta version of EPPlus.

EPPlus的3.1 beta版支持条件格式。

Take a look at the source-code here: http://epplus.codeplex.com/discussions/348196/

看看这里的源代码:http://epplus.codeplex.com/discussions/348196/。

#3

After many moons I found way more flexible and fast approach to do this using LINQ and EPPlus. All you need to do is: add extra property to your list to save Excel Row Numbers, and then retrieve cell addresses using LINQ. In this case it would look like this:

在许多次的卫星之后，我发现了使用LINQ和EPPlus更加灵活和快速的方法。您需要做的就是:向列表中添加额外的属性以保存Excel行号，然后使用LINQ检索单元地址。在这种情况下，它是这样的:

string sRng = string.Join(",", YourModel.Where(f => f.YourField == null)
    .Select(a => "H" + a.iRow + ",L" + a.iRow + ",AA" + 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); 
}

Here is the full article:

全文如下:

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

Also see here another example: https://*.com/a/49022692/8216122 hope this helps somebody in the future.

还可以看到另一个示例:https://*.com/a/49022692/8216122希望这对将来的人有帮助。

#1