diff --git a/ClosedXML_Tests/Excel/Misc/XlHelperTests.cs b/ClosedXML_Tests/Excel/Misc/XlHelperTests.cs index 20853d6..7ef97ff 100644 --- a/ClosedXML_Tests/Excel/Misc/XlHelperTests.cs +++ b/ClosedXML_Tests/Excel/Misc/XlHelperTests.cs @@ -1,5 +1,7 @@ using ClosedXML.Excel; using NUnit.Framework; +using System; +using System.Collections.Generic; namespace ClosedXML_Tests.Excel { @@ -71,6 +73,7 @@ CheckColumnNumber(52); CheckColumnNumber(53); CheckColumnNumber(1000); + CheckColumnNumber(1353); } [Test] @@ -91,5 +94,81 @@ Assert.IsTrue(XLHelper.IsValidA1Address(XLHelper.MaxColumnLetter + "1")); Assert.IsTrue(XLHelper.IsValidA1Address(XLHelper.MaxColumnLetter + XLHelper.MaxRowNumber)); } + + [Test] + public void TestColumnLetterLookup() + { + var columnLetters = new List(); + for (int c = 1; c <= XLHelper.MaxColumnNumber; c++) + { + var columnLetter = NaiveGetColumnLetterFromNumber(c); + columnLetters.Add(columnLetter); + + Assert.AreEqual(columnLetter, XLHelper.GetColumnLetterFromNumber(c)); + } + + foreach (var cl in columnLetters) + { + var columnNumber = NaiveGetColumnNumberFromLetter(cl); + Assert.AreEqual(columnNumber, XLHelper.GetColumnNumberFromLetter(cl)); + } + } + + #region Old XLHelper methods + + 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" }; + + /// + /// These used to be the methods in XLHelper, but were later changed + /// We now use them as a check against the new methods + /// Gets the column number of a given column letter. + /// + /// The column letter to translate into a column number. + private static int NaiveGetColumnNumberFromLetter(string columnLetter) + { + if (string.IsNullOrEmpty(columnLetter)) throw new ArgumentNullException("columnLetter"); + + int retVal; + columnLetter = columnLetter.ToUpper(); + + //Extra check because we allow users to pass row col positions in as strings + if (columnLetter[0] <= '9') + { + retVal = Int32.Parse(columnLetter, XLHelper.NumberStyle, XLHelper.ParseCulture); + return retVal; + } + + int sum = 0; + + for (int i = 0; i < columnLetter.Length; i++) + { + sum *= 26; + sum += (columnLetter[i] - 'A' + 1); + } + + return sum; + } + + /// + /// Gets the column letter of a given column number. + /// + /// The column number to translate into a column letter. + /// if set to true the column letter will be restricted to the allowed range. + /// + private static string NaiveGetColumnLetterFromNumber(int columnNumber, bool trimToAllowed = false) + { + if (trimToAllowed) columnNumber = XLHelper.TrimColumnNumber(columnNumber); + + columnNumber--; // Adjust for start on column 1 + if (columnNumber <= 25) + { + return letters[columnNumber]; + } + var firstPart = (columnNumber) / 26; + var remainder = ((columnNumber) % 26) + 1; + return NaiveGetColumnLetterFromNumber(firstPart) + NaiveGetColumnLetterFromNumber(remainder); + } + + #endregion Old XLHelper methods } -} \ No newline at end of file +}