Bestcomy.net blog

Coding for funny
posts - 118, comments - 2920, trackbacks - 22, articles - 1

导航

公告

Excel列名转换

Posted on 2009-02-04 00:21 bestcomy 阅读(...) 评论(...) 编辑 收藏

最近工作中发现需要转换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

仅供参考