CODEWITHSUNDEEP
c#vstoexcel-2007
venkat
venkat

Reputation: 5738

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

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

Upvotes: 5

Views: 5406

Answers (6)

Neil Dunlop
Neil Dunlop

Reputation: 405

This works well in VBA by using a double replace, where R is a Single Cell Excel Range:

ColumnLetter = Replace(Replace(R.AddressLocal(ReferenceStyle:=1), "$", vbNullString), R.Row, vbNullString) It is based on the equivalent idea for use on a Worksheet. In a Cell Formula use this, it is even shorter:

=SUBSTITUTE(ADDRESS(1,COLUMN(M1),4),1,"")

This returns the letter M and works right up to Column XFD. The cell reference M1 can be any Range anywhere. The top left Column is returned for Ranges or more than one cell.

It gets the ADDRESS of the first Cell in the Column and then removes the trailing 1 by substituting a NullString for it. (The 4 in the ADDRESS makes sure that the Address is returned as a Relative Address, i.e. one without and $ signs in it.)

Thanks to barry houdini who set me off on the quest for a good answer to this.

Upvotes: 0

Ian
Ian

Reputation: 170

I use these two:

public string GetExcelColumn(int index)
{
    int quotient = index / 26;

    if (quotient > 0)
        return GetExcelColumn(quotient - 1) + (char)((int)'A' + (index % 26));
    else
        return "" + (char)((int)'A' + index);
}

static IEnumerable<string> GetExcelColumns()
{
    var alphabet = new string[]{""}.Union(from c in Enumerable.Range((int)'A', 26) select Convert.ToString((char)c));

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

Upvotes: 0

friend
friend

Reputation: 11

public static long GetColumnNumber(string columnName)
{
    int letterPos = 0;   
    long columnNumber = 0;
    for (int placeHolder = columnName.Length - 1; placeHolder >= 0; placeHolder--)
    {
        int currentSum = 1;
        for (int multiplier = 0; multiplier < placeHolder; multiplier++)
            currentSum *= 26;
        int letterValue = (int) columnName[letterPos];
        currentSum *= letterValue - 64;
        columnNumber += currentSum;
        if (letterPos != columnName.Length)
            letterPos++;
        //Console.WriteLine(((int)columnName[i]-64) + " = " + columnName[i]);
    }
        return columnNumber;
}

Upvotes: 1

SEVUDAS
SEVUDAS

Reputation: 1

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

Upvotes: 0

Ahmad
Ahmad

Reputation: 24907

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

Upvotes: 2

LukeH
LukeH

Reputation: 269548

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

Upvotes: 5

Related Questions