Return column alphabets of excel from column numbers in c#

Column alphabets of excel from column numbers in c# 

Today we'll discuss method logic to return the column alphabet from the column number as in Microsoft excel in c#. We'll build a generic logic so that it can be implemented in any programming language with change in syntax off course. 

For example

If we pass 5 as column number, our method should return E or if we pass 28 our function should return AB..... 

So what is the purpose for implementing such a logic? While making an excel sheet programatically, sometimes it is required to implement formulas in a specific cell of excel and respective column alphabetic name is to be listed in the formula expression. So using this logic we can implement formulas dynamically in excel cells.

For example

If we want to apply formula to cells present in column 1 to column 10 as column 1 uses its column members in expression and column 2 uses its members and so on then we'll loop through 1 to 10 and call our function each time incrementing  its argument by 1.

Excel Column Alphabets

Source Code:

while (count <=55 )

                    {

                        cname = get_name(count);

                        String form = "=" + "SUM(" + cname + "8" + "," + cname + "17" + "," + cname + "53" + "," + cname + "62" + "," + cname + "71" + "," + cname + "80" + "," + cname + "89" + ")";

                        String form1 = "=" + "SUM(" + cname + "9" + "," + cname + "18" + "," + cname + "54" + "," + cname + "63" + "," + cname + "72" + "," + cname + "81" + "," + cname + "90" + ")";

                        ExcelApp.Cells[92, count].Formula = form;

                        ExcelApp.Cells[93, count].Formula = form1;

                       count++;

                    }

 private String get_name(int c_no)

        {

            int div = c_no;

            string columnName = String.Empty;

            int mod;

            while (div > 0)

            {

                mod = (div - 1) % 26;

                columnName = Convert.ToChar(65 + mod).ToString() + columnName;

                div = (int)((div - mod) / 26);

            }

            return columnName;

        }

Now let us try to understand the logic:

1. We'll send column no. as 1 to 55 as arguments to our method off course one bt one hence we will make th function call in a loop and catch the return value in a string identifier.

2. Now let us try to understand exhaustively, suppose our method got the argument "28".

3. So c_no(method local variable) got the value 28.

4. div is assigned value of c_no. i.e 28.

5. A string is initialized with name columnName along with an integer variable declared as mod.

6. Now div value is 28 hence we'll loop till it is greater than 0.

•   Now inside the loop block we'll assign mod value as (div-1)%26(A-Z) i.e mod will be 1

•  Column name will be assigned Ascii value of 65+mod, concatenated with column name retrieved in previous loop traversal i.e column name will be B(ascii value of 66).

• div will be (int)(div-mod)/26 i.e 1 which is greater than zero and hence the loop continues with div as 1.

•  Now inside the loop block we'll assign mod value as (div-1)%26(A-Z) i.e mod will be 0

•  Column name will be assigned Ascii value of 65+mod, concatenated with column name retrieved in previous loop traversal i.e column name will be A(ascii value of 65)+B(previous column name)=AB.

• div will be (int)(div-mod)/26 i.e 0(Type casting to integer) and since it is not greater than 0,hence the loop exits.

7. After we exit the while loop column name(AB)is returned and hence can be pocketed in a pocket variable and can be used for whatever purpose it is intended for.

The above logic csn be implemented in any programming language just by changing the syntax.