diff --git a/ClosedXML/Excel/Ranges/XLRange.cs b/ClosedXML/Excel/Ranges/XLRange.cs index 7950c82..4a19fcc 100644 --- a/ClosedXML/Excel/Ranges/XLRange.cs +++ b/ClosedXML/Excel/Ranges/XLRange.cs @@ -355,12 +355,12 @@ #endregion IXLRange Members - private void WorksheetRangeShiftedColumns(XLRange range, int columnsShifted) + internal void WorksheetRangeShiftedColumns(XLRange range, int columnsShifted) { ShiftColumns(RangeAddress, range, columnsShifted); } - private void WorksheetRangeShiftedRows(XLRange range, int rowsShifted) + internal void WorksheetRangeShiftedRows(XLRange range, int rowsShifted) { ShiftRows(RangeAddress, range, rowsShifted); } diff --git a/ClosedXML/Excel/Ranges/XLRangeBase.cs b/ClosedXML/Excel/Ranges/XLRangeBase.cs index ea18eeb..27caf49 100644 --- a/ClosedXML/Excel/Ranges/XLRangeBase.cs +++ b/ClosedXML/Excel/Ranges/XLRangeBase.cs @@ -1604,114 +1604,102 @@ { if (thisRangeAddress.IsInvalid || shiftedRange.RangeAddress.IsInvalid) return; - if ((columnsShifted < 0 - // all columns - && - thisRangeAddress.FirstAddress.ColumnNumber >= shiftedRange.RangeAddress.FirstAddress.ColumnNumber - && - thisRangeAddress.LastAddress.ColumnNumber <= - shiftedRange.RangeAddress.FirstAddress.ColumnNumber - columnsShifted - // all rows - && thisRangeAddress.FirstAddress.RowNumber >= shiftedRange.RangeAddress.FirstAddress.RowNumber - && thisRangeAddress.LastAddress.RowNumber <= shiftedRange.RangeAddress.LastAddress.RowNumber - ) || ( - shiftedRange.RangeAddress.FirstAddress.ColumnNumber <= - thisRangeAddress.FirstAddress.ColumnNumber - && - shiftedRange.RangeAddress.FirstAddress.RowNumber <= thisRangeAddress.FirstAddress.RowNumber - && - shiftedRange.RangeAddress.LastAddress.RowNumber >= thisRangeAddress.LastAddress.RowNumber - && shiftedRange.ColumnCount() > - (thisRangeAddress.LastAddress.ColumnNumber - thisRangeAddress.FirstAddress.ColumnNumber + 1) - + - (thisRangeAddress.FirstAddress.ColumnNumber - - shiftedRange.RangeAddress.FirstAddress.ColumnNumber))) - thisRangeAddress.IsInvalid = true; - else - { - if (shiftedRange.RangeAddress.FirstAddress.RowNumber <= thisRangeAddress.FirstAddress.RowNumber - && shiftedRange.RangeAddress.LastAddress.RowNumber >= thisRangeAddress.LastAddress.RowNumber) - { - if ( - (shiftedRange.RangeAddress.FirstAddress.ColumnNumber <= - thisRangeAddress.FirstAddress.ColumnNumber && - columnsShifted > 0) - || - (shiftedRange.RangeAddress.FirstAddress.ColumnNumber < - thisRangeAddress.FirstAddress.ColumnNumber && - columnsShifted < 0) - ) - { - thisRangeAddress.FirstAddress = new XLAddress(Worksheet, - thisRangeAddress.FirstAddress.RowNumber, - thisRangeAddress.FirstAddress.ColumnNumber + - columnsShifted, - thisRangeAddress.FirstAddress.FixedRow, - thisRangeAddress.FirstAddress.FixedColumn); - } + bool allRowsAreCovered = thisRangeAddress.FirstAddress.RowNumber >= shiftedRange.RangeAddress.FirstAddress.RowNumber && + thisRangeAddress.LastAddress.RowNumber <= shiftedRange.RangeAddress.LastAddress.RowNumber; - if (shiftedRange.RangeAddress.FirstAddress.ColumnNumber <= - thisRangeAddress.LastAddress.ColumnNumber) - { - thisRangeAddress.LastAddress = new XLAddress(Worksheet, - thisRangeAddress.LastAddress.RowNumber, - thisRangeAddress.LastAddress.ColumnNumber + - columnsShifted, - thisRangeAddress.LastAddress.FixedRow, - thisRangeAddress.LastAddress.FixedColumn); - } - } + if (!allRowsAreCovered) + return; + + bool shiftLeftBoundary = (columnsShifted > 0 && thisRangeAddress.FirstAddress.ColumnNumber >= shiftedRange.RangeAddress.FirstAddress.ColumnNumber) || + (columnsShifted < 0 && thisRangeAddress.FirstAddress.ColumnNumber > shiftedRange.RangeAddress.FirstAddress.ColumnNumber); + + bool shiftRightBoundary = thisRangeAddress.LastAddress.ColumnNumber >= shiftedRange.RangeAddress.FirstAddress.ColumnNumber; + + int newLeftBoundary = thisRangeAddress.FirstAddress.ColumnNumber; + if (shiftLeftBoundary) + { + if (newLeftBoundary + columnsShifted > shiftedRange.RangeAddress.FirstAddress.ColumnNumber) + newLeftBoundary = newLeftBoundary + columnsShifted; + else + newLeftBoundary = shiftedRange.RangeAddress.FirstAddress.ColumnNumber; } + + int newRightBoundary = thisRangeAddress.LastAddress.ColumnNumber; + if (shiftRightBoundary) + newRightBoundary += columnsShifted; + + bool destroyedByShift = newRightBoundary < newLeftBoundary; + + if (destroyedByShift) + { + thisRangeAddress.IsInvalid = true; + return; + } + + if (shiftLeftBoundary) + thisRangeAddress.FirstAddress = new XLAddress(Worksheet, + thisRangeAddress.FirstAddress.RowNumber, + newLeftBoundary, + thisRangeAddress.FirstAddress.FixedRow, + thisRangeAddress.FirstAddress.FixedColumn); + + if (shiftRightBoundary) + thisRangeAddress.LastAddress = new XLAddress(Worksheet, + thisRangeAddress.LastAddress.RowNumber, + newRightBoundary, + thisRangeAddress.LastAddress.FixedRow, + thisRangeAddress.LastAddress.FixedColumn); } protected void ShiftRows(IXLRangeAddress thisRangeAddress, XLRange shiftedRange, int rowsShifted) { if (thisRangeAddress.IsInvalid || shiftedRange.RangeAddress.IsInvalid) return; - if ((rowsShifted < 0 - // all columns - && thisRangeAddress.FirstAddress.ColumnNumber >= shiftedRange.RangeAddress.FirstAddress.ColumnNumber - && thisRangeAddress.LastAddress.ColumnNumber <= shiftedRange.RangeAddress.FirstAddress.ColumnNumber - // all rows - && thisRangeAddress.FirstAddress.RowNumber >= shiftedRange.RangeAddress.FirstAddress.RowNumber - && thisRangeAddress.LastAddress.RowNumber <= shiftedRange.RangeAddress.LastAddress.RowNumber - rowsShifted - ) || ( - shiftedRange.RangeAddress.FirstAddress.RowNumber <= thisRangeAddress.FirstAddress.RowNumber - && shiftedRange.RangeAddress.FirstAddress.ColumnNumber <= thisRangeAddress.FirstAddress.ColumnNumber - && shiftedRange.RangeAddress.LastAddress.ColumnNumber >= thisRangeAddress.LastAddress.ColumnNumber - && shiftedRange.RowCount() > - (thisRangeAddress.LastAddress.RowNumber - thisRangeAddress.FirstAddress.RowNumber + 1) - + (thisRangeAddress.FirstAddress.RowNumber - shiftedRange.RangeAddress.FirstAddress.RowNumber))) - thisRangeAddress.IsInvalid = true; - else - { - if (shiftedRange.RangeAddress.FirstAddress.ColumnNumber <= thisRangeAddress.FirstAddress.ColumnNumber - && shiftedRange.RangeAddress.LastAddress.ColumnNumber >= thisRangeAddress.LastAddress.ColumnNumber) - { - if ( - (shiftedRange.RangeAddress.FirstAddress.RowNumber <= thisRangeAddress.FirstAddress.RowNumber && rowsShifted > 0) - || (shiftedRange.RangeAddress.FirstAddress.RowNumber < thisRangeAddress.FirstAddress.RowNumber && rowsShifted < 0) - ) - { - thisRangeAddress.FirstAddress = new XLAddress(Worksheet, - thisRangeAddress.FirstAddress.RowNumber + - rowsShifted, - thisRangeAddress.FirstAddress.ColumnNumber, - thisRangeAddress.FirstAddress.FixedRow, - thisRangeAddress.FirstAddress.FixedColumn); - } + bool allColumnsAreCovered = thisRangeAddress.FirstAddress.ColumnNumber >= shiftedRange.RangeAddress.FirstAddress.ColumnNumber && + thisRangeAddress.LastAddress.ColumnNumber <= shiftedRange.RangeAddress.LastAddress.ColumnNumber; - if (shiftedRange.RangeAddress.FirstAddress.RowNumber <= thisRangeAddress.LastAddress.RowNumber) - { - thisRangeAddress.LastAddress = new XLAddress(Worksheet, - thisRangeAddress.LastAddress.RowNumber + - rowsShifted, - thisRangeAddress.LastAddress.ColumnNumber, - thisRangeAddress.LastAddress.FixedRow, - thisRangeAddress.LastAddress.FixedColumn); - } - } + if (!allColumnsAreCovered) + return; + + bool shiftTopBoundary = (rowsShifted > 0 && thisRangeAddress.FirstAddress.RowNumber >= shiftedRange.RangeAddress.FirstAddress.RowNumber) || + (rowsShifted < 0 && thisRangeAddress.FirstAddress.RowNumber > shiftedRange.RangeAddress.FirstAddress.RowNumber); + + bool shiftBottomBoundary = thisRangeAddress.LastAddress.RowNumber >= shiftedRange.RangeAddress.FirstAddress.RowNumber; + + int newTopBoundary = thisRangeAddress.FirstAddress.RowNumber; + if (shiftTopBoundary) + { + if (newTopBoundary + rowsShifted > shiftedRange.RangeAddress.FirstAddress.RowNumber) + newTopBoundary = newTopBoundary + rowsShifted; + else + newTopBoundary = shiftedRange.RangeAddress.FirstAddress.RowNumber; } + + int newBottomBoundary = thisRangeAddress.LastAddress.RowNumber; + if (shiftBottomBoundary) + newBottomBoundary += rowsShifted; + + bool destroyedByShift = newBottomBoundary < newTopBoundary; + + if (destroyedByShift) + { + thisRangeAddress.IsInvalid = true; + return; + } + + if (shiftTopBoundary) + thisRangeAddress.FirstAddress = new XLAddress(Worksheet, + newTopBoundary, + thisRangeAddress.FirstAddress.ColumnNumber, + thisRangeAddress.FirstAddress.FixedRow, + thisRangeAddress.FirstAddress.FixedColumn); + + if (shiftBottomBoundary) + thisRangeAddress.LastAddress = new XLAddress(Worksheet, + newBottomBoundary, + thisRangeAddress.LastAddress.ColumnNumber, + thisRangeAddress.LastAddress.FixedRow, + thisRangeAddress.LastAddress.FixedColumn); } public IXLRange RangeUsed() @@ -2189,4 +2177,4 @@ return cells; } } -} \ No newline at end of file +} diff --git a/ClosedXML_Tests/Excel/AutoFilters/AutoFilterTests.cs b/ClosedXML_Tests/Excel/AutoFilters/AutoFilterTests.cs index 9a4356a..6b68014 100644 --- a/ClosedXML_Tests/Excel/AutoFilters/AutoFilterTests.cs +++ b/ClosedXML_Tests/Excel/AutoFilters/AutoFilterTests.cs @@ -125,5 +125,36 @@ } } } + + [Test] + [TestCase("A1:A4")] + [TestCase("A1:B4")] + [TestCase("A1:C4")] + public void AutoFilterRangeRemainsValidOnInsertColumn(string rangeAddress) + { + //Arrange + using (var ms1 = new MemoryStream()) + { + using (var wb = new XLWorkbook()) + { + var ws = wb.Worksheets.Add("AutoFilter"); + ws.Cell("A1").Value = "Ids"; + ws.Cell("B1").Value = "Names"; + ws.Cell("B2").Value = "John"; + ws.Cell("B3").Value = "Hank"; + ws.Cell("B4").Value = "Dagny"; + ws.Cell("C1").Value = "Phones"; + + ws.Range("B1:B4").SetAutoFilter(true); + + //Act + var range = ws.Range(rangeAddress); + range.InsertColumnsBefore(1); + + //Assert + Assert.IsFalse(ws.AutoFilter.Range.RangeAddress.IsInvalid); + } + } + } } } diff --git a/ClosedXML_Tests/Excel/Ranges/InsertingRangesTests.cs b/ClosedXML_Tests/Excel/Ranges/InsertingRangesTests.cs index fb0c98f..87e90ed 100644 --- a/ClosedXML_Tests/Excel/Ranges/InsertingRangesTests.cs +++ b/ClosedXML_Tests/Excel/Ranges/InsertingRangesTests.cs @@ -109,5 +109,108 @@ ws.Column(1).InsertColumnsAfter(2); Assert.AreEqual("Comment here", ws.Cell("E1").Comment.Text); } + + [Test] + [TestCase("C4:F7", "C4:F7", 2, "E4:H7")] // Coincide, shift right + [TestCase("C4:F7", "C4:F7", -2, "C4:D7")] // Coincide, shift left + [TestCase("D5:E6", "C4:F7", 2, "F5:G6")] // Inside, shift right + [TestCase("D5:E6", "C4:F7", -2, "C5:C6")] // Inside, shift left + [TestCase("B4:G7", "C4:F7", 2, "B4:I7")] // Includes, shift right + [TestCase("B4:G7", "C4:F7", -2, "B4:E7")] // Includes, shift left + [TestCase("B4:E7", "C4:F7", 2, "B4:G7")] // Intersects at left, shift right + [TestCase("B4:E7", "C4:F7", -2, "B4:C7")] // Intersects at left, shift left + [TestCase("D4:G7", "C4:F7", 2, "F4:I7")] // Intersects at right, shift right + [TestCase("D4:G7", "C4:F7", -2, "C4:E7")] // Intersects at right, shift left + [TestCase("A5:B6", "C4:F7", 2, "A5:B6")] // No intersection, at left, shift right + [TestCase("A5:B6", "C4:F7", -1, "A5:B6")] // No intersection, at left, shift left + [TestCase("H5:I6", "C4:F7", 2, "J5:K6")] // No intersection, at right, shift right + [TestCase("H5:I6", "C4:F7", -2, "F5:G6")] // No intersection, at right, shift left + [TestCase("C8:F11", "C4:F7", 2, "C8:F11")] // Different rows + [TestCase("B1:B8", "A1:C4", 1, "B1:B8")] // More rows, shift right + [TestCase("B1:B8", "A1:C4", -1, "B1:B8")] // More rows, shift left + + public void ShiftColumnsValid(string thisRangeAddress, string shiftedRangeAddress, int shiftedColumns, string expectedRange) + { + using (var wb = new XLWorkbook()) + { + var ws = wb.Worksheets.Add("Sheet1"); + var thisRange = ws.Range(thisRangeAddress) as XLRange; + var shiftedRange = ws.Range(shiftedRangeAddress) as XLRange; + + thisRange.WorksheetRangeShiftedColumns(shiftedRange, shiftedColumns); + + Assert.IsFalse(thisRange.RangeAddress.IsInvalid); + Assert.AreEqual(expectedRange, thisRange.RangeAddress.ToString()); + } + } + + + [Test] + [TestCase("B1:B4", "A1:C4", -2)] // Shift left too much + public void ShiftColumnsInvalid(string thisRangeAddress, string shiftedRangeAddress, int shiftedColumns) + { + using (var wb = new XLWorkbook()) + { + var ws = wb.Worksheets.Add("Sheet1"); + var thisRange = ws.Range(thisRangeAddress) as XLRange; + var shiftedRange = ws.Range(shiftedRangeAddress) as XLRange; + + thisRange.WorksheetRangeShiftedColumns(shiftedRange, shiftedColumns); + + Assert.IsTrue(thisRange.RangeAddress.IsInvalid); + } + } + + + [Test] + [TestCase("C4:F7", "C4:F7", 2, "C6:F9")] // Coincide, shift down + [TestCase("C4:F7", "C4:F7", -2, "C4:F5")] // Coincide, shift up + [TestCase("D5:E6", "C4:F7", 2, "D7:E8")] // Inside, shift down + [TestCase("D5:E6", "C4:F7", -2, "D4:E4")] // Inside, shift up + [TestCase("C3:F8", "C4:F7", 2, "C3:F10")] // Includes, shift down + [TestCase("C3:F8", "C4:F7", -2, "C3:F6")] // Includes, shift up + [TestCase("C3:F6", "C4:F7", 2, "C3:F8")] // Intersects at top, shift down + [TestCase("C2:F6", "C4:F7", -3, "C2:F3")] // Intersects at top, shift up to the sheet boundary + [TestCase("C3:F6", "C4:F7", -2, "C3:F4")] // Intersects at top, shift up + [TestCase("C5:F8", "C4:F7", 2, "C7:F10")] // Intersects at bottom, shift down + [TestCase("C5:F8", "C4:F7", -2, "C4:F6")] // Intersects at bottom, shift up + [TestCase("C1:F3", "C4:F7", 2, "C1:F3")] // No intersection, at top, shift down + [TestCase("C1:F3", "C4:F7", -2, "C1:F3")] // No intersection, at top, shift up + [TestCase("C8:F10","C4:F7", 2, "C10:F12")] // No intersection, at bottom, shift down + [TestCase("C8:F10","C4:F7", -2, "C6:F8")] // No intersection, at bottom, shift up + [TestCase("G4:J7", "C4:F7", 2, "G4:J7")] // Different columns + [TestCase("A2:D2", "A1:C4", 1, "A2:D2")] // More columns, shift down + [TestCase("A2:D2", "A1:C4", -1, "A2:D2")] // More columns, shift up + + public void ShiftRowsValid(string thisRangeAddress, string shiftedRangeAddress, int shiftedRows, string expectedRange) + { + using (var wb = new XLWorkbook()) + { + var ws = wb.Worksheets.Add("Sheet1"); + var thisRange = ws.Range(thisRangeAddress) as XLRange; + var shiftedRange = ws.Range(shiftedRangeAddress) as XLRange; + + thisRange.WorksheetRangeShiftedRows(shiftedRange, shiftedRows); + + Assert.IsFalse(thisRange.RangeAddress.IsInvalid); + Assert.AreEqual(expectedRange, thisRange.RangeAddress.ToString()); + } + } + + [Test] + [TestCase("A2:C2", "A1:C4", -2)] // Shift up too much + public void ShiftRowsInvalid(string thisRangeAddress, string shiftedRangeAddress, int shiftedRows) + { + using (var wb = new XLWorkbook()) + { + var ws = wb.Worksheets.Add("Sheet1"); + var thisRange = ws.Range(thisRangeAddress) as XLRange; + var shiftedRange = ws.Range(shiftedRangeAddress) as XLRange; + + thisRange.WorksheetRangeShiftedRows(shiftedRange, shiftedRows); + + Assert.IsTrue(thisRange.RangeAddress.IsInvalid); + } + } } }