diff --git a/ClosedXML/Excel/NamedRanges/IXLNamedRange.cs b/ClosedXML/Excel/NamedRanges/IXLNamedRange.cs index 3f57c20..0c21e4c 100644 --- a/ClosedXML/Excel/NamedRanges/IXLNamedRange.cs +++ b/ClosedXML/Excel/NamedRanges/IXLNamedRange.cs @@ -2,6 +2,12 @@ namespace ClosedXML.Excel { + public enum XLNamedRangeScope + { + Worksheet, + Workbook + } + public interface IXLNamedRange { /// @@ -35,6 +41,11 @@ Boolean Visible { get; set; } /// + /// Gets the scope of this named range. + /// + XLNamedRangeScope Scope { get; } + + /// /// Adds the specified range to this named range. /// Note: A named range can point to multiple ranges. /// diff --git a/ClosedXML/Excel/NamedRanges/XLNamedRange.cs b/ClosedXML/Excel/NamedRanges/XLNamedRange.cs index ce7e2a5..b4a2799 100644 --- a/ClosedXML/Excel/NamedRanges/XLNamedRange.cs +++ b/ClosedXML/Excel/NamedRanges/XLNamedRange.cs @@ -53,6 +53,8 @@ public Boolean Visible { get; set; } + public XLNamedRangeScope Scope { get { return _namedRanges.Scope; } } + public IXLRanges Add(XLWorkbook workbook, String rangeAddress) { var ranges = new XLRanges(); diff --git a/ClosedXML/Excel/NamedRanges/XLNamedRanges.cs b/ClosedXML/Excel/NamedRanges/XLNamedRanges.cs index 9a9a2ee..ae0273a 100644 --- a/ClosedXML/Excel/NamedRanges/XLNamedRanges.cs +++ b/ClosedXML/Excel/NamedRanges/XLNamedRanges.cs @@ -9,16 +9,19 @@ private readonly Dictionary _namedRanges = new Dictionary(); internal XLWorkbook Workbook { get; set; } internal XLWorksheet Worksheet { get; set; } + internal XLNamedRangeScope Scope { get; } public XLNamedRanges(XLWorksheet worksheet) : this(worksheet.Workbook) { Worksheet = worksheet; + Scope = XLNamedRangeScope.Worksheet; } public XLNamedRanges(XLWorkbook workbook) { Workbook = workbook; + Scope = XLNamedRangeScope.Workbook; } #region IXLNamedRanges Members @@ -70,14 +73,20 @@ { if (XLHelper.IsValidRangeAddress(rangeAddress)) { - var range = Worksheet?.Range(rangeAddress) ?? Workbook.Range(rangeAddress); + IXLRange range = null; + if (Scope == XLNamedRangeScope.Worksheet) + range = Worksheet.Range(rangeAddress); + else if (Scope == XLNamedRangeScope.Workbook) + range = Workbook.Range(rangeAddress); + else + throw new NotSupportedException($"Scope {Scope} is not supported"); if (range == null) throw new ArgumentException(string.Format( "The range address '{0}' for the named range '{1}' is not a valid range.", rangeAddress, rangeName)); - if (Worksheet == null || !XLHelper.NamedRangeReferenceRegex.Match(range.ToString()).Success) + if (Scope == XLNamedRangeScope.Workbook || !XLHelper.NamedRangeReferenceRegex.Match(range.ToString()).Success) throw new ArgumentException( "For named ranges in the workbook scope, specify the sheet name in the reference."); @@ -143,9 +152,7 @@ { if (_namedRanges.TryGetValue(name, out range)) return true; - if (Worksheet != null) - range = Worksheet.NamedRange(name); - else + if (Scope == XLNamedRangeScope.Workbook) range = Workbook.NamedRange(name); return range != null; @@ -155,10 +162,10 @@ { if (_namedRanges.ContainsKey(name)) return true; - if (Worksheet != null) - return Worksheet.NamedRange(name) != null; - else + if (Scope == XLNamedRangeScope.Workbook) return Workbook.NamedRange(name) != null; + else + return false; } } } diff --git a/ClosedXML_Tests/Excel/NamedRanges/NamedRangesTests.cs b/ClosedXML_Tests/Excel/NamedRanges/NamedRangesTests.cs index 91779e4..5054c1e 100644 --- a/ClosedXML_Tests/Excel/NamedRanges/NamedRangesTests.cs +++ b/ClosedXML_Tests/Excel/NamedRanges/NamedRangesTests.cs @@ -36,6 +36,10 @@ Assert.AreEqual("Sheet1!$C$3,Sheet1!$C$4:$D$4,Sheet2!$D$3:$D$4,Sheet1!$7:$8,Sheet1!$G:$H", sheet1.NamedRanges.First().RefersTo); Assert.AreEqual("Sheet1!B2,Sheet2!A1", sheet2.NamedRanges.First().RefersTo); + + wb.NamedRanges.ForEach(nr => Assert.AreEqual(XLNamedRangeScope.Workbook, nr.Scope)); + sheet1.NamedRanges.ForEach(nr => Assert.AreEqual(XLNamedRangeScope.Worksheet, nr.Scope)); + sheet2.NamedRanges.ForEach(nr => Assert.AreEqual(XLNamedRangeScope.Worksheet, nr.Scope)); } [Test] @@ -54,6 +58,7 @@ Boolean result1 = wb.NamedRanges.TryGetValue("Sheet1!Name", out IXLNamedRange range1); Assert.IsTrue(result1); Assert.IsNotNull(range1); + Assert.AreEqual(XLNamedRangeScope.Worksheet, range1.Scope); Boolean result2 = wb.NamedRanges.TryGetValue("Sheet1!NameX", out IXLNamedRange range2); Assert.IsFalse(result2);