vue+elementui表格前端导出excel以及自定义导出样式

vue+elementui表格前端导出excel以及自定义导出样式
项目遇到前端自己导出表格，我自己把后端给我的数据处理了一波，合并重复列啊，以及表头合并啊。可没想到导出竟然也要前端自己来弄，于是乎请教同事以及博客。学到了学到了。
1，装包

npm install --save xlsx file-saver
npm 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}
    }
    // 样式的文档地址
    // https://www.npmjs.com/package/xlsx-style
    for (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"
    }); // 字符串转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;
  },
  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'"
      border
      style="width: 100%">
      <el-table-column
        prop="date"
        label="门架"
        width="150">
      </el-table-column>
      <el-table-column
        prop="date"
        label="识别车型"
        width="150">
      </el-table-column>
      <el-table-column label="车型不一致">
        <el-table-column
          prop="province"
          label="问题车数量"
          width="120">
        </el-table-column>
        <el-table-column
          prop="city"
          label="差额"
          width="120">
        </el-table-column>
      </el-table-column>
      <el-table-column label="交易缺失">
        <el-table-column
          prop="province"
          label="问题车数量"
          width="120">
        </el-table-column>
        <el-table-column
          prop="city"
          label="差额"
          width="120">
        </el-table-column>
      </el-table-column>
      <el-table-column label="清分缺失">
        <el-table-column
          prop="province"
          label="问题车数量"
          width="120">
        </el-table-column>
        <el-table-column
          prop="city"
          label="差额"
          width="120">
        </el-table-column>
      </el-table-column>
      <el-table-column
        prop="date"
        label="差额统计"
        width="150">
      </el-table-column>
      <el-table-column label="配送信息">
        <el-table-column
          prop="name"
          label="姓名"
          width="120">
        </el-table-column>
        <el-table-column label="地址">
          <el-table-column
            prop="province"
            label="省份"
            width="120">
          </el-table-column>
          <el-table-column
            prop="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: '2016-05-03',
          name: '王小虎',
          province: '上海',
          city: '普陀区',
          address: '上海市普陀区金沙江路 1518 弄',
          zip: 200333
        }, {
          date: '2016-05-02',
          name: '王小虎',
          province: '上海',
          city: '普陀区',
          address: '上海市普陀区金沙江路 1518 弄',
          zip: 200333
        }, {
          date: '2016-05-04',
          name: '王小虎',
          province: '上海',
          city: '普陀区',
          address: '上海市普陀区金沙江路 1518 弄',
          zip: 200333
        }, {
          date: '2016-05-01',
          name: '王小虎',
          province: '上海',
          city: '普陀区',
          address: '上海市普陀区金沙江路 1518 弄',
          zip: 200333
        }, {
          date: '2016-05-08',
          name: '王小虎',
          province: '上海',
          city: '普陀区',
          address: '上海市普陀区金沙江路 1518 弄',
          zip: 200333
        }, {
          date: '2016-05-06',
          name: '王小虎',
          province: '上海',
          city: '普陀区',
          address: '上海市普陀区金沙江路 1518 弄',
          zip: 200333
        }, {
          date: '2016-05-07',
          name: '王小虎',
          province: '上海',
          city: '普陀区',
          address: '上海市普陀区金沙江路 1518 弄',
          zip: 200333
        }]
      }
    },
    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}
        }
        // 样式的文档地址
        // https://www.npmjs.com/package/xlsx-style
        for (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"
        }); // 字符串转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;
      },
      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>