diff --git a/ClosedXML/ClosedXML/ClosedXML/ClosedXML.csproj b/ClosedXML/ClosedXML/ClosedXML/ClosedXML.csproj
index c8ea6df..825a242 100644
--- a/ClosedXML/ClosedXML/ClosedXML/ClosedXML.csproj
+++ b/ClosedXML/ClosedXML/ClosedXML/ClosedXML.csproj
@@ -130,7 +130,7 @@
-
+
diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRanges.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRanges.cs
index a4b012c..a3b5940 100644
--- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRanges.cs
+++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRanges.cs
@@ -52,7 +52,7 @@
public IEnumerator GetEnumerator()
{
var retList = new List();
- _ranges.ForEach(retList.Add);
+ retList.AddRange(_ranges.Where(r => XLHelper.IsValidRangeAddress(r.RangeAddress)).Cast());
return retList.GetEnumerator();
}
diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Load.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Load.cs
index 37ebf2e..73ab216 100644
--- a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Load.cs
+++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Load.cs
@@ -1621,13 +1621,11 @@
var selection = sheetView.Elements().FirstOrDefault();
if (selection != null)
{
- ws.SuspendEvents();
if (selection.SequenceOfReferences != null)
ws.Ranges(selection.SequenceOfReferences.InnerText.Replace(" ", ",")).Select();
if (selection.ActiveCell != null)
ws.Cell(selection.ActiveCell).SetActive();
- ws.ResumeEvents();
}
var pane = sheetView.Elements().FirstOrDefault();
diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Save.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Save.cs
index 82a4b48..ea0c2cf 100644
--- a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Save.cs
+++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Save.cs
@@ -3669,11 +3669,9 @@
var seqRef = new List {selection.ActiveCell.Value};
- if (xlWorksheet.SelectedRanges.Any())
- {
- seqRef.AddRange(
- xlWorksheet.SelectedRanges.Select(range => range.RangeAddress.ToStringRelative(false)));
- }
+ seqRef.AddRange(xlWorksheet.SelectedRanges
+ .Select(range => range.RangeAddress.ToStringRelative(false)));
+
selection.SequenceOfReferences = new ListValue
{InnerText = String.Join(" ", seqRef.Distinct().ToArray())};
diff --git a/ClosedXML/ClosedXML/ClosedXML/ExcelHelper.cs b/ClosedXML/ClosedXML/ClosedXML/ExcelHelper.cs
deleted file mode 100644
index d0b702a..0000000
--- a/ClosedXML/ClosedXML/ClosedXML/ExcelHelper.cs
+++ /dev/null
@@ -1,283 +0,0 @@
-using System;
-using System.Collections.Generic;
-using System.Globalization;
-using System.Text;
-
-namespace ClosedXML.Excel
-{
- using System.Linq;
- using System.Text.RegularExpressions;
- using System.Drawing;
-
- ///
- /// Common methods
- ///
- public static class XLHelper
- {
- public const int MinRowNumber = 1;
- public const int MinColumnNumber = 1;
- public const int MaxRowNumber = 1048576;
- public const int MaxColumnNumber = 16384;
- public const String MaxColumnLetter = "XFD";
- public const Double Epsilon = 1e-10;
-
- private const Int32 TwoT26 = 26*26;
- internal static readonly NumberFormatInfo NumberFormatForParse = CultureInfo.InvariantCulture.NumberFormat;
- internal static readonly Graphics Graphic = Graphics.FromImage(new Bitmap(200, 200));
- internal static readonly Double DpiX = Graphic.DpiX;
-
- internal static readonly Regex A1SimpleRegex = new Regex(
- @"\A"
- + @"(?" // Start Group to pick
- + @"(?" // Start Sheet Name, optional
- + @"("
- + @"\'([^\[\]\*/\\\?:\']+|\'\')\'"
- // Sheet name with special characters, surrounding apostrophes are required
- + @"|"
- + @"\'?\w+\'?" // Sheet name with letters and numbers, surrounding apostrophes are optional
- + @")"
- + @"!)?" // End Sheet Name, optional
- + @"(?" // Start range
- + @"\$?[a-zA-Z]{1,3}\$?\d{1,7}" // A1 Address 1
- + @"(?:\$?[a-zA-Z]{1,3}\$?\d{1,7})?" // A1 Address 2, optional
- + @"|"
- + @"(?\$?\d{1,7}:\$?\d{1,7})" // 1:1
- + @"|"
- + @"(?\$?[a-zA-Z]{1,3}:\$?[a-zA-Z]{1,3})" // A:A
- + @")" // End Range
- + @")" // End Group to pick
- + @"\Z"
- );
-
- internal static readonly Regex NamedRangeReferenceRegex =
- new Regex(@"^('?(?[^'!]+)'?!(?.+))|((?[^\[]+)\[(?[^\]]+)\])$",
- RegexOptions.Compiled | RegexOptions.CultureInvariant | RegexOptions.ExplicitCapture
- );
-
- ///
- /// Gets the column number of a given column letter.
- ///
- /// The column letter to translate into a column number.
- public static int GetColumnNumberFromLetter(string columnLetter)
- {
- if (columnLetter[0] <= '9')
- return Int32.Parse(columnLetter, NumberFormatForParse);
-
- columnLetter = columnLetter.ToUpper();
- var length = columnLetter.Length;
- if (length == 1)
- return Convert.ToByte(columnLetter[0]) - 64;
- if (length == 2)
- {
- return
- ((Convert.ToByte(columnLetter[0]) - 64)*26) +
- (Convert.ToByte(columnLetter[1]) - 64);
- }
- if (length == 3)
- {
- return ((Convert.ToByte(columnLetter[0]) - 64)*TwoT26) +
- ((Convert.ToByte(columnLetter[1]) - 64)*26) +
- (Convert.ToByte(columnLetter[2]) - 64);
- }
- throw new ApplicationException("Column Length must be between 1 and 3.");
- }
-
- ///
- /// Gets the column letter of a given column number.
- ///
- /// The column number to translate into a column letter.
- public static string GetColumnLetterFromNumber(int column)
- {
- #region Check
-
- if (column <= 0)
- throw new ArgumentOutOfRangeException("column", "Must be more than 0");
-
- #endregion
-
- var value = new StringBuilder(6);
- while (column > 0)
- {
- var residue = column%26;
- column /= 26;
- if (residue == 0)
- {
- residue = 26;
- column--;
- }
- value.Insert(0, (char) (64 + residue));
- }
- return value.ToString();
- }
-
- public static bool IsValidColumn(string column)
- {
- var length = column.Length;
- if (IsNullOrWhiteSpace(column) || length > 3)
- return false;
-
- var theColumn = column.ToUpper();
-
-
- var isValid = theColumn[0] >= 'A' && theColumn[0] <= 'Z';
- if (length == 1)
- return isValid;
-
- if (length == 2)
- return isValid && theColumn[1] >= 'A' && theColumn[1] <= 'Z';
-
- if (theColumn[0] >= 'A' && theColumn[0] < 'X')
- return theColumn[1] >= 'A' && theColumn[1] <= 'Z'
- && theColumn[2] >= 'A' && theColumn[2] <= 'Z';
-
- if (theColumn[0] != 'X') return false;
-
- if (theColumn[1] < 'F')
- return theColumn[2] >= 'A' && theColumn[2] <= 'Z';
-
- if (theColumn[1] != 'F') return false;
-
- return theColumn[2] >= 'A' && theColumn[2] <= 'D';
- }
-
- public static bool IsValidRow(string rowString)
- {
- Int32 row;
- if (Int32.TryParse(rowString, out row))
- return row > 0 && row <= MaxRowNumber;
- return false;
- }
-
- public static bool IsValidA1Address(string address)
- {
- if (IsNullOrWhiteSpace(address))
- return false;
-
- address = address.Replace("$", "");
- var rowPos = 0;
- var addressLength = address.Length;
- while (rowPos < addressLength && (address[rowPos] > '9' || address[rowPos] < '0'))
- rowPos++;
-
- return
- rowPos < addressLength
- && IsValidRow(address.Substring(rowPos))
- && IsValidColumn(address.Substring(0, rowPos));
- }
-
- public static Boolean IsValidRangeAddress(String rangeAddress)
- {
- return A1SimpleRegex.IsMatch(rangeAddress);
- }
-
- public static int GetColumnNumberFromAddress(string cellAddressString)
- {
- var rowPos = 0;
- while (cellAddressString[rowPos] > '9')
- rowPos++;
-
- return GetColumnNumberFromLetter(cellAddressString.Substring(0, rowPos));
- }
-
- internal static string[] SplitRange(string range)
- {
- return range.Contains('-') ? range.Replace('-', ':').Split(':') : range.Split(':');
- }
-
- public static Int32 GetPtFromPx(Double px)
- {
- return Convert.ToInt32(px*72.0/DpiX);
- }
-
- public static Double GetPxFromPt(Int32 pt)
- {
- return Convert.ToDouble(pt)*DpiX/72.0;
- }
-
- internal static IXLTableRows InsertRowsWithoutEvents(Func insertFunc,
- XLTableRange tableRange, Int32 numberOfRows,
- Boolean expandTable)
- {
- var ws = tableRange.Worksheet;
- var tracking = ws.EventTrackingEnabled;
- ws.EventTrackingEnabled = false;
-
- var rows = new XLTableRows(ws.Style);
- var inserted = insertFunc(numberOfRows, false);
- inserted.ForEach(r => rows.Add(new XLTableRow(tableRange, r as XLRangeRow)));
-
- if (expandTable)
- tableRange.Table.ExpandTableRows(numberOfRows);
-
- ws.EventTrackingEnabled = tracking;
-
- return rows;
- }
-
-
-
- public static bool IsNullOrWhiteSpace(string value)
- {
-#if NET4
- return String.IsNullOrWhiteSpace(value);
-#else
- if (value != null)
- {
- var length = value.Length;
- for (int i = 0; i < length; i++)
- {
- if (!char.IsWhiteSpace(value[i]))
- {
- return false;
- }
- }
- }
- return true;
-#endif
-
- }
-
- private static readonly Regex A1RegexRelative = new Regex(
- @"(?<=\W)(?\$?[a-zA-Z]{1,3}\$?\d{1,7})(?=\W)" // A1
- + @"|(?<=\W)(?\$?\d{1,7}:\$?\d{1,7})(?=\W)" // 1:1
- + @"|(?<=\W)(?\$?[a-zA-Z]{1,3}:\$?[a-zA-Z]{1,3})(?=\W)", RegexOptions.Compiled); // A:A
-
- private static string Evaluator(Match match, Int32 row, String column)
- {
- if (match.Groups["one"].Success)
- {
- var split = match.Groups["one"].Value.Split('$');
- if (split.Length == 1) return column + row; // A1
- if (split.Length == 3) return match.Groups["one"].Value; // $A$1
- var a = XLAddress.Create(match.Groups["one"].Value);
- if (split[0] == String.Empty) return "$" + a.ColumnLetter + row; // $A1
- return column + "$" + a.RowNumber;
- }
-
- if (match.Groups["two"].Success)
- return ReplaceGroup(match.Groups["two"].Value, row.ToString());
-
- return ReplaceGroup(match.Groups["three"].Value, column);
- }
-
- private static String ReplaceGroup(String value, String item)
- {
- var split = value.Split(':');
- String ret1 = split[0].StartsWith("$") ? split[0] : item;
- String ret2 = split[1].StartsWith("$") ? split[1] : item;
- return ret1 + ":" + ret2;
- }
-
- internal static String ReplaceRelative(String value, Int32 row, String column)
- {
- var oldValue = ">" + value + "<";
- var newVal = A1RegexRelative.Replace(oldValue, m => Evaluator(m, row, column));
- return newVal.Substring(1, newVal.Length - 2);
- }
-
- public static Boolean AreEqual(Double d1, Double d2)
- {
- return Math.Abs(d1 - d2) < Epsilon;
- }
- }
-}
\ No newline at end of file
diff --git a/ClosedXML/ClosedXML/ClosedXML/XLHelper.cs b/ClosedXML/ClosedXML/ClosedXML/XLHelper.cs
new file mode 100644
index 0000000..769e532
--- /dev/null
+++ b/ClosedXML/ClosedXML/ClosedXML/XLHelper.cs
@@ -0,0 +1,291 @@
+using System;
+using System.Collections.Generic;
+using System.Globalization;
+using System.Text;
+
+namespace ClosedXML.Excel
+{
+ using System.Linq;
+ using System.Text.RegularExpressions;
+ using System.Drawing;
+
+ ///
+ /// Common methods
+ ///
+ public static class XLHelper
+ {
+ public const int MinRowNumber = 1;
+ public const int MinColumnNumber = 1;
+ public const int MaxRowNumber = 1048576;
+ public const int MaxColumnNumber = 16384;
+ public const String MaxColumnLetter = "XFD";
+ public const Double Epsilon = 1e-10;
+
+ private const Int32 TwoT26 = 26*26;
+ internal static readonly NumberFormatInfo NumberFormatForParse = CultureInfo.InvariantCulture.NumberFormat;
+ internal static readonly Graphics Graphic = Graphics.FromImage(new Bitmap(200, 200));
+ internal static readonly Double DpiX = Graphic.DpiX;
+
+ internal static readonly Regex A1SimpleRegex = new Regex(
+ @"\A"
+ + @"(?" // Start Group to pick
+ + @"(?" // Start Sheet Name, optional
+ + @"("
+ + @"\'([^\[\]\*/\\\?:\']+|\'\')\'"
+ // Sheet name with special characters, surrounding apostrophes are required
+ + @"|"
+ + @"\'?\w+\'?" // Sheet name with letters and numbers, surrounding apostrophes are optional
+ + @")"
+ + @"!)?" // End Sheet Name, optional
+ + @"(?" // Start range
+ + @"\$?[a-zA-Z]{1,3}\$?\d{1,7}" // A1 Address 1
+ + @"(?:\$?[a-zA-Z]{1,3}\$?\d{1,7})?" // A1 Address 2, optional
+ + @"|"
+ + @"(?\$?\d{1,7}:\$?\d{1,7})" // 1:1
+ + @"|"
+ + @"(?\$?[a-zA-Z]{1,3}:\$?[a-zA-Z]{1,3})" // A:A
+ + @")" // End Range
+ + @")" // End Group to pick
+ + @"\Z"
+ );
+
+ internal static readonly Regex NamedRangeReferenceRegex =
+ new Regex(@"^('?(?[^'!]+)'?!(?.+))|((?[^\[]+)\[(?[^\]]+)\])$",
+ RegexOptions.Compiled | RegexOptions.CultureInvariant | RegexOptions.ExplicitCapture
+ );
+
+ ///
+ /// Gets the column number of a given column letter.
+ ///
+ /// The column letter to translate into a column number.
+ public static int GetColumnNumberFromLetter(string columnLetter)
+ {
+ if (columnLetter[0] <= '9')
+ return Int32.Parse(columnLetter, NumberFormatForParse);
+
+ columnLetter = columnLetter.ToUpper();
+ var length = columnLetter.Length;
+ if (length == 1)
+ return Convert.ToByte(columnLetter[0]) - 64;
+ if (length == 2)
+ {
+ return
+ ((Convert.ToByte(columnLetter[0]) - 64)*26) +
+ (Convert.ToByte(columnLetter[1]) - 64);
+ }
+ if (length == 3)
+ {
+ return ((Convert.ToByte(columnLetter[0]) - 64)*TwoT26) +
+ ((Convert.ToByte(columnLetter[1]) - 64)*26) +
+ (Convert.ToByte(columnLetter[2]) - 64);
+ }
+ throw new ApplicationException("Column Length must be between 1 and 3.");
+ }
+
+ ///
+ /// Gets the column letter of a given column number.
+ ///
+ /// The column number to translate into a column letter.
+ public static string GetColumnLetterFromNumber(int column)
+ {
+ #region Check
+
+ if (column <= 0)
+ throw new ArgumentOutOfRangeException("column", "Must be more than 0");
+
+ #endregion
+
+ var value = new StringBuilder(6);
+ while (column > 0)
+ {
+ var residue = column%26;
+ column /= 26;
+ if (residue == 0)
+ {
+ residue = 26;
+ column--;
+ }
+ value.Insert(0, (char) (64 + residue));
+ }
+ return value.ToString();
+ }
+
+ public static bool IsValidColumn(string column)
+ {
+ var length = column.Length;
+ if (IsNullOrWhiteSpace(column) || length > 3)
+ return false;
+
+ var theColumn = column.ToUpper();
+
+
+ var isValid = theColumn[0] >= 'A' && theColumn[0] <= 'Z';
+ if (length == 1)
+ return isValid;
+
+ if (length == 2)
+ return isValid && theColumn[1] >= 'A' && theColumn[1] <= 'Z';
+
+ if (theColumn[0] >= 'A' && theColumn[0] < 'X')
+ return theColumn[1] >= 'A' && theColumn[1] <= 'Z'
+ && theColumn[2] >= 'A' && theColumn[2] <= 'Z';
+
+ if (theColumn[0] != 'X') return false;
+
+ if (theColumn[1] < 'F')
+ return theColumn[2] >= 'A' && theColumn[2] <= 'Z';
+
+ if (theColumn[1] != 'F') return false;
+
+ return theColumn[2] >= 'A' && theColumn[2] <= 'D';
+ }
+
+ public static bool IsValidRow(string rowString)
+ {
+ Int32 row;
+ if (Int32.TryParse(rowString, out row))
+ return row > 0 && row <= MaxRowNumber;
+ return false;
+ }
+
+ public static bool IsValidA1Address(string address)
+ {
+ if (IsNullOrWhiteSpace(address))
+ return false;
+
+ address = address.Replace("$", "");
+ var rowPos = 0;
+ var addressLength = address.Length;
+ while (rowPos < addressLength && (address[rowPos] > '9' || address[rowPos] < '0'))
+ rowPos++;
+
+ return
+ rowPos < addressLength
+ && IsValidRow(address.Substring(rowPos))
+ && IsValidColumn(address.Substring(0, rowPos));
+ }
+
+ public static Boolean IsValidRangeAddress(String rangeAddress)
+ {
+ return A1SimpleRegex.IsMatch(rangeAddress);
+ }
+
+ public static Boolean IsValidRangeAddress(IXLRangeAddress rangeAddress)
+ {
+ return rangeAddress.FirstAddress.RowNumber >= 1 && rangeAddress.LastAddress.RowNumber <= MaxRowNumber
+ && rangeAddress.FirstAddress.ColumnNumber >= 1 && rangeAddress.LastAddress.ColumnNumber <= MaxColumnNumber
+ && rangeAddress.FirstAddress.RowNumber <= rangeAddress.LastAddress.RowNumber
+ && rangeAddress.FirstAddress.ColumnNumber <= rangeAddress.LastAddress.ColumnNumber;
+ }
+
+ public static int GetColumnNumberFromAddress(string cellAddressString)
+ {
+ var rowPos = 0;
+ while (cellAddressString[rowPos] > '9')
+ rowPos++;
+
+ return GetColumnNumberFromLetter(cellAddressString.Substring(0, rowPos));
+ }
+
+ internal static string[] SplitRange(string range)
+ {
+ return range.Contains('-') ? range.Replace('-', ':').Split(':') : range.Split(':');
+ }
+
+ public static Int32 GetPtFromPx(Double px)
+ {
+ return Convert.ToInt32(px*72.0/DpiX);
+ }
+
+ public static Double GetPxFromPt(Int32 pt)
+ {
+ return Convert.ToDouble(pt)*DpiX/72.0;
+ }
+
+ internal static IXLTableRows InsertRowsWithoutEvents(Func insertFunc,
+ XLTableRange tableRange, Int32 numberOfRows,
+ Boolean expandTable)
+ {
+ var ws = tableRange.Worksheet;
+ var tracking = ws.EventTrackingEnabled;
+ ws.EventTrackingEnabled = false;
+
+ var rows = new XLTableRows(ws.Style);
+ var inserted = insertFunc(numberOfRows, false);
+ inserted.ForEach(r => rows.Add(new XLTableRow(tableRange, r as XLRangeRow)));
+
+ if (expandTable)
+ tableRange.Table.ExpandTableRows(numberOfRows);
+
+ ws.EventTrackingEnabled = tracking;
+
+ return rows;
+ }
+
+
+
+ public static bool IsNullOrWhiteSpace(string value)
+ {
+#if NET4
+ return String.IsNullOrWhiteSpace(value);
+#else
+ if (value != null)
+ {
+ var length = value.Length;
+ for (int i = 0; i < length; i++)
+ {
+ if (!char.IsWhiteSpace(value[i]))
+ {
+ return false;
+ }
+ }
+ }
+ return true;
+#endif
+
+ }
+
+ private static readonly Regex A1RegexRelative = new Regex(
+ @"(?<=\W)(?\$?[a-zA-Z]{1,3}\$?\d{1,7})(?=\W)" // A1
+ + @"|(?<=\W)(?\$?\d{1,7}:\$?\d{1,7})(?=\W)" // 1:1
+ + @"|(?<=\W)(?\$?[a-zA-Z]{1,3}:\$?[a-zA-Z]{1,3})(?=\W)", RegexOptions.Compiled); // A:A
+
+ private static string Evaluator(Match match, Int32 row, String column)
+ {
+ if (match.Groups["one"].Success)
+ {
+ var split = match.Groups["one"].Value.Split('$');
+ if (split.Length == 1) return column + row; // A1
+ if (split.Length == 3) return match.Groups["one"].Value; // $A$1
+ var a = XLAddress.Create(match.Groups["one"].Value);
+ if (split[0] == String.Empty) return "$" + a.ColumnLetter + row; // $A1
+ return column + "$" + a.RowNumber;
+ }
+
+ if (match.Groups["two"].Success)
+ return ReplaceGroup(match.Groups["two"].Value, row.ToString());
+
+ return ReplaceGroup(match.Groups["three"].Value, column);
+ }
+
+ private static String ReplaceGroup(String value, String item)
+ {
+ var split = value.Split(':');
+ String ret1 = split[0].StartsWith("$") ? split[0] : item;
+ String ret2 = split[1].StartsWith("$") ? split[1] : item;
+ return ret1 + ":" + ret2;
+ }
+
+ internal static String ReplaceRelative(String value, Int32 row, String column)
+ {
+ var oldValue = ">" + value + "<";
+ var newVal = A1RegexRelative.Replace(oldValue, m => Evaluator(m, row, column));
+ return newVal.Substring(1, newVal.Length - 2);
+ }
+
+ public static Boolean AreEqual(Double d1, Double d2)
+ {
+ return Math.Abs(d1 - d2) < Epsilon;
+ }
+ }
+}
\ No newline at end of file
diff --git a/ClosedXML/ClosedXML/ClosedXML_Net3.5/ClosedXML_Net3.5.csproj b/ClosedXML/ClosedXML/ClosedXML_Net3.5/ClosedXML_Net3.5.csproj
index 845520a..2b8329e 100644
--- a/ClosedXML/ClosedXML/ClosedXML_Net3.5/ClosedXML_Net3.5.csproj
+++ b/ClosedXML/ClosedXML/ClosedXML_Net3.5/ClosedXML_Net3.5.csproj
@@ -126,9 +126,6 @@
-
- ExcelHelper.cs
-
Excel\AutoFilters\IXLAutoFilter.cs
@@ -864,6 +861,9 @@
PathHelper.cs
+
+ XLHelper.cs
+