diff --git a/ClosedXML/Excel/CalcEngine/XLCalcEngine.cs b/ClosedXML/Excel/CalcEngine/XLCalcEngine.cs index ba5779d..d0fe5bb 100644 --- a/ClosedXML/Excel/CalcEngine/XLCalcEngine.cs +++ b/ClosedXML/Excel/CalcEngine/XLCalcEngine.cs @@ -95,11 +95,31 @@ } } else if (_ws != null) + { + if (TryGetNamedRange(identifier, _ws, out IXLNamedRange namedRange)) + { + var references = (namedRange as XLNamedRange).RangeList.Select(r => + XLHelper.IsValidRangeAddress(r) + ? GetCellRangeReference(_ws.Workbook.Range(r)) + : new XLCalcEngine(_ws).Evaluate(r.ToString()) + ); + if (references.Count() == 1) + return references.Single(); + return references; + } + return GetCellRangeReference(_ws.Range(identifier)); + } else return identifier; } + private bool TryGetNamedRange(string identifier, IXLWorksheet worksheet, out IXLNamedRange namedRange) + { + return worksheet.NamedRanges.TryGetValue(identifier, out namedRange) || + worksheet.Workbook.NamedRanges.TryGetValue(identifier, out namedRange); + } + private CellRangeReference GetCellRangeReference(IXLRange range) { var res = new CellRangeReference(range, this); diff --git a/ClosedXML/Excel/NamedRanges/XLNamedRanges.cs b/ClosedXML/Excel/NamedRanges/XLNamedRanges.cs index 30d6be8..9a9a2ee 100644 --- a/ClosedXML/Excel/NamedRanges/XLNamedRanges.cs +++ b/ClosedXML/Excel/NamedRanges/XLNamedRanges.cs @@ -68,15 +68,20 @@ if (!match.Success) { - var range = Worksheet?.Range(rangeAddress) ?? Workbook.Range(rangeAddress); - if (range == null) - throw new ArgumentException(string.Format("The range address '{0}' for the named range '{1}' is not a valid range.", rangeAddress, rangeName)); - else + if (XLHelper.IsValidRangeAddress(rangeAddress)) { + var range = Worksheet?.Range(rangeAddress) ?? Workbook.Range(rangeAddress); + + 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) - throw new ArgumentException("For named ranges in the workbook scope, specify the sheet name in the reference."); - else - rangeAddress = Worksheet.Range(rangeAddress).ToString(); + throw new ArgumentException( + "For named ranges in the workbook scope, specify the sheet name in the reference."); + + rangeAddress = Worksheet.Range(rangeAddress).ToString(); } } } diff --git a/ClosedXML_Tests/Excel/NamedRanges/NamedRangesTests.cs b/ClosedXML_Tests/Excel/NamedRanges/NamedRangesTests.cs index f406d08..91779e4 100644 --- a/ClosedXML_Tests/Excel/NamedRanges/NamedRangesTests.cs +++ b/ClosedXML_Tests/Excel/NamedRanges/NamedRangesTests.cs @@ -1,6 +1,7 @@ using ClosedXML.Excel; using NUnit.Framework; using System; +using System.IO; using System.Linq; namespace ClosedXML_Tests.Excel @@ -162,10 +163,92 @@ Assert.AreEqual(3, wsCopy.Cell("C2").Value); Assert.AreEqual(104, wsCopy.Cell("C3").Value); - Assert.AreEqual("Sheet1!A1:A10", wb.NamedRange("wbNamedRange").Ranges.First().RangeAddress.ToStringRelative(true)); - Assert.AreEqual("Copy!A3:A3", wsCopy.NamedRange("wsNamedRange").Ranges.First().RangeAddress.ToStringRelative(true)); - Assert.AreEqual("Sheet2!A4:A4", wsCopy.NamedRange("wsNamedRangeAcrossSheets").Ranges.First().RangeAddress.ToStringRelative(true)); + Assert.AreEqual("Sheet1!A1:A10", + wb.NamedRange("wbNamedRange").Ranges.First().RangeAddress.ToStringRelative(true)); + Assert.AreEqual("Copy!A3:A3", + wsCopy.NamedRange("wsNamedRange").Ranges.First().RangeAddress.ToStringRelative(true)); + Assert.AreEqual("Sheet2!A4:A4", + wsCopy.NamedRange("wsNamedRangeAcrossSheets").Ranges.First().RangeAddress.ToStringRelative(true)); } } + + [Test] + public void NamedRangeMayReferToExpression() + { + using (var ms = new MemoryStream()) + { + using (var wb = new XLWorkbook()) + { + var ws1 = wb.AddWorksheet("Sheet1"); + wb.NamedRanges.Add("TEST", "=0.1"); + wb.NamedRanges.Add("TEST2", "=TEST*2"); + + ws1.Cell(1, 1).FormulaA1 = "TEST"; + ws1.Cell(2, 1).FormulaA1 = "TEST*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); + + wb.SaveAs(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); + } + } + } + + [Test] + public void CanEvaluateNamedMultiRange() + { + using (var wb = new XLWorkbook()) + { + var ws1 = wb.AddWorksheet("Sheet1"); + ws1.Range("A1:C1").Value = 1; + ws1.Range("A3:C3").Value = 3; + wb.NamedRanges.Add("TEST", ws1.Ranges("A1:C1,A3:C3")); + + ws1.Cell(2, 1).FormulaA1 = "=SUM(TEST)"; + + Assert.AreEqual(12.0, (double) ws1.Cell(2, 1).Value, XLHelper.Epsilon); + } + } + + [Test] + public void CanGetNamedFromAnother() + { + var wb = new XLWorkbook(); + var ws1 = wb.Worksheets.Add("Sheet1"); + ws1.Cell("A1").SetValue(1).AddToNamed("value1"); + + Assert.AreEqual(1, wb.Cell("value1").GetValue()); + Assert.AreEqual(1, wb.Range("value1").FirstCell().GetValue()); + + Assert.AreEqual(1, ws1.Cell("value1").GetValue()); + Assert.AreEqual(1, ws1.Range("value1").FirstCell().GetValue()); + + var ws2 = wb.Worksheets.Add("Sheet2"); + + ws2.Cell("A1").SetFormulaA1("=value1").AddToNamed("value2"); + + Assert.AreEqual(1, wb.Cell("value2").GetValue()); + Assert.AreEqual(1, wb.Range("value2").FirstCell().GetValue()); + + Assert.AreEqual(1, ws2.Cell("value1").GetValue()); + Assert.AreEqual(1, ws2.Range("value1").FirstCell().GetValue()); + + Assert.AreEqual(1, ws2.Cell("value2").GetValue()); + Assert.AreEqual(1, ws2.Range("value2").FirstCell().GetValue()); + } } } diff --git a/ClosedXML_Tests/Excel/Ranges/RangeUsedTests.cs b/ClosedXML_Tests/Excel/Ranges/RangeUsedTests.cs deleted file mode 100644 index 3c69051..0000000 --- a/ClosedXML_Tests/Excel/Ranges/RangeUsedTests.cs +++ /dev/null @@ -1,36 +0,0 @@ -using ClosedXML.Excel; -using NUnit.Framework; - -namespace ClosedXML_Tests -{ - [TestFixture] - public class RangeUsedTests - { - [Test] - public void CanGetNamedFromAnother() - { - var wb = new XLWorkbook(); - var ws1 = wb.Worksheets.Add("Sheet1"); - ws1.Cell("A1").SetValue(1).AddToNamed("value1"); - - Assert.AreEqual(1, wb.Cell("value1").GetValue()); - Assert.AreEqual(1, wb.Range("value1").FirstCell().GetValue()); - - Assert.AreEqual(1, ws1.Cell("value1").GetValue()); - Assert.AreEqual(1, ws1.Range("value1").FirstCell().GetValue()); - - var ws2 = wb.Worksheets.Add("Sheet2"); - - ws2.Cell("A1").SetFormulaA1("=value1").AddToNamed("value2"); - - Assert.AreEqual(1, wb.Cell("value2").GetValue()); - Assert.AreEqual(1, wb.Range("value2").FirstCell().GetValue()); - - Assert.AreEqual(1, ws2.Cell("value1").GetValue()); - Assert.AreEqual(1, ws2.Range("value1").FirstCell().GetValue()); - - Assert.AreEqual(1, ws2.Cell("value2").GetValue()); - Assert.AreEqual(1, ws2.Range("value2").FirstCell().GetValue()); - } - } -} \ No newline at end of file