diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/NamedRanges/IXLNamedRange.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/NamedRanges/IXLNamedRange.cs
index 3efba96..90af0b3 100644
--- a/ClosedXML/ClosedXML/ClosedXML/Excel/NamedRanges/IXLNamedRange.cs
+++ b/ClosedXML/ClosedXML/ClosedXML/Excel/NamedRanges/IXLNamedRange.cs
@@ -19,12 +19,6 @@
IXLRanges Ranges { get; }
///
- /// Gets the single range associated with this named range.
- /// An exception will be thrown if there are multiple ranges associated with this named range.
- ///
- IXLRange Range { get; }
-
- ///
/// Gets or sets the comment for this named range.
///
///
@@ -36,6 +30,7 @@
/// Adds the specified range to this named range.
/// Note: A named range can point to multiple ranges.
///
+ /// Workbook containing the range
/// The range address to add.
IXLRanges Add(XLWorkbook workbook, String rangeAddress);
@@ -43,14 +38,14 @@
/// Adds the specified range to this named range.
/// Note: A named range can point to multiple ranges.
///
- /// The range to add.
+ /// The range to add.
IXLRanges Add(IXLRange range);
///
/// Adds the specified ranges to this named range.
/// Note: A named range can point to multiple ranges.
///
- /// The ranges to add.
+ /// The ranges to add.
IXLRanges Add(IXLRanges ranges);
diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/NamedRanges/XLNamedRange.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/NamedRanges/XLNamedRange.cs
index 6b40313..6d6e11c 100644
--- a/ClosedXML/ClosedXML/ClosedXML/Excel/NamedRanges/XLNamedRange.cs
+++ b/ClosedXML/ClosedXML/ClosedXML/Excel/NamedRanges/XLNamedRange.cs
@@ -50,13 +50,7 @@
return ranges;
}
}
- public IXLRange Range
- {
- get
- {
- return Ranges.Single();
- }
- }
+
public String Comment { get; set; }
public IXLRanges Add(XLWorkbook workbook, String rangeAddress)
diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeBase.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeBase.cs
index 35d19b5..686db30 100644
--- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeBase.cs
+++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeBase.cs
@@ -468,7 +468,11 @@
public XLCell Cell(String cellAddressInRange)
{
- return Cell(XLAddress.Create(Worksheet, cellAddressInRange));
+
+ if (ExcelHelper.IsValidA1Address(cellAddressInRange))
+ return Cell(XLAddress.Create(Worksheet, cellAddressInRange));
+
+ return (XLCell)Worksheet.NamedRange(cellAddressInRange).Ranges.First().FirstCell();
}
public XLCell Cell(Int32 row, String column)
diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook.cs
index 4b52bb9..ebc4364 100644
--- a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook.cs
+++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook.cs
@@ -554,6 +554,28 @@
#endregion
- //--
+ public IXLCell Cell(String namedCell)
+ {
+ return NamedRange(namedCell).Ranges.First().FirstCell();
+ }
+
+ public IXLCells Cells(String namedCells)
+ {
+ return Ranges(namedCells).Cells();
+ }
+
+ public IXLRange Range(String namedRange)
+ {
+ return NamedRange(namedRange).Ranges.First();
+ }
+
+ public IXLRanges Ranges(String namedRanges)
+ {
+ var retVal = new XLRanges();
+ var rangePairs = namedRanges.Split(',');
+ foreach (string pair in rangePairs)
+ retVal.Add(Range(pair.Trim()));
+ return retVal;
+ }
}
}
\ No newline at end of file
diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheet.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheet.cs
index b342dae..0383fa7 100644
--- a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheet.cs
+++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheet.cs
@@ -1137,5 +1137,36 @@
RightToLeft = value;
return this;
}
+
+ public new XLCell Cell(String cellAddressInRange)
+ {
+
+ if (ExcelHelper.IsValidA1Address(cellAddressInRange))
+ return Cell(XLAddress.Create(this, cellAddressInRange));
+
+ if (NamedRanges.Any(n=> String.Compare(n.Name, cellAddressInRange, true) == 0))
+ return (XLCell)NamedRange(cellAddressInRange).Ranges.First().FirstCell();
+
+ return (XLCell)Workbook.NamedRanges.First(n =>
+ String.Compare(n.Name, cellAddressInRange, true) == 0
+ && n.Ranges.First().Worksheet == this
+ && n.Ranges.Count == 1)
+ .Ranges.First().FirstCell();
+ }
+
+ public override XLRange Range(String rangeAddressStr)
+ {
+ if (ExcelHelper.IsValidRangeAddress(rangeAddressStr))
+ return Range(new XLRangeAddress(Worksheet, rangeAddressStr));
+
+ if (NamedRanges.Any(n => String.Compare(n.Name, rangeAddressStr, true) == 0))
+ return (XLRange)NamedRange(rangeAddressStr).Ranges.First();
+
+ return (XLRange)Workbook.NamedRanges.First(n =>
+ String.Compare(n.Name, rangeAddressStr, true) == 0
+ && n.Ranges.First().Worksheet == this
+ && n.Ranges.Count == 1)
+ .Ranges.First();
+ }
}
}
\ No newline at end of file
diff --git a/ClosedXML/ClosedXML/ClosedXML/ExcelHelper.cs b/ClosedXML/ClosedXML/ClosedXML/ExcelHelper.cs
index 62bfb7f..afd8d1f 100644
--- a/ClosedXML/ClosedXML/ClosedXML/ExcelHelper.cs
+++ b/ClosedXML/ClosedXML/ClosedXML/ExcelHelper.cs
@@ -96,6 +96,9 @@
public static bool IsValidA1Address(string address)
{
+ if (StringExtensions.IsNullOrWhiteSpace(address))
+ return false;
+
address = address.Replace("$", "");
Int32 rowPos = 0;
Int32 addressLength = address.Length;
@@ -108,6 +111,26 @@
&& IsValidColumn(address.Substring(0, rowPos));
}
+ public static Boolean IsValidRangeAddress(String rangeAddress)
+ {
+ if (StringExtensions.IsNullOrWhiteSpace(rangeAddress))
+ return false;
+
+ string addressToUse = rangeAddress.Contains("!")
+ ? rangeAddress.Substring(rangeAddress.IndexOf("!") + 1)
+ : rangeAddress;
+
+ if (addressToUse.Contains(':'))
+ {
+ var arrRange = addressToUse.Split(':');
+ string firstPart = arrRange[0];
+ string secondPart = arrRange[1];
+ return IsValidA1Address(firstPart) && IsValidA1Address(secondPart);
+ }
+
+ return IsValidA1Address(addressToUse);
+ }
+
public static int GetRowFromAddress1(string cellAddressString)
{
Int32 rowPos = 1;
diff --git a/ClosedXML/ClosedXML/ClosedXML_Examples/Ranges/NamedRanges.cs b/ClosedXML/ClosedXML/ClosedXML_Examples/Ranges/NamedRanges.cs
index 6959ba7..8b035df 100644
--- a/ClosedXML/ClosedXML/ClosedXML_Examples/Ranges/NamedRanges.cs
+++ b/ClosedXML/ClosedXML/ClosedXML_Examples/Ranges/NamedRanges.cs
@@ -41,7 +41,7 @@
// Copy the data in a named range:
wsPresentation.Cell(4, 1).Value = "People Data:";
- wsPresentation.Cell(5, 1).Value = wb.NamedRanges.NamedRange("PeopleData").Range;
+ wsPresentation.Cell(5, 1).Value = wb.Range("PeopleData");
/////////////////////////////////////////////////////////////////////////
diff --git a/ClosedXML/ClosedXML/ClosedXML_Tests/ClosedXML_Tests.csproj b/ClosedXML/ClosedXML/ClosedXML_Tests/ClosedXML_Tests.csproj
index 5c28017..90129eb 100644
--- a/ClosedXML/ClosedXML/ClosedXML_Tests/ClosedXML_Tests.csproj
+++ b/ClosedXML/ClosedXML/ClosedXML_Tests/ClosedXML_Tests.csproj
@@ -65,6 +65,7 @@
+
diff --git a/ClosedXML/ClosedXML/ClosedXML_Tests/Excel/Misc/XLWorkbookTests.cs b/ClosedXML/ClosedXML/ClosedXML_Tests/Excel/Misc/XLWorkbookTests.cs
new file mode 100644
index 0000000..845f1e5
--- /dev/null
+++ b/ClosedXML/ClosedXML/ClosedXML_Tests/Excel/Misc/XLWorkbookTests.cs
@@ -0,0 +1,78 @@
+using System;
+using System.Text;
+using System.Collections.Generic;
+using System.Linq;
+using ClosedXML.Excel;
+using Microsoft.VisualStudio.TestTools.UnitTesting;
+
+namespace ClosedXML_Tests.Excel
+{
+ ///
+ /// Summary description for UnitTest1
+ ///
+ [TestClass]
+ public class XLWorkbookTests
+ {
+
+ [TestMethod]
+ public void WbNamedCell()
+ {
+ var wb = new XLWorkbook();
+ var ws = wb.Worksheets.Add("Sheet1");
+ ws.Cell(1, 1).SetValue("Test").AddToNamed("TestCell");
+ Assert.AreEqual("Test", wb.Cell("TestCell").GetString());
+ Assert.AreEqual("Test", ws.Cell("TestCell").GetString());
+ }
+
+ [TestMethod]
+ public void WbNamedCells()
+ {
+ var wb = new XLWorkbook();
+ var ws = wb.Worksheets.Add("Sheet1");
+ ws.Cell(1, 1).SetValue("Test").AddToNamed("TestCell");
+ ws.Cell(2, 1).SetValue("B").AddToNamed("Test2");
+ var wbCells = wb.Cells("TestCell, Test2");
+ Assert.AreEqual("Test", wbCells.First().GetString());
+ Assert.AreEqual("B", wbCells.Last().GetString());
+
+ var wsCells = ws.Cells("TestCell, Test2");
+ Assert.AreEqual("Test", wsCells.First().GetString());
+ Assert.AreEqual("B", wsCells.Last().GetString());
+
+ }
+
+ [TestMethod]
+ public void WbNamedRange()
+ {
+ var wb = new XLWorkbook();
+ var ws = wb.Worksheets.Add("Sheet1");
+ ws.Cell(1, 1).SetValue("A");
+ ws.Cell(2, 1).SetValue("B");
+ var original = ws.Range("A1:A2");
+ original.AddToNamed("TestRange");
+ Assert.AreEqual(original.RangeAddress.ToStringFixed(), wb.Range("TestRange").RangeAddress.ToString());
+ Assert.AreEqual(original.RangeAddress.ToStringFixed(), ws.Range("TestRange").RangeAddress.ToString());
+ }
+
+ [TestMethod]
+ public void WbNamedRanges()
+ {
+ var wb = new XLWorkbook();
+ var ws = wb.Worksheets.Add("Sheet1");
+ ws.Cell(1, 1).SetValue("A");
+ ws.Cell(2, 1).SetValue("B");
+ ws.Cell(3, 1).SetValue("C").AddToNamed("Test2");
+ var original = ws.Range("A1:A2");
+ original.AddToNamed("TestRange");
+ var wbRanges = wb.Ranges("TestRange, Test2");
+ Assert.AreEqual(original.RangeAddress.ToStringFixed(), wbRanges.First().RangeAddress.ToString());
+ Assert.AreEqual("$A$3:$A$3", wbRanges.Last().RangeAddress.ToStringFixed());
+
+ var wsRanges = wb.Ranges("TestRange, Test2");
+ Assert.AreEqual(original.RangeAddress.ToStringFixed(), wsRanges.First().RangeAddress.ToString());
+ Assert.AreEqual("$A$3:$A$3", wsRanges.Last().RangeAddress.ToStringFixed());
+ }
+
+
+ }
+}
diff --git a/ClosedXML/ClosedXML/ClosedXML_Tests/Excel/Ranges/XLRangeBaseTest.cs b/ClosedXML/ClosedXML/ClosedXML_Tests/Excel/Ranges/XLRangeBaseTest.cs
index e84e5da..1a038e4 100644
--- a/ClosedXML/ClosedXML/ClosedXML_Tests/Excel/Ranges/XLRangeBaseTest.cs
+++ b/ClosedXML/ClosedXML/ClosedXML_Tests/Excel/Ranges/XLRangeBaseTest.cs
@@ -91,7 +91,7 @@
var table = rangeColumn.CreateTable();
wb.NamedRanges.Add("FNameColumn", String.Format("{0}[{1}]", table.Name, "FName"));
- var namedRange = wb.NamedRange( "FNameColumn" ).Range;
+ var namedRange = wb.Range( "FNameColumn" );
Assert.AreEqual(3, namedRange.Cells().Count());
Assert.IsTrue(namedRange.CellsUsed().Select(cell => cell.GetString()).SequenceEqual(new[] { "John", "Hank", "Dagny" }));
}
@@ -103,10 +103,58 @@
var ws = wb.Worksheets.Add("Sheet1");
ws.Cell(1, 1).Value = "Hello World!";
wb.NamedRanges.Add("SingleCell", "Sheet1!$A$1");
- var range = wb.NamedRange( "SingleCell" ).Range;
+ var range = wb.Range( "SingleCell" );
Assert.AreEqual( 1, range.CellsUsed().Count() );
Assert.AreEqual("Hello World!", range.CellsUsed().Single().GetString());
}
+ [TestMethod]
+ public void WsNamedCell()
+ {
+ var wb = new XLWorkbook();
+ var ws = wb.Worksheets.Add("Sheet1");
+ ws.Cell(1, 1).SetValue("Test").AddToNamed("TestCell", XLScope.Worksheet);
+ Assert.AreEqual("Test", ws.Cell("TestCell").GetString());
+ }
+
+ [TestMethod]
+ public void WsNamedCells()
+ {
+ var wb = new XLWorkbook();
+ var ws = wb.Worksheets.Add("Sheet1");
+ ws.Cell(1, 1).SetValue("Test").AddToNamed("TestCell", XLScope.Worksheet);
+ ws.Cell(2, 1).SetValue("B");
+ var cells = ws.Cells("TestCell, A2");
+ Assert.AreEqual("Test", cells.First().GetString());
+ Assert.AreEqual("B", cells.Last().GetString());
+ }
+
+ [TestMethod]
+ public void WsNamedRange()
+ {
+ var wb = new XLWorkbook();
+ var ws = wb.Worksheets.Add("Sheet1");
+ ws.Cell(1, 1).SetValue("A");
+ ws.Cell(2, 1).SetValue("B");
+ var original = ws.Range("A1:A2");
+ original.AddToNamed("TestRange", XLScope.Worksheet);
+ var named = ws.Range("TestRange");
+ Assert.AreEqual(original.RangeAddress.ToStringFixed(), named.RangeAddress.ToString());
+ }
+
+ [TestMethod]
+ public void WsNamedRanges()
+ {
+ var wb = new XLWorkbook();
+ var ws = wb.Worksheets.Add("Sheet1");
+ ws.Cell(1, 1).SetValue("A");
+ ws.Cell(2, 1).SetValue("B");
+ ws.Cell(3, 1).SetValue("C");
+ var original = ws.Range("A1:A2");
+ original.AddToNamed("TestRange", XLScope.Worksheet);
+ var namedRanges = ws.Ranges("TestRange, A3");
+ Assert.AreEqual(original.RangeAddress.ToStringFixed(), namedRanges.First().RangeAddress.ToString());
+ Assert.AreEqual("$A$3:$A$3", namedRanges.Last().RangeAddress.ToStringFixed());
+ }
}
}