diff --git a/ClosedXML/Excel/Cells/XLCell.cs b/ClosedXML/Excel/Cells/XLCell.cs
index 7cdb089..3282441 100644
--- a/ClosedXML/Excel/Cells/XLCell.cs
+++ b/ClosedXML/Excel/Cells/XLCell.cs
@@ -2318,7 +2318,7 @@
row2 = (XLHelper.TrimRowNumber(Int32.Parse(row2String) + rowsShifted)).ToInvariantString();
sb.Append(useSheetName
- ? String.Format("{0}!{1}:{2}", sheetName.WrapSheetNameInQuotesIfRequired(), row1, row2)
+ ? String.Format("{0}!{1}:{2}", sheetName.EscapeSheetName(), row1, row2)
: String.Format("{0}:{1}", row1, row2));
}
else if (shiftedRange.RangeAddress.FirstAddress.RowNumber <=
@@ -2329,7 +2329,7 @@
if (useSheetName)
{
sb.Append(String.Format("{0}!{1}:{2}",
- sheetName.WrapSheetNameInQuotesIfRequired(),
+ sheetName.EscapeSheetName(),
new XLAddress(worksheetInAction,
XLHelper.TrimRowNumber(matchRange.RangeAddress.FirstAddress.RowNumber + rowsShifted),
matchRange.RangeAddress.
@@ -2373,7 +2373,7 @@
if (useSheetName)
{
sb.Append(String.Format("{0}!{1}",
- sheetName.WrapSheetNameInQuotesIfRequired(),
+ sheetName.EscapeSheetName(),
new XLAddress(worksheetInAction,
XLHelper.TrimRowNumber(matchRange.RangeAddress.FirstAddress.RowNumber + rowsShifted),
matchRange.RangeAddress.
@@ -2402,7 +2402,7 @@
if (useSheetName)
{
sb.Append(String.Format("{0}!{1}:{2}",
- sheetName.WrapSheetNameInQuotesIfRequired(),
+ sheetName.EscapeSheetName(),
matchRange.RangeAddress.FirstAddress,
new XLAddress(worksheetInAction,
XLHelper.TrimRowNumber(matchRange.RangeAddress.LastAddress.RowNumber + rowsShifted),
@@ -2543,7 +2543,7 @@
}
sb.Append(useSheetName
- ? String.Format("{0}!{1}:{2}", sheetName.WrapSheetNameInQuotesIfRequired(), column1, column2)
+ ? String.Format("{0}!{1}:{2}", sheetName.EscapeSheetName(), column1, column2)
: String.Format("{0}:{1}", column1, column2));
}
else if (shiftedRange.RangeAddress.FirstAddress.ColumnNumber <=
@@ -2554,7 +2554,7 @@
if (useSheetName)
{
sb.Append(String.Format("{0}!{1}:{2}",
- sheetName.WrapSheetNameInQuotesIfRequired(),
+ sheetName.EscapeSheetName(),
new XLAddress(worksheetInAction,
matchRange.RangeAddress.
FirstAddress.RowNumber,
@@ -2598,7 +2598,7 @@
if (useSheetName)
{
sb.Append(String.Format("{0}!{1}",
- sheetName.WrapSheetNameInQuotesIfRequired(),
+ sheetName.EscapeSheetName(),
new XLAddress(worksheetInAction,
matchRange.RangeAddress.
FirstAddress.RowNumber,
@@ -2627,7 +2627,7 @@
if (useSheetName)
{
sb.Append(String.Format("{0}!{1}:{2}",
- sheetName.WrapSheetNameInQuotesIfRequired(),
+ sheetName.EscapeSheetName(),
matchRange.RangeAddress.FirstAddress,
new XLAddress(worksheetInAction,
matchRange.RangeAddress.
diff --git a/ClosedXML/Excel/Coordinates/XLAddress.cs b/ClosedXML/Excel/Coordinates/XLAddress.cs
index 81f4977..56f3922 100644
--- a/ClosedXML/Excel/Coordinates/XLAddress.cs
+++ b/ClosedXML/Excel/Coordinates/XLAddress.cs
@@ -252,7 +252,7 @@
if (includeSheet)
return String.Format("{0}!{1}",
- Worksheet.Name.WrapSheetNameInQuotesIfRequired(),
+ Worksheet.Name.EscapeSheetName(),
address);
return address;
@@ -389,7 +389,7 @@
{
if (includeSheet)
return String.Format("{0}!{1}",
- Worksheet.Name.WrapSheetNameInQuotesIfRequired(),
+ Worksheet.Name.EscapeSheetName(),
GetTrimmedAddress());
return GetTrimmedAddress();
@@ -414,7 +414,7 @@
if (includeSheet)
return String.Format("{0}!{1}",
- Worksheet.Name.WrapSheetNameInQuotesIfRequired(),
+ Worksheet.Name.EscapeSheetName(),
address);
return address;
diff --git a/ClosedXML/Excel/Hyperlinks/XLHyperlink_public.cs b/ClosedXML/Excel/Hyperlinks/XLHyperlink_public.cs
index c347d16..f60e516 100644
--- a/ClosedXML/Excel/Hyperlinks/XLHyperlink_public.cs
+++ b/ClosedXML/Excel/Hyperlinks/XLHyperlink_public.cs
@@ -78,11 +78,11 @@
? String.Format("{0}!{1}",
_internalAddress
.Substring(0, _internalAddress.IndexOf('!'))
- .WrapSheetNameInQuotesIfRequired(),
+ .EscapeSheetName(),
_internalAddress.Substring(_internalAddress.IndexOf('!') + 1))
: _internalAddress;
}
- return String.Format("{0}!{1}", Worksheet.Name.WrapSheetNameInQuotesIfRequired(), _internalAddress);
+ return String.Format("{0}!{1}", Worksheet.Name.EscapeSheetName(), _internalAddress);
}
set
{
diff --git a/ClosedXML/Excel/Ranges/XLRangeAddress.cs b/ClosedXML/Excel/Ranges/XLRangeAddress.cs
index 13f986a..b1d20c4 100644
--- a/ClosedXML/Excel/Ranges/XLRangeAddress.cs
+++ b/ClosedXML/Excel/Ranges/XLRangeAddress.cs
@@ -143,7 +143,7 @@
{
if (includeSheet)
return String.Format("{0}!{1}:{2}",
- Worksheet.Name.WrapSheetNameInQuotesIfRequired(),
+ Worksheet.Name.EscapeSheetName(),
_firstAddress.ToStringRelative(),
_lastAddress.ToStringRelative());
@@ -159,7 +159,7 @@
{
if (includeSheet)
return String.Format("{0}!{1}:{2}",
- Worksheet.Name.WrapSheetNameInQuotesIfRequired(),
+ Worksheet.Name.EscapeSheetName(),
_firstAddress.ToStringFixed(referenceStyle),
_lastAddress.ToStringFixed(referenceStyle));
diff --git a/ClosedXML/Excel/Ranges/XLRangeBase.cs b/ClosedXML/Excel/Ranges/XLRangeBase.cs
index 1994897..ea18eeb 100644
--- a/ClosedXML/Excel/Ranges/XLRangeBase.cs
+++ b/ClosedXML/Excel/Ranges/XLRangeBase.cs
@@ -1597,7 +1597,7 @@
public override string ToString()
{
- return String.Format("{0}!{1}:{2}", Worksheet.Name.WrapSheetNameInQuotesIfRequired(), RangeAddress.FirstAddress, RangeAddress.LastAddress);
+ return String.Format("{0}!{1}:{2}", Worksheet.Name.EscapeSheetName(), 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 821c8d1..f5de092 100644
--- a/ClosedXML/Excel/XLWorkbook_Load.cs
+++ b/ClosedXML/Excel/XLWorkbook_Load.cs
@@ -1324,7 +1324,7 @@
}
else
{
- sheetName = sections[0].Replace("\'", "");
+ sheetName = sections[0].UnescapeSheetName();
sheetArea = sections[1];
}
}
diff --git a/ClosedXML/Excel/XLWorkbook_Save.cs b/ClosedXML/Excel/XLWorkbook_Save.cs
index 37d38ba..8f47b30 100644
--- a/ClosedXML/Excel/XLWorkbook_Save.cs
+++ b/ClosedXML/Excel/XLWorkbook_Save.cs
@@ -731,7 +731,7 @@
var definedNameText = worksheet.PageSetup.PrintAreas.Aggregate(String.Empty,
(current, printArea) =>
current +
- ("'" + worksheetName + "'!" +
+ (worksheetName.EscapeSheetName() + "!" +
printArea.RangeAddress.
FirstAddress.ToStringFixed(
XLReferenceStyle.A1) +
@@ -750,7 +750,7 @@
{
Name = "_xlnm._FilterDatabase",
LocalSheetId = sheetId,
- Text = "'" + worksheet.Name + "'!" +
+ Text = worksheet.Name.EscapeSheetName() + "!" +
worksheet.AutoFilter.Range.RangeAddress.FirstAddress.ToStringFixed(
XLReferenceStyle.A1) +
":" +
@@ -782,14 +782,14 @@
var definedNameTextColumn = String.Empty;
if (worksheet.PageSetup.FirstRowToRepeatAtTop > 0)
{
- definedNameTextRow = "'" + worksheet.Name + "'!" + worksheet.PageSetup.FirstRowToRepeatAtTop
+ definedNameTextRow = worksheet.Name.EscapeSheetName() + "!" + worksheet.PageSetup.FirstRowToRepeatAtTop
+ ":" + worksheet.PageSetup.LastRowToRepeatAtTop;
}
if (worksheet.PageSetup.FirstColumnToRepeatAtLeft > 0)
{
var minColumn = worksheet.PageSetup.FirstColumnToRepeatAtLeft;
var maxColumn = worksheet.PageSetup.LastColumnToRepeatAtLeft;
- definedNameTextColumn = "'" + worksheet.Name + "'!" +
+ definedNameTextColumn = worksheet.Name.EscapeSheetName() + "!" +
XLHelper.GetColumnLetterFromNumber(minColumn)
+ ":" + XLHelper.GetColumnLetterFromNumber(maxColumn);
}
diff --git a/ClosedXML/Excel/XLWorksheet.cs b/ClosedXML/Excel/XLWorksheet.cs
index f5e3a0d..be9cc23 100644
--- a/ClosedXML/Excel/XLWorksheet.cs
+++ b/ClosedXML/Excel/XLWorksheet.cs
@@ -182,6 +182,12 @@
if (value.Length > 31)
throw new ArgumentException("Worksheet names cannot be more than 31 characters");
+ if (value.StartsWith("'", StringComparison.Ordinal))
+ throw new ArgumentException("Worksheet names cannot start with an apostrophe");
+
+ if (value.EndsWith("'", StringComparison.Ordinal))
+ throw new ArgumentException("Worksheet names cannot end with an apostrophe");
+
Workbook.WorksheetsInternal.Rename(_name, value);
_name = value;
}
@@ -677,7 +683,7 @@
String name = sheetName.ToLower().Equals(Name.ToLower())
? newSheetName
: sheetName;
- newValue.Append(String.Format("{0}!{1}", name.WrapSheetNameInQuotesIfRequired(), pair[1]));
+ newValue.Append(String.Format("{0}!{1}", name.EscapeSheetName(), pair[1]));
}
else
{
diff --git a/ClosedXML/Excel/XLWorksheets.cs b/ClosedXML/Excel/XLWorksheets.cs
index af69de6..cfb0478 100644
--- a/ClosedXML/Excel/XLWorksheets.cs
+++ b/ClosedXML/Excel/XLWorksheets.cs
@@ -1,3 +1,4 @@
+using ClosedXML.Extensions;
using System;
using System.Collections;
using System.Collections.Generic;
@@ -47,7 +48,7 @@
public bool TryGetWorksheet(string sheetName, out IXLWorksheet worksheet)
{
XLWorksheet w;
- if (_worksheets.TryGetValue(TrimSheetName(sheetName).ToLowerInvariant(), out w))
+ if (_worksheets.TryGetValue(sheetName.UnescapeSheetName().ToLowerInvariant(), out w))
{
worksheet = w;
return true;
@@ -56,17 +57,9 @@
return false;
}
- internal static string TrimSheetName(string sheetName)
- {
- if (sheetName.StartsWith("'") && sheetName.EndsWith("'") && sheetName.Length > 2)
- sheetName = sheetName.Substring(1, sheetName.Length - 2);
-
- return sheetName;
- }
-
public IXLWorksheet Worksheet(String sheetName)
{
- sheetName = TrimSheetName(sheetName);
+ sheetName = sheetName.UnescapeSheetName();
XLWorksheet w;
diff --git a/ClosedXML/Extensions/StringExtensions.cs b/ClosedXML/Extensions/StringExtensions.cs
index facf0a5..e4a5009 100644
--- a/ClosedXML/Extensions/StringExtensions.cs
+++ b/ClosedXML/Extensions/StringExtensions.cs
@@ -8,12 +8,20 @@
{
internal static class StringExtensions
{
- internal static String WrapSheetNameInQuotesIfRequired(this String sheetName)
+ internal static string EscapeSheetName(this String sheetName)
{
- if (sheetName.Contains(' '))
- return "'" + sheetName + "'";
- else
- return sheetName;
+ if (sheetName.Contains("'") ||
+ sheetName.Contains(" "))
+ return string.Format("'{0}'", sheetName.Replace("'", "''"));
+
+ return sheetName;
+ }
+
+ internal static string UnescapeSheetName(this String sheetName)
+ {
+ return sheetName
+ .Trim('\'')
+ .Replace("''", "'");
}
internal static String HashPassword(this String password)
diff --git a/ClosedXML_Tests/ClosedXML_Tests.csproj b/ClosedXML_Tests/ClosedXML_Tests.csproj
index a47cead..a1c5279 100644
--- a/ClosedXML_Tests/ClosedXML_Tests.csproj
+++ b/ClosedXML_Tests/ClosedXML_Tests.csproj
@@ -304,6 +304,7 @@
+
diff --git a/ClosedXML_Tests/Excel/Loading/LoadingTests.cs b/ClosedXML_Tests/Excel/Loading/LoadingTests.cs
index f218890..d62b9f2 100644
--- a/ClosedXML_Tests/Excel/Loading/LoadingTests.cs
+++ b/ClosedXML_Tests/Excel/Loading/LoadingTests.cs
@@ -268,6 +268,27 @@
}
}
+ ///
+ /// Excel escapes symbol ' in worksheet title so we have to process this correctly.
+ ///
+ [Test]
+ public void CanOpenWorksheetWithEscapedApostrophe()
+ {
+ string title = "";
+ TestDelegate openWorkbook = () =>
+ {
+ using (var stream = TestHelper.GetStreamFromResource(TestHelper.GetResourcePath(@"Misc\EscapedApostrophe.xlsx")))
+ using (var wb = new XLWorkbook(stream))
+ {
+ var ws = wb.Worksheets.First();
+ title = ws.Name;
+ }
+ };
+
+ Assert.DoesNotThrow(openWorkbook);
+ Assert.AreEqual("L'E", title);
+ }
+
[Test]
public void CanRoundTripSheetProtectionForObjects()
{
diff --git a/ClosedXML_Tests/Excel/Worksheets/XLWorksheetTests.cs b/ClosedXML_Tests/Excel/Worksheets/XLWorksheetTests.cs
index 07a930c..3a41da8 100644
--- a/ClosedXML_Tests/Excel/Worksheets/XLWorksheetTests.cs
+++ b/ClosedXML_Tests/Excel/Worksheets/XLWorksheetTests.cs
@@ -234,5 +234,62 @@
ws3.CopyTo("Copy4");
}
}
+
+ [Test]
+ public void WorksheetNameCannotStartWithApostrophe()
+ {
+ var title = "'StartsWithApostrophe";
+ TestDelegate addWorksheet = () =>
+ {
+ using (var wb = new XLWorkbook())
+ {
+ wb.Worksheets.Add(title);
+ }
+ };
+
+ Assert.Throws(typeof(ArgumentException), addWorksheet);
+ }
+
+ [Test]
+ public void WorksheetNameCannotEndWithApostrophe()
+ {
+ var title = "EndsWithApostrophe'";
+ TestDelegate addWorksheet = () =>
+ {
+ using (var wb = new XLWorkbook())
+ {
+ wb.Worksheets.Add(title);
+ }
+ };
+
+ Assert.Throws(typeof(ArgumentException), addWorksheet);
+ }
+
+ [Test]
+ public void WorksheetNameCanContainApostrophe()
+ {
+ var title = "With'Apostrophe";
+ var savedTitle = "";
+ TestDelegate saveAndOpenWorkbook = () =>
+ {
+ using (var ms = new MemoryStream())
+ {
+ using (var wb = new XLWorkbook())
+ {
+ wb.Worksheets.Add(title);
+ wb.Worksheets.First().Cell(1, 1).FormulaA1 = $"{title}!A2";
+ wb.SaveAs(ms);
+ }
+
+ using (var wb = new XLWorkbook(ms))
+ {
+ savedTitle = wb.Worksheets.First().Name;
+ }
+ }
+ };
+
+ Assert.DoesNotThrow(saveAndOpenWorkbook);
+ Assert.AreEqual(title, savedTitle);
+ }
}
}
diff --git a/ClosedXML_Tests/Resource/Examples/Misc/AdjustToContentsWithAutoFilter.xlsx b/ClosedXML_Tests/Resource/Examples/Misc/AdjustToContentsWithAutoFilter.xlsx
index 5b1a0db..cbc6465 100644
--- a/ClosedXML_Tests/Resource/Examples/Misc/AdjustToContentsWithAutoFilter.xlsx
+++ b/ClosedXML_Tests/Resource/Examples/Misc/AdjustToContentsWithAutoFilter.xlsx
Binary files differ
diff --git a/ClosedXML_Tests/Resource/Examples/Misc/AutoFilter.xlsx b/ClosedXML_Tests/Resource/Examples/Misc/AutoFilter.xlsx
index 9765c38..da9b21d 100644
--- a/ClosedXML_Tests/Resource/Examples/Misc/AutoFilter.xlsx
+++ b/ClosedXML_Tests/Resource/Examples/Misc/AutoFilter.xlsx
Binary files differ
diff --git a/ClosedXML_Tests/Resource/Misc/EscapedApostrophe.xlsx b/ClosedXML_Tests/Resource/Misc/EscapedApostrophe.xlsx
new file mode 100644
index 0000000..29d15ff
--- /dev/null
+++ b/ClosedXML_Tests/Resource/Misc/EscapedApostrophe.xlsx
Binary files differ