diff --git a/ClosedXML/ClosedXML/ClosedXML/ClosedXML.csproj b/ClosedXML/ClosedXML/ClosedXML/ClosedXML.csproj index 57f1bd9..920f8e9 100644 --- a/ClosedXML/ClosedXML/ClosedXML/ClosedXML.csproj +++ b/ClosedXML/ClosedXML/ClosedXML/ClosedXML.csproj @@ -16,7 +16,7 @@ SAK SAK SAK - + Client true diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/NamedRanges/XLNamedRange.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/NamedRanges/XLNamedRange.cs index 1480fb2..6b40313 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/NamedRanges/XLNamedRange.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/NamedRanges/XLNamedRange.cs @@ -4,8 +4,15 @@ namespace ClosedXML.Excel { + using System.Text.RegularExpressions; + internal class XLNamedRange: IXLNamedRange { + private static readonly Regex _namedRangeReferenceRegex = + new Regex( + @"^('?(?[^'!]+)'?!(?.+))|((?[^\[]+)\[(?[^\]]+)\])$", + RegexOptions.Compiled | RegexOptions.CultureInvariant | RegexOptions.ExplicitCapture + ); private readonly List _rangeList = new List(); private readonly XLNamedRanges _namedRanges; public XLNamedRange(XLNamedRanges namedRanges , String rangeName, String range, String comment = null) @@ -30,8 +37,14 @@ get { var ranges = new XLRanges(); - foreach (var rangeToAdd in from rangeAddress in _rangeList select rangeAddress.Split('!') into byExclamation let wsName = byExclamation[0].Replace("'", "") let rng = byExclamation[1] select _namedRanges.Workbook.WorksheetsInternal.Worksheet(wsName).Range(rng)) - { + foreach (var rangeToAdd in + from rangeAddress in _rangeList + let match = _namedRangeReferenceRegex.Match(rangeAddress) + select + match.Groups["Sheet"].Success + ? _namedRanges.Workbook.WorksheetsInternal.Worksheet(match.Groups["Sheet"].Value).Range(match.Groups["Range"].Value) as IXLRangeBase + : _namedRanges.Workbook.Worksheets.SelectMany(sheet => sheet.Tables).Where(table => table.Name == match.Groups["Table"].Value).Single().Column(match.Groups["Column"].Value) ) + { ranges.Add(rangeToAdd); } return ranges; diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeColumn.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeColumn.cs index 1c3f687..7951260 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeColumn.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeColumn.cs @@ -98,7 +98,11 @@ IXLRangeColumn ColumnRight(Int32 step); IXLColumn WorksheetColumn(); - + + IXLTable AsTable(); + IXLTable AsTable(String name); + IXLTable CreateTable(); + IXLTable CreateTable(String name); } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeColumn.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeColumn.cs index c0ffac6..482daba 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeColumn.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeColumn.cs @@ -330,5 +330,26 @@ } #endregion + + + public IXLTable AsTable() + { + return AsRange().AsTable(); + } + + public IXLTable AsTable(string name) + { + return AsRange().AsTable(name); + } + + public IXLTable CreateTable() + { + return AsRange().CreateTable(); + } + + public IXLTable CreateTable(string name) + { + return AsRange().CreateTable(name); + } } } \ No newline at end of file diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Load.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Load.cs index 5228b17..4cf5c14 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Load.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Load.cs @@ -134,69 +134,71 @@ var styleList = new Dictionary();// {{0, ws.Style}}; - var reader = OpenXmlReader.Create(wsPart); - while (reader.Read()) + using (var reader = OpenXmlReader.Create(wsPart)) { -#pragma warning disable 252,253 - if (reader.ElementType == typeof (SheetFormatProperties)) + while (reader.Read()) { - var sheetFormatProperties = (SheetFormatProperties) reader.LoadCurrentElement(); - if (sheetFormatProperties != null) + if (reader.ElementType == typeof(SheetFormatProperties)) { - if (sheetFormatProperties.DefaultRowHeight != null) - ws.RowHeight = sheetFormatProperties.DefaultRowHeight; - - ws.RowHeightChanged = (sheetFormatProperties.CustomHeight != null && - sheetFormatProperties.CustomHeight.Value); - - if (sheetFormatProperties.DefaultColumnWidth != null) + var sheetFormatProperties = (SheetFormatProperties)reader.LoadCurrentElement(); + if (sheetFormatProperties != null) { - ws.ColumnWidth = sheetFormatProperties.DefaultColumnWidth; + if (sheetFormatProperties.DefaultRowHeight != null) + ws.RowHeight = sheetFormatProperties.DefaultRowHeight; + + ws.RowHeightChanged = (sheetFormatProperties.CustomHeight != null && + sheetFormatProperties.CustomHeight.Value); + + if (sheetFormatProperties.DefaultColumnWidth != null) + { + ws.ColumnWidth = sheetFormatProperties.DefaultColumnWidth; + } } } - } - else if (reader.ElementType == typeof (SheetViews)) - LoadSheetViews((SheetViews) reader.LoadCurrentElement(), ws); - else if (reader.ElementType == typeof (MergeCells)) - { - var mergedCells = (MergeCells) reader.LoadCurrentElement(); - if (mergedCells != null) + else if (reader.ElementType == typeof(SheetViews)) + LoadSheetViews((SheetViews)reader.LoadCurrentElement(), ws); + else if (reader.ElementType == typeof(MergeCells)) { - foreach (MergeCell mergeCell in mergedCells.Elements()) - ws.Range(mergeCell.Reference).Merge(); + var mergedCells = (MergeCells)reader.LoadCurrentElement(); + if (mergedCells != null) + { + foreach (MergeCell mergeCell in mergedCells.Elements()) + ws.Range(mergeCell.Reference).Merge(); + } } + else if (reader.ElementType == typeof(Columns)) + LoadColumns(s, numberingFormats, fills, borders, fonts, ws, + (Columns)reader.LoadCurrentElement()); + else if (reader.ElementType == typeof(Row)) + { + LoadRows(s, numberingFormats, fills, borders, fonts, ws, sharedStrings, sharedFormulasR1C1, + styleList, (Row)reader.LoadCurrentElement()); + } + else if (reader.ElementType == typeof(AutoFilter)) + LoadAutoFilter((AutoFilter)reader.LoadCurrentElement(), ws); + else if (reader.ElementType == typeof(SheetProtection)) + LoadSheetProtection((SheetProtection)reader.LoadCurrentElement(), ws); + else if (reader.ElementType == typeof(DataValidations)) + LoadDataValidations((DataValidations)reader.LoadCurrentElement(), ws); + else if (reader.ElementType == typeof(Hyperlinks)) + LoadHyperlinks((Hyperlinks)reader.LoadCurrentElement(), wsPart, ws); + else if (reader.ElementType == typeof(PrintOptions)) + LoadPrintOptions((PrintOptions)reader.LoadCurrentElement(), ws); + else if (reader.ElementType == typeof(PageMargins)) + LoadPageMargins((PageMargins)reader.LoadCurrentElement(), ws); + else if (reader.ElementType == typeof(PageSetup)) + LoadPageSetup((PageSetup)reader.LoadCurrentElement(), ws); + else if (reader.ElementType == typeof(HeaderFooter)) + LoadHeaderFooter((HeaderFooter)reader.LoadCurrentElement(), ws); + else if (reader.ElementType == typeof(SheetProperties)) + LoadSheetProperties((SheetProperties)reader.LoadCurrentElement(), ws); + else if (reader.ElementType == typeof(RowBreaks)) + LoadRowBreaks((RowBreaks)reader.LoadCurrentElement(), ws); + else if (reader.ElementType == typeof(ColumnBreaks)) + LoadColumnBreaks((ColumnBreaks)reader.LoadCurrentElement(), ws); + } - else if (reader.ElementType == typeof (Columns)) - LoadColumns(s, numberingFormats, fills, borders, fonts, ws, - (Columns) reader.LoadCurrentElement()); - else if (reader.ElementType == typeof (Row)) - { - LoadRows(s, numberingFormats, fills, borders, fonts, ws, sharedStrings, sharedFormulasR1C1, - styleList, (Row) reader.LoadCurrentElement()); - } - else if (reader.ElementType == typeof (AutoFilter)) - LoadAutoFilter((AutoFilter) reader.LoadCurrentElement(), ws); - else if (reader.ElementType == typeof (SheetProtection)) - LoadSheetProtection((SheetProtection) reader.LoadCurrentElement(), ws); - else if (reader.ElementType == typeof (DataValidations)) - LoadDataValidations((DataValidations) reader.LoadCurrentElement(), ws); - else if (reader.ElementType == typeof (Hyperlinks)) - LoadHyperlinks((Hyperlinks) reader.LoadCurrentElement(), wsPart, ws); - else if (reader.ElementType == typeof (PrintOptions)) - LoadPrintOptions((PrintOptions) reader.LoadCurrentElement(), ws); - else if (reader.ElementType == typeof (PageMargins)) - LoadPageMargins((PageMargins) reader.LoadCurrentElement(), ws); - else if (reader.ElementType == typeof (PageSetup)) - LoadPageSetup((PageSetup) reader.LoadCurrentElement(), ws); - else if (reader.ElementType == typeof (HeaderFooter)) - LoadHeaderFooter((HeaderFooter) reader.LoadCurrentElement(), ws); - else if (reader.ElementType == typeof (SheetProperties)) - LoadSheetProperties((SheetProperties) reader.LoadCurrentElement(), ws); - else if (reader.ElementType == typeof (RowBreaks)) - LoadRowBreaks((RowBreaks) reader.LoadCurrentElement(), ws); - else if (reader.ElementType == typeof (ColumnBreaks)) - LoadColumnBreaks((ColumnBreaks) reader.LoadCurrentElement(), ws); -#pragma warning restore 252,253 + reader.Close(); } #region LoadTables diff --git a/ClosedXML/ClosedXML/ClosedXML_Net3.5/ClosedXML_Net3.5.csproj b/ClosedXML/ClosedXML/ClosedXML_Net3.5/ClosedXML_Net3.5.csproj index 0722c8e..6e9eadb 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Net3.5/ClosedXML_Net3.5.csproj +++ b/ClosedXML/ClosedXML/ClosedXML_Net3.5/ClosedXML_Net3.5.csproj @@ -16,7 +16,7 @@ SAK SAK SAK - + Client true diff --git a/ClosedXML/ClosedXML/ClosedXML_Tests/Excel/Ranges/XLRangeBaseTest.cs b/ClosedXML/ClosedXML/ClosedXML_Tests/Excel/Ranges/XLRangeBaseTest.cs index 403cc73..e84e5da 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Tests/Excel/Ranges/XLRangeBaseTest.cs +++ b/ClosedXML/ClosedXML/ClosedXML_Tests/Excel/Ranges/XLRangeBaseTest.cs @@ -78,5 +78,35 @@ Assert.AreEqual(expected, actual); } + [TestMethod] + public void TableRange() + { + var wb = new XLWorkbook(); + var ws = wb.Worksheets.Add("Sheet1"); + var rangeColumn = ws.Column(1).Column(1, 4); + rangeColumn.Cell(1).Value = "FName"; + rangeColumn.Cell(2).Value = "John"; + rangeColumn.Cell(3).Value = "Hank"; + rangeColumn.Cell(4).Value = "Dagny"; + var table = rangeColumn.CreateTable(); + wb.NamedRanges.Add("FNameColumn", String.Format("{0}[{1}]", table.Name, "FName")); + + var namedRange = wb.NamedRange( "FNameColumn" ).Range; + Assert.AreEqual(3, namedRange.Cells().Count()); + Assert.IsTrue(namedRange.CellsUsed().Select(cell => cell.GetString()).SequenceEqual(new[] { "John", "Hank", "Dagny" })); + } + + [TestMethod] + public void SingleCell() + { + var wb = new XLWorkbook(); + 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; + Assert.AreEqual( 1, range.CellsUsed().Count() ); + Assert.AreEqual("Hello World!", range.CellsUsed().Single().GetString()); + } + } }