vue+elementui表格前端导出excel以及自定义导出样式
项目遇到前端自己导出表格，我自己把后端给我的数据处理了一波，合并重复列啊，以及表头合并啊。可没想到导出竟然也要前端自己来弄，于是乎请教同事以及博客。学到了学到了。
1，装包
npm install --save xlsx file-savernpm install --save xlsx-style
2，引入
import FileSaver from "file-saver";import XLSX2 from "xlsx";import XLSX from "xlsx-style";
3，使用
页面效果：这个页面是根据数据，手动合并了一波的，自动合并重复列，以及表头的效果，所以导出还想要这个样子。
导出效果：
4，页面表格需要有id
<el-table:id="'mytable'":highlight-current-row="highlightCurrentRow!==false?true:false":data="tableDataWidhtOrder"ref="table"@select="select"@select-all="selectAll"@row-click="clickTr"@cell-click="clickTd"@selection-change="handleSelectionChange"@sort-change="sortChange":row-class-name="tableRowClassName":row-style="rowStyleCb":height="height":border="border"></el-table>
5，事件
setExport2Excel() {/* generate workbook object from table */var xlsxParam = {raw: true } //这个保证表格只进行解析 不做运算var wb = XLSX2.utils.table_to_sheet(document.querySelector("#mytable"),xlsxParam);//mytable为表格的id名// if(!wb['!merges']){//这个东西是当表格有合并的时候才会存在 并不能作为判断有无数据的标准// this.$message.warning('无法导出：报表无数据');// return// }for(var i = 0;i<11;i++){wb["!cols"][i]={wpx:130}}// 样式的文档地址// /package/xlsx-stylefor (const key in wb) {if(key.indexOf('!') === -1&&wb[key].v){wb[key].s ={font:{//字体设置sz:13,bold:false,color:{rgb:'000000'//十六进制，不带#}},alignment:{//文字居中horizontal:'center',vertical:'center',wrap_text:true},border: {// 设置边框top: {style: 'thin' },bottom: {style: 'thin' },left: {style: 'thin' },right: {style: 'thin' }}}}}var data = this.methods('addRangeBorder',wb['!merges'],wb) //合并项添加边框var filedata = this.methods('sheet2blob',data)this.methods('openDownloadDialog',filedata,this.todayTimeString + "-xxx报表.xlsx")},//为合并项添加边框addRangeBorder(range,ws){let arr = ["A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q","R","S","T","U","V","W","X","Y","Z"];if(range){range.forEach(item=>{let startColNumber = Number(item.s.r), endColNumber = Number(item.e.r);let startRowNumber = Number(item.s.c), endRowNumber = Number(item.e.c);const test = ws[arr[startRowNumber] + (startColNumber + 1)];for(let col = startColNumber ; col <= endColNumber ; col++){for(let row = startRowNumber; row <= endRowNumber ; row++){ws[arr[row] + (col + 1)] = test;}}})}return ws;},//将一个sheet转成最终的excel文件的blob对象，然后利用URL.createObjectURL下载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'};var wbout = XLSX.write(workbook, wopts);var blob = new Blob([s2ab(wbout)], {type: "application/octet-stream"}); // 字符串转ArrayBufferfunction 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;},openDownloadDialog(url, saveName) {if (typeof url == 'object' && url instanceof Blob) {url = URL.createObjectURL(url); // 创建blob地址}var aLink = document.createElement('a');aLink.href = url;aLink.download = saveName || ''; // HTML5新增的属性，指定保存文件名，可以不要后缀，注意，file:///模式下不会生效var 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);}
下面是一个vue的完整示例，自己的vue安装xlsx以及slsx-style可能会遇到一个报错，可能与安装或者cnpm镜像有关，修改他的源码
因为引入这个报错：
import XLSX from “xlsx-style”
报错如下：
This relative module was not found: ./cptable in ./node_modules/xlsx-style@0.8.13@xlsx-style/dist/cpexcel.js
解决方法：找到在\node_modules\xlsx-style\dist\cpexcel.js 807行 的
var cpt = require('./cpt' + 'able');
改成：
var cpt = cptable;
页面效果：
导出excel：
<template><div><button @click="setExport2Excel">导出</button><el-table:data="tableData3":id="'mytable'"borderstyle="width: 100%"><el-table-columnprop="date"label="门架"width="150"></el-table-column><el-table-columnprop="date"label="识别车型"width="150"></el-table-column><el-table-column label="车型不一致"><el-table-columnprop="province"label="问题车数量"width="120"></el-table-column><el-table-columnprop="city"label="差额"width="120"></el-table-column></el-table-column><el-table-column label="交易缺失"><el-table-columnprop="province"label="问题车数量"width="120"></el-table-column><el-table-columnprop="city"label="差额"width="120"></el-table-column></el-table-column><el-table-column label="清分缺失"><el-table-columnprop="province"label="问题车数量"width="120"></el-table-column><el-table-columnprop="city"label="差额"width="120"></el-table-column></el-table-column><el-table-columnprop="date"label="差额统计"width="150"></el-table-column><el-table-column label="配送信息"><el-table-columnprop="name"label="姓名"width="120"></el-table-column><el-table-column label="地址"><el-table-columnprop="province"label="省份"width="120"></el-table-column><el-table-columnprop="city"label="市区"width="120"></el-table-column></el-table-column></el-table-column></el-table></div></template><script>// import FileSaver from 'file-saver'import XLSX2 from "xlsx";import XLSX from "xlsx-style";export default {data() {return {tableData3: [{date: '-05-03',name: '王小虎',province: '上海',city: '普陀区',address: '上海市普陀区金沙江路 1518 弄',zip: 33}, {date: '-05-02',name: '王小虎',province: '上海',city: '普陀区',address: '上海市普陀区金沙江路 1518 弄',zip: 33}, {date: '-05-04',name: '王小虎',province: '上海',city: '普陀区',address: '上海市普陀区金沙江路 1518 弄',zip: 33}, {date: '-05-01',name: '王小虎',province: '上海',city: '普陀区',address: '上海市普陀区金沙江路 1518 弄',zip: 33}, {date: '-05-08',name: '王小虎',province: '上海',city: '普陀区',address: '上海市普陀区金沙江路 1518 弄',zip: 33}, {date: '-05-06',name: '王小虎',province: '上海',city: '普陀区',address: '上海市普陀区金沙江路 1518 弄',zip: 33}, {date: '-05-07',name: '王小虎',province: '上海',city: '普陀区',address: '上海市普陀区金沙江路 1518 弄',zip: 33}]}},methods:{setExport2Excel() {/* generate workbook object from table */var wb = XLSX2.utils.table_to_sheet(document.querySelector("#mytable"));//mytable为表格的id名if(!wb['!merges']){this.$message.warning('无法导出：报表无数据');return}for(var i = 0;i<11;i++){wb["!cols"][i]={wpx:130}}// 样式的文档地址// /package/xlsx-stylefor (const key in wb) {if(key.indexOf('!') === -1&&wb[key].v){wb[key].s ={font:{//字体设置sz:13,bold:false,color:{rgb:'000000'//十六进制，不带#}},alignment:{//文字居中horizontal:'center',vertical:'center',wrap_text:true},border: {// 设置边框top: {style: 'thin' },bottom: {style: 'thin' },left: {style: 'thin' },right: {style: 'thin' }}}}}var data = this.addRangeBorder(wb['!merges'],wb) //合并项添加边框var filedata = this.sheet2blob(data)this.openDownloadDialog(filedata,this.todayTimeString + "-xxx报表.xlsx")},//为合并项添加边框addRangeBorder(range,ws){let arr = ["A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q","R","S","T","U","V","W","X","Y","Z"];range.forEach(item=>{let startColNumber = Number(item.s.r), endColNumber = Number(item.e.r);let startRowNumber = Number(item.s.c), endRowNumber = Number(item.e.c);const test = ws[arr[startRowNumber] + (startColNumber + 1)];for(let col = startColNumber ; col <= endColNumber ; col++){for(let row = startRowNumber; row <= endRowNumber ; row++){ws[arr[row] + (col + 1)] = test;}}})return ws;},//将一个sheet转成最终的excel文件的blob对象，然后利用URL.createObjectURL下载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'};var wbout = XLSX.write(workbook, wopts);var blob = new Blob([s2ab(wbout)], {type: "application/octet-stream"}); // 字符串转ArrayBufferfunction 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;},openDownloadDialog(url, saveName) {if (typeof url == 'object' && url instanceof Blob) {url = URL.createObjectURL(url); // 创建blob地址}var aLink = document.createElement('a');aLink.href = url;aLink.download = saveName || ''; // HTML5新增的属性，指定保存文件名，可以不要后缀，注意，file:///模式下不会生效var 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);}}}</script>
