一、环境准备
1.1 安装依赖
npm install xlsx
1.2 前端引入
import XLSX from "xlsx";
import XLSXD from "xlsx-style";
二、后端返回的数据格式
[
{
"k1":"v1",
"k2":"v2",
},
{
"k1":"v1",
"k2":"v2",
},
]
三、vue页面
<el-button type="primary" icon="el-icon-download" @click="exportExcel">导出Excel</el-button>
四、js方法
exportExcel(){
let params = {
sdate:this.pdate[0],
edate:this.pdate[1],
};
getData(params).then(response=>{
let bodyData = response.data.data;
setTimeout(function (){
that.export(bodyData);
},800)
});
}
export(bodyData){
let that = this;
let fileName='省间电力现货交易跟踪表.xlsx';
let sheetName = '省间电力现货交易跟踪表';
let headerData = getHeaderData();
let data = getData(headerData,bodyData);
let ws = XLSX.utils.json_to_sheet(header);
setStyle(ws);
sethHeaderStyle(ws);
setBodyStyle(ws,bodyData);
setTimeout(function (){
that.openDownloadDialog(
that.sheet2blob(ws, sheetName),fileName);
},200);
},
getHeaderData(){
let header = [
["列名11", "列名12", null, null, null, null, null, null, null, null, null],
["列名21", "列名22", null, null, null, null, null, null, null, null, null],
];
return header;
},
getData(header,data){
for(let row=0;row<data.length;row++){
for(let row2=0;row2< 子集合的长度 ;row2++){
header.push(
[
value1,
value2,
value3,
value4,
value5,
value6,
value7,
value8,
value9,
"",
""
]);
}
}
return header;
},
setStyle(ws){
let fontSize = 12;
let fontName = "宋体";
let fontBold = false;
let horizontal = "center";
let borderStyle = "thin";
let borderColor = "FF000000";
for (let index in ws) {
if (!index.startsWith("!")) {
ws[index].s = {
font: {
name: fontName,
sz: fontSize,
bold: fontBold
},
alignment: {
horizontal:horizontal ,
vertical:horizontal ,
wrapText: false
},
border: {
top: {
style: borderStyle,
color: {
rgb: borderColor
}
},
bottom: {
style: borderStyle,
color: {
rgb: borderColor
}
},
left: {
style: borderStyle,
color: {
rgb: borderColor
}
},
right: {
style: borderStyle,
color: {
rgb: borderColor
}
}
}
};
}
}
},
sethHeaderStyle(){
ws['!cols'] = [
{wch: 14}, {wch: 10}, {wch: 10}, {wch: 10}, {wch: 10},
{wch: 10}, {wch: 10}, {wch: 10}, {wch: 15}, {wch: 17},{wch: 8},{wch: 10}
];
ws['!merges'] = [
{s: {r: 0, c: 0}, e: {r: 1, c: 12}},
{s: {r: 2, c: 0}, e: {r: 2, c: 12}},
{s: {r: 3, c: 0}, e: {r: 4, c: 0}},
{s: {r: 3, c: 1}, e: {r: 3, c: 5}},
{s: {r: 3, c: 6}, e: {r: 3, c: 11}},
{s: {r: 3, c: 12}, e: {r: 4, c: 12}},
];
ws["A1"] = {
t:"s",
v:"A1单元格的值需要在这重新设置",
s:{
font:{
name:"宋体",
size:12,
bold:true,
},
alignment:{
horizontal:"center",
vertical:"center",
},
style: "thin",
color: {
rgb: "FF000000"
}
}
}
},
setBodyStyle(ws,bodyData){
ws['!merges'].push(
{
s: {r: 0, c: 0},
e: {r: 0, c: 0}
},
{
s: {r: 0, c: 0},
e: {r: 0, c: 0}
},
)
},
openDownloadDialog(url, saveName) {
if (window.navigator && window.navigator.msSaveOrOpenBlob) {
window.navigator.msSaveOrOpenBlob(url, saveName);
} else {
if (typeof url == "object" && url instanceof Blob) {
url = URL.createObjectURL(url);
}
let aLink = document.createElement("a");
aLink.href = url;
aLink.download = saveName || "";
let event;
if (window.MouseEvent) {
event = new MouseEvent("click");
} else {
event = document.createEvent("MouseEvents");
event.initMouseEvent("click", true, false, window, 0, 0, 0, 0, 0, false, false, false, false, 0, null);
}
aLink.dispatchEvent(event);
}
},
sheet2blob(sheet, sheetName) {
sheetName = sheetName || "sheet1";
var workbook = {
SheetNames: [sheetName],
Sheets: {}
};
workbook.Sheets[sheetName] = sheet;
var wopts = {
bookType: "xlsx",
bookSST: false,
type: "binary",
showGridLines: false
};
var wbout = XLSXD.write(workbook, wopts);
var blob = new Blob([s2ab(wbout)], {
type: "application/octet-stream"
});
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;
}
return blob;
}