Java Excel 列号数字与字母互相转换

我们在实现对Excel的导入导出的时候，往往需要准确的给用户提示信息，提示到具体的Excel的单元格，这里就需要对Excel的列号进行数字和字母的转换，今天正好用到这个需求，所以就写了一个demo，总结一下：

Java实现：

 1 package test;
 2 
 3 /**
 4  * Deal with Excel column indexToStr and strToIndex
 5  * @author Stephen.Huang
 6  * @version 2015-7-8
 7  * @see http://blog.csdn.net/u010571844
 8  */
 9 public class ExcelColumn {
10 
11     public static void main(String[] args) {
12         String colstr = "AA";
13         int colIndex = excelColStrToNum(colstr, colstr.length());
14         System.out.println("'" + colstr + "' column index of " + colIndex);
15 
16         colIndex = 26;
17         colstr = excelColIndexToStr(colIndex);
18         System.out.println(colIndex + " column in excel of " + colstr);
19 
20         colstr = "AAAA";
21         colIndex = excelColStrToNum(colstr, colstr.length());
22         System.out.println("'" + colstr + "' column index of " + colIndex);
23 
24         colIndex = 466948;
25         colstr = excelColIndexToStr(colIndex);
26         System.out.println(colIndex + " column in excel of " + colstr);
27     }
28 
29     /**
30      * Excel column index begin 1
31      * @param colStr
32      * @param length
33      * @return
34      */
35     public static int excelColStrToNum(String colStr, int length) {
36         int num = 0;
37         int result = 0;
38         for(int i = 0; i < length; i++) {
39             char ch = colStr.charAt(length - i - 1);
40             num = (int)(ch - 'A' + 1) ;
41             num *= Math.pow(26, i);
42             result += num;
43         }
44         return result;
45     }
46 
47     /**
48      * Excel column index begin 1
49      * @param columnIndex
50      * @return
51      */
52     public static String excelColIndexToStr(int columnIndex) {
53         if (columnIndex <= 0) {
54             return null;
55         }
56         String columnStr = "";
57         columnIndex--;
58         do {
59             if (columnStr.length() > 0) {
60                 columnIndex--;
61             }
62             columnStr = ((char) (columnIndex % 26 + (int) 'A')) + columnStr;
63             columnIndex = (int) ((columnIndex - columnIndex % 26) / 26);
64         } while (columnIndex > 0);
65         return columnStr;
66     }
67 }

测试结果：

‘AA’ column index of 27 
26 column in excel of Z 
‘AAAA’ column index of 18279 
466948 column in excel of ZNSN

参照来源【Stephen102】：https://blog.csdn.net/u010571844/article/details/46806265