Just Some Excel Helpers and Respective Unit Tests

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.