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());
+ }
+ }
}
}