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