diff --git a/ClosedXML/ClosedXML.csproj b/ClosedXML/ClosedXML.csproj index 7de9988..ba986aa 100644 --- a/ClosedXML/ClosedXML.csproj +++ b/ClosedXML/ClosedXML.csproj @@ -1,357 +1,358 @@ - - - - Debug - AnyCPU - 8.0.30703 - 2.0 - {BD5E6BFE-E837-4A35-BCA9-39667D873A20} - Library - Properties - ClosedXML - ClosedXML - v4.5.2 - 512 - ..\ - true - - - true - full - false - bin\Debug\ - DEBUG;TRACE - NET4;TRACE;DEBUG - prompt - 4 - 1591 - false - - - pdbonly - true - bin\Release\ - TRACE - NET4;TRACE - prompt - 4 - bin\Release\ClosedXML.xml - 1591 - false - - - true - - - ClosedXML.snk - - - - ..\packages\DocumentFormat.OpenXml.2.7.2\lib\net40\DocumentFormat.OpenXml.dll - True - - - ..\packages\FastMember.Signed.1.1.0\lib\net40\FastMember.Signed.dll - True - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - .editorconfig - - - - Designer - - - - + + + + Debug + AnyCPU + 8.0.30703 + 2.0 + {BD5E6BFE-E837-4A35-BCA9-39667D873A20} + Library + Properties + ClosedXML + ClosedXML + v4.5.2 + 512 + ..\ + true + + + true + full + false + bin\Debug\ + DEBUG;TRACE + NET4;TRACE;DEBUG + prompt + 4 + 1591 + false + + + pdbonly + true + bin\Release\ + TRACE + NET4;TRACE + prompt + 4 + bin\Release\ClosedXML.xml + 1591 + false + + + true + + + ClosedXML.snk + + + + ..\packages\DocumentFormat.OpenXml.2.7.2\lib\net40\DocumentFormat.OpenXml.dll + True + + + ..\packages\FastMember.Signed.1.1.0\lib\net40\FastMember.Signed.dll + True + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + .editorconfig + + + + Designer + + + + \ No newline at end of file diff --git a/ClosedXML/Excel/Cells/XLCell.cs b/ClosedXML/Excel/Cells/XLCell.cs index a45a568..8e756ab 100644 --- a/ClosedXML/Excel/Cells/XLCell.cs +++ b/ClosedXML/Excel/Cells/XLCell.cs @@ -11,6 +11,7 @@ namespace ClosedXML.Excel { using Attributes; + using ClosedXML.Extensions; using FastMember; internal class XLCell : IXLCell, IXLStylized @@ -2104,22 +2105,22 @@ if (row1String[0] == '$') { row1 = "$" + - (Int32.Parse(row1String.Substring(1)) + rowsShifted).ToInvariantString(); + (XLHelper.TrimRowNumber(Int32.Parse(row1String.Substring(1)) + rowsShifted)).ToInvariantString(); } else - row1 = (Int32.Parse(row1String) + rowsShifted).ToInvariantString(); + row1 = (XLHelper.TrimRowNumber(Int32.Parse(row1String) + rowsShifted)).ToInvariantString(); string row2; if (row2String[0] == '$') { row2 = "$" + - (Int32.Parse(row2String.Substring(1)) + rowsShifted).ToInvariantString(); + (XLHelper.TrimRowNumber(Int32.Parse(row2String.Substring(1)) + rowsShifted)).ToInvariantString(); } else - row2 = (Int32.Parse(row2String) + rowsShifted).ToInvariantString(); + row2 = (XLHelper.TrimRowNumber(Int32.Parse(row2String) + rowsShifted)).ToInvariantString(); sb.Append(useSheetName - ? String.Format("'{0}'!{1}:{2}", sheetName, row1, row2) + ? String.Format("{0}!{1}:{2}", sheetName.WrapSheetNameInQuotesIfRequired(), row1, row2) : String.Format("{0}:{1}", row1, row2)); } else if (shiftedRange.RangeAddress.FirstAddress.RowNumber <= @@ -2129,12 +2130,10 @@ { if (useSheetName) { - sb.Append(String.Format("'{0}'!{1}:{2}", - sheetName, + sb.Append(String.Format("{0}!{1}:{2}", + sheetName.WrapSheetNameInQuotesIfRequired(), new XLAddress(worksheetInAction, - matchRange.RangeAddress. - FirstAddress.RowNumber + - rowsShifted, + XLHelper.TrimRowNumber(matchRange.RangeAddress.FirstAddress.RowNumber + rowsShifted), matchRange.RangeAddress. FirstAddress.ColumnLetter, matchRange.RangeAddress. @@ -2142,9 +2141,7 @@ matchRange.RangeAddress. FirstAddress.FixedColumn), new XLAddress(worksheetInAction, - matchRange.RangeAddress. - LastAddress.RowNumber + - rowsShifted, + XLHelper.TrimRowNumber(matchRange.RangeAddress.LastAddress.RowNumber + rowsShifted), matchRange.RangeAddress. LastAddress.ColumnLetter, matchRange.RangeAddress. @@ -2156,9 +2153,7 @@ { sb.Append(String.Format("{0}:{1}", new XLAddress(worksheetInAction, - matchRange.RangeAddress. - FirstAddress.RowNumber + - rowsShifted, + XLHelper.TrimRowNumber(matchRange.RangeAddress.FirstAddress.RowNumber + rowsShifted), matchRange.RangeAddress. FirstAddress.ColumnLetter, matchRange.RangeAddress. @@ -2166,9 +2161,7 @@ matchRange.RangeAddress. FirstAddress.FixedColumn), new XLAddress(worksheetInAction, - matchRange.RangeAddress. - LastAddress.RowNumber + - rowsShifted, + XLHelper.TrimRowNumber(matchRange.RangeAddress.LastAddress.RowNumber + rowsShifted), matchRange.RangeAddress. LastAddress.ColumnLetter, matchRange.RangeAddress. @@ -2181,12 +2174,10 @@ { if (useSheetName) { - sb.Append(String.Format("'{0}'!{1}", - sheetName, + sb.Append(String.Format("{0}!{1}", + sheetName.WrapSheetNameInQuotesIfRequired(), new XLAddress(worksheetInAction, - matchRange.RangeAddress. - FirstAddress.RowNumber + - rowsShifted, + XLHelper.TrimRowNumber(matchRange.RangeAddress.FirstAddress.RowNumber + rowsShifted), matchRange.RangeAddress. FirstAddress.ColumnLetter, matchRange.RangeAddress. @@ -2198,9 +2189,7 @@ { sb.Append(String.Format("{0}", new XLAddress(worksheetInAction, - matchRange.RangeAddress. - FirstAddress.RowNumber + - rowsShifted, + XLHelper.TrimRowNumber(matchRange.RangeAddress.FirstAddress.RowNumber + rowsShifted), matchRange.RangeAddress. FirstAddress.ColumnLetter, matchRange.RangeAddress. @@ -2214,13 +2203,11 @@ { if (useSheetName) { - sb.Append(String.Format("'{0}'!{1}:{2}", - sheetName, + sb.Append(String.Format("{0}!{1}:{2}", + sheetName.WrapSheetNameInQuotesIfRequired(), matchRange.RangeAddress.FirstAddress, new XLAddress(worksheetInAction, - matchRange.RangeAddress. - LastAddress.RowNumber + - rowsShifted, + XLHelper.TrimRowNumber(matchRange.RangeAddress.LastAddress.RowNumber + rowsShifted), matchRange.RangeAddress. LastAddress.ColumnLetter, matchRange.RangeAddress. @@ -2233,9 +2220,7 @@ sb.Append(String.Format("{0}:{1}", matchRange.RangeAddress.FirstAddress, new XLAddress(worksheetInAction, - matchRange.RangeAddress. - LastAddress.RowNumber + - rowsShifted, + XLHelper.TrimRowNumber(matchRange.RangeAddress.LastAddress.RowNumber + rowsShifted), matchRange.RangeAddress. LastAddress.ColumnLetter, matchRange.RangeAddress. @@ -2331,14 +2316,14 @@ column1 = "$" + XLHelper.GetColumnLetterFromNumber( XLHelper.GetColumnNumberFromLetter( - column1String.Substring(1)) + columnsShifted); + column1String.Substring(1)) + columnsShifted, true); } else { column1 = XLHelper.GetColumnLetterFromNumber( XLHelper.GetColumnNumberFromLetter(column1String) + - columnsShifted); + columnsShifted, true); } string column2; @@ -2347,18 +2332,18 @@ column2 = "$" + XLHelper.GetColumnLetterFromNumber( XLHelper.GetColumnNumberFromLetter( - column2String.Substring(1)) + columnsShifted); + column2String.Substring(1)) + columnsShifted, true); } else { column2 = XLHelper.GetColumnLetterFromNumber( XLHelper.GetColumnNumberFromLetter(column2String) + - columnsShifted); + columnsShifted, true); } sb.Append(useSheetName - ? String.Format("'{0}'!{1}:{2}", sheetName, column1, column2) + ? String.Format("{0}!{1}:{2}", sheetName.WrapSheetNameInQuotesIfRequired(), column1, column2) : String.Format("{0}:{1}", column1, column2)); } else if (shiftedRange.RangeAddress.FirstAddress.ColumnNumber <= @@ -2368,14 +2353,12 @@ { if (useSheetName) { - sb.Append(String.Format("'{0}'!{1}:{2}", - sheetName, + sb.Append(String.Format("{0}!{1}:{2}", + sheetName.WrapSheetNameInQuotesIfRequired(), new XLAddress(worksheetInAction, matchRange.RangeAddress. FirstAddress.RowNumber, - matchRange.RangeAddress. - FirstAddress.ColumnNumber + - columnsShifted, + XLHelper.TrimColumnNumber(matchRange.RangeAddress.FirstAddress.ColumnNumber + columnsShifted), matchRange.RangeAddress. FirstAddress.FixedRow, matchRange.RangeAddress. @@ -2383,9 +2366,7 @@ new XLAddress(worksheetInAction, matchRange.RangeAddress. LastAddress.RowNumber, - matchRange.RangeAddress. - LastAddress.ColumnNumber + - columnsShifted, + XLHelper.TrimColumnNumber(matchRange.RangeAddress.LastAddress.ColumnNumber + columnsShifted), matchRange.RangeAddress. LastAddress.FixedRow, matchRange.RangeAddress. @@ -2397,9 +2378,7 @@ new XLAddress(worksheetInAction, matchRange.RangeAddress. FirstAddress.RowNumber, - matchRange.RangeAddress. - FirstAddress.ColumnNumber + - columnsShifted, + XLHelper.TrimColumnNumber(matchRange.RangeAddress.FirstAddress.ColumnNumber + columnsShifted), matchRange.RangeAddress. FirstAddress.FixedRow, matchRange.RangeAddress. @@ -2407,9 +2386,7 @@ new XLAddress(worksheetInAction, matchRange.RangeAddress. LastAddress.RowNumber, - matchRange.RangeAddress. - LastAddress.ColumnNumber + - columnsShifted, + XLHelper.TrimColumnNumber(matchRange.RangeAddress.LastAddress.ColumnNumber + columnsShifted), matchRange.RangeAddress. LastAddress.FixedRow, matchRange.RangeAddress. @@ -2420,14 +2397,12 @@ { if (useSheetName) { - sb.Append(String.Format("'{0}'!{1}", - sheetName, + sb.Append(String.Format("{0}!{1}", + sheetName.WrapSheetNameInQuotesIfRequired(), new XLAddress(worksheetInAction, matchRange.RangeAddress. FirstAddress.RowNumber, - matchRange.RangeAddress. - FirstAddress.ColumnNumber + - columnsShifted, + XLHelper.TrimColumnNumber(matchRange.RangeAddress.FirstAddress.ColumnNumber + columnsShifted), matchRange.RangeAddress. FirstAddress.FixedRow, matchRange.RangeAddress. @@ -2439,9 +2414,7 @@ new XLAddress(worksheetInAction, matchRange.RangeAddress. FirstAddress.RowNumber, - matchRange.RangeAddress. - FirstAddress.ColumnNumber + - columnsShifted, + XLHelper.TrimColumnNumber(matchRange.RangeAddress.FirstAddress.ColumnNumber + columnsShifted), matchRange.RangeAddress. FirstAddress.FixedRow, matchRange.RangeAddress. @@ -2453,15 +2426,13 @@ { if (useSheetName) { - sb.Append(String.Format("'{0}'!{1}:{2}", - sheetName, + sb.Append(String.Format("{0}!{1}:{2}", + sheetName.WrapSheetNameInQuotesIfRequired(), matchRange.RangeAddress.FirstAddress, new XLAddress(worksheetInAction, matchRange.RangeAddress. LastAddress.RowNumber, - matchRange.RangeAddress. - LastAddress.ColumnNumber + - columnsShifted, + XLHelper.TrimColumnNumber(matchRange.RangeAddress.LastAddress.ColumnNumber + columnsShifted), matchRange.RangeAddress. LastAddress.FixedRow, matchRange.RangeAddress. @@ -2474,9 +2445,7 @@ new XLAddress(worksheetInAction, matchRange.RangeAddress. LastAddress.RowNumber, - matchRange.RangeAddress. - LastAddress.ColumnNumber + - columnsShifted, + XLHelper.TrimColumnNumber(matchRange.RangeAddress.LastAddress.ColumnNumber + columnsShifted), matchRange.RangeAddress. LastAddress.FixedRow, matchRange.RangeAddress. diff --git a/ClosedXML/Excel/Coordinates/XLAddress.cs b/ClosedXML/Excel/Coordinates/XLAddress.cs index ee54bf0..b1d7ff8 100644 --- a/ClosedXML/Excel/Coordinates/XLAddress.cs +++ b/ClosedXML/Excel/Coordinates/XLAddress.cs @@ -1,3 +1,4 @@ +using ClosedXML.Extensions; using System; using System.Diagnostics; @@ -346,8 +347,8 @@ public String ToStringRelative(Boolean includeSheet) { if (includeSheet) - return String.Format("'{0}'!{1}", - Worksheet.Name, + return String.Format("{0}!{1}", + Worksheet.Name.WrapSheetNameInQuotesIfRequired(), GetTrimmedAddress()); return GetTrimmedAddress(); @@ -371,8 +372,8 @@ address = String.Format("${0}${1}", ColumnLetter, _rowNumber.ToInvariantString()); if (includeSheet) - return String.Format("'{0}'!{1}", - Worksheet.Name, + return String.Format("{0}!{1}", + Worksheet.Name.WrapSheetNameInQuotesIfRequired(), address); return address; diff --git a/ClosedXML/Excel/Hyperlinks/XLHyperlink_public.cs b/ClosedXML/Excel/Hyperlinks/XLHyperlink_public.cs index 12bd3da..c347d16 100644 --- a/ClosedXML/Excel/Hyperlinks/XLHyperlink_public.cs +++ b/ClosedXML/Excel/Hyperlinks/XLHyperlink_public.cs @@ -1,3 +1,4 @@ +using ClosedXML.Extensions; using System; using System.Linq; @@ -74,11 +75,14 @@ if (_internalAddress.Contains('!')) { return _internalAddress[0] != '\'' - ? String.Format("'{0}'!{1}", _internalAddress.Substring(0, _internalAddress.IndexOf('!')), - _internalAddress.Substring(_internalAddress.IndexOf('!') + 1)) + ? String.Format("{0}!{1}", + _internalAddress + .Substring(0, _internalAddress.IndexOf('!')) + .WrapSheetNameInQuotesIfRequired(), + _internalAddress.Substring(_internalAddress.IndexOf('!') + 1)) : _internalAddress; } - return String.Format("'{0}'!{1}", Worksheet.Name, _internalAddress); + return String.Format("{0}!{1}", Worksheet.Name.WrapSheetNameInQuotesIfRequired(), _internalAddress); } set { @@ -99,4 +103,4 @@ Cell.Style.Font.Underline = Worksheet.Style.Font.Underline; } } -} \ No newline at end of file +} diff --git a/ClosedXML/Excel/NamedRanges/XLNamedRanges.cs b/ClosedXML/Excel/NamedRanges/XLNamedRanges.cs index 504cb03..a7b191f 100644 --- a/ClosedXML/Excel/NamedRanges/XLNamedRanges.cs +++ b/ClosedXML/Excel/NamedRanges/XLNamedRanges.cs @@ -4,11 +4,18 @@ namespace ClosedXML.Excel { - internal class XLNamedRanges: IXLNamedRanges + internal class XLNamedRanges : IXLNamedRanges { - readonly Dictionary _namedRanges = new Dictionary(); + private readonly Dictionary _namedRanges = new Dictionary(); internal XLWorkbook Workbook { get; set; } - + internal XLWorksheet Worksheet { get; set; } + + public XLNamedRanges(XLWorksheet worksheet) + : this(worksheet.Workbook) + { + Worksheet = worksheet; + } + public XLNamedRanges(XLWorkbook workbook) { Workbook = workbook; @@ -29,25 +36,57 @@ { return Add(rangeName, rangeAddress, null); } + public IXLNamedRange Add(String rangeName, IXLRange range) { return Add(rangeName, range, null); } + public IXLNamedRange Add(String rangeName, IXLRanges ranges) { return Add(rangeName, ranges, null); } - public IXLNamedRange Add(String rangeName, String rangeAddress, String comment ) + + public IXLNamedRange Add(String rangeName, String rangeAddress, String comment) { + return Add(rangeName, rangeAddress, comment, false); + } + + /// + /// Adds the specified range name. + /// + /// Name of the range. + /// The range address. + /// The comment. + /// if set to true range address will not be checked for validity. Necessary when loading files as is. + /// + /// For named ranges in the workbook scope, specify the sheet name in the reference. + internal IXLNamedRange Add(String rangeName, String rangeAddress, String comment, bool acceptInvalidReferences) + { + if (!acceptInvalidReferences) + { + var match = XLHelper.NamedRangeReferenceRegex.Match(rangeAddress); + + if (!match.Success) + { + if (Worksheet == null || !XLHelper.NamedRangeReferenceRegex.Match(Worksheet.Range(rangeAddress).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(); + } + } + var namedRange = new XLNamedRange(this, rangeName, rangeAddress, comment); _namedRanges.Add(rangeName, namedRange); return namedRange; } + public IXLNamedRange Add(String rangeName, IXLRange range, String comment) { - var ranges = new XLRanges {range}; + var ranges = new XLRanges { range }; return Add(rangeName, ranges, comment); } + public IXLNamedRange Add(String rangeName, IXLRanges ranges, String comment) { var namedRange = new XLNamedRange(this, rangeName, ranges, comment); @@ -59,16 +98,18 @@ { _namedRanges.Remove(rangeName); } + public void Delete(Int32 rangeIndex) { _namedRanges.Remove(_namedRanges.ElementAt(rangeIndex).Key); } + public void DeleteAll() { _namedRanges.Clear(); } - - #endregion + + #endregion IXLNamedRanges Members #region IEnumerable Members @@ -77,7 +118,7 @@ return _namedRanges.Values.GetEnumerator(); } - #endregion + #endregion IEnumerable Members #region IEnumerable Members @@ -86,21 +127,28 @@ return GetEnumerator(); } - #endregion + #endregion IEnumerable Members public Boolean TryGetValue(String name, out IXLNamedRange range) { if (_namedRanges.TryGetValue(name, out range)) return true; - range = Workbook.NamedRange(name); + if (Worksheet != null) + range = Worksheet.NamedRange(name); + else + range = Workbook.NamedRange(name); + return range != null; } public Boolean Contains(String name) { if (_namedRanges.ContainsKey(name)) return true; - return Workbook.NamedRange(name) != null; - } + if (Worksheet != null) + return Worksheet.NamedRange(name) != null; + else + return Workbook.NamedRange(name) != null; + } } } diff --git a/ClosedXML/Excel/Ranges/XLRangeAddress.cs b/ClosedXML/Excel/Ranges/XLRangeAddress.cs index 3a5a1cd..df631ec 100644 --- a/ClosedXML/Excel/Ranges/XLRangeAddress.cs +++ b/ClosedXML/Excel/Ranges/XLRangeAddress.cs @@ -1,3 +1,4 @@ +using ClosedXML.Extensions; using System; using System.Diagnostics; using System.Globalization; @@ -18,14 +19,14 @@ public XLRangeAddress(XLRangeAddress rangeAddress): this(rangeAddress.FirstAddress, rangeAddress.LastAddress) { - + } public XLRangeAddress(XLAddress firstAddress, XLAddress lastAddress) { Worksheet = firstAddress.Worksheet; FirstAddress = XLAddress.Create(firstAddress); - LastAddress = XLAddress.Create(lastAddress); + LastAddress = XLAddress.Create(lastAddress); } public XLRangeAddress(XLWorksheet worksheet, String rangeAddress) @@ -141,8 +142,8 @@ public String ToStringRelative(Boolean includeSheet) { if (includeSheet) - return String.Format("'{0}'!{1}:{2}", - Worksheet.Name, + return String.Format("{0}!{1}:{2}", + Worksheet.Name.WrapSheetNameInQuotesIfRequired(), _firstAddress.ToStringRelative(), _lastAddress.ToStringRelative()); @@ -157,12 +158,12 @@ public String ToStringFixed(XLReferenceStyle referenceStyle, Boolean includeSheet) { if (includeSheet) - return String.Format("'{0}'!{1}:{2}", - Worksheet.Name, + return String.Format("{0}!{1}:{2}", + Worksheet.Name.WrapSheetNameInQuotesIfRequired(), _firstAddress.ToStringFixed(referenceStyle), _lastAddress.ToStringFixed(referenceStyle)); - return _firstAddress.ToStringFixed(referenceStyle) + ":" + _lastAddress.ToStringFixed(referenceStyle); + return _firstAddress.ToStringFixed(referenceStyle) + ":" + _lastAddress.ToStringFixed(referenceStyle); } public override string ToString() @@ -188,4 +189,4 @@ #endregion } -} \ No newline at end of file +} diff --git a/ClosedXML/Excel/Ranges/XLRangeBase.cs b/ClosedXML/Excel/Ranges/XLRangeBase.cs index c4f8381..52bef35 100644 --- a/ClosedXML/Excel/Ranges/XLRangeBase.cs +++ b/ClosedXML/Excel/Ranges/XLRangeBase.cs @@ -1,4 +1,5 @@ using ClosedXML.Excel.Misc; +using ClosedXML.Extensions; using System; using System.Collections.Generic; using System.Linq; @@ -1538,7 +1539,7 @@ public override string ToString() { - return String.Format("'{0}'!{1}:{2}", Worksheet.Name, RangeAddress.FirstAddress, RangeAddress.LastAddress); + return String.Format("{0}!{1}:{2}", Worksheet.Name.WrapSheetNameInQuotesIfRequired(), RangeAddress.FirstAddress, RangeAddress.LastAddress); } protected void ShiftColumns(IXLRangeAddress thisRangeAddress, XLRange shiftedRange, int columnsShifted) diff --git a/ClosedXML/Excel/XLWorkbook_Load.cs b/ClosedXML/Excel/XLWorkbook_Load.cs index 15de636..fab0c94 100644 --- a/ClosedXML/Excel/XLWorkbook_Load.cs +++ b/ClosedXML/Excel/XLWorkbook_Load.cs @@ -1051,7 +1051,7 @@ if (localSheetId == null) { if (!NamedRanges.Any(nr => nr.Name == name)) - NamedRanges.Add(name, text, comment).Visible = visible; + (NamedRanges as XLNamedRanges).Add(name, text, comment, true).Visible = visible; } else { @@ -1125,8 +1125,16 @@ private static void ParseReference(string item, out string sheetName, out string sheetArea) { var sections = item.Trim().Split('!'); - sheetName = sections[0].Replace("\'", ""); - sheetArea = sections[1]; + if (sections.Count() == 1) + { + sheetName = string.Empty; + sheetArea = item; + } + else + { + sheetName = sections[0].Replace("\'", ""); + sheetArea = sections[1]; + } } private Int32 lastCell; diff --git a/ClosedXML/Excel/XLWorksheet.cs b/ClosedXML/Excel/XLWorksheet.cs index 6579424..c28ef98 100644 --- a/ClosedXML/Excel/XLWorksheet.cs +++ b/ClosedXML/Excel/XLWorksheet.cs @@ -1,6 +1,7 @@ using ClosedXML.Excel.CalcEngine; using ClosedXML.Excel.Drawings; using ClosedXML.Excel.Misc; +using ClosedXML.Extensions; using System; using System.Collections.Generic; using System.Drawing; @@ -43,6 +44,8 @@ { EventTrackingEnabled = workbook.EventTracking == XLEventTracking.Enabled; + Workbook = workbook; + RangeShiftedRows = new XLReentrantEnumerableSet(); RangeShiftedColumns = new XLReentrantEnumerableSet(); @@ -51,7 +54,7 @@ RangeAddress.LastAddress.Worksheet = this; Pictures = new XLPictures(this); - NamedRanges = new XLNamedRanges(workbook); + NamedRanges = new XLNamedRanges(this); SheetView = new XLSheetView(); Tables = new XLTables(); Hyperlinks = new XLHyperlinks(); @@ -60,7 +63,6 @@ Protection = new XLSheetProtection(); AutoFilter = new XLAutoFilter(); ConditionalFormats = new XLConditionalFormats(); - Workbook = workbook; SetStyle(workbook.Style); Internals = new XLWorksheetInternals(new XLCellsCollection(), new XLColumnsCollection(), new XLRowsCollection(), new XLRanges()); @@ -665,7 +667,7 @@ String name = sheetName.ToLower().Equals(Name.ToLower()) ? newSheetName : sheetName; - newValue.Append(String.Format("'{0}'!{1}", name, pair[1])); + newValue.Append(String.Format("{0}!{1}", name.WrapSheetNameInQuotesIfRequired(), pair[1])); } else { diff --git a/ClosedXML/Extensions/StringExtensions.cs b/ClosedXML/Extensions/StringExtensions.cs new file mode 100644 index 0000000..a3b49ec --- /dev/null +++ b/ClosedXML/Extensions/StringExtensions.cs @@ -0,0 +1,19 @@ +using System; +using System.Collections.Generic; +using System.Linq; +using System.Text; +using System.Threading.Tasks; + +namespace ClosedXML.Extensions +{ + internal static class StringExtensions + { + internal static string WrapSheetNameInQuotesIfRequired(this string sheetName) + { + if (sheetName.Contains(' ')) + return "'" + sheetName + "'"; + else + return sheetName; + } + } +} diff --git a/ClosedXML/XLHelper.cs b/ClosedXML/XLHelper.cs index 66d7fa2..585b8ca 100644 --- a/ClosedXML/XLHelper.cs +++ b/ClosedXML/XLHelper.cs @@ -1,13 +1,11 @@ using System; -using System.Collections.Generic; using System.Globalization; -using System.Text; namespace ClosedXML.Excel { + using System.Drawing; using System.Linq; using System.Text.RegularExpressions; - using System.Drawing; /// /// Common methods @@ -21,7 +19,7 @@ public const String MaxColumnLetter = "XFD"; public const Double Epsilon = 1e-10; - private const Int32 TwoT26 = 26*26; + private const Int32 TwoT26 = 26 * 26; internal static readonly Graphics Graphic = Graphics.FromImage(new Bitmap(200, 200)); internal static readonly Double DpiX = Graphic.DpiX; internal static readonly NumberStyles NumberStyle = NumberStyles.AllowDecimalPoint | NumberStyles.AllowLeadingSign | NumberStyles.AllowLeadingWhite | NumberStyles.AllowTrailingWhite | NumberStyles.AllowExponent; @@ -87,11 +85,15 @@ private static readonly string[] letters = new[] { "A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z" }; /// - /// Gets the column letter of a given column number. + /// Gets the column letter of a given column number. /// - /// The column number to translate into a column letter. - public static string GetColumnLetterFromNumber(int columnNumber) + /// The column number to translate into a column letter. + /// if set to true the column letter will be restricted to the allowed range. + /// + public static string GetColumnLetterFromNumber(int columnNumber, bool trimToAllowed = false) { + if (trimToAllowed) columnNumber = TrimColumnNumber(columnNumber); + columnNumber--; // Adjust for start on column 1 if (columnNumber <= 25) { @@ -102,6 +104,16 @@ return GetColumnLetterFromNumber(firstPart) + GetColumnLetterFromNumber(remainder); } + internal static int TrimColumnNumber(int columnNumber) + { + return Math.Max(XLHelper.MinColumnNumber, Math.Min(XLHelper.MaxColumnNumber, columnNumber)); + } + + internal static int TrimRowNumber(int rowNumber) + { + return Math.Max(XLHelper.MinRowNumber, Math.Min(XLHelper.MaxRowNumber, rowNumber)); + } + public static bool IsValidColumn(string column) { var length = column.Length; @@ -110,7 +122,6 @@ var theColumn = column.ToUpper(); - var isValid = theColumn[0] >= 'A' && theColumn[0] <= 'Z'; if (length == 1) return isValid; @@ -164,7 +175,6 @@ public static Boolean IsValidRangeAddress(IXLRangeAddress rangeAddress) { - return !rangeAddress.IsInvalid && rangeAddress.FirstAddress.RowNumber >= 1 && rangeAddress.LastAddress.RowNumber <= MaxRowNumber && rangeAddress.FirstAddress.ColumnNumber >= 1 && rangeAddress.LastAddress.ColumnNumber <= MaxColumnNumber @@ -188,12 +198,12 @@ public static Int32 GetPtFromPx(Double px) { - return Convert.ToInt32(px*72.0/DpiX); + return Convert.ToInt32(px * 72.0 / DpiX); } public static Double GetPxFromPt(Int32 pt) { - return Convert.ToDouble(pt)*DpiX/72.0; + return Convert.ToDouble(pt) * DpiX / 72.0; } internal static IXLTableRows InsertRowsWithoutEvents(Func insertFunc, @@ -216,8 +226,6 @@ return rows; } - - public static bool IsNullOrWhiteSpace(string value) { #if NET4 @@ -236,7 +244,6 @@ } return true; #endif - } private static readonly Regex A1RegexRelative = new Regex( diff --git a/ClosedXML_Tests/Excel/Cells/XLCellTests.cs b/ClosedXML_Tests/Excel/Cells/XLCellTests.cs index f4ed9eb..31e66f9 100644 --- a/ClosedXML_Tests/Excel/Cells/XLCellTests.cs +++ b/ClosedXML_Tests/Excel/Cells/XLCellTests.cs @@ -79,7 +79,7 @@ { IXLWorksheet ws = new XLWorkbook().Worksheets.Add("Sheet1"); IXLRange range = ws.Cell(2, 2).InsertData(new[] { "a", "b", "c" }); - Assert.AreEqual("'Sheet1'!B2:B4", range.ToString()); + Assert.AreEqual("Sheet1!B2:B4", range.ToString()); } [Test] diff --git a/ClosedXML_Tests/Excel/Columns/ColumnTests.cs b/ClosedXML_Tests/Excel/Columns/ColumnTests.cs index 8505bb7..5410863 100644 --- a/ClosedXML_Tests/Excel/Columns/ColumnTests.cs +++ b/ClosedXML_Tests/Excel/Columns/ColumnTests.cs @@ -236,5 +236,6 @@ var lastCoUsed = ws.LastColumnUsed().ColumnNumber(); Assert.AreEqual(2, lastCoUsed); } + } } diff --git a/ClosedXML_Tests/Excel/Coordinates/XLAddressTests.cs b/ClosedXML_Tests/Excel/Coordinates/XLAddressTests.cs index 0c658f8..4d0329f 100644 --- a/ClosedXML_Tests/Excel/Coordinates/XLAddressTests.cs +++ b/ClosedXML_Tests/Excel/Coordinates/XLAddressTests.cs @@ -18,15 +18,38 @@ Assert.AreEqual("A1", address.ToString(XLReferenceStyle.Default)); Assert.AreEqual("A1", address.ToStringRelative()); - Assert.AreEqual("'Sheet1'!A1", address.ToStringRelative(true)); + Assert.AreEqual("Sheet1!A1", address.ToStringRelative(true)); Assert.AreEqual("$A$1", address.ToStringFixed()); Assert.AreEqual("$A$1", address.ToStringFixed(XLReferenceStyle.A1)); Assert.AreEqual("R1C1", address.ToStringFixed(XLReferenceStyle.R1C1)); Assert.AreEqual("$A$1", address.ToStringFixed(XLReferenceStyle.Default)); - Assert.AreEqual("'Sheet1'!$A$1", address.ToStringFixed(XLReferenceStyle.A1, true)); - Assert.AreEqual("'Sheet1'!R1C1", address.ToStringFixed(XLReferenceStyle.R1C1, true)); - Assert.AreEqual("'Sheet1'!$A$1", address.ToStringFixed(XLReferenceStyle.Default, true)); + Assert.AreEqual("Sheet1!$A$1", address.ToStringFixed(XLReferenceStyle.A1, true)); + Assert.AreEqual("Sheet1!R1C1", address.ToStringFixed(XLReferenceStyle.R1C1, true)); + Assert.AreEqual("Sheet1!$A$1", address.ToStringFixed(XLReferenceStyle.Default, true)); + } + + [Test] + public void ToStringTestWithSpace() + { + IXLWorksheet ws = new XLWorkbook().Worksheets.Add("Sheet 1"); + IXLAddress address = ws.Cell(1, 1).Address; + + Assert.AreEqual("A1", address.ToString()); + Assert.AreEqual("A1", address.ToString(XLReferenceStyle.A1)); + Assert.AreEqual("R1C1", address.ToString(XLReferenceStyle.R1C1)); + Assert.AreEqual("A1", address.ToString(XLReferenceStyle.Default)); + + Assert.AreEqual("A1", address.ToStringRelative()); + Assert.AreEqual("'Sheet 1'!A1", address.ToStringRelative(true)); + + Assert.AreEqual("$A$1", address.ToStringFixed()); + Assert.AreEqual("$A$1", address.ToStringFixed(XLReferenceStyle.A1)); + Assert.AreEqual("R1C1", address.ToStringFixed(XLReferenceStyle.R1C1)); + Assert.AreEqual("$A$1", address.ToStringFixed(XLReferenceStyle.Default)); + Assert.AreEqual("'Sheet 1'!$A$1", address.ToStringFixed(XLReferenceStyle.A1, true)); + Assert.AreEqual("'Sheet 1'!R1C1", address.ToStringFixed(XLReferenceStyle.R1C1, true)); + Assert.AreEqual("'Sheet 1'!$A$1", address.ToStringFixed(XLReferenceStyle.Default, true)); } } -} \ No newline at end of file +} diff --git a/ClosedXML_Tests/Excel/NamedRanges/NamedRangesTests.cs b/ClosedXML_Tests/Excel/NamedRanges/NamedRangesTests.cs index da78aab..907bc96 100644 --- a/ClosedXML_Tests/Excel/NamedRanges/NamedRangesTests.cs +++ b/ClosedXML_Tests/Excel/NamedRanges/NamedRangesTests.cs @@ -1,7 +1,7 @@ -using System; -using System.Linq; using ClosedXML.Excel; using NUnit.Framework; +using System; +using System.Linq; namespace ClosedXML_Tests.Excel { @@ -30,12 +30,11 @@ sheet1.Column(1).InsertColumnsBefore(2); sheet1.Column(1).Delete(); - - Assert.AreEqual("'Sheet1'!$C$3,'Sheet1'!$C$4:$D$4,Sheet2!$D$3:$D$4,'Sheet1'!$7:$8,'Sheet1'!$G:$H", + Assert.AreEqual("Sheet1!$C$3,Sheet1!$C$4:$D$4,Sheet2!$D$3:$D$4,Sheet1!$7:$8,Sheet1!$G:$H", wb.NamedRanges.First().RefersTo); - Assert.AreEqual("'Sheet1'!$C$3,'Sheet1'!$C$4:$D$4,Sheet2!$D$3:$D$4,'Sheet1'!$7:$8,'Sheet1'!$G:$H", + Assert.AreEqual("Sheet1!$C$3,Sheet1!$C$4:$D$4,Sheet2!$D$3:$D$4,Sheet1!$7:$8,Sheet1!$G:$H", sheet1.NamedRanges.First().RefersTo); - Assert.AreEqual("'Sheet1'!B2,Sheet2!A1", sheet2.NamedRanges.First().RefersTo); + Assert.AreEqual("Sheet1!B2,Sheet2!A1", sheet2.NamedRanges.First().RefersTo); } [Test] @@ -108,5 +107,38 @@ Assert.IsFalse(result2); Assert.IsNull(range2); } + + [Test] + public void DeleteColumnUsedInNamedRange() + { + using (var wb = new XLWorkbook()) + { + var ws = wb.AddWorksheet("Sheet1"); + ws.FirstCell().SetValue("Column1"); + ws.FirstCell().CellRight().SetValue("Column2").Style.Font.SetBold(); + ws.FirstCell().CellRight(2).SetValue("Column3"); + ws.NamedRanges.Add("MyRange", "A1:C1"); + + ws.Column(1).Delete(); + + Assert.IsTrue(ws.Cell("A1").Style.Font.Bold); + Assert.AreEqual("Column3", ws.Cell("B1").GetValue()); + Assert.IsEmpty(ws.Cell("C1").GetValue()); + } + } + + [Test] + public void TestInvalidNamedRangeOnWorkbookScope() + { + using (var wb = new XLWorkbook()) + { + var ws = wb.AddWorksheet("Sheet1"); + ws.FirstCell().SetValue("Column1"); + ws.FirstCell().CellRight().SetValue("Column2").Style.Font.SetBold(); + ws.FirstCell().CellRight(2).SetValue("Column3"); + + Assert.Throws(() => wb.NamedRanges.Add("MyRange", "A1:C1")); + } + } } -} \ No newline at end of file +} diff --git a/ClosedXML_Tests/Excel/Ranges/XLRangeAddressTests.cs b/ClosedXML_Tests/Excel/Ranges/XLRangeAddressTests.cs index 0d74a60..669e811 100644 --- a/ClosedXML_Tests/Excel/Ranges/XLRangeAddressTests.cs +++ b/ClosedXML_Tests/Excel/Ranges/XLRangeAddressTests.cs @@ -15,15 +15,35 @@ Assert.AreEqual("A1:A1", address.ToString()); Assert.AreEqual("A1:A1", address.ToStringRelative()); - Assert.AreEqual("'Sheet1'!A1:A1", address.ToStringRelative(true)); + Assert.AreEqual("Sheet1!A1:A1", address.ToStringRelative(true)); Assert.AreEqual("$A$1:$A$1", address.ToStringFixed()); Assert.AreEqual("$A$1:$A$1", address.ToStringFixed(XLReferenceStyle.A1)); Assert.AreEqual("R1C1:R1C1", address.ToStringFixed(XLReferenceStyle.R1C1)); Assert.AreEqual("$A$1:$A$1", address.ToStringFixed(XLReferenceStyle.Default)); - Assert.AreEqual("'Sheet1'!$A$1:$A$1", address.ToStringFixed(XLReferenceStyle.A1, true)); - Assert.AreEqual("'Sheet1'!R1C1:R1C1", address.ToStringFixed(XLReferenceStyle.R1C1, true)); - Assert.AreEqual("'Sheet1'!$A$1:$A$1", address.ToStringFixed(XLReferenceStyle.Default, true)); + Assert.AreEqual("Sheet1!$A$1:$A$1", address.ToStringFixed(XLReferenceStyle.A1, true)); + Assert.AreEqual("Sheet1!R1C1:R1C1", address.ToStringFixed(XLReferenceStyle.R1C1, true)); + Assert.AreEqual("Sheet1!$A$1:$A$1", address.ToStringFixed(XLReferenceStyle.Default, true)); + } + + [Test] + public void ToStringTestWithSpace() + { + IXLWorksheet ws = new XLWorkbook().Worksheets.Add("Sheet 1"); + IXLRangeAddress address = ws.Cell(1, 1).AsRange().RangeAddress; + + Assert.AreEqual("A1:A1", address.ToString()); + + Assert.AreEqual("A1:A1", address.ToStringRelative()); + Assert.AreEqual("'Sheet 1'!A1:A1", address.ToStringRelative(true)); + + Assert.AreEqual("$A$1:$A$1", address.ToStringFixed()); + Assert.AreEqual("$A$1:$A$1", address.ToStringFixed(XLReferenceStyle.A1)); + Assert.AreEqual("R1C1:R1C1", address.ToStringFixed(XLReferenceStyle.R1C1)); + Assert.AreEqual("$A$1:$A$1", address.ToStringFixed(XLReferenceStyle.Default)); + Assert.AreEqual("'Sheet 1'!$A$1:$A$1", address.ToStringFixed(XLReferenceStyle.A1, true)); + Assert.AreEqual("'Sheet 1'!R1C1:R1C1", address.ToStringFixed(XLReferenceStyle.R1C1, true)); + Assert.AreEqual("'Sheet 1'!$A$1:$A$1", address.ToStringFixed(XLReferenceStyle.Default, true)); } } -} \ No newline at end of file +} diff --git a/ClosedXML_Tests/Resource/Examples/Misc/Collections.xlsx b/ClosedXML_Tests/Resource/Examples/Misc/Collections.xlsx index 9da08d6..d791985 100644 --- a/ClosedXML_Tests/Resource/Examples/Misc/Collections.xlsx +++ b/ClosedXML_Tests/Resource/Examples/Misc/Collections.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/Misc/Formulas.xlsx b/ClosedXML_Tests/Resource/Examples/Misc/Formulas.xlsx index e478c28..c2ad2c1 100644 --- a/ClosedXML_Tests/Resource/Examples/Misc/Formulas.xlsx +++ b/ClosedXML_Tests/Resource/Examples/Misc/Formulas.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/Misc/Hyperlinks.xlsx b/ClosedXML_Tests/Resource/Examples/Misc/Hyperlinks.xlsx index 67ea273..222ba7b 100644 --- a/ClosedXML_Tests/Resource/Examples/Misc/Hyperlinks.xlsx +++ b/ClosedXML_Tests/Resource/Examples/Misc/Hyperlinks.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/PivotTables/PivotTables.xlsx b/ClosedXML_Tests/Resource/Examples/PivotTables/PivotTables.xlsx index c582a15..6e3156d 100644 --- a/ClosedXML_Tests/Resource/Examples/PivotTables/PivotTables.xlsx +++ b/ClosedXML_Tests/Resource/Examples/PivotTables/PivotTables.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/Ranges/NamedRanges.xlsx b/ClosedXML_Tests/Resource/Examples/Ranges/NamedRanges.xlsx index c1109bf..8d4e6a2 100644 --- a/ClosedXML_Tests/Resource/Examples/Ranges/NamedRanges.xlsx +++ b/ClosedXML_Tests/Resource/Examples/Ranges/NamedRanges.xlsx Binary files differ diff --git a/ECMA-376, Third Edition, Part 1 - Fundamentals And Markup Language Reference.zip b/ECMA-376, Third Edition, Part 1 - Fundamentals And Markup Language Reference.zip new file mode 100644 index 0000000..32c8dee --- /dev/null +++ b/ECMA-376, Third Edition, Part 1 - Fundamentals And Markup Language Reference.zip Binary files differ diff --git a/Open XML Explained.pdf b/Open XML Explained.pdf new file mode 100644 index 0000000..dce5eb5 --- /dev/null +++ b/Open XML Explained.pdf Binary files differ