Excel列名转换

最近工作中发现需要转换Excel列名，例如A列序号为0，Z列序号为25，ZB列则为27
发现字母列名实际为26进制，于是写了如下Helper Class来解决我的问题：

 1 
 2         public class ExcelColumnTranslator
 3         {
 4             private ExcelColumnTranslator()
 5             { 
 6             }
 7 
 8             public static int ToIndex(string columnName)
 9             {
10                 if (!Regex.IsMatch(columnName.ToUpper(), @"[A-Z]+"))
11                     throw new Exception("invalid parameter");
12                 int index = 0;
13                 char[] chars = columnName.ToUpper().ToCharArray();
14                 for (int i = 0; i < chars.Length; i++)
15                 {
16                     index += ((int)chars[i] - (int)'A' + 1) * (int)Math.Pow(26, chars.Length - i - 1);
17                 }
18                 return index - 1;
19             }
20 
21             public static string ToName(int index)
22             {
23                 if (index < 0)
24                     throw new Exception("invalid parameter");
25                 List<string> chars = new List<string>();
26                 do
27                 {
28                     if (chars.Count > 0) index--;
29                     chars.Insert(0, ((char)(index % 26 + (int)'A')).ToString());
30                     index = (int)((index - index % 26) / 26);
31                 } while (index > 0);
32                 
33                 return String.Join(string.Empty, chars.ToArray());
34             }
35         }

测试代码：

 
2             string[] cols = new string[] { "A", "AA", "AAA", "Z", "ZZ", "ZZZ", "ABC", "CBA", "XZB" };
3             for (int i = 0; i < cols.Length; i++)
4             {
5                 Console.WriteLine("{0} == {1}", cols[i], ExcelColumnTranslator.ToName(ExcelColumnTranslator.ToIndex(cols[i])));
6             }

测试输出：
A == A
AA == AA
AAA == AAA
Z == Z
ZZ == ZZ
ZZZ == ZZZ
ABC == ABC
CBA == CBA
XZB == XZB

仅供参考