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...
-
Text Box Hint in c# Windows Form Application Text Box Hint in c# Windows Form Application While developing a windows form applicat...
-
File Versioning C# File versioning, saving file with unique file name in c# File versioning allows a user to have several versions of ...
-
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...
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