Thursday, 23 February 2017
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;
}
The above logic csn be implemented in any programming language just by changing the syntax.
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.
The above logic csn be implemented in any programming language just by changing the syntax.
Subscribe to:
Post Comments (Atom)
Search
Popular Posts
-
Troubleshooting Cisco VPN client Before starting troubleshooting, Let us see what VPN is and what it requires to perform its intended f...
-
Evolution-Mobile Phones With the development of portable technology,wireless communication has so evolved that (According to the announce...
-
File Versioning C# File versioning, saving file with unique file name in c# File versioning allows a user to have several versions of ...
-
Text Box Hint in c# Windows Form Application Text Box Hint in c# Windows Form Application While developing a windows form applicat...
-
Unable to set the Freeze Panes property of Window Class C# It is generally easy to resolve the compile time errors because the reason fo...
0 comments:
Post a Comment