protected List <EvaluationConditionalFormatRule> GetRules(ISheet sheet)
{
String sheetName = sheet.SheetName;
List <EvaluationConditionalFormatRule> rules = formats[sheetName];
if (rules == null)
{
if (formats.ContainsKey(sheetName))
{
return(new List <EvaluationConditionalFormatRule>());
}
ISheetConditionalFormatting scf = sheet.SheetConditionalFormatting;
int count = scf.NumConditionalFormattings;
rules = new List <EvaluationConditionalFormatRule>(count);
formats.Add(sheetName, rules);
for (int i = 0; i < count; i++)
{
IConditionalFormatting f = scf.GetConditionalFormattingAt(i);
//optimization, as this may be expensive for lots of ranges
CellRangeAddress[] regions = f.GetFormattingRanges();
for (int r = 0; r < f.NumberOfRules; r++)
{
IConditionalFormattingRule rule = f.GetRule(r);
rules.Add(new EvaluationConditionalFormatRule(workbookEvaluator, sheet, f, i, rule, r, regions));
}
}
// need them in formatting and priority order so logic works right
rules.Sort();
}
return(rules);
}
/// <summary>
/// 单元格是否存在条件格式
/// </summary>
/// <param name="cell">单元格</param>
/// <param name="cf">条件格式</param>
/// <returns></returns>
private static bool ExistConditionalFormatting(this ICell cell, IConditionalFormatting cf)
{
CellRangeAddress[] cfRangeAddrs = cf.GetFormattingRanges();
foreach (CellRangeAddress cfRangeAddr in cfRangeAddrs)
{
if (cell.RowIndex >= cfRangeAddr.FirstRow && cell.RowIndex <= cfRangeAddr.LastRow &&
cell.ColumnIndex >= cfRangeAddr.FirstColumn && cell.ColumnIndex <= cfRangeAddr.LastColumn)
{
return(true);
}
}
return(false);
}
/// <summary>
/// 单元格是否存在条件格式
/// </summary>
/// <param name="cell">单元格</param>
/// <param name="cf">条件格式</param>
/// <returns></returns>
private static bool ExistConditionalFormatting(this ICell cell, IConditionalFormatting cf)
{
var cfRangeAddrs = cf.GetFormattingRanges();
foreach (var cfRangeAddr in cfRangeAddrs)
{
if (cell.RowIndex >= cfRangeAddr.FirstRow && cell.RowIndex <= cfRangeAddr.LastRow
&& cell.ColumnIndex >= cfRangeAddr.FirstColumn && cell.ColumnIndex <= cfRangeAddr.LastColumn)
{
return true;
}
}
return false;
}
//
public void TestRead(string sampleFile)
{
IWorkbook wb = _testDataProvider.OpenSampleWorkbook(sampleFile);
ISheet sh = wb.GetSheet("CF");
ISheetConditionalFormatting sheetCF = sh.SheetConditionalFormatting;
Assert.AreEqual(3, sheetCF.NumConditionalFormattings);
IConditionalFormatting cf1 = sheetCF.GetConditionalFormattingAt(0);
Assert.AreEqual(2, cf1.NumberOfRules);
CellRangeAddress[] regions1 = cf1.GetFormattingRanges();
Assert.AreEqual(1, regions1.Length);
Assert.AreEqual("A1:A8", regions1[0].FormatAsString());
// CF1 has two rules: values less than -3 are bold-italic red, values greater than 3 are green
IConditionalFormattingRule rule1 = cf1.GetRule(0);
Assert.AreEqual(ConditionType.CellValueIs, rule1.ConditionType);
Assert.AreEqual(ComparisonOperator.GreaterThan, rule1.ComparisonOperation);
Assert.AreEqual("3", rule1.Formula1);
Assert.IsNull(rule1.Formula2);
// Fills and borders are not Set
Assert.IsNull(rule1.GetPatternFormatting());
Assert.IsNull(rule1.GetBorderFormatting());
IFontFormatting fmt1 = rule1.GetFontFormatting();
// Assert.AreEqual(HSSFColor.GREEN.index, fmt1.FontColorIndex);
Assert.IsTrue(fmt1.IsBold);
Assert.IsFalse(fmt1.IsItalic);
IConditionalFormattingRule rule2 = cf1.GetRule(1);
Assert.AreEqual(ConditionType.CellValueIs, rule2.ConditionType);
Assert.AreEqual(ComparisonOperator.LessThan, rule2.ComparisonOperation);
Assert.AreEqual("-3", rule2.Formula1);
Assert.IsNull(rule2.Formula2);
Assert.IsNull(rule2.GetPatternFormatting());
Assert.IsNull(rule2.GetBorderFormatting());
IFontFormatting fmt2 = rule2.GetFontFormatting();
// Assert.AreEqual(HSSFColor.Red.index, fmt2.FontColorIndex);
Assert.IsTrue(fmt2.IsBold);
Assert.IsTrue(fmt2.IsItalic);
IConditionalFormatting cf2 = sheetCF.GetConditionalFormattingAt(1);
Assert.AreEqual(1, cf2.NumberOfRules);
CellRangeAddress[] regions2 = cf2.GetFormattingRanges();
Assert.AreEqual(1, regions2.Length);
Assert.AreEqual("B9", regions2[0].FormatAsString());
IConditionalFormattingRule rule3 = cf2.GetRule(0);
Assert.AreEqual(ConditionType.Formula, rule3.ConditionType);
Assert.AreEqual(ComparisonOperator.NoComparison, rule3.ComparisonOperation);
Assert.AreEqual("$A$8>5", rule3.Formula1);
Assert.IsNull(rule3.Formula2);
IFontFormatting fmt3 = rule3.GetFontFormatting();
// Assert.AreEqual(HSSFColor.Red.index, fmt3.FontColorIndex);
Assert.IsTrue(fmt3.IsBold);
Assert.IsTrue(fmt3.IsItalic);
IPatternFormatting fmt4 = rule3.GetPatternFormatting();
// Assert.AreEqual(HSSFColor.LIGHT_CORNFLOWER_BLUE.index, fmt4.FillBackgroundColor);
// Assert.AreEqual(HSSFColor.Automatic.index, fmt4.FillForegroundColor);
Assert.AreEqual((short)FillPattern.NoFill, fmt4.FillPattern);
// borders are not Set
Assert.IsNull(rule3.GetBorderFormatting());
IConditionalFormatting cf3 = sheetCF.GetConditionalFormattingAt(2);
CellRangeAddress[] regions3 = cf3.GetFormattingRanges();
Assert.AreEqual(1, regions3.Length);
Assert.AreEqual("B1:B7", regions3[0].FormatAsString());
Assert.AreEqual(2, cf3.NumberOfRules);
IConditionalFormattingRule rule4 = cf3.GetRule(0);
Assert.AreEqual(ConditionType.CellValueIs, rule4.ConditionType);
Assert.AreEqual(ComparisonOperator.LessThanOrEqual, rule4.ComparisonOperation);
Assert.AreEqual("\"AAA\"", rule4.Formula1);
Assert.IsNull(rule4.Formula2);
IConditionalFormattingRule rule5 = cf3.GetRule(1);
Assert.AreEqual(ConditionType.CellValueIs, rule5.ConditionType);
Assert.AreEqual(ComparisonOperator.Between, rule5.ComparisonOperation);
Assert.AreEqual("\"A\"", rule5.Formula1);
Assert.AreEqual("\"AAA\"", rule5.Formula2);
}
public void TestCreateCF()
{
IWorkbook workbook = _testDataProvider.CreateWorkbook();
ISheet sheet = workbook.CreateSheet();
String formula = "7";
ISheetConditionalFormatting sheetCF = sheet.SheetConditionalFormatting;
IConditionalFormattingRule rule1 = sheetCF.CreateConditionalFormattingRule(formula);
IFontFormatting fontFmt = rule1.CreateFontFormatting();
fontFmt.SetFontStyle(true, false);
IBorderFormatting bordFmt = rule1.CreateBorderFormatting();
bordFmt.BorderBottom = (/*setter*/ BorderStyle.Thin);
bordFmt.BorderTop = (/*setter*/ BorderStyle.Thick);
bordFmt.BorderLeft = (/*setter*/ BorderStyle.Dashed);
bordFmt.BorderRight = (/*setter*/ BorderStyle.Dotted);
IPatternFormatting patternFmt = rule1.CreatePatternFormatting();
patternFmt.FillBackgroundColor = (/*setter*/ HSSFColor.Yellow.Index);
IConditionalFormattingRule rule2 = sheetCF.CreateConditionalFormattingRule(ComparisonOperator.Between, "1", "2");
IConditionalFormattingRule[] cfRules =
{
rule1, rule2
};
short col = 1;
CellRangeAddress[] regions =
{
new CellRangeAddress(0, 65535, col, col)
};
sheetCF.AddConditionalFormatting(regions, cfRules);
sheetCF.AddConditionalFormatting(regions, cfRules);
// Verification
Assert.AreEqual(2, sheetCF.NumConditionalFormattings);
sheetCF.RemoveConditionalFormatting(1);
Assert.AreEqual(1, sheetCF.NumConditionalFormattings);
IConditionalFormatting cf = sheetCF.GetConditionalFormattingAt(0);
Assert.IsNotNull(cf);
regions = cf.GetFormattingRanges();
Assert.IsNotNull(regions);
Assert.AreEqual(1, regions.Length);
CellRangeAddress r = regions[0];
Assert.AreEqual(1, r.FirstColumn);
Assert.AreEqual(1, r.LastColumn);
Assert.AreEqual(0, r.FirstRow);
Assert.AreEqual(65535, r.LastRow);
Assert.AreEqual(2, cf.NumberOfRules);
rule1 = cf.GetRule(0);
Assert.AreEqual("7", rule1.Formula1);
Assert.IsNull(rule1.Formula2);
IFontFormatting r1fp = rule1.GetFontFormatting();
Assert.IsNotNull(r1fp);
Assert.IsTrue(r1fp.IsItalic);
Assert.IsFalse(r1fp.IsBold);
IBorderFormatting r1bf = rule1.GetBorderFormatting();
Assert.IsNotNull(r1bf);
Assert.AreEqual(BorderStyle.Thin, r1bf.BorderBottom);
Assert.AreEqual(BorderStyle.Thick, r1bf.BorderTop);
Assert.AreEqual(BorderStyle.Dashed, r1bf.BorderLeft);
Assert.AreEqual(BorderStyle.Dotted, r1bf.BorderRight);
IPatternFormatting r1pf = rule1.GetPatternFormatting();
Assert.IsNotNull(r1pf);
// Assert.AreEqual(HSSFColor.Yellow.index,r1pf.FillBackgroundColor);
rule2 = cf.GetRule(1);
Assert.AreEqual("2", rule2.Formula2);
Assert.AreEqual("1", rule2.Formula1);
}
/// <summary>
/// 判断单元格是否存在条件格式
/// </summary>
/// <param name="cell">单元格</param>
/// <param name="cf">条件格式</param>
private static bool ExistConditionalFormatting(this ICell cell, IConditionalFormatting cf)
{
return(cf.GetFormattingRanges().Any(cra =>
cell.RowIndex >= cra.FirstRow && cell.RowIndex <= cra.LastRow && cell.ColumnIndex >= cra.FirstColumn &&
cell.ColumnIndex <= cra.LastColumn));
}
public void TestRead()
{
IWorkbook wb = _testDataProvider.OpenSampleWorkbook("WithConditionalFormatting.xls");
ISheet sh = wb.GetSheet("CF");
ISheetConditionalFormatting sheetCF = sh.SheetConditionalFormatting;
Assert.AreEqual(3, sheetCF.NumConditionalFormattings);
IConditionalFormatting cf1 = sheetCF.GetConditionalFormattingAt(0);
Assert.AreEqual(2, cf1.NumberOfRules);
CellRangeAddress[] regions1 = cf1.GetFormattingRanges();
Assert.AreEqual(1, regions1.Length);
Assert.AreEqual("A1:A8", regions1[0].FormatAsString());
// CF1 has two rules: values less than -3 are bold-italic red, values greater than 3 are green
IConditionalFormattingRule rule1 = cf1.GetRule(0);
Assert.AreEqual(ConditionType.CELL_VALUE_IS, rule1.ConditionType);
Assert.AreEqual(ComparisonOperator.GT, rule1.ComparisonOperation);
Assert.AreEqual("3", rule1.Formula1);
Assert.IsNull(rule1.Formula2);
// Fills and borders are not Set
Assert.IsNull(rule1.GetPatternFormatting());
Assert.IsNull(rule1.GetBorderFormatting());
IFontFormatting fmt1 = rule1.GetFontFormatting();
// Assert.AreEqual(IndexedColors.GREEN.index, fmt1.FontColorIndex);
Assert.IsTrue(fmt1.IsBold);
Assert.IsFalse(fmt1.IsItalic);
IConditionalFormattingRule rule2 = cf1.GetRule(1);
Assert.AreEqual(ConditionType.CELL_VALUE_IS, rule2.ConditionType);
Assert.AreEqual(ComparisonOperator.LT, rule2.ComparisonOperation);
Assert.AreEqual("-3", rule2.Formula1);
Assert.IsNull(rule2.Formula2);
Assert.IsNull(rule2.GetPatternFormatting());
Assert.IsNull(rule2.GetBorderFormatting());
IFontFormatting fmt2 = rule2.GetFontFormatting();
// Assert.AreEqual(IndexedColors.RED.index, fmt2.FontColorIndex);
Assert.IsTrue(fmt2.IsBold);
Assert.IsTrue(fmt2.IsItalic);
IConditionalFormatting cf2 = sheetCF.GetConditionalFormattingAt(1);
Assert.AreEqual(1, cf2.NumberOfRules);
CellRangeAddress[] regions2 = cf2.GetFormattingRanges();
Assert.AreEqual(1, regions2.Length);
Assert.AreEqual("B9", regions2[0].FormatAsString());
IConditionalFormattingRule rule3 = cf2.GetRule(0);
Assert.AreEqual(ConditionType.FORMULA, rule3.ConditionType);
Assert.AreEqual(ComparisonOperator.NO_COMPARISON, rule3.ComparisonOperation);
Assert.AreEqual("$A$8>5", rule3.Formula1);
Assert.IsNull(rule3.Formula2);
IFontFormatting fmt3 = rule3.GetFontFormatting();
// Assert.AreEqual(IndexedColors.RED.index, fmt3.FontColorIndex);
Assert.IsTrue(fmt3.IsBold);
Assert.IsTrue(fmt3.IsItalic);
IPatternFormatting fmt4 = rule3.GetPatternFormatting();
// Assert.AreEqual(IndexedColors.LIGHT_CORNFLOWER_BLUE.index, fmt4.FillBackgroundColor);
// Assert.AreEqual(IndexedColors.AUTOMATIC.index, fmt4.FillForegroundColor);
Assert.AreEqual(PatternFormatting.NO_Fill, fmt4.FillPattern);
// borders are not Set
Assert.IsNull(rule3.GetBorderFormatting());
IConditionalFormatting cf3 = sheetCF.GetConditionalFormattingAt(2);
CellRangeAddress[] regions3 = cf3.GetFormattingRanges();
Assert.AreEqual(1, regions3.Length);
Assert.AreEqual("B1:B7", regions3[0].FormatAsString());
Assert.AreEqual(2, cf3.NumberOfRules);
IConditionalFormattingRule rule4 = cf3.GetRule(0);
Assert.AreEqual(ConditionType.CELL_VALUE_IS, rule4.ConditionType);
Assert.AreEqual(ComparisonOperator.LE, rule4.ComparisonOperation);
Assert.AreEqual("\"AAA\"", rule4.Formula1);
Assert.IsNull(rule4.Formula2);
IConditionalFormattingRule rule5 = cf3.GetRule(1);
Assert.AreEqual(ConditionType.CELL_VALUE_IS, rule5.ConditionType);
Assert.AreEqual(ComparisonOperator.BETWEEN, rule5.ComparisonOperation);
Assert.AreEqual("\"A\"", rule5.Formula1);
Assert.AreEqual("\"AAA\"", rule5.Formula2);
}