Just some semi-elegant stuff I’ve conjured up recently to get column names in Excel and do some other functional things. I wrote this code in a true TDD fashion. It still, for some reason, seems odd to me to write tests in VB, since it was always a language that encouraged dirtiness. But alas, this is fairly solid stuff.
Imports NUnit.Framework
<TestFixture()> _
Public Class HelperTests
<Test()> <Category("Presenter Helper Tests")> _
Public Sub TestHelperGetExcelColumnLetterSingleLetter()
Dim theLetter As String = Helper.GetExcelColumnLetter(3)
Assert.AreEqual(theLetter, "C")
End Sub
<Test()> <Category("Presenter Helper Tests")> _
Public Sub TestHelperGetExcelColumnLetterDoubleLetter()
Dim theLetter As String = Helper.GetExcelColumnLetter(29)
Assert.AreEqual(theLetter, "AC")
End Sub
<Test()> <Category("Presenter Helper Tests")> _
Public Sub TestHelperGetExcelColumnDoubleLetterThirdAlphabet()
Dim theLetter As String = Helper.GetExcelColumnLetter(56)
Assert.AreEqual(theLetter, "BD")
End Sub
<Test()> <Category("Presenter Helper Tests")> _
Public Sub TestHelperGetExcelColumnTripleLetter()
Dim theLetter As String = Helper.GetExcelColumnLetter(2356)
Assert.AreEqual(theLetter, "CLP")
End Sub
<Test()> <Category("Presenter Helper Tests")> _
Public Sub TestHelperGetExcelColumnTripleLetterSomething()
Dim theLetter As String = Helper.GetExcelColumnLetter(14999)
Assert.AreEqual(theLetter, "VDW")
End Sub
<Test()> <Category("Presenter Helper Tests")> _
Public Sub TestHelperGetExcelColumnTripleLetterTheEndOfZzz()
Dim theLetter As String = Helper.GetExcelColumnLetter(18278)
Assert.AreEqual(theLetter, "ZZZ")
End Sub
<Test()> <Category("Presenter Helper Tests")> _
Public Sub TestHelperGetExcelColumnF1C1()
Dim theColumnF1C1 As String = Helper.GetExcelColumnF1C1(3, 6)
Assert.AreEqual("C:F", theColumnF1C1)
End Sub
End Class
I churned out each of these tests, the first few I did at one time, counting out where the column “letters” would be derived at various column numbers. It almost got confusing a few times. All tests of course went red and I began coding the innards of the method.
What I came up with, after a few gotchas here and there is this.
Public Class Helper
''' <summary>
''' This method gets the appropriate "A" thru "ZZZ" column header for Excel based
''' on a passed in long data type.
''' </summary>
''' <param name="colNum">Pass in a long type to derive the appropriate column header letter.</param>
''' <returns>A string of the appropriate "A" thru "ZZZ" header letter.</returns>
Public Shared Function GetExcelColumnLetter(ByVal colNum As Long) As String
Do
GetExcelColumnLetter = Chr(65 + (colNum - 1) Mod 26) & GetExcelColumnLetter
colNum = (colNum - 1) \ 26
Loop While colNum > 0
End Function
''' <summary>
''' This method returns the ("A:ZZZ") part of the Range("A:ZZZ") column interface.
''' </summary>
''' <param name="firstColumn">Enter the first column to start the range with.</param>
''' <param name="secondColumn">Enter the second column to end the range with.</param>
''' <returns>A string formatted as A:ZZZ is returned from this method.</returns>
Public Shared Function GetExcelColumnF1C1(ByVal firstColumn As String, ByVal secondColumn As String) As String
Return GetExcelColumnLetter(firstColumn) + ":" + GetExcelColumnLetter(secondColumn)
End Function
End Class
If you work with Excel frequently you know all about F1C1, which I decided to stick on the end of the GetExcelColumnF1C1. Not sure if it really makes sense, but since so much of Excel is kind of just mess, I figured it might conjure the right thought in a coder’s mind.
![Stick out tongue [:P]](/emoticons/emotion-4.gif)
![Big Smile [:D]](/emoticons/emotion-2.gif)