diff --git a/ClosedXML/Excel/Cells/XLCell.cs b/ClosedXML/Excel/Cells/XLCell.cs index e085c64..09b87ab 100644 --- a/ClosedXML/Excel/Cells/XLCell.cs +++ b/ClosedXML/Excel/Cells/XLCell.cs @@ -2751,31 +2751,16 @@ { get { - return this.Worksheet.Range(SearchCurrentRegion(this.AsRange())); + return this.Worksheet.Range(FindCurrentRegion(this.AsRange())); } } - internal IEnumerable SurroundingCells(IXLRangeBase range) - { - var rowNumbers = Enumerable.Range(range.RangeAddress.FirstAddress.RowNumber - 1, range.RangeAddress.LastAddress.RowNumber - range.RangeAddress.FirstAddress.RowNumber + 3); - var columnNumbers = Enumerable.Range(range.RangeAddress.FirstAddress.ColumnNumber - 1, range.RangeAddress.LastAddress.ColumnNumber - range.RangeAddress.FirstAddress.ColumnNumber + 3); - - // Cartesian product - var addresses = rowNumbers.SelectMany(row => columnNumbers, (row, column) => new { row, column }); - - addresses = addresses.Where(a => a.row >= 1 && a.row <= XLHelper.MaxRowNumber && a.column >= 1 && a.column <= XLHelper.MaxColumnNumber) - // Don't select range cells self - .Where(a => !range.Cells().Any(rc => a.row == rc.Address.RowNumber && a.column == rc.Address.ColumnNumber)); - - return addresses.Select(a => this.Worksheet.Cell(a.row, a.column)); - } - - internal IXLRangeAddress SearchCurrentRegion(IXLRangeBase range) + internal IXLRangeAddress FindCurrentRegion(IXLRangeBase range) { var rangeAddress = range.RangeAddress; - var filledCells = SurroundingCells(this.Worksheet.Range(rangeAddress)) - .Where(c => !c.IsEmpty(false, false)) + var filledCells = range + .SurroundingCells(c => !(c as XLCell).IsEmpty(false, false)) .Concat(this.Worksheet.Range(rangeAddress).Cells()); var grownRangeAddress = new XLRangeAddress( @@ -2783,11 +2768,10 @@ new XLAddress(this.Worksheet, filledCells.Max(c => c.Address.RowNumber), filledCells.Max(c => c.Address.ColumnNumber), false, false) ); - if (rangeAddress.Equals(grownRangeAddress)) return this.Worksheet.Range(grownRangeAddress).RangeAddress; else - return SearchCurrentRegion(this.Worksheet.Range(grownRangeAddress)); + return FindCurrentRegion(this.Worksheet.Range(grownRangeAddress)); } } -} +} \ No newline at end of file diff --git a/ClosedXML/Excel/Ranges/IXLRangeBase.cs b/ClosedXML/Excel/Ranges/IXLRangeBase.cs index 064d975..aad8eec 100644 --- a/ClosedXML/Excel/Ranges/IXLRangeBase.cs +++ b/ClosedXML/Excel/Ranges/IXLRangeBase.cs @@ -267,5 +267,64 @@ IXLConditionalFormat AddConditionalFormat(); void Select(); + + /// + /// Grows this the current range by one cell to each side + /// + IXLRangeBase Grow(); + + /// + /// Grows this the current range by the specified number of cells to each side. + /// + /// The grow count. + /// + IXLRangeBase Grow(Int32 growCount); + + /// + /// Shrinks this current range by one cell. + /// + IXLRangeBase Shrink(); + + /// + /// Shrinks the current range by the specified number of cells from each side. + /// + /// The shrink count. + /// + IXLRangeBase Shrink(Int32 shrinkCount); + + /// + /// Returns the intersection of this range with another range on the same worksheet. + /// + /// The other range. + /// Predicate applied to this range's cells. + /// Predicate applied to the other range's cells. + /// + IXLRangeBase Intersection(IXLRangeBase otherRange, Func thisRangePredicate = null, Func otherRangePredicate = null); + + /// + /// Returns the set of cells surrounding the current range. + /// + /// The predicate to apply on the resulting set of cells. + IXLCells SurroundingCells(Func predicate = null); + + /// + /// Calculates the union of two ranges on the same worksheet. + /// + /// The other range. + /// Predicate applied to this range's cells. + /// Predicate applied to the other range's cells. + /// + /// The union + /// + IXLCells Union(IXLRangeBase otherRange, Func thisRangePredicate = null, Func otherRangePredicate = null); + + /// + /// Returns all cells in the current range that are not in the other range. + /// + /// The other range. + /// Predicate applied to this range's cells. + /// Predicate applied to the other range's cells. + /// + IXLCells Difference(IXLRangeBase otherRange, Func thisRangePredicate = null, Func otherRangePredicate = null); } } diff --git a/ClosedXML/Excel/Ranges/XLRangeBase.cs b/ClosedXML/Excel/Ranges/XLRangeBase.cs index 190d473..ac7d6b6 100644 --- a/ClosedXML/Excel/Ranges/XLRangeBase.cs +++ b/ClosedXML/Excel/Ranges/XLRangeBase.cs @@ -803,14 +803,18 @@ if (absRow <= 0 || absRow > XLHelper.MaxRowNumber) { - throw new IndexOutOfRangeException(String.Format("Row number must be between 1 and {0}", - XLHelper.MaxRowNumber)); + throw new ArgumentOutOfRangeException( + nameof(cellAddressInRange), + String.Format("Row number must be between 1 and {0}", XLHelper.MaxRowNumber) + ); } if (absColumn <= 0 || absColumn > XLHelper.MaxColumnNumber) { - throw new IndexOutOfRangeException(String.Format("Column number must be between 1 and {0}", - XLHelper.MaxColumnNumber)); + throw new ArgumentOutOfRangeException( + nameof(cellAddressInRange), + String.Format("Column number must be between 1 and {0}", XLHelper.MaxColumnNumber) + ); } var cell = Worksheet.Internals.CellsCollection.GetCell(absRow, @@ -2067,5 +2071,104 @@ { Worksheet.SelectedRanges.Add(AsRange()); } + + public IXLRangeBase Grow() + { + return Grow(1); + } + + public IXLRangeBase Grow(int growCount) + { + var firstRow = Math.Max(1, this.RangeAddress.FirstAddress.RowNumber - growCount); + var firstColumn = Math.Max(1, this.RangeAddress.FirstAddress.ColumnNumber - growCount); + + var lastRow = Math.Min(XLHelper.MaxRowNumber, this.RangeAddress.LastAddress.RowNumber + growCount); + var lastColumn = Math.Min(XLHelper.MaxColumnNumber, this.RangeAddress.LastAddress.ColumnNumber + growCount); + + return this.Worksheet.Range(firstRow, firstColumn, lastRow, lastColumn); + } + + public IXLRangeBase Shrink() + { + return Shrink(1); + } + + public IXLRangeBase Shrink(int shrinkCount) + { + var firstRow = this.RangeAddress.FirstAddress.RowNumber + shrinkCount; + var firstColumn = this.RangeAddress.FirstAddress.ColumnNumber + shrinkCount; + + var lastRow = this.RangeAddress.LastAddress.RowNumber - shrinkCount; + var lastColumn = this.RangeAddress.LastAddress.ColumnNumber - shrinkCount; + + if (firstRow > lastRow || firstColumn > lastColumn) + return null; + + return this.Worksheet.Range(firstRow, firstColumn, lastRow, lastColumn); + } + + public IXLRangeBase Intersection(IXLRangeBase otherRange, Func thisRangePredicate = null, Func otherRangePredicate = null) + { + if (otherRange == null) + return null; + + if (!this.Worksheet.Equals(otherRange.Worksheet)) + return null; + + if (thisRangePredicate == null) thisRangePredicate = c => true; + if (otherRangePredicate == null) otherRangePredicate = c => true; + + var intersectionCells = this.Cells(c => thisRangePredicate(c) && otherRange.Cells(otherRangePredicate).Contains(c)); + + if (!intersectionCells.Any()) + return null; + + var firstRow = intersectionCells.Min(c => c.Address.RowNumber); + var firstColumn = intersectionCells.Min(c => c.Address.ColumnNumber); + + var lastRow = intersectionCells.Max(c => c.Address.RowNumber); + var lastColumn = intersectionCells.Max(c => c.Address.ColumnNumber); + + return this.Worksheet.Range(firstRow, firstColumn, lastRow, lastColumn); + } + + public IXLCells SurroundingCells(Func predicate = null) + { + var cells = new XLCells(false, false, predicate); + this.Grow().Cells(c => !this.Contains(c)).ForEach(c => cells.Add(c as XLCell)); + return cells; + } + + public IXLCells Union(IXLRangeBase otherRange, Func thisRangePredicate = null, Func otherRangePredicate = null) + { + if (otherRange == null) + return this.Cells(thisRangePredicate); + + var cells = new XLCells(false, false); + if (!this.Worksheet.Equals(otherRange.Worksheet)) + return cells; + + if (thisRangePredicate == null) thisRangePredicate = c => true; + if (otherRangePredicate == null) otherRangePredicate = c => true; + + this.Cells(thisRangePredicate).Concat(otherRange.Cells(otherRangePredicate)).Distinct().ForEach(c => cells.Add(c as XLCell)); + return cells; + } + + public IXLCells Difference(IXLRangeBase otherRange, Func thisRangePredicate = null, Func otherRangePredicate = null) + { + if (otherRange == null) + return this.Cells(thisRangePredicate); + + var cells = new XLCells(false, false); + if (!this.Worksheet.Equals(otherRange.Worksheet)) + return cells; + + if (thisRangePredicate == null) thisRangePredicate = c => true; + if (otherRangePredicate == null) otherRangePredicate = c => true; + + this.Cells(c => thisRangePredicate(c) && !otherRange.Cells(otherRangePredicate).Contains(c)).ForEach(c => cells.Add(c as XLCell)); + return cells; + } } -} +} \ No newline at end of file diff --git a/ClosedXML_Tests/Excel/Ranges/XLRangeBaseTests.cs b/ClosedXML_Tests/Excel/Ranges/XLRangeBaseTests.cs index 348b213..28a7b6f 100644 --- a/ClosedXML_Tests/Excel/Ranges/XLRangeBaseTests.cs +++ b/ClosedXML_Tests/Excel/Ranges/XLRangeBaseTests.cs @@ -1,7 +1,7 @@ -using System; -using System.Linq; using ClosedXML.Excel; using NUnit.Framework; +using System; +using System.Linq; namespace ClosedXML_Tests { @@ -106,7 +106,7 @@ IXLRange namedRange = wb.Range("FNameColumn"); Assert.AreEqual(3, namedRange.Cells().Count()); Assert.IsTrue( - namedRange.CellsUsed().Select(cell => cell.GetString()).SequenceEqual(new[] {"John", "Hank", "Dagny"})); + namedRange.CellsUsed().Select(cell => cell.GetString()).SequenceEqual(new[] { "John", "Hank", "Dagny" })); } [Test] @@ -184,7 +184,119 @@ // memoryStream.Close(); // } - //} + + [Test] + public void GrowRange() + { + using (var wb = new XLWorkbook()) + { + var ws = wb.AddWorksheet("Sheet1"); + Assert.AreEqual("A1:B2", ws.Cell("A1").AsRange().Grow().RangeAddress.ToString()); + Assert.AreEqual("A1:B3", ws.Cell("A2").AsRange().Grow().RangeAddress.ToString()); + Assert.AreEqual("A1:C2", ws.Cell("B1").AsRange().Grow().RangeAddress.ToString()); + + Assert.AreEqual("E4:G6", ws.Cell("F5").AsRange().Grow().RangeAddress.ToString()); + Assert.AreEqual("D3:H7", ws.Cell("F5").AsRange().Grow(2).RangeAddress.ToString()); + Assert.AreEqual("A1:DB105", ws.Cell("F5").AsRange().Grow(100).RangeAddress.ToString()); + } + } + + [Test] + public void ShrinkRange() + { + using (var wb = new XLWorkbook()) + { + var ws = wb.AddWorksheet("Sheet1"); + Assert.Null(ws.Cell("A1").AsRange().Shrink()); + Assert.Null(ws.Range("B2:C3").Shrink()); + Assert.AreEqual("C3:C3", ws.Range("B2:D4").Shrink().RangeAddress.ToString()); + Assert.AreEqual("K11:P16", ws.Range("A1:Z26").Shrink(10).RangeAddress.ToString()); + + // Grow and shrink back + Assert.AreEqual("Z26:Z26", ws.Cell("Z26").AsRange().Grow(10).Shrink(10).RangeAddress.ToString()); + } + } + + [Test] + public void Intersection() + { + using (var wb = new XLWorkbook()) + { + var ws = wb.AddWorksheet("Sheet1"); + + Assert.AreEqual("D9:G11", ws.Range("B9:I11").Intersection(ws.Range("D4:G16")).RangeAddress.ToString()); + Assert.AreEqual("E9:G11", ws.Range("E9:I11").Intersection(ws.Range("D4:G16")).RangeAddress.ToString()); + Assert.AreEqual("E9:E9", ws.Cell("E9").AsRange().Intersection(ws.Range("D4:G16")).RangeAddress.ToString()); + Assert.AreEqual("E9:E9", ws.Range("D4:G16").Intersection(ws.Cell("E9").AsRange()).RangeAddress.ToString()); + + Assert.Null(ws.Cell("A1").AsRange().Intersection(ws.Cell("C3").AsRange())); + + Assert.Null(ws.Range("A1:C3").Intersection(null)); + + var otherWs = wb.AddWorksheet("Sheet2"); + Assert.Null(ws.Intersection(otherWs)); + Assert.Null(ws.Cell("A1").AsRange().Intersection(otherWs.Cell("A2").AsRange())); + } + } + + [Test] + public void Union() + { + using (var wb = new XLWorkbook()) + { + var ws = wb.AddWorksheet("Sheet1"); + + Assert.AreEqual(64, ws.Range("B9:I11").Union(ws.Range("D4:G16")).Count()); + Assert.AreEqual(58, ws.Range("E9:I11").Union(ws.Range("D4:G16")).Count()); + Assert.AreEqual(52, ws.Cell("E9").AsRange().Union(ws.Range("D4:G16")).Count()); + Assert.AreEqual(52, ws.Range("D4:G16").Union(ws.Cell("E9").AsRange()).Count()); + + Assert.AreEqual(2, ws.Cell("A1").AsRange().Union(ws.Cell("C3").AsRange()).Count()); + + Assert.AreEqual(9, ws.Range("A1:C3").Union(null).Count()); + + var otherWs = wb.AddWorksheet("Sheet2"); + Assert.False(ws.Union(otherWs).Any()); + Assert.False(ws.Cell("A1").AsRange().Union(otherWs.Cell("A2").AsRange()).Any()); + } + } + + [Test] + public void Difference() + { + using (var wb = new XLWorkbook()) + { + var ws = wb.AddWorksheet("Sheet1"); + + Assert.AreEqual(12, ws.Range("B9:I11").Difference(ws.Range("D4:G16")).Count()); + Assert.AreEqual(6, ws.Range("E9:I11").Difference(ws.Range("D4:G16")).Count()); + Assert.AreEqual(0, ws.Cell("E9").AsRange().Difference(ws.Range("D4:G16")).Count()); + Assert.AreEqual(51, ws.Range("D4:G16").Difference(ws.Cell("E9").AsRange()).Count()); + + Assert.AreEqual(1, ws.Cell("A1").AsRange().Difference(ws.Cell("C3").AsRange()).Count()); + + Assert.AreEqual(9, ws.Range("A1:C3").Difference(null).Count()); + + var otherWs = wb.AddWorksheet("Sheet2"); + Assert.False(ws.Difference(otherWs).Any()); + Assert.False(ws.Cell("A1").AsRange().Difference(otherWs.Cell("A2").AsRange()).Any()); + } + } + + [Test] + public void SurroundingCells() + { + using (var wb = new XLWorkbook()) + { + var ws = wb.AddWorksheet("Sheet1"); + + Assert.AreEqual(3, ws.FirstCell().AsRange().SurroundingCells().Count()); + Assert.AreEqual(8, ws.Cell("C3").AsRange().SurroundingCells().Count()); + Assert.AreEqual(16, ws.Range("C3:D6").AsRange().SurroundingCells().Count()); + + Assert.AreEqual(0, ws.Range("C3:D6").AsRange().SurroundingCells(c => !c.IsEmpty()).Count()); + } + } } }