Fastest function to generate Excel column letters in C#

Question

I currently use this, with Excel 2007

public static string ExcelColumnFromNumber(int column)
        {
            string columnString = "";
            decimal columnNumber = column;
            while (columnNumber > 0)
            {
                decimal currentLetterNumber = (columnNumber - 1) % 26;
                char currentLetter = (char)(currentLetterNumber + 65);
                columnString = currentLetter + columnString;
                columnNumber = (columnNumber - (currentLetterNumber + 1)) / 26;
            }
            return columnString;
        }

and

public static int NumberFromExcelColumn(string column)
        {
            int retVal = 0;
            string col = column.ToUpper();
            for (int iChar = col.Length - 1; iChar >= 0; iChar--)
            {
                char colPiece = col[iChar];
                int colNum = colPiece - 64;
                retVal = retVal + colNum * (int)Math.Pow(26, col.Length - (iChar + 1));
            }
            return retVal;
        }

As mentioned in other posts, the results can be cached.

I can tell you that the fastest function will not be the prettiest function. Here it is:

private string[] map = new string[]
    { 
        "A", "B", "C", "D", "E" .............
    };

public string getColumn(int number)
{
    return map[number];
}

Don't convert it at all. Excel can work in R1C1 notation just as well as in A1 notation.

So (apologies for using VBA rather than C#):

Application.Worksheets("Sheet1").Range("B1").Font.Bold = True

can just as easily be written as:

Application.Worksheets("Sheet1").Cells(1, 2).Font.Bold = True

The Range property takes A1 notation whereas the Cells property takes (row number, column number).

To select multiple cells: Range(Cells(1, 1), Cells(4, 6)) (NB would need some kind of object qualifier if not using the active worksheet) rather than Range("A1:F4")

The Columns property can take either a letter (e.g. F) or a number (e.g. 6)

Here's my version: This does not have any limitation as such 2-letter or 3-letter. Simply pass-in the required number (starting with 0) Will return the Excel Column Header like Alphabet sequence for passed-in number:

private string GenerateSequence(int num)
{
    string str = "";
    char achar;
    int mod;
    while (true)
    {
        mod = (num % 26) + 65;
        num = (int)(num / 26);
        achar = (char)mod;
        str = achar + str;
        if (num > 0) num--;
        else if (num == 0) break;
    }
    return str;
}

I did not tested this for performance, if someone can do that will great for others. (Sorry for being lazy) :)

Cheers!

You could pre-generate all the values into an array of strings. This would take very little memory and could be calculated on the first call.

Here is a concise implementation using LINQ.

static IEnumerable<string> GetExcelStrings()
{
    string[] alphabet = { string.Empty, "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" };

    return from c1 in alphabet
           from c2 in alphabet
           from c3 in alphabet.Skip(1)                    // c3 is never empty
           where c1 == string.Empty || c2 != string.Empty // only allow c2 to be empty if c1 is also empty
           select c1 + c2 + c3;
}

This generates A to Z, then AA to ZZ, then AAA to ZZZ.

On my PC, calling GetExcelStrings().ToArray() takes about 30 ms. Thereafter, you can refer to this array of strings if you need it thousands of times.