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,使用
页面效果:这个页面是根据数据,手动合并了一波的,自动合并重复列,以及表头的效果,所以导出还想要这个样子。
《vue+elementui表格前端导出excel以及自定义导出样式》

导出效果:
《vue+elementui表格前端导出excel以及自定义导出样式》
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;

页面效果:
《vue+elementui表格前端导出excel以及自定义导出样式》

导出excel:
《vue+elementui表格前端导出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>
    原文作者:_小郑有点困了
    原文地址: https://blog.csdn.net/qq_44706619/article/details/111573194
    本文转自网络文章,转载此文章仅为分享知识,如有侵权,请联系博主进行删除。
点赞