<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>Document</title>
</head>
<body>
<!-- <button id="export">Export Sheet to XLSX</button> -->
<button onclick="downloadExl(jsono)">导出xlsx带样式</button>
<button id="export2">导出xlsx</button>
<!-- 使用js-xlsx将后端返回数据导出为excel(.xlsx格式) -->
<!-- 引入的js只能和下面相连的结合使用 使用时请注释掉138行以下-->
<script src="js/xlsx.core.min.js"></script>
<script>
document.getElementById("export2").onclick = function() {
// 导出多张表
// let data = { '基本信息': [{name: '李四', sex: 'nan', age: 12}], '成绩': [{class: '计算机', teacher: 'Mrs wang', score: 90}] };
// let columnHeaders = { '基本信息': ['name', 'sex', 'age'], '成绩': ['class', 'teacher', 'score']}
// outputXlsxFile(
// data,
// [{ wch: 50 }, { wch: 50 }, { wch: 10 }],
// "test-xlsx"
// );
// function outputXlsxFile(data, wscols, xlsxName) {
// let sheetNames = [];
// let sheetsList = {};
// const wb = XLSX.utils.book_new();
// console.log(wb)
// for (let key in data) {
// sheetNames.push(key);
// let columnHeader = columnHeaders[key] // 此处是每个sheet的表头
// let temp = transferData(data[key], columnHeader);
// console.log(temp)
// sheetsList[key] = XLSX.utils.aoa_to_sheet(temp);
// sheetsList[key]["!cols"] = wscols;
// }
// console.log(sheetsList)
// wb["SheetNames"] = sheetNames;
// wb["Sheets"] = sheetsList;
// XLSX.writeFile(wb, xlsxName + ".xlsx");
// }
// function transferData(data, columnHeader) {
// let content = [];
// content.push(columnHeader);
// data.forEach((item, index) => {
// let arr = [];
// columnHeader.map(column =>{
// arr.push(item[column]);
// })
// content.push(arr);
// });
// return content;
// }
// // 导出一张表
var sheetN = '我是表名字,不是导出文件名'
var data = [{name: '后台返回数据11', sex: 'women', age: 56},{name: '后台返回数据2', sex: 'nan', age: 12}]
var columnHeaders = ['name', 'sex', 'age']
outputXlsxFile(
data, // 数据
[{ wch: 50 }, { wch: 50 }, { wch: 10 }], // 列宽
"test-xlsx" // 导出文件名
);
function outputXlsxFile(data, wscols, xlsxName) {
let sheetsList = {};
console.log(XLSX.utils)
const wb = XLSX.utils.book_new();
let temp = transferData(data, columnHeaders);
temp.unshift(['我是表格上面的标题啊标题,不是表头不是表头'])
console.log(temp)
sheetsList[sheetN] = XLSX.utils.aoa_to_sheet(temp);
console.log(XLSX.utils.aoa_to_sheet(temp))
sheetsList[sheetN]["!merges"] = [{ //合并第一行数据,也就是表头[B1,C1,D1,E1]
s: {//s为开始
c: 0,//开始列
r: 0//开始取值范围
},
e: {//e结束
c: 4,//结束列
r: 0//结束范围
}
}];
sheetsList[sheetN]["!cols"] = wscols;
sheetsList[sheetN]["!rows"] = [{ hpx: 50 }];
// console.log(sheetsList)
// 官网写入样式api,但是引入xlsx.core.min.js没用
// sheetsList[sheetN]['A1'].s = { font: { sz: 14, bold: true, color: { rgb: "FFFFAA00" } }, fill: { bgColor: { indexed: 64 }, fgColor: { rgb: "FFFF00" } } }
wb["SheetNames"] = [sheetN];
wb["Sheets"] = sheetsList;
console.log(wb)
XLSX.writeFile(wb, xlsxName + ".xlsx",{defaultCellStyle: { font: {name: 'Arial', sz: '40'}}});
console.log(XLSX.utils)
function transferData(data, columnHeader) {
let content = [];
content.push(columnHeader);
data.forEach((item, index) => {
let arr = [];
columnHeader.map(column =>{
arr.push(item[column]);
})
content.push(arr);
});
return content;
}
}
}
</script>
<!-- 导出带样式 引入的js只能和下面相连的结合使用,注释掉135行以上-->
<!-- <script src="js/xlsx.full.min.js"></script> -->
<script>
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);
}
var jsono = [{
"id": 1, "合并的列头1": "数据11", "合并的列头2": "数据12", "合并的列头3": "数据13", "合并的列头4": "数据14",
}, {
"id": 2, "合并的列头1": "数据21", "合并的列头2": "数据22", "合并的列头3": "数据23", "合并的列头4": "数据24",
}];
const wopts = { bookType: 'xlsx', bookSST: true, type: 'binary', cellStyles: true };
function downloadExl(json, type) {
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
console.log(tmpdata)
tmpdata["B1"].s = { font: { sz: 14, bold: true, color: { rgb: "FFFFAA00" } }, fill: { bgColor: { indexed: 64 }, fgColor: { rgb: "FFFF00" } } };//<====设置xlsx单元格样式
tmpdata["!merges"] = [{
s: { c: 1, r: 0 },
e: { c: 4, r: 0 }
}];//<====合并单元格
var tmpWB = {
SheetNames: ['mySheet'], //保存的表标题
Sheets: {
'mySheet': Object.assign({},
tmpdata, //内容
{
'!ref': outputPos[0] + ':' + outputPos[outputPos.length - 1] //设置填充区域
})
}
};
console.log(XLSX.utils)
tmpDown = new Blob([s2ab(XLSX.write(tmpWB,
{ bookType: (type == undefined ? 'xlsx' : type), bookSST: false, type: 'binary' }//这里的数据是用来定义导出的格式类型
))], {
type: ""
});
saveAs(tmpDown, "这里是下载的文件名" + '.' + (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;
}
}
</script>
<!-- <script>
var ViewModel = function() {
/* use an array of arrays */
this.aoa = ko.observableArray([
[1,2],
[3,4]
]);
};
// var UserInfos = [
// { id: 1, name: "张三", age: "21", aclass: "1801" },
// { id: 2, name: "李四", age: "22", aclass: "1802" },
// { id: 3, name: "王五", age: "23", aclass: "1803" }
// ];
// var ViewModel = {};
// ViewModel = function ()
// {
// this.aoa = ko.observableArray(UserInfos);
// }
// ko.applyBindings(ViewModel);
var model = new ViewModel();
ko.applyBindings(model);
/* do an update to confirm KO was loaded properly */
// model.aoa([[1,2,3],[4,5,6]]);
// model.aoa.push([7,8,9]);
// model.aoa([[1,2,3],[4,5,6]]);
document.getElementById("export").onclick = function() {
var aoa = [
['姓名', '性别', '年龄', '注册时间'],
['张三', null, null, new Date()],
['李四', '女', 22, new Date()]
];
var sheet = XLSX.utils.aoa_to_sheet(aoa);
console.log(sheet)
// openDownloadDialog(sheet2blob(sheet), '导出.xlsx');
/* get array of arrays */
var data = model.aoa();
/* convert to worksheet */
var ws = XLSX.utils.aoa_to_sheet(aoa);
/* build new workbook */
var wb = XLSX.utils.book_new();
XLSX.utils.book_append_sheet(wb, ws, "Sheet1");
/* write file */
XLSX.writeFile(wb, "knockout.xlsx")
};
</script> -->
</body>
</html>
使用js-xlsx将后端返回数据导出为excel(.xlsx格式)
最新推荐文章于 2024-08-19 11:16:23 发布