How can this fancy-pants Column-number-to-column-Alpha representation function be simplified?

Question

I have this legacy code:

private Worksheet _xlSheet;
. . .
_xlSheet.PageSetup.PrintArea = "A1:" +
    GetExcelTextColumnName(_grandTotalsColumn) + finalRow;
. . .
protected string GetExcelTextColumnName(int columnNum)
{
    StringBuilder sb = new StringBuilder();
    if (columnNum > 26)
    {
        int firstLetter = ((columnNum - 1) / 26) + 64;
        int secondLetter = ((columnNum - 1) % 26) + 65;
        sb.Append((char)firstLetter);
        sb.Append((char)secondLetter);
    }
    else
    {
        sb.Append((char)(64 + (columnNum - 1)));
    }
    return sb.ToString();
}

I simplified the code to this extent:

_xlSheet.PageSetup.PrintArea = "A1:" + GetExcelTextColumnName(_xlSheet.UsedRange.Columns.Count) + _xlSheet.UsedRange.Rows.Count;

...but wonder if there is a way to simplify the "Column-number-to-column-Alpha" fancy-pants code. Is there, or is the existing GetExcelTextColumnName() actually elegant and as-concise-and-maintainable-as-possible code?

Answer 1

I would use a iterative algorithm, which will support columns beyond ZZ which you find in recent versions of Excel.

   private const string Alphabet = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";

    public string GetColumnName(int columnIndex)
    {
        if (columnIndex < 0) throw new ArgumentOutOfRangeException("columnIndex", columnIndex, "Column index index may not be negative.");
        string result = "";
        for (; ; )
        {
            result = Alphabet[(columnIndex) % 26] + result;
            if (columnIndex < 26) break;
            columnIndex = columnIndex / 26 - 1;
        }
        return result;
    }

I also think the use of a StringBuilder is overkill, since you know you're likely to have only one or two concatenations.

You could also consider using a Range object to avoid the need to do the calculation - probably slightly slower, but arguably more readable. Something like:

_xlSheet.PageSetup.PrintArea = 
    _xlSheet.Range("A1")
    .Resize(finalRow, _grandTotalsColumn).Address;