XLSX-前端导出实例

---

一、环境准备

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);
//    const wb = XLSX.utils.book_new(); //创建工作薄
    //将对象数组转换为sheet
    let ws = XLSX.utils.json_to_sheet(header); //将json转换为sheet
//    let ws = XLSX.utils.aoa_to_sheet(header);//将二维数组转换为sheet
    //设置全局样式
    setStyle(ws);
    //单独设置表头的样式（根据需要合并单元格）
    sethHeaderStyle(ws);
    //设置表数据的样式
    setBodyStyle(ws,bodyData);
    setTimeout(function (){
    	//将js对象转化为二进制，并弹出下载窗口
        that.openDownloadDialog(
        that.sheet2blob(ws, sheetName),fileName);
    },200);    
},
//获取表头数据，根据需要设置表头的列名，可以是对象数组，也可以是二维数组
getHeaderData(){
//	let header = [
//		{},
//		{}
//	];
	let header = [
    	["列名11", "列名12", null, null, null, null, null, null, null, null, null], 
    	// 特别注意合并的地方后面预留2个null
        ["列名21", "列名22", null, null, null, null, null, null, null, null, null],
	];
	return header; 
},
//将导出的数据添加到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}},//合并第0行到第1行，第0列到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}},
    ];
    //设置单元格A1的样式
    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}
		},
	)	
},
//打开下载dialog
openDownloadDialog(url, saveName) {
    if (window.navigator && window.navigator.msSaveOrOpenBlob) {
        //兼容IE
        window.navigator.msSaveOrOpenBlob(url, saveName);
    } else {
        //正常浏览器
        if (typeof url == "object" && url instanceof Blob) {
            url = URL.createObjectURL(url); // 创建blob地址
        }
        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);
    }
},
//excel格式的js对象转化为二进制
sheet2blob(sheet, sheetName) {
    sheetName = sheetName || "sheet1";
    var workbook = {
        SheetNames: [sheetName],
        Sheets: {}
    };
    workbook.Sheets[sheetName] = sheet; // 生成excel的配置项
    var wopts = {
        bookType: "xlsx", // 要生成的文件类型
        bookSST: false, // 是否生成Shared String Table，官方解释是，如果开启生成速度会下降，但在低版本IOS设备上有更好的兼容性
        type: "binary",
        showGridLines: false
    };
    var wbout = XLSXD.write(workbook, wopts);
    var blob = new Blob([s2ab(wbout)], {
        type: "application/octet-stream"
    });
    // 字符串转ArrayBuffer
    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;
}

---