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()); + } } }