获取Excel表格的列序号和列名

在处理Excel文件时，通常要获取xx列的数据，这就要求先找到列序号（从0开始）。

;(function(){

    var COL_ABCs=[
          "A", "B", "C", "D", "E", "F", "G", "H", "I", "J"
        , "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T"
        , "U", "V", "W", "X", "Y", "Z" 
    ];

    // 获取Excel列序号名称
    function _GetColumnNumberName(columnNumber){

        // 序号值是0-16383
        if (!(-1 < columnNumber && columnNumber < 16384))
        {
            throw "Excel列序号值必须在0-16383的区间（包含两端）";
        }
        var quyu26 = columnNumber % 26;
        var right0 = COL_ABCs[quyu26];

        if (columnNumber < 26) {
            return right0;
        }

        var right1 = "";
        var right2 = "";

        // 大于等于26，且小于 702
        if (columnNumber < 702)
        {
            var beishu01 = columnNumber * 1.0 / 26;
            var abcIndex1 =  Math.floor(beishu01) - 1;

            right1 = COL_ABCs[abcIndex1];
        }
        // 大于等于 702，且小于 16384
        if (columnNumber > 701) {
            var beishu02 = columnNumber * 1.0 / 676;// 676 == 26 * 26
            var abcIndex02 = Math.floor(beishu02) - 1;
            right2 = COL_ABCs[abcIndex02];

            var _beishu01 = (columnNumber - Math.floor(beishu02) * 676 ) * 1.0 / 26;
            var _abcIndex1 = Math.floor(_beishu01) - 1;

            right1 = COL_ABCs[_abcIndex1];

        }
        
        return right2 + right1 + right0;


    }
    
    // 获取Excel列序号
    function _GetColumnNumber(columnNumberName){


        columnNumberName = columnNumberName === undefined || columnNumberName === null ? "" : ( columnNumberName+"");
        columnNumberName = columnNumberName.toUpperCase();
        var nameLength  = columnNumberName.length;

        // 获取
        var columnNumberDic = {};
        var abcLen = COL_ABCs.length;
        for (var ind = 0; ind < abcLen; ind++) {
            columnNumberDic[COL_ABCs[ind]] = ind;
        }

        switch (nameLength) {
            case 1: {
                    return columnNumberDic[columnNumberName];
                };
            case 2:
                {
                    var str1 = columnNumberName[0]+"";
                    var str0 = columnNumberName[1]+"";

                    var _right1 = columnNumberDic[str1];

                    var right1 = (_right1 + 1) * 26;
                    var right0 = columnNumberDic[str0];

                    return right1 + right0;
                };
            case 3:
                {
                    var str2 = columnNumberName[0]+"";
                    var str1 = columnNumberName[1]+"";
                    var str0 = columnNumberName[2]+"";

                    var _right2 = columnNumberDic[str2];
                    var _right1 = columnNumberDic[str1];

                    var right2 = (_right2 + 1) * 676;
                    var right1 = (_right1 + 1) * 26;
                    var right0 = columnNumberDic[str0];

                    var _columnNumber = right2 + right1 + right0;

                    if (_columnNumber > 16383) {
                        throw "Excel列序号名称最大是XFD";
                    }

                    return _columnNumber;

                }
            default: {
                    throw "Excel列序号名称值必须是1到3位字符串";
                }
        };
        
    }

    // Excel列序号助手
    window.ExcelColumnNumberHelper={
        // 获取Excel列序号名称
        'GetColumnNumberName':_GetColumnNumberName,
        
        // 获取Excel列序号
        'GetColumnNumber':_GetColumnNumber
    };

}());