diff --git a/ClosedXML/Excel/Ranges/XLRangeBase.cs b/ClosedXML/Excel/Ranges/XLRangeBase.cs index 17df4e2..64e49db 100644 --- a/ClosedXML/Excel/Ranges/XLRangeBase.cs +++ b/ClosedXML/Excel/Ranges/XLRangeBase.cs @@ -334,6 +334,7 @@ if (includeFormats) { ClearMerged(); + RemoveConditionalFormatting(); } if (clearOptions == XLClearOptions.ContentsAndFormats) @@ -348,6 +349,56 @@ return this; } + private void RemoveConditionalFormatting() + { + var mf = RangeAddress.FirstAddress; + var ml = RangeAddress.LastAddress; + foreach (var format in Worksheet.ConditionalFormats.Where(x => x.Range.Intersects(this)).ToList()) + { + var f = format.Range.RangeAddress.FirstAddress; + var l = format.Range.RangeAddress.LastAddress; + bool byWidth = false, byHeight = false; + XLRange rng1 = null, rng2 = null; + if (mf.ColumnNumber <= f.ColumnNumber && ml.ColumnNumber >= l.ColumnNumber) + { + if (mf.RowNumber.Between(f.RowNumber, l.RowNumber) || ml.RowNumber.Between(f.RowNumber, l.RowNumber)) + { + if (mf.RowNumber > f.RowNumber) + rng1 = Worksheet.Range(f.RowNumber, f.ColumnNumber, mf.RowNumber - 1, l.ColumnNumber); + if (ml.RowNumber < l.RowNumber) + rng2 = Worksheet.Range(ml.RowNumber + 1, f.ColumnNumber, l.RowNumber, l.ColumnNumber); + } + byWidth = true; + } + + if (mf.RowNumber <= f.RowNumber && ml.RowNumber >= l.RowNumber) + { + if (mf.ColumnNumber.Between(f.ColumnNumber, l.ColumnNumber) || ml.ColumnNumber.Between(f.ColumnNumber, l.ColumnNumber)) + { + if (mf.ColumnNumber > f.ColumnNumber) + rng1 = Worksheet.Range(f.RowNumber, f.ColumnNumber, l.RowNumber, mf.ColumnNumber - 1); + if (ml.ColumnNumber < l.ColumnNumber) + rng2 = Worksheet.Range(f.RowNumber, ml.ColumnNumber + 1, l.RowNumber, l.ColumnNumber); + } + byHeight = true; + } + + if (rng1 != null) + { + format.Range = rng1; + } + if (rng2 != null) + { + //TODO: reflect the formula for a new range + if (rng1 == null) + format.Range = rng2; + else + ((XLConditionalFormat)rng2.AddConditionalFormat()).CopyFrom(format); + } + if (byWidth && byHeight) Worksheet.ConditionalFormats.Remove(x => x == format); + } + } + public void DeleteComments() { Cells().DeleteComments(); diff --git a/ClosedXML_Tests/Excel/Ranges/XLRangeBaseTests.cs b/ClosedXML_Tests/Excel/Ranges/XLRangeBaseTests.cs index 28a7b6f..e3ee4e3 100644 --- a/ClosedXML_Tests/Excel/Ranges/XLRangeBaseTests.cs +++ b/ClosedXML_Tests/Excel/Ranges/XLRangeBaseTests.cs @@ -298,5 +298,94 @@ Assert.AreEqual(0, ws.Range("C3:D6").AsRange().SurroundingCells(c => !c.IsEmpty()).Count()); } } + + [Test] + public void ClearConditionalFormattingsWhenRangeAbove1() + { + var ws = new XLWorkbook().Worksheets.Add("Sheet1"); + ws.Range("C3:D7").AddConditionalFormat(); + ws.Range("B2:E3").Clear(XLClearOptions.Formats); + + Assert.AreEqual(1, ws.ConditionalFormats.Count()); + Assert.AreEqual("C4:D7", ws.ConditionalFormats.Single().Range.RangeAddress.ToStringRelative()); + } + + [Test] + public void ClearConditionalFormattingsWhenRangeAbove2() + { + var ws = new XLWorkbook().Worksheets.Add("Sheet1"); + ws.Range("C3:D7").AddConditionalFormat(); + ws.Range("C3:D3").Clear(XLClearOptions.Formats); + + Assert.AreEqual(1, ws.ConditionalFormats.Count()); + Assert.AreEqual("C4:D7", ws.ConditionalFormats.Single().Range.RangeAddress.ToStringRelative()); + } + + [Test] + public void ClearConditionalFormattingsWhenRangeBelow1() + { + var ws = new XLWorkbook().Worksheets.Add("Sheet1"); + ws.Range("C3:D7").AddConditionalFormat(); + ws.Range("B7:E8").Clear(XLClearOptions.Formats); + + Assert.AreEqual(1, ws.ConditionalFormats.Count()); + Assert.AreEqual("C3:D6", ws.ConditionalFormats.Single().Range.RangeAddress.ToStringRelative()); + } + + [Test] + public void ClearConditionalFormattingsWhenRangeBelow2() + { + var ws = new XLWorkbook().Worksheets.Add("Sheet1"); + ws.Range("C3:D7").AddConditionalFormat(); + ws.Range("C7:D7").Clear(XLClearOptions.Formats); + + Assert.AreEqual(1, ws.ConditionalFormats.Count()); + Assert.AreEqual("C3:D6", ws.ConditionalFormats.Single().Range.RangeAddress.ToStringRelative()); + } + + [Test] + public void ClearConditionalFormattingsWhenRangeRowInMiddle() + { + var ws = new XLWorkbook().Worksheets.Add("Sheet1"); + ws.Range("C3:D7").AddConditionalFormat(); + ws.Range("C5:E5").Clear(XLClearOptions.Formats); + + Assert.AreEqual(2, ws.ConditionalFormats.Count()); + Assert.IsTrue(ws.ConditionalFormats.Any(x => x.Range.RangeAddress.ToStringRelative() == "C3:D4")); + Assert.IsTrue(ws.ConditionalFormats.Any(x => x.Range.RangeAddress.ToStringRelative() == "C6:D7")); + } + + [Test] + public void ClearConditionalFormattingsWhenRangeColumnInMiddle() + { + var ws = new XLWorkbook().Worksheets.Add("Sheet1"); + ws.Range("C3:G4").AddConditionalFormat(); + ws.Range("E2:E4").Clear(XLClearOptions.Formats); + + Assert.AreEqual(2, ws.ConditionalFormats.Count()); + Assert.IsTrue(ws.ConditionalFormats.Any(x => x.Range.RangeAddress.ToStringRelative() == "C3:D4")); + Assert.IsTrue(ws.ConditionalFormats.Any(x => x.Range.RangeAddress.ToStringRelative() == "F3:G4")); + } + + [Test] + public void ClearConditionalFormattingsWhenRangeContainsFormatWhole() + { + var ws = new XLWorkbook().Worksheets.Add("Sheet1"); + ws.Range("C3:G4").AddConditionalFormat(); + ws.Range("B2:G4").Clear(XLClearOptions.Formats); + + Assert.AreEqual(0, ws.ConditionalFormats.Count()); + } + + [Test] + public void NoClearConditionalFormattingsWhenRangePartiallySuperimposed() + { + var ws = new XLWorkbook().Worksheets.Add("Sheet1"); + ws.Range("C3:G4").AddConditionalFormat(); + ws.Range("C2:D3").Clear(XLClearOptions.Formats); + + Assert.AreEqual(1, ws.ConditionalFormats.Count()); + Assert.AreEqual("C3:G4", ws.ConditionalFormats.Single().Range.RangeAddress.ToStringRelative()); + } } }