自己写的SHEETJS中需要转EXCEL列的例子

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

<html>
<head>
 
    <title>JS-XLSX Live Demo</title>
 
    <style>
        #drop {
            border: 2px dashed #bbb;
            -moz-border-radius: 5px;
            -webkit-border-radius: 5px;
            border-radius: 5px;
            padding: 25px;
            text-align: center;
            font: 20pt bold,"Vollkorn";
            color: #bbb
        }
 
        #b64data {
            width: 100%;
        }
 
        a {
            text-decoration: none
        }
 
        button {
        }
    </style>
 
    <script type="text/javascript" src="dist/xlsx.full.min.js"></script>
    <script type="text/javascript" src="dist/xlsx.core.min.js"></script>
 
    <script type="text/javascript" src="dist/jquery.min.js"></script>
 
</head>
 
<body>
<pre>
<input type="file" name="xlfile" id="xlf" onchange="importf(this)">
</pre>
<pre id="out"></pre>
    <br>
    <div id="demo"></div>
 
    <script>
            /*
                  FileReader共有4种读取方法：
                  1.readAsArrayBuffer(file)：将文件读取为ArrayBuffer。
                  2.readAsBinaryString(file)：将文件读取为二进制字符串
                  3.readAsDataURL(file)：将文件读取为Data URL
                  4.readAsText(file, [encoding])：将文件读取为文本，encoding缺省值为'UTF-8'
                               */
            var workbook;//读取完成的数据
            var rABS = false; //是否将文件读取为二进制字符串
 
            function importf(obj) {//导入
 
                if (!obj.files) {
                    return;
                }
                var f = obj.files[0];
                var reader = new FileReader();
                reader.onload = function (e) {
                    var data = e.target.result;
                    if (rABS) {
                        workbook = XLSX.read(btoa(fixdata(data)), {//手动转化
                            type: 'base64'
                        });
                    } else {
                        workbook = XLSX.read(data, {
                            type: 'binary'
                        });
                    }
                    // 循环页签取数
                    console.log("页签数量：" + workbook.SheetNames.length);
                    var showHtml = "";
 
                    for (var sheetNum = 0; sheetNum < Number(workbook.SheetNames.length); sheetNum++) {
 
                        var first_sheet_name = workbook.SheetNames[sheetNum]; // 获取工作簿中的工作表名字
                        console.log("workbook.SheetNames[0]" + first_sheet_name)
                        var address_of_cell = 'B1'; // 提供一个引用样式(单元格下标)
                        var address_of_cell2 = 'B2'; // 提供一个引用样式(单元格下标)
 
                        var worksheet = workbook.Sheets[first_sheet_name]; // 获取对应的工作表对象
                        //              console.log(worksheet['!ref']) // 获得该excel的内容范围 例如： A1:F30
 
 
                            var scope = worksheet['!ref'];
                            if (scope == undefined) {
                                console.log("文件格式有误！")
                                showHtml = showHtml + first_sheet_name + "页签解析有误，请检查格式是否正确！ <br />"
                                break;
                            } else {
                                showHtml = showHtml + first_sheet_name + "页签已解析完成 <br />"
                            }
                            // js截取某个字符串后面的内容：
                            scope = scope.match(/:(\S*)/)[1]; // 获取表格边界 例如：F30
                            console.log("aaaa====" + scope);
                            var reg = /[A-Z]/g;
                            char_scope = scope.match(reg); //
                            console.log("====bbb=======" + char_scope);
                            console.log("====bbb=======" + char_scope[0]);
 
                            var col_loop = 0;
                            var col_loop_2 = 1;//--第二个字母 例如AE列的E
                            var col_z = 1;
 
                            if (char_scope.length == 1) {
                                col_loop = 1;
                            }
                            //---正常情况下不会大于ZZ列
                            if (char_scope.length > 1) {
                                col_loop = char_scope[0].charCodeAt(0) - 64;
                                col_loop2 = char_scope[1].charCodeAt(0) - 64;
                            }
 
                            col_z = col_loop * 26 + col_loop2;
                            console.log("=====col_z======" + col_z);
 
 
                            // 用正则去除字符串种的数字,获取表格边界字母值  例如：F
                            var scopeLetter = scope.replace(/\d+/g, '');
                            console.log("=====ssss======" + scopeLetter.length);
                            // 用正则将非数字的去除,获取表格边界行值  例如：30
                            var scopeNum = scope.replace(/[^0-9]/ig, "");
                            console.log("==行数====" + Number(scopeNum));
 
                         
 
                        var letters;
 
                            for (var y = 0; y < col_z; y++) {
                                if (y <26) {
                                    console.log(String.fromCharCode(64 + (parseInt(y / 26) + 1 + (y % 26))));
                                }
                                if (y >= 26) {
 
                                    letters = (String.fromCharCode(64 + (parseInt(y / 26))));
                                    var letter2 = String.fromCharCode((64 - (parseInt(y / 26)) + (parseInt(y / 26) + 1 + (y % 26))));
                                    console.log(letters + letter2);
                                }
                                       
                            }
 
                         
                    }
                    document.getElementById("demo").innerHTML = showHtml;
                };
                if (rABS) {
                    reader.readAsArrayBuffer(f);
                } else {
                    reader.readAsBinaryString(f);
                }
            }
 
            function fixdata(data) { //文件流转BinaryString
                var o = "",
                    l = 0,
                    w = 10240;
                for (; l < data.byteLength / w; ++l) o += String.fromCharCode.apply(null, new Uint8Array(data.slice(l * w, l * w + w)));
                o += String.fromCharCode.apply(null, new Uint8Array(data.slice(l * w)));
                return o;
            }
    </script>
 
 
