Sheet列号与数字之间的相互转换

一.概述

1. 在sheet中，sheet的列号为A、B。。。Z、AA、AB。。。

2. 但是有时我们想用数字表示列号，就需要字符串与数字之间相互转换

二.方法

1.位置：org.apache.poi.ss.util.CellReference

2.字符串转数字方法

/**     * takes in a column reference portion of a CellRef and converts it from     * ALPHA-26 number format to 0-based base 10.     * 'A' -> 0     * 'Z' -> 25     * 'AA' -> 26     * 'IV' -> 255     * @return zero based column index     */    public static int convertColStringToIndex(String ref) {        int retval=0;        char[] refs = ref.toUpperCase(Locale.ROOT).toCharArray();        for (int k=0; k<refs.length; k++) {            char thechar = refs[k];            if (thechar == ABSOLUTE_REFERENCE_MARKER) {                if (k != 0) {                    throw new IllegalArgumentException("Bad col ref format '" + ref + "'");                }                continue;            }            // Character is uppercase letter, find relative value to A            retval = (retval * 26) + (thechar - 'A' + 1);        }        return retval-1;    }

3.数字转字符串

 /**     * Takes in a 0-based base-10 column and returns a ALPHA-26     *  representation.     * eg column #3 -> D     */    public static String convertNumToColString(int col) {        // Excel counts column A as the 1st column, we        //  treat it as the 0th one        int excelColNum = col + 1;        StringBuilder colRef = new StringBuilder(2);        int colRemain = excelColNum;        while(colRemain > 0) {            int thisPart = colRemain % 26;            if(thisPart == 0) { thisPart = 26; }            colRemain = (colRemain - thisPart) / 26;            // The letter A is at 65            char colChar = (char)(thisPart+64);            colRef.insert(0, colChar);        }        return colRef.toString();    }