diff --git a/ClosedXML/Excel/Cells/IXLCell.cs b/ClosedXML/Excel/Cells/IXLCell.cs index fc3724e..3212d2e 100644 --- a/ClosedXML/Excel/Cells/IXLCell.cs +++ b/ClosedXML/Excel/Cells/IXLCell.cs @@ -35,6 +35,14 @@ IXLAddress Address { get; } /// + /// Returns the current region. The current region is a range bounded by any combination of blank rows and blank columns + /// + /// + /// The current region. + /// + IXLRange CurrentRegion { get; } + + /// /// Gets or sets the type of this cell's data. /// Changing the data type will cause ClosedXML to covert the current value to the new data type. /// An exception will be thrown if the current value cannot be converted to the new data type. diff --git a/ClosedXML/Excel/Cells/XLCell.cs b/ClosedXML/Excel/Cells/XLCell.cs index b0d8fa3..e085c64 100644 --- a/ClosedXML/Excel/Cells/XLCell.cs +++ b/ClosedXML/Excel/Cells/XLCell.cs @@ -2746,5 +2746,48 @@ public Boolean HasArrayFormula { get { return FormulaA1.StartsWith("{"); } } public IXLRangeAddress FormulaReference { get; set; } + + public IXLRange CurrentRegion + { + get + { + return this.Worksheet.Range(SearchCurrentRegion(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) + { + var rangeAddress = range.RangeAddress; + + var filledCells = SurroundingCells(this.Worksheet.Range(rangeAddress)) + .Where(c => !c.IsEmpty(false, false)) + .Concat(this.Worksheet.Range(rangeAddress).Cells()); + + var grownRangeAddress = new XLRangeAddress( + new XLAddress(this.Worksheet, filledCells.Min(c => c.Address.RowNumber), filledCells.Min(c => c.Address.ColumnNumber), false, false), + 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)); + } } } diff --git a/ClosedXML_Tests/Excel/Cells/XLCellTests.cs b/ClosedXML_Tests/Excel/Cells/XLCellTests.cs index cfa1951..161f17a 100644 --- a/ClosedXML_Tests/Excel/Cells/XLCellTests.cs +++ b/ClosedXML_Tests/Excel/Cells/XLCellTests.cs @@ -235,7 +235,6 @@ Assert.IsFalse(success); } - [Test] public void TryGetValue_DateTime_BadString2() { @@ -426,5 +425,90 @@ Assert.AreEqual(string.Empty, cell.Value); } } + + [Test] + public void CurrentRegion() + { + // Partially based on sample in https://github.com/ClosedXML/ClosedXML/issues/120 + using (var wb = new XLWorkbook()) + { + var ws = wb.AddWorksheet("Sheet1"); + + ws.Cell("B1").SetValue("x") + .CellBelow().SetValue("x") + .CellBelow().SetValue("x"); + + ws.Cell("C1").SetValue("x") + .CellBelow().SetValue("x") + .CellBelow().SetValue("x"); + + //Deliberately D2 + ws.Cell("D2").SetValue("x") + .CellBelow().SetValue("x"); + + ws.Cell("G1").SetValue("x") + .CellBelow() // skip a cell + .CellBelow().SetValue("x") + .CellBelow().SetValue("x"); + + // Deliberately H2 + ws.Cell("H2").SetValue("x") + .CellBelow().SetValue("x") + .CellBelow().SetValue("x"); + + // A diagonal + ws.Cell("E8").SetValue("x") + .CellBelow().CellRight().SetValue("x") + .CellBelow().CellRight().SetValue("x") + .CellBelow().CellRight().SetValue("x") + .CellBelow().CellRight().SetValue("x"); + + Assert.AreEqual("A10:A10", ws.Cell("A10").CurrentRegion.RangeAddress.ToString()); + Assert.AreEqual("B5:B5", ws.Cell("B5").CurrentRegion.RangeAddress.ToString()); + Assert.AreEqual("P1:P1", ws.Cell("P1").CurrentRegion.RangeAddress.ToString()); + + Assert.AreEqual("B1:D3", ws.Cell("D3").CurrentRegion.RangeAddress.ToString()); + Assert.AreEqual("B1:D4", ws.Cell("D4").CurrentRegion.RangeAddress.ToString()); + Assert.AreEqual("B1:E4", ws.Cell("E4").CurrentRegion.RangeAddress.ToString()); + + foreach (var c in ws.Range("B1:D3").Cells()) + { + Assert.AreEqual("B1:D3", c.CurrentRegion.RangeAddress.ToString()); + } + + foreach (var c in ws.Range("A1:A3").Cells()) + { + Assert.AreEqual("A1:D3", c.CurrentRegion.RangeAddress.ToString()); + } + + Assert.AreEqual("A1:D4", ws.Cell("A4").CurrentRegion.RangeAddress.ToString()); + + foreach (var c in ws.Range("E1:E3").Cells()) + { + Assert.AreEqual("B1:E3", c.CurrentRegion.RangeAddress.ToString()); + } + Assert.AreEqual("B1:E4", ws.Cell("E4").CurrentRegion.RangeAddress.ToString()); + + //// SECOND REGION + foreach (var c in ws.Range("F1:F4").Cells()) + { + Assert.AreEqual("F1:H4", c.CurrentRegion.RangeAddress.ToString()); + } + Assert.AreEqual("F1:H5", ws.Cell("F5").CurrentRegion.RangeAddress.ToString()); + + //// DIAGONAL + Assert.AreEqual("E8:I12", ws.Cell("E8").CurrentRegion.RangeAddress.ToString()); + Assert.AreEqual("E8:I12", ws.Cell("F9").CurrentRegion.RangeAddress.ToString()); + Assert.AreEqual("E8:I12", ws.Cell("G10").CurrentRegion.RangeAddress.ToString()); + Assert.AreEqual("E8:I12", ws.Cell("H11").CurrentRegion.RangeAddress.ToString()); + Assert.AreEqual("E8:I12", ws.Cell("I12").CurrentRegion.RangeAddress.ToString()); + + Assert.AreEqual("E8:I12", ws.Cell("G9").CurrentRegion.RangeAddress.ToString()); + Assert.AreEqual("E8:I12", ws.Cell("F10").CurrentRegion.RangeAddress.ToString()); + + Assert.AreEqual("D7:I12", ws.Cell("D7").CurrentRegion.RangeAddress.ToString()); + Assert.AreEqual("E8:J13", ws.Cell("J13").CurrentRegion.RangeAddress.ToString()); + } + } } }