Column No to Column Letter in Excel/VSTO using C#

Question

How to find column's name or header?

For example if i select column 5 in excel means i want the result as "E". How to get the alphabet or letter corresponding to column no.

Please help me with the code

Answer 1

public static string GetColumnName(int columnNumber)
{
    const string letters = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
    string columnName = "";

    while (columnNumber > 0)
    {
        columnName = letters[(columnNumber - 1) % 26] + columnName;
        columnNumber = (columnNumber - 1) / 26;
    }

    return columnName;
}

Answer 2

I noticed that you keep asking questions but never mark anyone's reply as answer... dunno why people will want to help you if you keep that up...!!!

Answer 3

What about using Application.ActiveCell.get_Address(true, true, Excel.AlReferenceStyle.xlA1, missing, missing) and then parse the result string or use a RegEx to get the column heading?

I simply used:

string location = Application.ActiveCell.get_Address(true, true, Excel.AlReferenceStyle.xlA1, missing, missing);
string tokens = x.Split("$".ToCharArray());
MessageBox.Show(String.Format("Column {0}", result[0]));

Answer 4

The following is a complete method which gives you the corresponding alphabet for an integer value that is passed.

private String Number2String(int number, bool isCaps)
    {
        int number1 = number / 27;
        int number2 = number - (number1 * 26);
        if (number2 > 26)
        {
            number1 = number1 + 1;
            number2 = number - (number1 * 26);
        }
        Char a = (Char)((isCaps ? 65 : 97) + (number1 - 1));
        Char b = (Char)((isCaps ? 65 : 97) + (number2 - 1));
        Char c = (Char)((isCaps ? 65 : 97) + (number - 1));
        string d = String.Concat(a, b);
        if (number <= 26)
            return c.ToString();
        else
            return d;
    }