public void TestRemove() { IWorkbook wb = _testDataProvider.CreateWorkbook(); ISheet sheet1 = wb.CreateSheet(); ISheetConditionalFormatting sheetCF = sheet1.SheetConditionalFormatting; Assert.AreEqual(0, sheetCF.NumConditionalFormattings); IConditionalFormattingRule rule1 = sheetCF.CreateConditionalFormattingRule( ComparisonOperator.Equal, "SUM(A1:A5)"); // adjacent Address are merged int formatIndex = sheetCF.AddConditionalFormatting( new CellRangeAddress[] { CellRangeAddress.ValueOf("A1:A5") }, rule1); Assert.AreEqual(0, formatIndex); Assert.AreEqual(1, sheetCF.NumConditionalFormattings); sheetCF.RemoveConditionalFormatting(0); Assert.AreEqual(0, sheetCF.NumConditionalFormattings); try { Assert.IsNull(sheetCF.GetConditionalFormattingAt(0)); Assert.Fail("expected exception"); } catch (ArgumentException e) { Assert.IsTrue(e.Message.StartsWith("Specified CF index 0 is outside the allowable range")); } formatIndex = sheetCF.AddConditionalFormatting( new CellRangeAddress[] { CellRangeAddress.ValueOf("A1:A5") }, rule1); Assert.AreEqual(0, formatIndex); Assert.AreEqual(1, sheetCF.NumConditionalFormattings); sheetCF.RemoveConditionalFormatting(0); Assert.AreEqual(0, sheetCF.NumConditionalFormattings); try { Assert.IsNull(sheetCF.GetConditionalFormattingAt(0)); Assert.Fail("expected exception"); } catch (ArgumentException e) { Assert.IsTrue(e.Message.StartsWith("Specified CF index 0 is outside the allowable range")); } }
private void RemoveConditionalFormatting(ISheet sheet) { ISheetConditionalFormatting cf = sheet.SheetConditionalFormatting; for (int j = 0; j < cf.NumConditionalFormattings; j++) { cf.RemoveConditionalFormatting(j); } }
public void TestBasic() { IWorkbook wb = _testDataProvider.CreateWorkbook(); ISheet sh = wb.CreateSheet(); ISheetConditionalFormatting sheetCF = sh.SheetConditionalFormatting; Assert.AreEqual(0, sheetCF.NumConditionalFormattings); try { Assert.IsNull(sheetCF.GetConditionalFormattingAt(0)); Assert.Fail("expected exception"); } catch (ArgumentException e) { Assert.IsTrue(e.Message.StartsWith("Specified CF index 0 is outside the allowable range")); } try { sheetCF.RemoveConditionalFormatting(0); Assert.Fail("expected exception"); } catch (ArgumentException e) { Assert.IsTrue(e.Message.StartsWith("Specified CF index 0 is outside the allowable range")); } IConditionalFormattingRule rule1 = sheetCF.CreateConditionalFormattingRule("1"); IConditionalFormattingRule rule2 = sheetCF.CreateConditionalFormattingRule("2"); IConditionalFormattingRule rule3 = sheetCF.CreateConditionalFormattingRule("3"); IConditionalFormattingRule rule4 = sheetCF.CreateConditionalFormattingRule("4"); try { sheetCF.AddConditionalFormatting(null, rule1); Assert.Fail("expected exception"); } catch (ArgumentException e) { Assert.IsTrue(e.Message.StartsWith("regions must not be null")); } try { sheetCF.AddConditionalFormatting( new CellRangeAddress[] { CellRangeAddress.ValueOf("A1:A3") }, (IConditionalFormattingRule)null); Assert.Fail("expected exception"); } catch (ArgumentException e) { Assert.IsTrue(e.Message.StartsWith("cfRules must not be null")); } try { sheetCF.AddConditionalFormatting( new CellRangeAddress[] { CellRangeAddress.ValueOf("A1:A3") }, new IConditionalFormattingRule[0]); Assert.Fail("expected exception"); } catch (ArgumentException e) { Assert.IsTrue(e.Message.StartsWith("cfRules must not be empty")); } try { sheetCF.AddConditionalFormatting( new CellRangeAddress[] { CellRangeAddress.ValueOf("A1:A3") }, new IConditionalFormattingRule[] { rule1, rule2, rule3, rule4 }); Assert.Fail("expected exception"); } catch (ArgumentException e) { Assert.IsTrue(e.Message.StartsWith("Number of rules must not exceed 3")); } }
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); }