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

Question

How to find a column name or heading?

For example, if I select column 5 in excel, I want the result to be "E". How to get the alphabet or letter corresponding to column no.

Please help me with the code

Answer 1

How about using Application.ActiveCell.get_Address(true, true, Excel.AlReferenceStyle.xlA1, missing, missing) and then parse the result row or use RegEx to get the column header?

I just 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 2

 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 3

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

Answer 4

Below is the complete method that gives you the appropriate alphabet for the passed integer value.

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

Answer 5

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

Answer 6

This works well in VBA using double substitution, where R is the range of Excel single cells:

ColumnLetter = Replace(Replace(R.AddressLocal(ReferenceStyle:=1), "$", vbNullString), R.Row, vbNullString) It is based on an equivalent idea for use on a worksheet. In the cell formula, this is even shorter:

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

This returns the letter M and works right up to the XFD column. The reference to cell 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 trailing-1, replacing NullString for it. (4 in ADDRESS makes sure that the address is returned as a relative address, i.e. One without and $ characters in it.)

Thanks to barry houdini who put me in search of a good answer to this question.