Conversion of letters and numbers-Excel column names

When using code to operate an Excel file (such as OpenXml), you can convert the column name to a number before performing computation and validation.

It is easy to locate a column by converting it into a number. The main functions of these two methods are as follows:

(1) convert letters into numbers, for example, 1 to A, AA to 27, and then process them;

(2) convert the number into A letter, A-> 1, 27-> AA ...... (This is common).

1. Letters to numbers

Thought: from the last digit of a string to the first digit, multiply by the power of 26, and add them in turn.

Algorithm: 26 ^ 0 * (last digit) + 26 ^ 1 * (previous digit) + ...... + 26 ^ n * (first ).

 1         private int MoreCharToInt(string value)
 2         {
 3             int rtn = 0;
 4             int powIndex = 0;
 5 
 6             for (int i = value.Length - 1; i >= 0; i--)
 7             {
 8                 int tmpInt = value[i];
 9                 tmpInt -= 64;
10 
11                 rtn += (int)Math.Pow(26, powIndex) * tmpInt;
12                 powIndex++;
13             }
14 
15             return rtn;
16         }

2. convert numbers into letters

Thought: the algorithm for numbers corresponding to letters is: 26 ^ 0 * A + 26 ^ 1 * ......,

According to this rule, each time divided by 26, we can get the value of each bit, and then convert it.

But there is a small problem, that is, if this character is 'Z', it will carry. After the conversion, You can process the carry value (Here is the key.).

 1         private string IntToMoreChar(int value)
 2         {
 3             string rtn = string.Empty;
 4             List<int> iList = new List<int>();
 5 
 6             //To single Int
 7             while (value / 26 != 0 || value % 26 != 0)
 8             {
 9                 iList.Add(value % 26);
10                 value /= 26;
11             }
12 
13             //Change 0 To 26
14             for (int j = 0; j < iList.Count - 1; j++)
15             {
16                 if (iList[j] == 0)
17                 {
18                     iList[j + 1] -= 1;
19                     iList[j] = 26;
20                 }
21             }
22 
23             //Remove 0 at last
24             if (iList[iList.Count - 1] == 0)
25             {
26                 iList.Remove(iList[iList.Count - 1]);
27             }
28 
29             //To String
30             for (int j = iList.Count - 1; j >= 0; j--)
31             {
32                 char c = (char)(iList[j] + 64);
33                 rtn += c.ToString();
34             }
35 
36             return rtn;
37         }

It took a while for the younger brother to come up with it. I hope it will help you!

In the future, I will share functions and methods as much as possible for you to discuss and get a better idea.