条件格式使用EPPlus表达 [英] Conditional Formatting by Expression using EPPlus

问题描述

我试图用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.

我很困惑。不知道如何使用该功能。下面是我的一些问题：

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

1. 我们可以使用多个范围投入参数ExcelAddress（如
   H1：H17，L1 ：L17，AA1：AA17）


2. 公式投入Formula属性是有点像互操作Excel或没有（像我们用A1来表示当前单元格$ b？ $ b。在互操作格式化EXCEL）


3. 您能给我使用条件格式表达一个小的演示代码腿。

感谢您！

（对不起，我英文不好，我写）

(Sorry for bad English I wrote)

推荐答案

我发现我自己的解决方案，请举个例子代码：

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;

在上面的例子中，

• _formatRangeAddress 是将用于由表达式的
  条件格式的范围。在此
  范围中的第一个小区将所述的条件式中使用。 （B3）。


• _statement 是
  用于计算该条件的公式，该字符串的不
  开始用等号（ = ）（从MS Excel中的差异点），这是用来做表达
  细胞是在
  第一个单元格 _formatRangeAddress 。 （B3）。

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

希望这有助于其他人谁需要。 -Han -

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

这篇关于条件格式使用EPPlus表达的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！