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

迈不过友情╰ 2022-12-19 00:41 203阅读 0赞

在工作中Excel解析时，常常需要将列号的字母转换成对应的数字序号。

package test;
    
    
    public class ExcelColumn {
    
        public static void main(String[] args) {
            String colstr = "AA";
            int colIndex = excelColStrToNum(colstr, colstr.length());
            System.out.println("'" + colstr + "' column index of " + colIndex);
    
            colIndex = 26;
            colstr = excelColIndexToStr(colIndex);
            System.out.println(colIndex + " column in excel of " + colstr);
    
            colstr = "AAAA";
            colIndex = excelColStrToNum(colstr, colstr.length());
            System.out.println("'" + colstr + "' column index of " + colIndex);
    
            colIndex = 466948;
            colstr = excelColIndexToStr(colIndex);
            System.out.println(colIndex + " column in excel of " + colstr);
        }
    
        /**
         * Excel column index begin 1
         * @param colStr
         * @param length
         * @return
         */
        public static int excelColStrToNum(String colStr, int length) {
            int num = 0;
            int result = 0;
            for(int i = 0; i < length; i++) {
                char ch = colStr.charAt(length - i - 1);
                num = (int)(ch - 'A' + 1) ;
                num *= Math.pow(26, i);
                result += num;
            }
            return result;
        }
    
        /**
         * Excel column index begin 1
         * @param columnIndex
         * @return
         */
        public static String excelColIndexToStr(int columnIndex) {
            if (columnIndex <= 0) {
                return null;
            }
            String columnStr = "";
            columnIndex--;
            do {
                if (columnStr.length() > 0) {
                    columnIndex--;
                }
                columnStr = ((char) (columnIndex % 26 + (int) 'A')) + columnStr;
                columnIndex = (int) ((columnIndex - columnIndex % 26) / 26);
            } while (columnIndex > 0);
            return columnStr;
        }
    }

--------------------

[参考][Link 1]

[Link 1]: https://blog.csdn.net/u010571844/article/details/46806265