最近工作中发现需要转换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 }
测试代码：
1
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
仅供参考