diff --git a/ClosedXML/Excel/Coordinates/XLAddress.cs b/ClosedXML/Excel/Coordinates/XLAddress.cs index e232a76..11d34d1 100644 --- a/ClosedXML/Excel/Coordinates/XLAddress.cs +++ b/ClosedXML/Excel/Coordinates/XLAddress.cs @@ -204,6 +204,9 @@ public override string ToString() { + if (!IsValid) + return "#REF!"; + String retVal = ColumnLetter; if (_fixedColumn) { @@ -225,7 +228,9 @@ public string ToString(XLReferenceStyle referenceStyle, bool includeSheet) { string address; - if (referenceStyle == XLReferenceStyle.A1) + if (!IsValid) + address = "#REF!"; + else if (referenceStyle == XLReferenceStyle.A1) address = GetTrimmedAddress(); else if (referenceStyle == XLReferenceStyle.R1C1 || HasWorksheet && Worksheet.Workbook.ReferenceStyle == XLReferenceStyle.R1C1) @@ -235,7 +240,7 @@ if (includeSheet) return String.Concat( - Worksheet.Name.EscapeSheetName(), + WorksheetIsDeleted ? "#REF" : Worksheet.Name.EscapeSheetName(), '!', address); @@ -380,14 +385,16 @@ public String ToStringRelative(Boolean includeSheet) { + var address = IsValid ? GetTrimmedAddress() : "#REF!"; + if (includeSheet) return String.Concat( - Worksheet.Name.EscapeSheetName(), + WorksheetIsDeleted ? "#REF" : Worksheet.Name.EscapeSheetName(), '!', - GetTrimmedAddress() + address ); - return GetTrimmedAddress(); + return address; } internal XLAddress WithoutWorksheet() @@ -412,23 +419,30 @@ Debug.Assert(referenceStyle != XLReferenceStyle.Default); - switch (referenceStyle) + if (!IsValid) { - case XLReferenceStyle.A1: - address = String.Concat('$', ColumnLetter, '$', _rowNumber.ToInvariantString()); - break; + address = "#REF!"; + } + else + { + switch (referenceStyle) + { + case XLReferenceStyle.A1: + address = String.Concat('$', ColumnLetter, '$', _rowNumber.ToInvariantString()); + break; - case XLReferenceStyle.R1C1: - address = String.Concat('R', _rowNumber.ToInvariantString(), 'C', ColumnNumber); - break; + case XLReferenceStyle.R1C1: + address = String.Concat('R', _rowNumber.ToInvariantString(), 'C', ColumnNumber); + break; - default: - throw new NotImplementedException(); + default: + throw new NotImplementedException(); + } } if (includeSheet) return String.Concat( - Worksheet.Name.EscapeSheetName(), + WorksheetIsDeleted ? "#REF" : Worksheet.Name.EscapeSheetName(), '!', address); @@ -445,5 +459,7 @@ 0 < ColumnNumber && ColumnNumber <= XLHelper.MaxColumnNumber; } } + + private bool WorksheetIsDeleted => Worksheet?.IsDeleted == true; } } diff --git a/ClosedXML/Excel/NamedRanges/IXLNamedRange.cs b/ClosedXML/Excel/NamedRanges/IXLNamedRange.cs index d47e3bc..56de925 100644 --- a/ClosedXML/Excel/NamedRanges/IXLNamedRange.cs +++ b/ClosedXML/Excel/NamedRanges/IXLNamedRange.cs @@ -21,6 +21,11 @@ String Comment { get; set; } /// + /// Checks if the named range contains invalid references (#REF!). + /// + bool IsValid { get; } + + /// /// Gets or sets the name of the range. /// /// diff --git a/ClosedXML/Excel/NamedRanges/IXLNamedRanges.cs b/ClosedXML/Excel/NamedRanges/IXLNamedRanges.cs index 3084e81..3d0eb51 100644 --- a/ClosedXML/Excel/NamedRanges/IXLNamedRanges.cs +++ b/ClosedXML/Excel/NamedRanges/IXLNamedRanges.cs @@ -80,5 +80,15 @@ Boolean TryGetValue(String name, out IXLNamedRange range); Boolean Contains(String name); + + /// + /// Returns a subset of named ranges that do not have invalid references. + /// + IEnumerable ValidNamedRanges(); + + /// + /// Returns a subset of named ranges that do have invalid references. + /// + IEnumerable InvalidNamedRanges(); } } diff --git a/ClosedXML/Excel/NamedRanges/XLNamedRange.cs b/ClosedXML/Excel/NamedRanges/XLNamedRange.cs index 4943a4f..fc55948 100644 --- a/ClosedXML/Excel/NamedRanges/XLNamedRange.cs +++ b/ClosedXML/Excel/NamedRanges/XLNamedRange.cs @@ -1,3 +1,4 @@ +using ClosedXML.Extensions; using System; using System.Collections.Generic; using System.Linq; @@ -9,24 +10,41 @@ private String _name; private readonly XLNamedRanges _namedRanges; + internal XLWorkbook Workbook => _namedRanges.Workbook; + public XLNamedRange(XLNamedRanges namedRanges, String rangeName, String range, String comment = null) + : this(namedRanges, rangeName, comment) { - _namedRanges = namedRanges; - Visible = true; - Name = rangeName; RangeList.Add(range); - Comment = comment; } public XLNamedRange(XLNamedRanges namedRanges, String rangeName, IXLRanges ranges, String comment = null) + : this(namedRanges, rangeName, comment) { - _namedRanges = namedRanges; + ranges.ForEach(r => RangeList.Add(r.RangeAddress.ToStringFixed(XLReferenceStyle.A1, true))); + } + + private XLNamedRange(XLNamedRanges namedRanges, String rangeName, String comment) + { + _namedRanges = namedRanges ?? throw new ArgumentNullException(nameof(namedRanges)); Visible = true; Name = rangeName; - ranges.ForEach(r => RangeList.Add(r.RangeAddress.ToStringFixed(XLReferenceStyle.A1, true))); Comment = comment; } + /// + /// Checks if the named range contains invalid references (#REF!). + /// + public bool IsValid + { + get + { + return RangeList.SelectMany(c => c.Split(',')).All(r => + !r.StartsWith("#REF!", StringComparison.OrdinalIgnoreCase) && + !r.EndsWith("#REF!", StringComparison.OrdinalIgnoreCase)); + } + } + public String Name { get { return _name; } @@ -67,11 +85,13 @@ from rangeAddress in RangeList.SelectMany(c => c.Split(',')).Where(s => s[0] != '"') let match = XLHelper.NamedRangeReferenceRegex.Match(rangeAddress) select - match.Groups["Sheet"].Success - ? _namedRanges.Workbook.WorksheetsInternal.Worksheet(match.Groups["Sheet"].Value).Range(match.Groups["Range"].Value) as IXLRangeBase - : _namedRanges.Workbook.Worksheets.SelectMany(sheet => sheet.Tables).Single(table => table.Name == match.Groups["Table"].Value).DataRange.Column(match.Groups["Column"].Value)) + match.Groups["Sheet"].Success && Workbook.Worksheets.Contains(match.Groups["Sheet"].Value) + ? Workbook.WorksheetsInternal.Worksheet(match.Groups["Sheet"].Value).Range(match.Groups["Range"].Value) as IXLRangeBase + : Workbook.Worksheets.SelectMany(sheet => sheet.Tables).SingleOrDefault(table => table.Name == match.Groups["Table"].Value)? + .DataRange?.Column(match.Groups["Column"].Value)) { - ranges.Add(rangeToAdd); + if (rangeToAdd != null) + ranges.Add(rangeToAdd); } return ranges; } @@ -188,5 +208,18 @@ ranges.ForEach(r => RangeList.Add(r.RangeAddress.ToStringFixed(XLReferenceStyle.A1, true))); return this; } + + internal void OnWorksheetDeleted(string worksheetName) + { + var escapedSheetName = worksheetName.EscapeSheetName(); + RangeList = RangeList + .Select( + rl => string.Join(",", rl + .Split(',') + .Select(r => r.StartsWith(escapedSheetName + "!", StringComparison.OrdinalIgnoreCase) + ? "#REF!" + r.Substring(escapedSheetName.Length + 1) + : r)) + ).ToList(); + } } } diff --git a/ClosedXML/Excel/NamedRanges/XLNamedRanges.cs b/ClosedXML/Excel/NamedRanges/XLNamedRanges.cs index 8d7e013..b750a01 100644 --- a/ClosedXML/Excel/NamedRanges/XLNamedRanges.cs +++ b/ClosedXML/Excel/NamedRanges/XLNamedRanges.cs @@ -7,8 +7,11 @@ internal class XLNamedRanges : IXLNamedRanges { private readonly Dictionary _namedRanges = new Dictionary(StringComparer.OrdinalIgnoreCase); + internal XLWorkbook Workbook { get; set; } + internal XLWorksheet Worksheet { get; set; } + internal XLNamedRangeScope Scope { get; } public XLNamedRanges(XLWorksheet worksheet) @@ -134,6 +137,22 @@ _namedRanges.Clear(); } + /// + /// Returns a subset of named ranges that do not have invalid references. + /// + public IEnumerable ValidNamedRanges() + { + return this.Where(nr => nr.IsValid); + } + + /// + /// Returns a subset of named ranges that do have invalid references. + /// + public IEnumerable InvalidNamedRanges() + { + return this.Where(nr => !nr.IsValid); + } + #endregion IXLNamedRanges Members #region IEnumerable Members @@ -173,5 +192,12 @@ else return false; } + + internal void OnWorksheetDeleted(string worksheetName) + { + _namedRanges.Values + .Cast() + .ForEach(nr => nr.OnWorksheetDeleted(worksheetName)); + } } } diff --git a/ClosedXML/Excel/Ranges/XLRangeAddress.cs b/ClosedXML/Excel/Ranges/XLRangeAddress.cs index 7ebfb18..357656b 100644 --- a/ClosedXML/Excel/Ranges/XLRangeAddress.cs +++ b/ClosedXML/Excel/Ranges/XLRangeAddress.cs @@ -106,17 +106,13 @@ get { return LastAddress; } } - public bool IsValid - { - get - { - return FirstAddress.IsValid && - LastAddress.IsValid; - } - } + public bool IsValid => FirstAddress.IsValid && LastAddress.IsValid; + + private bool WorksheetIsDeleted => Worksheet?.IsDeleted == true; #endregion Public properties + #region Public methods /// @@ -168,32 +164,6 @@ new XLAddress(LastAddress.Worksheet, lastRow, lastColumn, lastRowFixed, lastColumnFixed)); } - public String ToStringRelative() - { - return ToStringRelative(false); - } - - public String ToStringFixed() - { - return ToStringFixed(XLReferenceStyle.A1); - } - - public String ToStringRelative(Boolean includeSheet) - { - if (includeSheet) - return String.Concat( - Worksheet.Name.EscapeSheetName(), - '!', - FirstAddress.ToStringRelative(), - ':', - LastAddress.ToStringRelative()); - else - return string.Concat( - FirstAddress.ToStringRelative(), - ":", - LastAddress.ToStringRelative()); - } - public bool Intersects(IXLRangeAddress otherAddress) { var xlOtherAddress = (XLRangeAddress)otherAddress; @@ -231,6 +201,32 @@ address.ColumnNumber <= LastAddress.ColumnNumber; } + public String ToStringRelative() + { + return ToStringRelative(false); + } + + public String ToStringFixed() + { + return ToStringFixed(XLReferenceStyle.A1); + } + + public String ToStringRelative(Boolean includeSheet) + { + string address = IsValid + ? String.Concat( + FirstAddress.ToStringRelative(), ":", + LastAddress.ToStringRelative()) + : "#REF!"; + + if (includeSheet || WorksheetIsDeleted) + return String.Concat( + WorksheetIsDeleted ? "#REF" : Worksheet.Name.EscapeSheetName(), + "!", address); + + return address; + } + public String ToStringFixed(XLReferenceStyle referenceStyle) { return ToStringFixed(referenceStyle, false); @@ -238,18 +234,26 @@ public String ToStringFixed(XLReferenceStyle referenceStyle, Boolean includeSheet) { - if (includeSheet) - return String.Format("{0}!{1}:{2}", - Worksheet.Name.EscapeSheetName(), - FirstAddress.ToStringFixed(referenceStyle), - LastAddress.ToStringFixed(referenceStyle)); + string address = IsValid + ? String.Concat( + FirstAddress.ToStringFixed(referenceStyle), ":", + LastAddress.ToStringFixed(referenceStyle)) + : "#REF!"; - return FirstAddress.ToStringFixed(referenceStyle) + ":" + LastAddress.ToStringFixed(referenceStyle); + if (includeSheet || WorksheetIsDeleted) + return String.Concat( + WorksheetIsDeleted ? "#REF" : Worksheet.Name.EscapeSheetName(), + "!", address); + + return address; } public override string ToString() { - return String.Concat(FirstAddress, ':', LastAddress); + string address = IsValid ? String.Concat(FirstAddress, ':', LastAddress) : "#REF!"; + + return String.Concat(WorksheetIsDeleted ? "#REF!" : "", + address); } public string ToString(XLReferenceStyle referenceStyle) diff --git a/ClosedXML/Excel/XLWorkbook_Load.cs b/ClosedXML/Excel/XLWorkbook_Load.cs index 4ae0459..15268d9 100644 --- a/ClosedXML/Excel/XLWorkbook_Load.cs +++ b/ClosedXML/Excel/XLWorkbook_Load.cs @@ -1282,20 +1282,17 @@ { string text = definedName.Text; - if (!(text.Equals("#REF") || text.EndsWith("#REF!"))) + var localSheetId = definedName.LocalSheetId; + var comment = definedName.Comment; + if (localSheetId == null) { - var localSheetId = definedName.LocalSheetId; - var comment = definedName.Comment; - if (localSheetId == null) - { - if (NamedRanges.All(nr => nr.Name != name)) - (NamedRanges as XLNamedRanges).Add(name, text, comment, true).Visible = visible; - } - else - { - if (Worksheet(Int32.Parse(localSheetId) + 1).NamedRanges.All(nr => nr.Name != name)) - (Worksheet(Int32.Parse(localSheetId) + 1).NamedRanges as XLNamedRanges).Add(name, text, comment, true).Visible = visible; - } + if (NamedRanges.All(nr => nr.Name != name)) + (NamedRanges as XLNamedRanges).Add(name, text, comment, true).Visible = visible; + } + else + { + if (Worksheet(Int32.Parse(localSheetId) + 1).NamedRanges.All(nr => nr.Name != name)) + (Worksheet(Int32.Parse(localSheetId) + 1).NamedRanges as XLNamedRanges).Add(name, text, comment, true).Visible = visible; } } } diff --git a/ClosedXML/Excel/XLWorksheet.cs b/ClosedXML/Excel/XLWorksheet.cs index 57e0d6a..6be26bb 100644 --- a/ClosedXML/Excel/XLWorksheet.cs +++ b/ClosedXML/Excel/XLWorksheet.cs @@ -140,6 +140,8 @@ public XLSheetProtection Protection { get; private set; } public XLAutoFilter AutoFilter { get; private set; } + public bool IsDeleted { get; private set; } + #region IXLWorksheet Members public XLWorkbook Workbook { get; private set; } @@ -524,6 +526,8 @@ public void Delete() { + IsDeleted = true; + (Workbook.NamedRanges as XLNamedRanges).OnWorksheetDeleted(Name); Workbook.WorksheetsInternal.Delete(Name); } diff --git a/ClosedXML_Tests/Excel/Coordinates/XLAddressTests.cs b/ClosedXML_Tests/Excel/Coordinates/XLAddressTests.cs index 686c1f4..77dc0ed 100644 --- a/ClosedXML_Tests/Excel/Coordinates/XLAddressTests.cs +++ b/ClosedXML_Tests/Excel/Coordinates/XLAddressTests.cs @@ -53,5 +53,139 @@ Assert.AreEqual("'Sheet 1'!R1C1", address.ToStringFixed(XLReferenceStyle.R1C1, true)); Assert.AreEqual("'Sheet 1'!$A$1", address.ToStringFixed(XLReferenceStyle.Default, true)); } + + [Test] + public void InvalidAddressToStringTest() + { + var address = ProduceInvalidAddress(); + + Assert.AreEqual("#REF!", address.ToString()); + Assert.AreEqual("#REF!", address.ToString(XLReferenceStyle.A1)); + Assert.AreEqual("#REF!", address.ToString(XLReferenceStyle.R1C1)); + Assert.AreEqual("#REF!", address.ToString(XLReferenceStyle.Default)); + Assert.AreEqual("'Sheet 1'!#REF!", address.ToString(XLReferenceStyle.Default, true)); + } + + [Test] + public void InvalidAddressToStringFixedTest() + { + var address = ProduceInvalidAddress(); + + Assert.AreEqual("#REF!", address.ToStringFixed()); + Assert.AreEqual("#REF!", address.ToStringFixed(XLReferenceStyle.A1)); + Assert.AreEqual("#REF!", address.ToStringFixed(XLReferenceStyle.R1C1)); + Assert.AreEqual("#REF!", address.ToStringFixed(XLReferenceStyle.Default)); + Assert.AreEqual("'Sheet 1'!#REF!", address.ToStringFixed(XLReferenceStyle.A1, true)); + Assert.AreEqual("'Sheet 1'!#REF!", address.ToStringFixed(XLReferenceStyle.R1C1, true)); + Assert.AreEqual("'Sheet 1'!#REF!", address.ToStringFixed(XLReferenceStyle.Default, true)); + } + + [Test] + public void InvalidAddressToStringRelativeTest() + { + var address = ProduceInvalidAddress(); + + Assert.AreEqual("#REF!", address.ToStringRelative()); + Assert.AreEqual("'Sheet 1'!#REF!", address.ToStringRelative(true)); + } + + [Test] + public void AddressOnDeletedWorksheetToStringTest() + { + var address = ProduceAddressOnDeletedWorksheet(); + + Assert.AreEqual("A1", address.ToString()); + Assert.AreEqual("A1", address.ToString(XLReferenceStyle.A1)); + Assert.AreEqual("R1C1", address.ToString(XLReferenceStyle.R1C1)); + Assert.AreEqual("A1", address.ToString(XLReferenceStyle.Default)); + Assert.AreEqual("#REF!A1", address.ToString(XLReferenceStyle.Default, true)); + } + + [Test] + public void AddressOnDeletedWorksheetToStringFixedTest() + { + var address = ProduceAddressOnDeletedWorksheet(); + + Assert.AreEqual("$A$1", address.ToStringFixed()); + Assert.AreEqual("$A$1", address.ToStringFixed(XLReferenceStyle.A1)); + Assert.AreEqual("R1C1", address.ToStringFixed(XLReferenceStyle.R1C1)); + Assert.AreEqual("$A$1", address.ToStringFixed(XLReferenceStyle.Default)); + Assert.AreEqual("#REF!$A$1", address.ToStringFixed(XLReferenceStyle.A1, true)); + Assert.AreEqual("#REF!R1C1", address.ToStringFixed(XLReferenceStyle.R1C1, true)); + Assert.AreEqual("#REF!$A$1", address.ToStringFixed(XLReferenceStyle.Default, true)); + } + + [Test] + public void AddressOnDeletedWorksheetToStringRelativeTest() + { + var address = ProduceAddressOnDeletedWorksheet(); + + Assert.AreEqual("A1", address.ToStringRelative()); + Assert.AreEqual("#REF!A1", address.ToStringRelative(true)); + } + + [Test] + public void InvalidAddressOnDeletedWorksheetToStringTest() + { + var address = ProduceInvalidAddressOnDeletedWorksheet(); + + Assert.AreEqual("#REF!", address.ToString()); + Assert.AreEqual("#REF!", address.ToString(XLReferenceStyle.A1)); + Assert.AreEqual("#REF!", address.ToString(XLReferenceStyle.R1C1)); + Assert.AreEqual("#REF!", address.ToString(XLReferenceStyle.Default)); + Assert.AreEqual("#REF!#REF!", address.ToString(XLReferenceStyle.Default, true)); + } + + [Test] + public void InvalidAddressOnDeletedWorksheetToStringFixedTest() + { + var address = ProduceInvalidAddressOnDeletedWorksheet(); + + Assert.AreEqual("#REF!", address.ToStringFixed()); + Assert.AreEqual("#REF!", address.ToStringFixed(XLReferenceStyle.A1)); + Assert.AreEqual("#REF!", address.ToStringFixed(XLReferenceStyle.R1C1)); + Assert.AreEqual("#REF!", address.ToStringFixed(XLReferenceStyle.Default)); + Assert.AreEqual("#REF!#REF!", address.ToStringFixed(XLReferenceStyle.A1, true)); + Assert.AreEqual("#REF!#REF!", address.ToStringFixed(XLReferenceStyle.R1C1, true)); + Assert.AreEqual("#REF!#REF!", address.ToStringFixed(XLReferenceStyle.Default, true)); + } + + [Test] + public void InvalidAddressOnDeletedWorksheetToStringRelativeTest() + { + var address = ProduceInvalidAddressOnDeletedWorksheet(); + + Assert.AreEqual("#REF!", address.ToStringRelative()); + Assert.AreEqual("#REF!#REF!", address.ToStringRelative(true)); + } + + #region Private Methods + + private IXLAddress ProduceInvalidAddress() + { + IXLWorksheet ws = new XLWorkbook().Worksheets.Add("Sheet 1"); + var range = ws.Range("A1:B2"); + + ws.Rows(1, 5).Delete(); + return range.RangeAddress.FirstAddress; + } + + private IXLAddress ProduceAddressOnDeletedWorksheet() + { + IXLWorksheet ws = new XLWorkbook().Worksheets.Add("Sheet 1"); + var address = ws.Cell("A1").Address; + + ws.Delete(); + return address; + } + + private IXLAddress ProduceInvalidAddressOnDeletedWorksheet() + { + var address = ProduceInvalidAddress(); + address.Worksheet.Delete(); + return address; + } + + #endregion Private Methods } } diff --git a/ClosedXML_Tests/Excel/NamedRanges/NamedRangesTests.cs b/ClosedXML_Tests/Excel/NamedRanges/NamedRangesTests.cs index 264838a..e8fc8dd 100644 --- a/ClosedXML_Tests/Excel/NamedRanges/NamedRangesTests.cs +++ b/ClosedXML_Tests/Excel/NamedRanges/NamedRangesTests.cs @@ -323,5 +323,246 @@ Assert.AreEqual("Sheet2!B2:E6", copy.Ranges.First().RangeAddress.ToString(XLReferenceStyle.A1, true)); Assert.AreEqual("Sheet2!D1:E2", copy.Ranges.Last().RangeAddress.ToString(XLReferenceStyle.A1, true)); } + + [Test] + public void NamedRangesBecomeInvalidOnWorksheetDeleting() + { + using (var wb = new XLWorkbook()) + { + var ws1 = wb.Worksheets.Add("Sheet 1"); + var ws2 = wb.Worksheets.Add("Sheet 2"); + var ws3 = wb.Worksheets.Add("Sheet'3"); + + ws1.Range("A1:D1").AddToNamed("Named range 1", XLScope.Worksheet); + ws1.Range("A2:D2").AddToNamed("Named range 2", XLScope.Workbook); + ws2.Range("A3:D3").AddToNamed("Named range 3", XLScope.Worksheet); + ws2.Range("A4:D4").AddToNamed("Named range 4", XLScope.Workbook); + wb.NamedRanges.Add("Named range 5", new XLRanges + { + ws1.Range("A5:D5"), + ws3.Range("A5:D5") + }); + + ws2.Delete(); + ws3.Delete(); + + Assert.AreEqual(1, ws1.NamedRanges.Count()); + Assert.AreEqual("Named range 1", ws1.NamedRanges.First().Name); + Assert.AreEqual(XLNamedRangeScope.Worksheet, ws1.NamedRanges.First().Scope); + Assert.AreEqual("'Sheet 1'!$A$1:$D$1", ws1.NamedRanges.First().RefersTo); + Assert.AreEqual("'Sheet 1'!A1:D1", ws1.NamedRanges.First().Ranges.Single().RangeAddress.ToString(XLReferenceStyle.A1, true)); + + Assert.AreEqual(3, wb.NamedRanges.Count()); + + Assert.AreEqual("Named range 2", wb.NamedRanges.ElementAt(0).Name); + Assert.AreEqual(XLNamedRangeScope.Workbook, wb.NamedRanges.ElementAt(0).Scope); + Assert.AreEqual("'Sheet 1'!$A$2:$D$2", wb.NamedRanges.ElementAt(0).RefersTo); + Assert.AreEqual("'Sheet 1'!A2:D2", wb.NamedRanges.ElementAt(0).Ranges.Single().RangeAddress.ToString(XLReferenceStyle.A1, true)); + + Assert.AreEqual("Named range 4", wb.NamedRanges.ElementAt(1).Name); + Assert.AreEqual(XLNamedRangeScope.Workbook, wb.NamedRanges.ElementAt(1).Scope); + Assert.AreEqual("#REF!$A$4:$D$4", wb.NamedRanges.ElementAt(1).RefersTo); + Assert.IsFalse(wb.NamedRanges.ElementAt(1).Ranges.Any()); + + Assert.AreEqual("Named range 5", wb.NamedRanges.ElementAt(2).Name); + Assert.AreEqual(XLNamedRangeScope.Workbook, wb.NamedRanges.ElementAt(2).Scope); + Assert.AreEqual("'Sheet 1'!$A$5:$D$5,#REF!$A$5:$D$5", wb.NamedRanges.ElementAt(2).RefersTo); + Assert.AreEqual(1, wb.NamedRanges.ElementAt(2).Ranges.Count); + Assert.AreEqual("'Sheet 1'!A5:D5", wb.NamedRanges.ElementAt(2).Ranges.Single().RangeAddress.ToString(XLReferenceStyle.A1, true)); + } + } + + [Test] + public void SavedNamedRangesBecomeInvalidOnWorksheetDeleting() + { + using (var ms = new MemoryStream()) + { + using (var wb = new XLWorkbook()) + { + var ws1 = wb.Worksheets.Add("Sheet 1"); + var ws2 = wb.Worksheets.Add("Sheet2"); + var ws3 = wb.Worksheets.Add("Sheet'3"); + + ws1.Range("A1:D1").AddToNamed("Named range 1", XLScope.Worksheet); + ws1.Range("A2:D2").AddToNamed("Named range 2", XLScope.Workbook); + ws2.Range("A3:D3").AddToNamed("Named range 3", XLScope.Worksheet); + ws2.Range("A4:D4").AddToNamed("Named range 4", XLScope.Workbook); + wb.NamedRanges.Add("Named range 5", new XLRanges + { + ws1.Range("A5:D5"), + ws3.Range("A5:D5") + }); + + wb.SaveAs(ms); + } + + using (var wb = new XLWorkbook(ms)) + { + wb.Worksheet("Sheet2").Delete(); + wb.Worksheet("Sheet'3").Delete(); + wb.Save(); + } + + using (var wb = new XLWorkbook(ms)) + { + var ws1 = wb.Worksheet("Sheet 1"); + Assert.AreEqual(1, ws1.NamedRanges.Count()); + Assert.AreEqual("Named range 1", ws1.NamedRanges.First().Name); + Assert.AreEqual(XLNamedRangeScope.Worksheet, ws1.NamedRanges.First().Scope); + Assert.AreEqual("'Sheet 1'!$A$1:$D$1", ws1.NamedRanges.First().RefersTo); + Assert.AreEqual("'Sheet 1'!A1:D1", + ws1.NamedRanges.First().Ranges.Single().RangeAddress.ToString(XLReferenceStyle.A1, true)); + + Assert.AreEqual(3, wb.NamedRanges.Count()); + + Assert.AreEqual("Named range 2", wb.NamedRanges.ElementAt(0).Name); + Assert.AreEqual(XLNamedRangeScope.Workbook, wb.NamedRanges.ElementAt(0).Scope); + Assert.AreEqual("'Sheet 1'!$A$2:$D$2", wb.NamedRanges.ElementAt(0).RefersTo); + Assert.AreEqual("'Sheet 1'!A2:D2", + wb.NamedRanges.ElementAt(0).Ranges.Single().RangeAddress.ToString(XLReferenceStyle.A1, true)); + + Assert.AreEqual("Named range 4", wb.NamedRanges.ElementAt(1).Name); + Assert.AreEqual(XLNamedRangeScope.Workbook, wb.NamedRanges.ElementAt(1).Scope); + Assert.AreEqual("#REF!$A$4:$D$4", wb.NamedRanges.ElementAt(1).RefersTo); + Assert.IsFalse(wb.NamedRanges.ElementAt(1).Ranges.Any()); + + Assert.AreEqual("Named range 5", wb.NamedRanges.ElementAt(2).Name); + Assert.AreEqual(XLNamedRangeScope.Workbook, wb.NamedRanges.ElementAt(2).Scope); + Assert.AreEqual("'Sheet 1'!$A$5:$D$5,#REF!$A$5:$D$5", wb.NamedRanges.ElementAt(2).RefersTo); + Assert.AreEqual(1, wb.NamedRanges.ElementAt(2).Ranges.Count); + Assert.AreEqual("'Sheet 1'!A5:D5", + wb.NamedRanges.ElementAt(2).Ranges.Single().RangeAddress.ToString(XLReferenceStyle.A1, true)); + } + } + } + + [Test, Ignore("Muted until shifting is fixed (see #880)")] + public void NamedRangeBecomesInvalidOnRangeDeleting() + { + using (var wb = new XLWorkbook()) + { + var ws = wb.Worksheets.Add("Sheet 1"); + ws.Range("A1:B2").AddToNamed("Simple", XLScope.Workbook); + wb.NamedRanges.Add("Compound", new XLRanges + { + ws.Range("C1:D2"), + ws.Range("A10:D15") + }); + + ws.Rows(1,5).Delete(); + + Assert.AreEqual(2, wb.NamedRanges.Count()); + Assert.AreEqual(0, wb.NamedRanges.ValidNamedRanges().Count()); + Assert.AreEqual("'Sheet 1'!#REF!", wb.NamedRanges.ElementAt(0).RefersTo); + Assert.AreEqual("'Sheet 1'!#REF!,'Sheet 1'!A5:D10", wb.NamedRanges.ElementAt(0).RefersTo); + } + } + + [Test, Ignore("Muted until shifting is fixed (see #880)")] + public void NamedRangeBecomesInvalidOnRangeAndWorksheetDeleting() + { + using (var wb = new XLWorkbook()) + { + var ws1 = wb.Worksheets.Add("Sheet 1"); + var ws2 = wb.Worksheets.Add("Sheet 2"); + ws1.Range("A1:B2").AddToNamed("Simple", XLScope.Workbook); + wb.NamedRanges.Add("Compound", new XLRanges + { + ws1.Range("C1:D2"), + ws2.Range("A10:D15") + }); + + ws1.Rows(1, 5).Delete(); + ws1.Delete(); + + Assert.AreEqual(2, wb.NamedRanges.Count()); + Assert.AreEqual(0, wb.NamedRanges.ValidNamedRanges().Count()); + Assert.AreEqual("#REF!#REF!", wb.NamedRanges.ElementAt(0).RefersTo); + Assert.AreEqual("#REF!#REF!,'Sheet 2'!A10:D15", wb.NamedRanges.ElementAt(0).RefersTo); + } + } + + [Test] + public void CanSaveAndLoadNamedRanges() + { + using (var ms = new MemoryStream()) + { + using (var wb = new XLWorkbook()) + { + var sheet1 = wb.Worksheets.Add("Sheet1"); + var sheet2 = wb.Worksheets.Add("Sheet2"); + + wb.NamedRanges.Add("wbNamedRange", + "Sheet1!$B$2,Sheet1!$B$3:$C$3,Sheet2!$D$3:$D$4,Sheet1!$6:$7,Sheet1!$F:$G"); + sheet1.NamedRanges.Add("sheet1NamedRange", + "Sheet1!$B$2,Sheet1!$B$3:$C$3,Sheet2!$D$3:$D$4,Sheet1!$6:$7,Sheet1!$F:$G"); + sheet2.NamedRanges.Add("sheet2NamedRange", "Sheet1!A1,Sheet2!A1"); + + wb.SaveAs(ms); + } + + using (var wb = new XLWorkbook(ms)) + { + var sheet1 = wb.Worksheet("Sheet1"); + var sheet2 = wb.Worksheet("Sheet2"); + + Assert.AreEqual(1, wb.NamedRanges.Count()); + Assert.AreEqual("wbNamedRange", wb.NamedRanges.Single().Name); + Assert.AreEqual("Sheet1!$B$2,Sheet1!$B$3:$C$3,Sheet2!$D$3:$D$4,Sheet1!$6:$7,Sheet1!$F:$G", wb.NamedRanges.Single().RefersTo); + Assert.AreEqual(5, wb.NamedRanges.Single().Ranges.Count); + + Assert.AreEqual(1, sheet1.NamedRanges.Count()); + Assert.AreEqual("sheet1NamedRange", sheet1.NamedRanges.Single().Name); + Assert.AreEqual("Sheet1!$B$2,Sheet1!$B$3:$C$3,Sheet2!$D$3:$D$4,Sheet1!$6:$7,Sheet1!$F:$G", sheet1.NamedRanges.Single().RefersTo); + Assert.AreEqual(5, sheet1.NamedRanges.Single().Ranges.Count); + + Assert.AreEqual(1, sheet2.NamedRanges.Count()); + Assert.AreEqual("sheet2NamedRange", sheet2.NamedRanges.Single().Name); + Assert.AreEqual("Sheet1!A1,Sheet2!A1", sheet2.NamedRanges.Single().RefersTo); + Assert.AreEqual(2, sheet2.NamedRanges.Single().Ranges.Count); + } + } + } + + [Test] + public void CanGetValidNamedRanges() + { + using (var wb = new XLWorkbook()) + { + var ws1 = wb.Worksheets.Add("Sheet 1"); + var ws2 = wb.Worksheets.Add("Sheet 2"); + var ws3 = wb.Worksheets.Add("Sheet'3"); + + ws1.Range("A1:D1").AddToNamed("Named range 1", XLScope.Worksheet); + ws1.Range("A2:D2").AddToNamed("Named range 2", XLScope.Workbook); + ws2.Range("A3:D3").AddToNamed("Named range 3", XLScope.Worksheet); + ws2.Range("A4:D4").AddToNamed("Named range 4", XLScope.Workbook); + wb.NamedRanges.Add("Named range 5", new XLRanges + { + ws1.Range("A5:D5"), + ws3.Range("A5:D5") + }); + + ws2.Delete(); + ws3.Delete(); + + var globalValidRanges = wb.NamedRanges.ValidNamedRanges(); + var globalInvalidRanges = wb.NamedRanges.InvalidNamedRanges(); + var localValidRanges = ws1.NamedRanges.ValidNamedRanges(); + var localInvalidRanges = ws1.NamedRanges.InvalidNamedRanges(); + + Assert.AreEqual(1, globalValidRanges.Count()); + Assert.AreEqual("Named range 2", globalValidRanges.First().Name); + + Assert.AreEqual(2, globalInvalidRanges.Count()); + Assert.AreEqual("Named range 4", globalInvalidRanges.First().Name); + Assert.AreEqual("Named range 5", globalInvalidRanges.Last().Name); + + Assert.AreEqual(1, localValidRanges.Count()); + Assert.AreEqual("Named range 1", localValidRanges.First().Name); + + Assert.AreEqual(0, localInvalidRanges.Count()); + } + } } } diff --git a/ClosedXML_Tests/Excel/Ranges/XLRangeAddressTests.cs b/ClosedXML_Tests/Excel/Ranges/XLRangeAddressTests.cs index 42077e6..10b13b1 100644 --- a/ClosedXML_Tests/Excel/Ranges/XLRangeAddressTests.cs +++ b/ClosedXML_Tests/Excel/Ranges/XLRangeAddressTests.cs @@ -69,5 +69,144 @@ Assert.AreSame(ws, rangeAddress.Worksheet); Assert.AreEqual(expectedAddress, normalizedAddress.ToString()); } + + [Test] + public void InvalidRangeAddressToStringTest() + { + var address = ProduceInvalidAddress(); + + Assert.AreEqual("#REF!", address.ToString()); + Assert.AreEqual("#REF!", address.ToString(XLReferenceStyle.A1)); + Assert.AreEqual("#REF!", address.ToString(XLReferenceStyle.Default)); + Assert.AreEqual("'Sheet 1'!#REF!", address.ToString(XLReferenceStyle.R1C1)); + Assert.AreEqual("'Sheet 1'!#REF!", address.ToString(XLReferenceStyle.A1, true)); + Assert.AreEqual("'Sheet 1'!#REF!", address.ToString(XLReferenceStyle.Default, true)); + Assert.AreEqual("'Sheet 1'!#REF!", address.ToString(XLReferenceStyle.R1C1, true)); + } + + [Test] + public void InvalidRangeAddressToStringFixedTest() + { + var address = ProduceInvalidAddress(); + + Assert.AreEqual("#REF!", address.ToStringFixed()); + Assert.AreEqual("#REF!", address.ToStringFixed(XLReferenceStyle.A1)); + Assert.AreEqual("#REF!", address.ToStringFixed(XLReferenceStyle.Default)); + Assert.AreEqual("#REF!", address.ToStringFixed(XLReferenceStyle.R1C1)); + Assert.AreEqual("'Sheet 1'!#REF!", address.ToStringFixed(XLReferenceStyle.A1, true)); + Assert.AreEqual("'Sheet 1'!#REF!", address.ToStringFixed(XLReferenceStyle.Default, true)); + Assert.AreEqual("'Sheet 1'!#REF!", address.ToStringFixed(XLReferenceStyle.R1C1, true)); + } + + [Test] + public void InvalidRangeAddressToStringRelativeTest() + { + var address = ProduceInvalidAddress(); + + Assert.AreEqual("#REF!", address.ToStringRelative()); + Assert.AreEqual("'Sheet 1'!#REF!", address.ToStringRelative(true)); + } + + [Test] + public void RangeAddressOnDeletedWorksheetToStringTest() + { + var address = ProduceAddressOnDeletedWorksheet(); + + Assert.AreEqual("#REF!A1:B2", address.ToString()); + Assert.AreEqual("#REF!A1:B2", address.ToString(XLReferenceStyle.A1)); + Assert.AreEqual("#REF!A1:B2", address.ToString(XLReferenceStyle.Default)); + Assert.AreEqual("#REF!R1C1:R2C2", address.ToString(XLReferenceStyle.R1C1)); + Assert.AreEqual("#REF!A1:B2", address.ToString(XLReferenceStyle.A1, true)); + Assert.AreEqual("#REF!A1:B2", address.ToString(XLReferenceStyle.Default, true)); + Assert.AreEqual("#REF!R1C1:R2C2", address.ToString(XLReferenceStyle.R1C1, true)); + } + + [Test] + public void RangeAddressOnDeletedWorksheetToStringFixedTest() + { + var address = ProduceAddressOnDeletedWorksheet(); + + Assert.AreEqual("#REF!$A$1:$B$2", address.ToStringFixed()); + Assert.AreEqual("#REF!$A$1:$B$2", address.ToStringFixed(XLReferenceStyle.A1)); + Assert.AreEqual("#REF!$A$1:$B$2", address.ToStringFixed(XLReferenceStyle.Default)); + Assert.AreEqual("#REF!R1C1:R2C2", address.ToStringFixed(XLReferenceStyle.R1C1)); + Assert.AreEqual("#REF!$A$1:$B$2", address.ToStringFixed(XLReferenceStyle.A1, true)); + Assert.AreEqual("#REF!$A$1:$B$2", address.ToStringFixed(XLReferenceStyle.Default, true)); + Assert.AreEqual("#REF!R1C1:R2C2", address.ToStringFixed(XLReferenceStyle.R1C1, true)); + } + + [Test] + public void RangeAddressOnDeletedWorksheetToStringRelativeTest() + { + var address = ProduceAddressOnDeletedWorksheet(); + + Assert.AreEqual("#REF!A1:B2", address.ToStringRelative()); + Assert.AreEqual("#REF!A1:B2", address.ToStringRelative(true)); + } + + [Test] + public void InvalidRangeAddressOnDeletedWorksheetToStringTest() + { + var address = ProduceInvalidAddressOnDeletedWorksheet(); + + Assert.AreEqual("#REF!#REF!", address.ToString()); + Assert.AreEqual("#REF!#REF!", address.ToString(XLReferenceStyle.A1)); + Assert.AreEqual("#REF!#REF!", address.ToString(XLReferenceStyle.Default)); + Assert.AreEqual("#REF!#REF!", address.ToString(XLReferenceStyle.R1C1)); + Assert.AreEqual("#REF!#REF!", address.ToString(XLReferenceStyle.A1, true)); + Assert.AreEqual("#REF!#REF!", address.ToString(XLReferenceStyle.Default, true)); + Assert.AreEqual("#REF!#REF!", address.ToString(XLReferenceStyle.R1C1, true)); + } + + [Test] + public void InvalidRangeAddressOnDeletedWorksheetToStringFixedTest() + { + var address = ProduceInvalidAddressOnDeletedWorksheet(); + + Assert.AreEqual("#REF!#REF!", address.ToStringFixed()); + Assert.AreEqual("#REF!#REF!", address.ToStringFixed(XLReferenceStyle.A1)); + Assert.AreEqual("#REF!#REF!", address.ToStringFixed(XLReferenceStyle.Default)); + Assert.AreEqual("#REF!#REF!", address.ToStringFixed(XLReferenceStyle.R1C1)); + Assert.AreEqual("#REF!#REF!", address.ToStringFixed(XLReferenceStyle.A1, true)); + Assert.AreEqual("#REF!#REF!", address.ToStringFixed(XLReferenceStyle.Default, true)); + Assert.AreEqual("#REF!#REF!", address.ToStringFixed(XLReferenceStyle.R1C1, true)); + } + + [Test] + public void InvalidRangeAddressOnDeletedWorksheetToStringRelativeTest() + { + var address = ProduceInvalidAddressOnDeletedWorksheet(); + + Assert.AreEqual("#REF!#REF!", address.ToStringRelative()); + Assert.AreEqual("#REF!#REF!", address.ToStringRelative(true)); + } + #region Private Methods + + private IXLRangeAddress ProduceInvalidAddress() + { + IXLWorksheet ws = new XLWorkbook().Worksheets.Add("Sheet 1"); + var range = ws.Range("A1:B2"); + + ws.Rows(1, 5).Delete(); + return range.RangeAddress; + } + + private IXLRangeAddress ProduceAddressOnDeletedWorksheet() + { + IXLWorksheet ws = new XLWorkbook().Worksheets.Add("Sheet 1"); + var address = ws.Range("A1:B2").RangeAddress; + + ws.Delete(); + return address; + } + + private IXLRangeAddress ProduceInvalidAddressOnDeletedWorksheet() + { + var address = ProduceInvalidAddress(); + address.Worksheet.Delete(); + return address; + } + + #endregion Private Methods } } diff --git a/ClosedXML_Tests/Excel/Worksheets/XLWorksheetTests.cs b/ClosedXML_Tests/Excel/Worksheets/XLWorksheetTests.cs index 3e97b4c..c280c16 100644 --- a/ClosedXML_Tests/Excel/Worksheets/XLWorksheetTests.cs +++ b/ClosedXML_Tests/Excel/Worksheets/XLWorksheetTests.cs @@ -815,5 +815,20 @@ Assert.AreEqual("Renamed!A1 * 3", ws1.Cell("A2").FormulaA1); } } + + [Test] + public void RangesFromDeletedWorksheetContainREF() + { + using (var wb1 = new XLWorkbook()) + { + wb1.Worksheets.Add("Sheet1"); + var ws2 = wb1.Worksheets.Add("Sheet2"); + var range = ws2.Range("A1:B2"); + + ws2.Delete(); + + Assert.AreEqual("#REF!A1:B2", range.RangeAddress.ToString()); + } + } } }