具体代码见：
一：导入功能
读取excel的多个sheet数据
<script type="text/javascript" src="js/xlsx.core.min.js"></script>
<table style="width:80%;margin-left:10%;margin-top:30px;">
<td align="right" style="width: 50%;">
<h3 align="center">导入参赛名单：</h3>
</td>
<td align="left" style="width: 50%;">
<input type="file" id="uploadfile" onchange="importf(this);"/>
</td>
</table>
<div style="width:80%;margin-left:10%;margin-top:30px;">
<div style="width:30%;height:100%;float:left;">
<table id="txtArea1" style="width:100%;" cellspacing="0" cellpadding="0"> </table>
</div>
<div style="width:30%;height:100%;margin-left:5%;margin-right:5%;float:left;">
<table id="txtArea2" style="width:100%;" cellspacing="0" cellpadding="0"> </table>
</div>
<div style="width:30%;height:100%;float:left;">
<table id="txtArea3" style="width:100%;" cellspacing="0" cellpadding="0"> </table>
</div>
</div>js代码：
<script type="text/javascript">
// 遍历每张表读取
var persons1 = [];
var persons2 = [];
var persons3 = [];
function importf(obj) {
var wb;// 读取完成的数据
var f = obj.files[0];
var reader = new FileReader();
reader.onload = function(e) {
var data = e.target.result;
wb = XLSX.read(data, {
type : 'binary'
}); // 以二进制流方式读取得到整份excel表格对象
// 表格的表格范围，可用于判断表头是否数量是否正确
var fromTo = '';
// wb.SheetNames[0]是获取Sheets中第一个Sheet的名字
// wb.Sheets[Sheet名]获取第一个Sheet的数据JSON.stringify(XLSX.utils.sheet_to_json(wb.Sheets[wb.SheetNames[0]]));
for (var i = 0; i < wb.SheetNames.length; i++) {
if (wb.Sheets.hasOwnProperty(wb.SheetNames[i])) {
fromTo = wb.Sheets[wb.SheetNames[i]]['!ref']; // output: A1:D5
if(i==0){
persons1 = persons1.concat(XLSX.utils.sheet_to_json(wb.Sheets[wb.SheetNames[i]]));
}else if(i==1){
persons2 = persons2.concat(XLSX.utils.sheet_to_json(wb.Sheets[wb.SheetNames[i]]));
}else if(i==2){
persons3 = persons3.concat(XLSX.utils.sheet_to_json(wb.Sheets[wb.SheetNames[i]]));
}
//break; //如果只取第一张表，就加上这行
}
}
//document.getElementById("txtArea1").innerHTML= JSON.stringify(persons1)+JSON.stringify(persons2)+JSON.stringify(persons3);
appendTr("txtArea1",persons1);
appendTr("txtArea2",persons2);
appendTr("txtArea3",persons3);
};
reader.readAsBinaryString(f);
}
function appendTr(id, array){
if(array.length>0){
for(var i = 0; i < array.length; i++){
o=document.createElement("TR"); //<td align="center" width="20%" class="cx-nr"><%=orgname%></td>
// 所属公司
o1=document.createElement("TD");
o1.innerHTML=array[i].所属公司;
o1.setAttribute("align","center");
o1.setAttribute("width","50%");
o1.setAttribute("class","cx-nr");
// 参赛人员
o2=document.createElement("TD");
o2.innerHTML=array[i].参赛人员;
o2.setAttribute("align","center");
o2.setAttribute("width","50%");
o2.setAttribute("class","cx-nr1");
o.appendChild(o1);
o.appendChild(o2);
document.getElementById(id).appendChild(o);
}
}
}
</script>二：导出功能
var json;
var tmpdata;//?
var tmpdata1 = [];
var tmpdata2 = [];
var tmpdata3 = [];
var outputPos1;
var outputPos2;
var outputPos3;
var json1;
var json2;
var json3;
function getJson(num){
var tabLen = document.getElementById("jinji"+num);
var jsonStr = "[";
for (var i = 1; i < tabLen.rows.length; i++) {
jsonStr += '{"十六强":"' + tabLen.rows[i].cells[0].innerHTML + '","八强":"' + tabLen.rows[i].cells[1].innerHTML + '","四强":"' + tabLen.rows[i].cells[2].innerHTML + '"},'
}
jsonStr= jsonStr.substr(0, jsonStr.length - 1);
jsonStr += "]";
if(num==1) json1 = JSON.parse(jsonStr); //将json字符串转换为json数组
if(num==2) json2 = JSON.parse(jsonStr); //将json字符串转换为json数组
if(num==3) json3 = JSON.parse(jsonStr); //将json字符串转换为json数组
}
function getTmpdataOutputPos(num){
if(num==1){json=json1; tmpdata = json1[0]; json1.unshift({});}
if(num==2){json=json2; tmpdata = json2[0]; json2.unshift({});}
if(num==3){json=json3; tmpdata = json3[0]; json3.unshift({});}
//var tmpdata = json[0];
//json.unshift({});
var keyMap = []; //获取keys
for (var k in tmpdata) {
keyMap.push(k);
json[0][k] = k;
}
if(num==1){
tmpdata1 = [];//用来保存转换好的json
json.map((v, i) => keyMap.map((k, j) => Object.assign({}, {
v: v[k],
position: (j > 25 ? getCharCol(j) : String.fromCharCode(65 + j)) + (i + 1)
}))).reduce((prev, next) => prev.concat(next)).forEach((v, i) => tmpdata1[v.position] = {
v: v.v
});
outputPos1 = Object.keys(tmpdata1); //设置区域,比如表格从A1到D10
}else if(num==2){
tmpdata2 = [];//用来保存转换好的json
json.map((v, i) => keyMap.map((k, j) => Object.assign({}, {
v: v[k],
position: (j > 25 ? getCharCol(j) : String.fromCharCode(65 + j)) + (i + 1)
}))).reduce((prev, next) => prev.concat(next)).forEach((v, i) => tmpdata2[v.position] = {
v: v.v
});
outputPos2 = Object.keys(tmpdata2); //设置区域,比如表格从A1到D10
}else if(num==3){
tmpdata3 = [];//用来保存转换好的json
json.map((v, i) => keyMap.map((k, j) => Object.assign({}, {
v: v[k],
position: (j > 25 ? getCharCol(j) : String.fromCharCode(65 + j)) + (i + 1)
}))).reduce((prev, next) => prev.concat(next)).forEach((v, i) => tmpdata3[v.position] = {
v: v.v
});
outputPos3 = Object.keys(tmpdata3); //设置区域,比如表格从A1到D10
}
}
var tmpDown; //导出的二进制对象
function PromotionTeamToExcel(type) {
getJson(1); //女单
getJson(2); //男单
getJson(3); //混双
var uploadfile2 = document.getElementById("uploadfile2").value;
var filename = uploadfile2.substring(uploadfile2.lastIndexOf("\\")+1);
document.getElementById("hf").download=filename;
getTmpdataOutputPos(1); //json1
getTmpdataOutputPos(2); //json2
getTmpdataOutputPos(3); //json3
var tmpWB = {
SheetNames: ['女单晋级','男单晋级','混双晋级'], //保存的表标题
Sheets: {
'女单晋级': Object.assign({},
tmpdata1, //内容
{
'!ref': outputPos1[0] + ':' + outputPos1[outputPos1.length - 1] //设置填充区域
}),
'男单晋级': Object.assign({},
tmpdata2, //内容
{
'!ref': outputPos2[0] + ':' + outputPos2[outputPos2.length - 1] //设置填充区域
}),
'混双晋级': Object.assign({},
tmpdata3, //内容
{
'!ref': outputPos3[0] + ':' + outputPos3[outputPos3.length - 1] //设置填充区域
})
}
};
tmpDown = new Blob([s2ab(XLSX.write(tmpWB,
{bookType: (type == undefined ? 'xlsx':type),bookSST: false, type: 'binary'}//这里的数据是用来定义导出的格式类型
))], {
type: ""
}); //创建二进制对象写入转换好的字节流
var href = URL.createObjectURL(tmpDown); //创建对象超链接
document.getElementById("hf").href = href; //绑定a标签
document.getElementById("hf").click(); //模拟点击实现下载
setTimeout(function() { //延时释放
URL.revokeObjectURL(tmpDown); //用URL.revokeObjectURL()来释放这个object URL
}, 100);
}
function s2ab(s) { //字符串转字符流
var buf = new ArrayBuffer(s.length);
var view = new Uint8Array(buf);
for (var i = 0; i != s.length; ++i) view[i] = s.charCodeAt(i) & 0xFF;
return buf;
}
// 将指定的自然数转换为26进制表示。映射关系：[0-25] -> [A-Z]。
function getCharCol(n) {
let temCol = '',
s = '',
m = 0
while (n > 0) {
m = n % 26 + 1
s = String.fromCharCode(m + 64) + s
n = (n - m) / 26
}
return s
}补充1：合并单元格，但是设置单元格样式无效
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title></title>
</head>
<body>
<script src="http://oss.sheetjs.com/js-xlsx/xlsx.full.min.js"></script>
<script>
//如果使用 FileSaver.js 就不要同时使用以下函数
function saveAs(obj, fileName) {
//当然可以自定义简单的下载文件实现方式
var tmpa = document.createElement("a");
tmpa.download = fileName || "下载";
tmpa.href = URL.createObjectURL(obj); //绑定a标签
tmpa.click(); //模拟点击实现下载
setTimeout(function () { //延时释放
URL.revokeObjectURL(obj); //用URL.revokeObjectURL()来释放这个object URL
}, 100);
}
var json = [{ //测试数据
"分组": 1,//A
"所属公司": "aa",
"参赛人员": "bb"
}, {
"分组": 2,
"所属公司": "cc",
"参赛人员": "dd"
}, {
"分组": 3,
"所属公司": "ee",
"参赛人员": "ff"
}];
const wopts = { bookType: 'xlsx', bookSST: true, type: 'binary' };//这里的数据是用来定义导出的格式类型
function downloadExl(type) {
var wb = { SheetNames: ['Sheet1'], Sheets: {}, Props: {} };
//wb.Sheets['Sheet1'] = XLSX.utils.json_to_sheet(json);//通过json_to_sheet转成单页(Sheet)数据
json = XLSX.utils.json_to_sheet(json);
json["A2"] = { t: "s", v: "A组" };
json["!merges"] = [{//合并第一列数据[A2,A3,A4,A5]
s: {//s为开始
c: 0,//开始列
r: 1//开始行
}, e: {//e结束
c: 0,//结束列
r: 4//结束行
}
}];
wb.Sheets['Sheet1'] = json;
saveAs(
new Blob([s2ab(XLSX.write(wb, wopts))], { type: "application/octet-stream"}),
"这里是下载的文件名" + '.' + (wopts.bookType == "biff2" ? "xls" : wopts.bookType));
}
function s2ab(s) {
if (typeof ArrayBuffer !== 'undefined') {
var buf = new ArrayBuffer(s.length);
var view = new Uint8Array(buf);
for (var i = 0; i != s.length; ++i) view[i] = s.charCodeAt(i) & 0xFF;
return buf;
} else {
var buf = new Array(s.length);
for (var i = 0; i != s.length; ++i) buf[i] = s.charCodeAt(i) & 0xFF;
return buf;
}
}
</script>
<button onclick="downloadExl()">导出</button>
</body>
</html>补充2：合并单元格，可以设置单元格的宽度、居中、字体、颜色等
下载protobi/js-xlsx：https://github.com/protobi/js-xlsx
将js-xlsx-master\dist\xlsx.full.min.js放到js目录中
<script type="text/javascript" src="js/xlsx.full.min.js"></script>
<input type="button" onclick="PromotionTeamToExcel();" value="导出为Excel"/>var resGroup = ["A组","B组","C组","D组","E组","F组","G组","H组"];
function saveAs(obj, fileName) {
var tmpa = document.createElement("a");
tmpa.download = fileName || "下载";
tmpa.href = URL.createObjectURL(obj);
tmpa.click();
setTimeout(function () {
URL.revokeObjectURL(obj);
}, 100);
}
const wopts = { bookType: 'xlsx', bookSST: true, type: 'binary', cellStyles: true };
function PromotionTeamToExcel(type) {
var jsonStr = "[";
for (var i = 0; i < result.length; i++) {
jsonStr += '{"分组":"","所属公司":"' + $('#t'+i+'-1').text() + '","参赛人员":"' + $('#t'+i+'-2').text() + '"},'
}
jsonStr= jsonStr.substr(0, jsonStr.length - 1);
jsonStr += "]";
var json = JSON.parse(jsonStr); //将json字符串转换为json数组
var tmpdata = json[0];
json.unshift({});
var keyMap = []; //获取keys
for (var k in tmpdata) {
keyMap.push(k);
json[0][k] = k;
}
var tmpdata = [];//用来保存转换好的json
json.map((v, i) => keyMap.map((k, j) => Object.assign({}, {v: v[k], position: (j > 25 ? getCharCol(j) : String.fromCharCode(65 + j)) + (i + 1)
}))).reduce((prev, next) => prev.concat(next)).forEach((v, i) => tmpdata[v.position] = {v: v.v});
var outputPos = Object.keys(tmpdata); //设置区域,比如表格从A1到D10
//t = [[2,5],[6,9],[10,13],[14,17],[18,21],[22,25],[26,28],[29,31]];
//t = [[2,5],[6,9],[10,13],[14,17],[18,21],[22,24],[25,27],[28,30]];
//t = [[2,5],[6,9],[10,13],[14,17],[18,20],[21,23],[24,26],[27,29]];
//t = [[2,5],[6,9],[10,13],[14,16],[17,19],[20,22],[23,25],[26,28]];
//t = [[2,5],[6,9],[10,12],[13,15],[16,18],[19,21],[22,24],[25,27]];
//t = [[2,5],[6,8],[9,11],[12,14],[15,17],[18,20],[21,23],[24,26]];
var end = 0;
var t = [];
for(var i = 0; i < 8; i++){
// 分组
if(end < ((result.length%8)*4+1)){
end = 4*(i+1)+1;
t[i] = [end-3,end];
}else{
end = end+3;
t[i] = [end-2,end];
}
}
//列的宽度
tmpdata["!cols"] = [{wpx: 80}, {wpx: 160}, {wpx: 160}];
//合并单元格
var merges=[];
for(var i = 0; i < 8; i++){
merges.push({s: {c: 0, r: t[i][0]-1}, e: {c: 0, r: t[i][1]-1}});
}
tmpdata["!merges"] = merges;
//每个合并单元格的数据
for(var i = 0; i < 8; i++){
tmpdata["A"+t[i][0]] = { t: "s", v: resGroup[i], s: {alignment: {horizontal: "center", vertical: "center"} } };
}
//设置单元格居中显示
for(var i = 0; i < result.length; i++){
var ii=i+2;
tmpdata["B"+ii].s = {alignment: {horizontal: "center", vertical: "center"} };
tmpdata["C"+ii].s = {alignment: {horizontal: "center", vertical: "center"} };
}
//设置标题字体、加粗、居中
tmpdata["A1"].s = {font: { sz: 13, bold: true, }, alignment: {horizontal: "center", vertical: "center", wrap_text:true} };//<====设置xlsx单元格样式
tmpdata["B1"].s = {font: { sz: 13, bold: true, }, alignment: {horizontal: "center", vertical: "center", wrap_text:true} };//<====设置xlsx单元格样式
tmpdata["C1"].s = {font: { sz: 13, bold: true, }, alignment: {horizontal: "center", vertical: "center", wrap_text:true} };//<====设置xlsx单元格样式
var tmpWB = {
SheetNames: ['sheet1'], //保存的表标题
Sheets: {
'sheet1': Object.assign({}, tmpdata, {
'!ref': outputPos[0] + ':' + outputPos[outputPos.length - 1] //设置填充区域
})
}
};
tmpDown = new Blob([s2ab(XLSX.write(tmpWB, { bookType: (type == undefined ? 'xlsx' : type), bookSST: false, type: 'binary' }))], {type: ""});
saveAs(tmpDown, title + '.' + (wopts.bookType == "biff2" ? "xls" : wopts.bookType));
}
function getCharCol(n) {
let temCol = '',
s = '',
m = 0
while (n > 0) {
m = n % 26 + 1
s = String.fromCharCode(m + 64) + s
n = (n - m) / 26
}
return s;
}
function s2ab(s) {
if (typeof ArrayBuffer !== 'undefined') {
var buf = new ArrayBuffer(s.length);
var view = new Uint8Array(buf);
for (var i = 0; i != s.length; ++i) view[i] = s.charCodeAt(i) & 0xFF;
return buf;
} else {
var buf = new Array(s.length);
for (var i = 0; i != s.length; ++i) buf[i] = s.charCodeAt(i) & 0xFF;
return buf;
}
}
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
