diff --git a/ClosedXML/ClosedXML.csproj b/ClosedXML/ClosedXML.csproj
index ef9ef98..3b3364f 100644
--- a/ClosedXML/ClosedXML.csproj
+++ b/ClosedXML/ClosedXML.csproj
@@ -85,6 +85,7 @@
+
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..4a419bd 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,40 @@
{
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)
{
+ 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 +81,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 +101,7 @@
return _namedRanges.Values.GetEnumerator();
}
- #endregion
+ #endregion IEnumerable Members
#region IEnumerable Members
@@ -86,21 +110,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 af014f2..943fab0 100644
--- a/ClosedXML/Excel/XLWorkbook_Load.cs
+++ b/ClosedXML/Excel/XLWorkbook_Load.cs
@@ -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..33acd91 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;
@@ -90,8 +88,10 @@
/// Gets the column letter of a given column number.
///
/// The column number to translate into a column letter.
- public static string GetColumnLetterFromNumber(int columnNumber)
+ 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 +102,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 +120,6 @@
var theColumn = column.ToUpper();
-
var isValid = theColumn[0] >= 'A' && theColumn[0] <= 'Z';
if (length == 1)
return isValid;
@@ -164,7 +173,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 +196,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 +224,6 @@
return rows;
}
-
-
public static bool IsNullOrWhiteSpace(string value)
{
#if NET4
@@ -236,7 +242,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 1d83aad..fcb3bf9 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 3a48083..5e7839d 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 d5ffe20..d72a5c0 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 5bc0db9..39fd9fb 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 27bb627..0f909aa 100644
--- a/ClosedXML_Tests/Resource/Examples/Ranges/NamedRanges.xlsx
+++ b/ClosedXML_Tests/Resource/Examples/Ranges/NamedRanges.xlsx
Binary files differ