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/Excel/Drawings/XLPicture.cs b/ClosedXML/Excel/Drawings/XLPicture.cs index 9266215..b1e44e8 100644 --- a/ClosedXML/Excel/Drawings/XLPicture.cs +++ b/ClosedXML/Excel/Drawings/XLPicture.cs @@ -147,6 +147,8 @@ { if ((_worksheet.Pictures.FirstOrDefault(p => p.Id.Equals(value)) ?? this) != this) throw new ArgumentException($"The picture ID '{value}' already exists."); + + id = value; } } 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()); + } + } } } diff --git a/ClosedXML_Tests/Excel/ImageHandling/PictureTests.cs b/ClosedXML_Tests/Excel/ImageHandling/PictureTests.cs index a06902e..0cd0edf 100644 --- a/ClosedXML_Tests/Excel/ImageHandling/PictureTests.cs +++ b/ClosedXML_Tests/Excel/ImageHandling/PictureTests.cs @@ -260,5 +260,40 @@ Assert.Throws(() => picture.Name = "picTURE 1"); } } + + [Test] + public void HandleDuplicatePictureIdsAcrossWorksheets() + { + using (var wb = new XLWorkbook()) + { + var ws1 = wb.AddWorksheet("Sheet1"); + var ws2 = wb.AddWorksheet("Sheet2"); + + using (var stream = Assembly.GetExecutingAssembly().GetManifestResourceStream("ClosedXML_Tests.Resource.Images.ImageHandling.png")) + { + (ws1 as XLWorksheet).AddPicture(stream, "Picture 1", 2); + (ws1 as XLWorksheet).AddPicture(stream, "Picture 2", 3); + + //Internal method - used for loading files + var pic = (ws2 as XLWorksheet).AddPicture(stream, "Picture 1", 2) + .WithPlacement(XLPicturePlacement.FreeFloating) + .MoveTo(220, 155) as XLPicture; + + var id = pic.Id; + + pic.Id = id; + Assert.AreEqual(id, pic.Id); + + pic.Id = 3; + Assert.AreEqual(3, pic.Id); + + pic.Id = id; + + var pic2 = (ws2 as XLWorksheet).AddPicture(stream, "Picture 2", 3) + .WithPlacement(XLPicturePlacement.FreeFloating) + .MoveTo(440, 300) as XLPicture; + } + } + } } } diff --git a/ClosedXML_Tests/Utils/StreamHelper.cs b/ClosedXML_Tests/Utils/StreamHelper.cs index b68a11f..8ecb4f9 100644 --- a/ClosedXML_Tests/Utils/StreamHelper.cs +++ b/ClosedXML_Tests/Utils/StreamHelper.cs @@ -51,7 +51,7 @@ throw new ArgumentException("Can't write to stream", "pStream"); } - #endregion + #endregion Check params foreach (byte b in pBynaryArray) { @@ -86,7 +86,7 @@ throw new ArgumentException("Can't write to stream", "streamToWrite"); } - #endregion + #endregion Check params var buf = new byte[512]; long length; @@ -135,37 +135,53 @@ throw new ArgumentException("Must be in position 0", "other"); } - #endregion + #endregion Check - var stringOne = new StreamReader(one).ReadToEnd().StripColumnWidths(stripColumnWidths); - var stringOther = new StreamReader(other).ReadToEnd().StripColumnWidths(stripColumnWidths); + var stringOne = new StreamReader(one).ReadToEnd().RemoveIgnoredParts(stripColumnWidths, ignoreGuids: true); + var stringOther = new StreamReader(other).ReadToEnd().RemoveIgnoredParts(stripColumnWidths, ignoreGuids: true); return stringOne == stringOther; } + private static string RemoveIgnoredParts(this string s, Boolean ignoreColumnWidths, Boolean ignoreGuids) + { + if (ignoreColumnWidths) + s = RemoveColumnWidths(s); + + if (ignoreGuids) + s = RemoveGuids(s); + + return s; + } + private static Regex columnRegex = new Regex("", RegexOptions.Compiled); private static Regex widthRegex = new Regex("width=\"\\d+(\\.\\d+)?\"\\s+", RegexOptions.Compiled); - private static string StripColumnWidths(this string s, bool stripIt) + private static String RemoveColumnWidths(String s) { - if (!stripIt) - return s; - else - { - var replacements = new Dictionary(); - - foreach (var m in columnRegex.Matches(s).OfType()) - { - var original = m.Groups[0].Value; - var replacement = widthRegex.Replace(original, ""); - replacements.Add(original, replacement); - } + var replacements = new Dictionary(); - foreach (var r in replacements) - { - s = s.Replace(r.Key, r.Value); - } - return s; + foreach (var m in columnRegex.Matches(s).OfType()) + { + var original = m.Groups[0].Value; + var replacement = widthRegex.Replace(original, ""); + replacements.Add(original, replacement); } + + foreach (var r in replacements) + { + s = s.Replace(r.Key, r.Value); + } + return s; + } + + private static Regex guidRegex = new Regex(@"{[0-9A-Fa-f]{8}-([0-9A-Fa-f]{4}-){3}[0-9A-Fa-f]{12}}", RegexOptions.Compiled | RegexOptions.Multiline); + + private static String RemoveGuids(String s) + { + return guidRegex.Replace(s, delegate (Match m) + { + return string.Empty; + }); } } -} \ No newline at end of file +}