Q: How to convert a column number (eg. 127) into an excel column (eg. AA)

D: How do you convert a numerical number to an Excel column name in C# without using automation getting the value directly from Excel.
Excel 2007 has a possible range of 1 to 16384, which is the number of columns that it supports. The resulting values should be in the form of excel column names, e.g. A, AA, AAA etc.

Test Case #6


File ID: #182924-0-cc


public static string GetExcelColumnName(int columnNumber)
{
    int dividend = columnNumber;
    string columnName = String.Empty;
    int modulo;
    while (dividend > 0)
    {
        modulo = (dividend - 1) % 26;
        columnName = Convert.ToChar(65 + modulo).ToString() + columnName;
        dividend = (int)((dividend - modulo) / 26);
    }
    return columnName;
}

  1. This code works well. It assumes Column A is columnNumber 1. I had to make a quick change to account for my system using Column A as columnNumber 0. I changed the line int dividend to int dividend = columnNumber + 1; Keith
  2. Just tried it out using `StringBuilder`. It takes about twice as long. It's a very short string (max 3 characters - Excel 2010 goes up to column XFD), so maximum of 2 string concatenations. (I used 100 iterations of translating the integers 1 to 16384, i.e. Excel columns A to XFD, as the test).
  3. I think it would be better to use 'A' instead of 65. And 26 could be evaluated as ('Z' - 'A' + 1), for example: const int AlphabetLength = 'Z' - 'A' + 1;

Comments Quality
Accurate?:
Precise?:
Concise?:
Useful?: