diff --git a/ClosedXML/Excel/Ranges/XLRangeBase.cs b/ClosedXML/Excel/Ranges/XLRangeBase.cs index 96cd2db..9e2edaa 100644 --- a/ClosedXML/Excel/Ranges/XLRangeBase.cs +++ b/ClosedXML/Excel/Ranges/XLRangeBase.cs @@ -1193,7 +1193,6 @@ Int32 firstColumnReturn = RangeAddress.FirstAddress.ColumnNumber; Int32 lastColumnReturn = RangeAddress.FirstAddress.ColumnNumber + numberOfColumns - 1; - Worksheet.BreakConditionalFormatsIntoCells(cellsToDelete.Except(cellsToInsert.Keys).ToList()); using (var asRange = AsRange()) Worksheet.NotifyRangeShiftedColumns(asRange, numberOfColumns); @@ -1437,7 +1436,6 @@ Int32 firstColumnReturn = RangeAddress.FirstAddress.ColumnNumber; Int32 lastColumnReturn = RangeAddress.LastAddress.ColumnNumber; - Worksheet.BreakConditionalFormatsIntoCells(cellsToDelete.Except(cellsToInsert.Keys).ToList()); using (var asRange = AsRange()) Worksheet.NotifyRangeShiftedRows(asRange, numberOfRows); @@ -1586,7 +1584,6 @@ var hyperlinksToRemove = Worksheet.Hyperlinks.Where(hl => Contains(hl.Cell.AsRange())).ToList(); hyperlinksToRemove.ForEach(hl => Worksheet.Hyperlinks.Delete(hl)); - Worksheet.BreakConditionalFormatsIntoCells(cellsToDelete.Except(cellsToInsert.Keys).ToList()); using (var shiftedRange = AsRange()) { if (shiftDeleteCells == XLShiftDeletedCells.ShiftCellsUp) diff --git a/ClosedXML/Excel/XLWorksheet.cs b/ClosedXML/Excel/XLWorksheet.cs index be9cc23..1c7b41b 100644 --- a/ClosedXML/Excel/XLWorksheet.cs +++ b/ClosedXML/Excel/XLWorksheet.cs @@ -1237,30 +1237,36 @@ private void ShiftConditionalFormattingColumns(XLRange range, int columnsShifted) { - Int32 firstColumn = range.RangeAddress.FirstAddress.ColumnNumber; - if (firstColumn == 1) return; + if (!ConditionalFormats.Any()) return; + Int32 firstCol = range.RangeAddress.FirstAddress.ColumnNumber; + if (firstCol == 1) return; - Int32 lastColumn = range.RangeAddress.FirstAddress.ColumnNumber + columnsShifted - 1; - Int32 firstRow = range.RangeAddress.FirstAddress.RowNumber; - Int32 lastRow = range.RangeAddress.LastAddress.RowNumber; - var insertedRange = Range(firstRow, firstColumn, lastRow, lastColumn); - var fc = insertedRange.FirstColumn(); - var model = fc.ColumnLeft(); - Int32 modelFirstRow = model.RangeAddress.FirstAddress.RowNumber; - if (ConditionalFormats.Any(cf => cf.Range.Intersects(model))) + int colNum = columnsShifted > 0 ? firstCol - 1 : firstCol; + var model = Column(colNum).AsRange(); + + foreach (var cf in ConditionalFormats.ToList()) { - for (Int32 ro = firstRow; ro <= lastRow; ro++) + var cfAddress = cf.Range.RangeAddress; + if (cf.Range.Intersects(model)) { - using (var cellModel = model.Cell(ro - modelFirstRow + 1).AsRange()) - foreach (var cf in ConditionalFormats.Where(cf => cf.Range.Intersects(cellModel)).ToList()) - { - using (var r = Range(ro, firstColumn, ro, lastColumn)) r.AddConditionalFormat(cf); - } + cf.Range = Range(cfAddress.FirstAddress.RowNumber, + cfAddress.FirstAddress.ColumnNumber, + cfAddress.LastAddress.RowNumber, + cfAddress.LastAddress.ColumnNumber + columnsShifted); } + else if (cfAddress.FirstAddress.ColumnNumber >= firstCol) + { + cf.Range = Range(cfAddress.FirstAddress.RowNumber, + Math.Max(cfAddress.FirstAddress.ColumnNumber + columnsShifted, firstCol), + cfAddress.LastAddress.RowNumber, + cfAddress.LastAddress.ColumnNumber + columnsShifted); + } + if (cf.Range.RangeAddress.IsInvalid || + cf.Range.RangeAddress.FirstAddress.ColumnNumber > cf.Range.RangeAddress.LastAddress.ColumnNumber) + ConditionalFormats.Remove(f => f == cf); } - insertedRange.Dispose(); + model.Dispose(); - fc.Dispose(); } private void WorksheetRangeShiftedRows(XLRange range, int rowsShifted) @@ -1305,69 +1311,36 @@ private void ShiftConditionalFormattingRows(XLRange range, int rowsShifted) { + if (!ConditionalFormats.Any()) return; Int32 firstRow = range.RangeAddress.FirstAddress.RowNumber; if (firstRow == 1) return; - SuspendEvents(); - IXLRangeAddress usedAddress; - using (var rangeUsed = range.Worksheet.RangeUsed(true)) + int rowNum = rowsShifted > 0 ? firstRow - 1 : firstRow; + var model = Row(rowNum).AsRange(); + + foreach (var cf in ConditionalFormats.ToList()) { - usedAddress = rangeUsed == null ? range.RangeAddress : rangeUsed.RangeAddress; - } - ResumeEvents(); - - if (firstRow < usedAddress.FirstAddress.RowNumber) firstRow = usedAddress.FirstAddress.RowNumber; - - Int32 lastRow = range.RangeAddress.FirstAddress.RowNumber + rowsShifted - 1; - if (lastRow > usedAddress.LastAddress.RowNumber) lastRow = usedAddress.LastAddress.RowNumber; - - Int32 firstColumn = range.RangeAddress.FirstAddress.ColumnNumber; - if (firstColumn < usedAddress.FirstAddress.ColumnNumber) firstColumn = usedAddress.FirstAddress.ColumnNumber; - - Int32 lastColumn = range.RangeAddress.LastAddress.ColumnNumber; - if (lastColumn > usedAddress.LastAddress.ColumnNumber) lastColumn = usedAddress.LastAddress.ColumnNumber; - - var insertedRange = Range(firstRow, firstColumn, lastRow, lastColumn); - var fr = insertedRange.FirstRow(); - var model = fr.RowAbove(); - Int32 modelFirstColumn = model.RangeAddress.FirstAddress.ColumnNumber; - if (ConditionalFormats.Any(cf => cf.Range.Intersects(model))) - { - for (Int32 co = firstColumn; co <= lastColumn; co++) + var cfAddress = cf.Range.RangeAddress; + if (cf.Range.Intersects(model)) { - using (var cellModel = model.Cell(co - modelFirstColumn + 1).AsRange()) - foreach (var cf in ConditionalFormats.Where(cf => cf.Range.Intersects(cellModel)).ToList()) - { - using (var r = Range(firstRow, co, lastRow, co)) r.AddConditionalFormat(cf); - } + cf.Range = Range(cfAddress.FirstAddress.RowNumber, + cfAddress.FirstAddress.ColumnNumber, + cfAddress.LastAddress.RowNumber + rowsShifted, + cfAddress.LastAddress.ColumnNumber); } + else if (cfAddress.FirstAddress.RowNumber >= firstRow) + { + cf.Range = Range(Math.Max(cfAddress.FirstAddress.RowNumber + rowsShifted, firstRow), + cfAddress.FirstAddress.ColumnNumber, + cfAddress.LastAddress.RowNumber + rowsShifted, + cfAddress.LastAddress.ColumnNumber); + } + if (cf.Range.RangeAddress.IsInvalid || + cf.Range.RangeAddress.FirstAddress.RowNumber > cf.Range.RangeAddress.LastAddress.RowNumber) + ConditionalFormats.Remove(f => f == cf); } - insertedRange.Dispose(); + model.Dispose(); - fr.Dispose(); - } - - internal void BreakConditionalFormatsIntoCells(List addresses) - { - var newConditionalFormats = new XLConditionalFormats(); - SuspendEvents(); - foreach (var conditionalFormat in ConditionalFormats) - { - foreach (XLCell cell in conditionalFormat.Range.Cells(c => !addresses.Contains(c.Address))) - { - var row = cell.Address.RowNumber; - var column = cell.Address.ColumnLetter; - var newConditionalFormat = new XLConditionalFormat(cell.AsRange(), true); - newConditionalFormat.CopyFrom(conditionalFormat); - newConditionalFormat.Values.Values.Where(f => f.IsFormula) - .ForEach(f => f._value = XLHelper.ReplaceRelative(f.Value, row, column)); - newConditionalFormats.Add(newConditionalFormat); - } - conditionalFormat.Range.Dispose(); - } - ResumeEvents(); - newConditionalFormats.Consolidate(); - ConditionalFormats = newConditionalFormats; } private void MoveNamedRangesRows(XLRange range, int rowsShifted, IXLNamedRanges namedRanges) diff --git a/ClosedXML_Tests/ClosedXML_Tests.csproj b/ClosedXML_Tests/ClosedXML_Tests.csproj index f7a92c2..9e00c0e 100644 --- a/ClosedXML_Tests/ClosedXML_Tests.csproj +++ b/ClosedXML_Tests/ClosedXML_Tests.csproj @@ -90,6 +90,7 @@ + diff --git a/ClosedXML_Tests/Excel/ConditionalFormats/ConditionalFormatShiftTests.cs b/ClosedXML_Tests/Excel/ConditionalFormats/ConditionalFormatShiftTests.cs new file mode 100644 index 0000000..bb1caed --- /dev/null +++ b/ClosedXML_Tests/Excel/ConditionalFormats/ConditionalFormatShiftTests.cs @@ -0,0 +1,109 @@ +using ClosedXML.Excel; +using NUnit.Framework; +using System; +using System.Linq; + +namespace ClosedXML_Tests.Excel.ConditionalFormats +{ + [TestFixture] + public class ConditionalFormatShiftTests + { + [Test] + public void CFShiftedOnColumnInsert() + { + using (var wb = new XLWorkbook()) + { + var ws = wb.AddWorksheet("CFShift"); + ws.Range("A1:A1").AddConditionalFormat().WhenGreaterThan(0).Fill.SetBackgroundColor(XLColor.AirForceBlue); + ws.Range("A2:B2").AddConditionalFormat().WhenGreaterThan(0).Fill.SetBackgroundColor(XLColor.AliceBlue); + ws.Range("A3:C3").AddConditionalFormat().WhenGreaterThan(0).Fill.SetBackgroundColor(XLColor.Alizarin); + ws.Range("B4:B6").AddConditionalFormat().WhenGreaterThan(0).Fill.SetBackgroundColor(XLColor.Almond); + ws.Range("C7:D7").AddConditionalFormat().WhenGreaterThan(0).Fill.SetBackgroundColor(XLColor.Amaranth); + ws.Cells("A1:D7").Value = 1; + + ws.Column(2).InsertColumnsAfter(2); + var cf = ws.ConditionalFormats.ToArray(); + + Assert.AreEqual(5, cf.Length); + Assert.AreEqual("A1:A1", cf[0].Range.RangeAddress.ToString()); + Assert.AreEqual("A2:D2", cf[1].Range.RangeAddress.ToString()); + Assert.AreEqual("A3:E3", cf[2].Range.RangeAddress.ToString()); + Assert.AreEqual("B4:D6", cf[3].Range.RangeAddress.ToString()); + Assert.AreEqual("E7:F7", cf[4].Range.RangeAddress.ToString()); + } + } + + [Test] + public void CFShiftedOnRowInsert() + { + using (var wb = new XLWorkbook()) + { + var ws = wb.AddWorksheet("CFShift"); + ws.Range("A1:A1").AddConditionalFormat().WhenGreaterThan(0).Fill.SetBackgroundColor(XLColor.AirForceBlue); + ws.Range("B1:B2").AddConditionalFormat().WhenGreaterThan(0).Fill.SetBackgroundColor(XLColor.AliceBlue); + ws.Range("C1:C3").AddConditionalFormat().WhenGreaterThan(0).Fill.SetBackgroundColor(XLColor.Alizarin); + ws.Range("D2:F2").AddConditionalFormat().WhenGreaterThan(0).Fill.SetBackgroundColor(XLColor.Almond); + ws.Range("G4:G5").AddConditionalFormat().WhenGreaterThan(0).Fill.SetBackgroundColor(XLColor.Amaranth); + ws.Cells("A1:G5").Value = 1; + + ws.Row(2).InsertRowsBelow(2); + var cf = ws.ConditionalFormats.ToArray(); + + Assert.AreEqual(5, cf.Length); + Assert.AreEqual("A1:A1", cf[0].Range.RangeAddress.ToString()); + Assert.AreEqual("B1:B4", cf[1].Range.RangeAddress.ToString()); + Assert.AreEqual("C1:C5", cf[2].Range.RangeAddress.ToString()); + Assert.AreEqual("D2:F4", cf[3].Range.RangeAddress.ToString()); + Assert.AreEqual("G6:G7", cf[4].Range.RangeAddress.ToString()); + } + } + + [Test] + public void CFShiftedOnColumnDelete() + { + using (var wb = new XLWorkbook()) + { + var ws = wb.AddWorksheet("CFShift"); + ws.Range("A1:A1").AddConditionalFormat().WhenGreaterThan(0).Fill.SetBackgroundColor(XLColor.AirForceBlue); + ws.Range("A2:B2").AddConditionalFormat().WhenGreaterThan(0).Fill.SetBackgroundColor(XLColor.AliceBlue); + ws.Range("A3:C3").AddConditionalFormat().WhenGreaterThan(0).Fill.SetBackgroundColor(XLColor.Alizarin); + ws.Range("B4:B6").AddConditionalFormat().WhenGreaterThan(0).Fill.SetBackgroundColor(XLColor.Almond); + ws.Range("C7:D7").AddConditionalFormat().WhenGreaterThan(0).Fill.SetBackgroundColor(XLColor.Amaranth); + ws.Cells("A1:D7").Value = 1; + + ws.Column(2).Delete(); + var cf = ws.ConditionalFormats.ToArray(); + + Assert.AreEqual(4, cf.Length); + Assert.AreEqual("A1:A1", cf[0].Range.RangeAddress.ToString()); + Assert.AreEqual("A2:A2", cf[1].Range.RangeAddress.ToString()); + Assert.AreEqual("A3:B3", cf[2].Range.RangeAddress.ToString()); + Assert.AreEqual("B7:C7", cf[3].Range.RangeAddress.ToString()); + } + } + + [Test] + public void CFShiftedOnRowDelete() + { + using (var wb = new XLWorkbook()) + { + var ws = wb.AddWorksheet("CFShift"); + ws.Range("A1:A1").AddConditionalFormat().WhenGreaterThan(0).Fill.SetBackgroundColor(XLColor.AirForceBlue); + ws.Range("B1:B2").AddConditionalFormat().WhenGreaterThan(0).Fill.SetBackgroundColor(XLColor.AliceBlue); + ws.Range("C1:C3").AddConditionalFormat().WhenGreaterThan(0).Fill.SetBackgroundColor(XLColor.Alizarin); + ws.Range("D2:F2").AddConditionalFormat().WhenGreaterThan(0).Fill.SetBackgroundColor(XLColor.Almond); + ws.Range("G4:G5").AddConditionalFormat().WhenGreaterThan(0).Fill.SetBackgroundColor(XLColor.Amaranth); + ws.Cells("A1:G5").Value = 1; + + ws.Row(2).Delete(); + var cf = ws.ConditionalFormats.ToArray(); + + Assert.AreEqual(4, cf.Length); + Assert.AreEqual("A1:A1", cf[0].Range.RangeAddress.ToString()); + Assert.AreEqual("B1:B1", cf[1].Range.RangeAddress.ToString()); + Assert.AreEqual("C1:C2", cf[2].Range.RangeAddress.ToString()); + Assert.AreEqual("G3:G4", cf[3].Range.RangeAddress.ToString()); + } + } + } +} diff --git a/ClosedXML_Tests/Resource/Examples/Ranges/DeletingRanges.xlsx b/ClosedXML_Tests/Resource/Examples/Ranges/DeletingRanges.xlsx index ed8b923..b34c7d8 100644 --- a/ClosedXML_Tests/Resource/Examples/Ranges/DeletingRanges.xlsx +++ b/ClosedXML_Tests/Resource/Examples/Ranges/DeletingRanges.xlsx Binary files differ