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

查看:387
本文介绍了条件格式使用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屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