diff --git a/ClosedXML/Excel/NamedRanges/XLNamedRange.cs b/ClosedXML/Excel/NamedRanges/XLNamedRange.cs index 4f648b8..6920917 100644 --- a/ClosedXML/Excel/NamedRanges/XLNamedRange.cs +++ b/ClosedXML/Excel/NamedRanges/XLNamedRange.cs @@ -6,27 +6,57 @@ { internal class XLNamedRange : IXLNamedRange { + private String _name; private readonly XLNamedRanges _namedRanges; public XLNamedRange(XLNamedRanges namedRanges, String rangeName, String range, String comment = null) { + _namedRanges = namedRanges; Visible = true; Name = rangeName; RangeList.Add(range); Comment = comment; - _namedRanges = namedRanges; } public XLNamedRange(XLNamedRanges namedRanges, String rangeName, IXLRanges ranges, String comment = null) { + _namedRanges = namedRanges; Visible = true; Name = rangeName; ranges.ForEach(r => RangeList.Add(r.RangeAddress.ToStringFixed(XLReferenceStyle.A1, true))); Comment = comment; - _namedRanges = namedRanges; } - public String Name { get; set; } + public String Name + { + get { return _name; } + set + { + if (_name == value) return; + + var oldname = _name ?? string.Empty; + + var existingNames = _namedRanges.Select(nr => nr.Name).ToList(); + if (_namedRanges.Scope == XLNamedRangeScope.Workbook) + existingNames.AddRange(_namedRanges.Workbook.NamedRanges.Select(nr => nr.Name)); + + if (_namedRanges.Scope == XLNamedRangeScope.Worksheet) + existingNames.AddRange(_namedRanges.Worksheet.NamedRanges.Select(nr => nr.Name)); + + existingNames = existingNames.Distinct().ToList(); + + if (!XLHelper.ValidateName("named range", value, oldname, existingNames, out String message)) + throw new ArgumentException(message, nameof(value)); + + _name = value; + + if (!String.IsNullOrWhiteSpace(oldname) && !String.Equals(oldname, _name, StringComparison.OrdinalIgnoreCase)) + { + _namedRanges.Delete(oldname); + _namedRanges.Add(_name, this); + } + } + } public IXLRanges Ranges { diff --git a/ClosedXML/Excel/NamedRanges/XLNamedRanges.cs b/ClosedXML/Excel/NamedRanges/XLNamedRanges.cs index ae0273a..8d7e013 100644 --- a/ClosedXML/Excel/NamedRanges/XLNamedRanges.cs +++ b/ClosedXML/Excel/NamedRanges/XLNamedRanges.cs @@ -6,7 +6,7 @@ { internal class XLNamedRanges : IXLNamedRanges { - private readonly Dictionary _namedRanges = new Dictionary(); + private readonly Dictionary _namedRanges = new Dictionary(StringComparer.OrdinalIgnoreCase); internal XLWorkbook Workbook { get; set; } internal XLWorksheet Worksheet { get; set; } internal XLNamedRangeScope Scope { get; } @@ -113,6 +113,12 @@ return namedRange; } + public IXLNamedRange Add(String rangeName, IXLNamedRange namedRange) + { + _namedRanges.Add(rangeName, namedRange); + return namedRange; + } + public void Delete(String rangeName) { _namedRanges.Remove(rangeName); diff --git a/ClosedXML_Tests/Excel/NamedRanges/NamedRangesTests.cs b/ClosedXML_Tests/Excel/NamedRanges/NamedRangesTests.cs index 5054c1e..1e4bc85 100644 --- a/ClosedXML_Tests/Excel/NamedRanges/NamedRangesTests.cs +++ b/ClosedXML_Tests/Excel/NamedRanges/NamedRangesTests.cs @@ -193,22 +193,22 @@ ws1.Cell(3, 1).FormulaA1 = "TEST2"; ws1.Cell(4, 1).FormulaA1 = "TEST2*3"; - Assert.AreEqual(0.1, (double) ws1.Cell(1, 1).Value, XLHelper.Epsilon); - Assert.AreEqual(1.0, (double) ws1.Cell(2, 1).Value, XLHelper.Epsilon); - Assert.AreEqual(0.2, (double) ws1.Cell(3, 1).Value, XLHelper.Epsilon); - Assert.AreEqual(0.6, (double) ws1.Cell(4, 1).Value, XLHelper.Epsilon); + Assert.AreEqual(0.1, (double)ws1.Cell(1, 1).Value, XLHelper.Epsilon); + Assert.AreEqual(1.0, (double)ws1.Cell(2, 1).Value, XLHelper.Epsilon); + Assert.AreEqual(0.2, (double)ws1.Cell(3, 1).Value, XLHelper.Epsilon); + Assert.AreEqual(0.6, (double)ws1.Cell(4, 1).Value, XLHelper.Epsilon); wb.SaveAs(ms); } - using (var wb = new XLWorkbook(ms)) + using (var wb = new XLWorkbook(ms)) { var ws1 = wb.Worksheets.First(); - Assert.AreEqual(0.1, (double) ws1.Cell(1, 1).Value, XLHelper.Epsilon); - Assert.AreEqual(1.0, (double) ws1.Cell(2, 1).Value, XLHelper.Epsilon); - Assert.AreEqual(0.2, (double) ws1.Cell(3, 1).Value, XLHelper.Epsilon); - Assert.AreEqual(0.6, (double) ws1.Cell(4, 1).Value, XLHelper.Epsilon); + Assert.AreEqual(0.1, (double)ws1.Cell(1, 1).Value, XLHelper.Epsilon); + Assert.AreEqual(1.0, (double)ws1.Cell(2, 1).Value, XLHelper.Epsilon); + Assert.AreEqual(0.2, (double)ws1.Cell(3, 1).Value, XLHelper.Epsilon); + Assert.AreEqual(0.6, (double)ws1.Cell(4, 1).Value, XLHelper.Epsilon); } } } @@ -225,7 +225,7 @@ ws1.Cell(2, 1).FormulaA1 = "=SUM(TEST)"; - Assert.AreEqual(12.0, (double) ws1.Cell(2, 1).Value, XLHelper.Epsilon); + Assert.AreEqual(12.0, (double)ws1.Cell(2, 1).Value, XLHelper.Epsilon); } } @@ -255,5 +255,35 @@ Assert.AreEqual(1, ws2.Cell("value2").GetValue()); Assert.AreEqual(1, ws2.Range("value2").FirstCell().GetValue()); } + + [Test] + public void CanRenameNamedRange() + { + using (var wb = new XLWorkbook()) + { + var ws1 = wb.AddWorksheet("Sheet1"); + var nr1 = wb.NamedRanges.Add("TEST", "=0.1"); + + Assert.IsTrue(wb.NamedRanges.TryGetValue("TEST", out IXLNamedRange _)); + Assert.IsFalse(wb.NamedRanges.TryGetValue("TEST1", out IXLNamedRange _)); + + nr1.Name = "TEST1"; + + Assert.IsFalse(wb.NamedRanges.TryGetValue("TEST", out IXLNamedRange _)); + Assert.IsTrue(wb.NamedRanges.TryGetValue("TEST1", out IXLNamedRange _)); + + var nr2 = wb.NamedRanges.Add("TEST2", "=TEST1*2"); + + ws1.Cell(1, 1).FormulaA1 = "TEST1"; + ws1.Cell(2, 1).FormulaA1 = "TEST1*10"; + ws1.Cell(3, 1).FormulaA1 = "TEST2"; + ws1.Cell(4, 1).FormulaA1 = "TEST2*3"; + + Assert.AreEqual(0.1, (double)ws1.Cell(1, 1).Value, XLHelper.Epsilon); + Assert.AreEqual(1.0, (double)ws1.Cell(2, 1).Value, XLHelper.Epsilon); + Assert.AreEqual(0.2, (double)ws1.Cell(3, 1).Value, XLHelper.Epsilon); + Assert.AreEqual(0.6, (double)ws1.Cell(4, 1).Value, XLHelper.Epsilon); + } + } } }