</body>
</html>

主要是转换 EXCEL 中的列，求对应的列数所对应的EXCEL列，例如 AA列对应的是27列，57列对应到BE列

留存作为记录

<html><head>
<title>JS-XLSX Live Demo</title>
<style> #drop { border: 2px dashed #bbb; -moz-border-radius: 5px; -webkit-border-radius: 5px; border-radius: 5px; padding: 25px; text-align: center; font: 20pt bold,"Vollkorn"; color: #bbb }
#b64data { width: 100%; }
a { text-decoration: none }
button { } </style>
<script type="text/javascript" src="dist/xlsx.full.min.js"></script> <script type="text/javascript" src="dist/xlsx.core.min.js"></script>
<script type="text/javascript" src="dist/jquery.min.js"></script>
</head>
<body><pre><input type="file" name="xlfile" id="xlf" onchange="importf(this)"></pre><pre id="out"></pre> <br> <div id="demo"></div>
<script> /* FileReader共有4种读取方法： 1.readAsArrayBuffer(file)：将文件读取为ArrayBuffer。 2.readAsBinaryString(file)：将文件读取为二进制字符串 3.readAsDataURL(file)：将文件读取为Data URL 4.readAsText(file, [encoding])：将文件读取为文本，encoding缺省值为'UTF-8' */ var workbook;//读取完成的数据 var rABS = false; //是否将文件读取为二进制字符串
function importf(obj) {//导入
if (!obj.files) { return; } var f = obj.files[0]; var reader = new FileReader(); reader.onload = function (e) { var data = e.target.result; if (rABS) { workbook = XLSX.read(btoa(fixdata(data)), {//手动转化 type: 'base64' }); } else { workbook = XLSX.read(data, { type: 'binary' }); } // 循环页签取数 console.log("页签数量：" + workbook.SheetNames.length); var showHtml = "";
for (var sheetNum = 0; sheetNum < Number(workbook.SheetNames.length); sheetNum++) {
var first_sheet_name = workbook.SheetNames[sheetNum]; // 获取工作簿中的工作表名字 console.log("workbook.SheetNames[0]" + first_sheet_name) var address_of_cell = 'B1'; // 提供一个引用样式(单元格下标) var address_of_cell2 = 'B2'; // 提供一个引用样式(单元格下标)
var worksheet = workbook.Sheets[first_sheet_name]; // 获取对应的工作表对象 //console.log(worksheet['!ref']) // 获得该excel的内容范围例如： A1:F30

var scope = worksheet['!ref']; if (scope == undefined) { console.log("文件格式有误！") showHtml = showHtml + first_sheet_name + "页签解析有误，请检查格式是否正确！ <br />" break; } else { showHtml = showHtml + first_sheet_name + "页签已解析完成 <br />" } // js截取某个字符串后面的内容： scope = scope.match(/:(\S*)/)[1]; // 获取表格边界例如：F30 console.log("aaaa====" + scope); var reg = /[A-Z]/g; char_scope = scope.match(reg); // console.log("====bbb=======" + char_scope); console.log("====bbb=======" + char_scope[0]);
var col_loop = 0; var col_loop_2 = 1;//--第二个字母例如AE列的E var col_z = 1;
if (char_scope.length == 1) { col_loop = 1; } //---正常情况下不会大于ZZ列 if (char_scope.length > 1) { col_loop = char_scope[0].charCodeAt(0) - 64; col_loop2 = char_scope[1].charCodeAt(0) - 64; }
col_z = col_loop * 26 + col_loop2; console.log("=====col_z======" + col_z);

// 用正则去除字符串种的数字,获取表格边界字母值例如：F var scopeLetter = scope.replace(/\d+/g, ''); console.log("=====ssss======" + scopeLetter.length); // 用正则将非数字的去除,获取表格边界行值例如：30 var scopeNum = scope.replace(/[^0-9]/ig, ""); console.log("==行数====" + Number(scopeNum));

var letters;
for (var y = 0; y < col_z; y++) { if (y <26) { console.log(String.fromCharCode(64 + (parseInt(y / 26) + 1 + (y % 26)))); } if (y >= 26) {
letters = (String.fromCharCode(64 + (parseInt(y / 26)))); var letter2 = String.fromCharCode((64 - (parseInt(y / 26)) + (parseInt(y / 26) + 1 + (y % 26)))); console.log(letters + letter2); }     }
    } document.getElementById("demo").innerHTML = showHtml; }; if (rABS) { reader.readAsArrayBuffer(f); } else { reader.readAsBinaryString(f); } }
function fixdata(data) { //文件流转BinaryString var o = "", l = 0, w = 10240; for (; l < data.byteLength / w; ++l) o += String.fromCharCode.apply(null, new Uint8Array(data.slice(l * w, l * w + w))); o += String.fromCharCode.apply(null, new Uint8Array(data.slice(l * w))); return o; } </script>

</body></html>