自己写的SHEETJS中需要转EXCEL列的例子
<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>
