Never underestimate the power of Passion!

Thursday, 23 February 2017

On 22:53 by Vardan Kumar in ,    1 comment
Return column alphabets of excel

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.

Column alphabets of excel  from column numbers in c#
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.








1 comment:

  1. When in search of one of the best slots, it is value wanting on the RTP. We have listed one of the best slots on this page already, with Mega Joker, Devil’s Delight and Book of 99 that includes. Having selected your 우리카지노 chosen on line casino, enter the site and spend a few minutes attending to grips with where issues are and what's on offer. This contains something from legal, finance, HR to the construction of our studios. Are you a talented and impressive person who is looking to grow professionally with a world staff be a part of us as we break new floor in one of many world’s most fast-moving industries. Embracing range by hiring personalities across any ethnicity, gender expression, and religion, Evolution is a worldwide enterprise hiring individuals that will elevate our model.

    ReplyDelete