Fastest function to generate Excel column letters in C#

Solution 1:

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.

Solution 2:

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];
}

Solution 3:

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)

Solution 4:

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!

Solution 5:

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.