diff --git a/ClosedXML/ClosedXML/ClosedXML.sln b/ClosedXML/ClosedXML/ClosedXML.sln index 3c43b32..6f03ef0 100644 --- a/ClosedXML/ClosedXML/ClosedXML.sln +++ b/ClosedXML/ClosedXML/ClosedXML.sln @@ -1,6 +1,8 @@  Microsoft Visual Studio Solution File, Format Version 12.00 -# Visual Studio 2010 +# Visual Studio 2013 +VisualStudioVersion = 12.0.21005.1 +MinimumVisualStudioVersion = 10.0.40219.1 Project("{FAE04EC0-301F-11D3-BF4B-00C04F79EFBC}") = "ClosedXML_Sandbox", "ClosedXML_Sandbox\ClosedXML_Sandbox.csproj", "{38B882F0-E6F2-45C5-9BE9-CDC27FBEB4AB}" EndProject Project("{2150E333-8FDC-42A3-9474-1A3956D46DE8}") = "Solution Items", "Solution Items", "{5C94E22C-85AA-48FD-B082-CF929FFC6C31}" @@ -30,9 +32,6 @@ EndProjectSection EndProject Global - GlobalSection(TestCaseManagementSettings) = postSolution - CategoryFile = ClosedXML.vsmdi - EndGlobalSection GlobalSection(SolutionConfigurationPlatforms) = preSolution Debug|Any CPU = Debug|Any CPU Debug|Mixed Platforms = Debug|Mixed Platforms @@ -54,8 +53,8 @@ {38B882F0-E6F2-45C5-9BE9-CDC27FBEB4AB}.Debug|x86.Build.0 = Debug|x86 {38B882F0-E6F2-45C5-9BE9-CDC27FBEB4AB}.Release|Any CPU.ActiveCfg = Release|Any CPU {38B882F0-E6F2-45C5-9BE9-CDC27FBEB4AB}.Release|Any CPU.Build.0 = Release|Any CPU - {38B882F0-E6F2-45C5-9BE9-CDC27FBEB4AB}.Release|Mixed Platforms.ActiveCfg = Release|Any CPU - {38B882F0-E6F2-45C5-9BE9-CDC27FBEB4AB}.Release|Mixed Platforms.Build.0 = Release|Any CPU + {38B882F0-E6F2-45C5-9BE9-CDC27FBEB4AB}.Release|Mixed Platforms.ActiveCfg = Release|x64 + {38B882F0-E6F2-45C5-9BE9-CDC27FBEB4AB}.Release|Mixed Platforms.Build.0 = Release|x64 {38B882F0-E6F2-45C5-9BE9-CDC27FBEB4AB}.Release|x64.ActiveCfg = Release|x64 {38B882F0-E6F2-45C5-9BE9-CDC27FBEB4AB}.Release|x64.Build.0 = Release|x64 {38B882F0-E6F2-45C5-9BE9-CDC27FBEB4AB}.Release|x86.ActiveCfg = Release|Any CPU @@ -102,8 +101,8 @@ {BD5E6BFE-E837-4A35-BCA9-39667D873A20}.Debug|x86.Build.0 = Debug|x86 {BD5E6BFE-E837-4A35-BCA9-39667D873A20}.Release|Any CPU.ActiveCfg = Release|Any CPU {BD5E6BFE-E837-4A35-BCA9-39667D873A20}.Release|Any CPU.Build.0 = Release|Any CPU - {BD5E6BFE-E837-4A35-BCA9-39667D873A20}.Release|Mixed Platforms.ActiveCfg = Release|Any CPU - {BD5E6BFE-E837-4A35-BCA9-39667D873A20}.Release|Mixed Platforms.Build.0 = Release|Any CPU + {BD5E6BFE-E837-4A35-BCA9-39667D873A20}.Release|Mixed Platforms.ActiveCfg = Release|x64 + {BD5E6BFE-E837-4A35-BCA9-39667D873A20}.Release|Mixed Platforms.Build.0 = Release|x64 {BD5E6BFE-E837-4A35-BCA9-39667D873A20}.Release|x64.ActiveCfg = Release|x64 {BD5E6BFE-E837-4A35-BCA9-39667D873A20}.Release|x64.Build.0 = Release|x64 {BD5E6BFE-E837-4A35-BCA9-39667D873A20}.Release|x86.ActiveCfg = Release|Any CPU @@ -136,4 +135,10 @@ GlobalSection(SolutionProperties) = preSolution HideSolutionNode = FALSE EndGlobalSection + GlobalSection(Performance) = preSolution + HasPerformanceSessions = true + EndGlobalSection + GlobalSection(TestCaseManagementSettings) = postSolution + CategoryFile = ClosedXML.vsmdi + EndGlobalSection EndGlobal diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCell.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCell.cs index 08fa698..20cd3d3 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCell.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCell.cs @@ -22,7 +22,7 @@ private static readonly Regex A1Regex = 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)"); // A:A + + @"|(?<=\W)(\$?[a-zA-Z]{1,3}:\$?[a-zA-Z]{1,3})(?=\W)", RegexOptions.Compiled); // A:A public static readonly Regex A1SimpleRegex = new Regex( // @"(?<=\W)" // Start with non word @@ -45,22 +45,22 @@ + @")" // End Range + @")" // End Group to pick //+ @"(?=\W)" // End with non word - ); + , RegexOptions.Compiled); private static readonly Regex A1RowRegex = new Regex( @"(\$?\d{1,7}:\$?\d{1,7})" // 1:1 - ); + , RegexOptions.Compiled); private static readonly Regex A1ColumnRegex = new Regex( @"(\$?[a-zA-Z]{1,3}:\$?[a-zA-Z]{1,3})" // A:A - ); + , RegexOptions.Compiled); private static readonly Regex R1C1Regex = new Regex( @"(?<=\W)([Rr]\[?-?\d{0,7}\]?[Cc]\[?-?\d{0,7}\]?)(?=\W)" // R1C1 + @"|(?<=\W)([Rr]\[?-?\d{0,7}\]?:[Rr]\[?-?\d{0,7}\]?)(?=\W)" // R:R - + @"|(?<=\W)([Cc]\[?-?\d{0,5}\]?:[Cc]\[?-?\d{0,5}\]?)(?=\W)"); // C:C + + @"|(?<=\W)([Cc]\[?-?\d{0,5}\]?:[Cc]\[?-?\d{0,5}\]?)(?=\W)", RegexOptions.Compiled); // C:C - private static readonly Regex utfPattern = new Regex(@"(?[^'!]+)'?!(?.+))|((?[^\[]+)\[(?[^\]]+)\])$", RegexOptions.Compiled | RegexOptions.CultureInvariant | RegexOptions.ExplicitCapture ); + private static Dictionary columnNumbers = new Dictionary(); /// /// 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); - + int retVal; columnLetter = columnLetter.ToUpper(); + if (columnNumbers.TryGetValue(columnLetter, out retVal)) + return retVal; + + if (columnLetter[0] <= '9') + { + retVal = Int32.Parse(columnLetter, NumberFormatForParse); + columnNumbers.Add(columnLetter, retVal); + return retVal; + } + var length = columnLetter.Length; if (length == 1) - return Convert.ToByte(columnLetter[0]) - 64; + { + retVal = Convert.ToByte(columnLetter[0]) - 64; + columnNumbers.Add(columnLetter, retVal); + return retVal; + } if (length == 2) { - return + retVal = ((Convert.ToByte(columnLetter[0]) - 64)*26) + (Convert.ToByte(columnLetter[1]) - 64); + columnNumbers.Add(columnLetter, retVal); + return retVal; } if (length == 3) { - return ((Convert.ToByte(columnLetter[0]) - 64)*TwoT26) + + retVal = ((Convert.ToByte(columnLetter[0]) - 64)*TwoT26) + ((Convert.ToByte(columnLetter[1]) - 64)*26) + (Convert.ToByte(columnLetter[2]) - 64); + columnNumbers.Add(columnLetter, retVal); + return retVal; } throw new ApplicationException("Column Length must be between 1 and 3."); } + private static Dictionary columnLetters = new Dictionary(); /// /// Gets the column letter of a given column number. /// /// The column number to translate into a column letter. - public static string GetColumnLetterFromNumber(int column) + public static string GetColumnLetterFromNumber(int columnNumber) { + String retVal; + if (columnLetters.TryGetValue(columnNumber, out retVal)) + return retVal; + #region Check - if (column <= 0) + if (columnNumber <= 0) throw new ArgumentOutOfRangeException("column", "Must be more than 0"); #endregion var value = new StringBuilder(6); + int column = columnNumber; while (column > 0) { var residue = column%26; @@ -107,7 +130,9 @@ } value.Insert(0, (char) (64 + residue)); } - return value.ToString(); + retVal = value.ToString(); + columnLetters.Add(columnNumber, retVal); + return retVal; } public static bool IsValidColumn(string column) diff --git a/ClosedXML/ClosedXML/ClosedXML_Sandbox/Program.cs b/ClosedXML/ClosedXML/ClosedXML_Sandbox/Program.cs index 40bf205..5145c4d 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Sandbox/Program.cs +++ b/ClosedXML/ClosedXML/ClosedXML_Sandbox/Program.cs @@ -6,10 +6,13 @@ using ClosedXML; using System.Drawing; using System.IO; +using DocumentFormat.OpenXml.Spreadsheet; +using DocumentFormat.OpenXml; namespace ClosedXML_Sandbox { class Program { + static void Main(string[] args) { var wb = new XLWorkbook(); @@ -19,7 +22,7 @@ foreach (var sheetId in Enumerable.Range(1, 4)) { var ws = wb.AddWorksheet("Sheet" + sheetId); - foreach (var ro in Enumerable.Range(1, 40000)) + foreach (var ro in Enumerable.Range(1, 4000)) { foreach (var co in Enumerable.Range(1, 30)) { @@ -28,13 +31,13 @@ } } var end = DateTime.Now; - Console.WriteLine((end - start).TotalMinutes); + Console.WriteLine((end - start).TotalSeconds); Console.WriteLine("Saving"); start = DateTime.Now; wb.SaveAs(@"c:\temp\saved.xlsx"); end = DateTime.Now; - Console.WriteLine((end - start).TotalMinutes); + Console.WriteLine((end - start).TotalSeconds); Console.WriteLine("Done"); Console.ReadKey();