vue-element-admin Excel导出使用

介绍： vue-element-admin 中已经帮我们封装好的导入导出组件 ，我们直接拿过来使用就可以了。
            Excel 的导入导出都是依赖于js-xlsx来实现的。
            在 js-xlsx的基础上又封装了Export2Excel.js来方便导出数据。

1.下载必须包

npm install xlsx file-saver-S
npm install script-loader-S-D

2.下载或复制组件到自己项目

源代码地址

/* eslint-disable */import{ saveAs}from'file-saver'importXLSXfrom'xlsx'functiongenerateArray(table){var out=[];var rows= table.querySelectorAll('tr');var ranges=[];for(varR=0;R< rows.length;++R){var outRow=[];var row= rows[R];var columns= row.querySelectorAll('td');for(varC=0;C< columns.length;++C){var cell= columns[C];var colspan= cell.getAttribute('colspan');var rowspan= cell.getAttribute('rowspan');var cellValue= cell.innerText;if(cellValue!==""&& cellValue==+cellValue) cellValue=+cellValue;//Skip ranges
      ranges.forEach(function(range){if(R>= range.s.r&&R<= range.e.r&& outRow.length>= range.s.c&& outRow.length<= range.e.c){for(var i=0; i<= range.e.c- range.s.c;++i) outRow.push(null);}});//Handle Row Spanif(rowspan|| colspan){
        rowspan= rowspan||1;
        colspan= colspan||1;
        ranges.push({
          s:{
            r:R,
            c: outRow.length},
          e:{
            r:R+ rowspan-1,
            c: outRow.length+ colspan-1}});};//Handle Value
      outRow.push(cellValue!==""? cellValue:null);//Handle Colspanif(colspan)for(var k=0; k< colspan-1;++k) outRow.push(null);}
    out.push(outRow);}return[out, ranges];};functiondatenum(v, date1904){if(date1904) v+=1462;var epoch= Date.parse(v);return(epoch-newDate(Date.UTC(1899,11,30)))/(24*60*60*1000);}functionsheet_from_array_of_arrays(data, opts){var ws={};var range={
    s:{
      c:10000000,
      r:10000000},
    e:{
      c:0,
      r:0}};for(varR=0;R!= data.length;++R){for(varC=0;C!= data[R].length;++C){if(range.s.r>R) range.s.r=R;if(range.s.c>C) range.s.c=C;if(range.e.r<R) range.e.r=R;if(range.e.c<C) range.e.c=C;var cell={
        v: data[R][C]};if(cell.v==null)continue;var cell_ref=XLSX.utils.encode_cell({
        c:C,
        r:R});if(typeof cell.v==='number') cell.t='n';elseif(typeof cell.v==='boolean') cell.t='b';elseif(cell.vinstanceofDate){
        cell.t='n';
        cell.z=XLSX.SSF._table[14];
        cell.v=datenum(cell.v);}else cell.t='s';

      ws[cell_ref]= cell;}}if(range.s.c<10000000) ws['!ref']=XLSX.utils.encode_range(range);return ws;}functionWorkbook(){if(!(thisinstanceofWorkbook))returnnewWorkbook();this.SheetNames=[];this.Sheets={};}functions2ab(s){var buf=newArrayBuffer(s.length);var view=newUint8Array(buf);for(var i=0; i!= s.length;++i) view[i]= s.charCodeAt(i)&0xFF;return buf;}exportfunctionexport_table_to_excel(id){var theTable= document.getElementById(id);var oo=generateArray(theTable);var ranges= oo[1];/* original data */var data= oo[0];var ws_name="SheetJS";var wb=newWorkbook(),
    ws=sheet_from_array_of_arrays(data);/* add ranges to worksheet */// ws['!cols'] = ['apple', 'banan'];
  ws['!merges']= ranges;/* add worksheet to workbook */
  wb.SheetNames.push(ws_name);
  wb.Sheets[ws_name]= ws;var wbout=XLSX.write(wb,{
    bookType:'xlsx',
    bookSST:false,
    type:'binary'});saveAs(newBlob([s2ab(wbout)],{
    type:"application/octet-stream"}),"test.xlsx")}exportfunctionexport_json_to_excel({
  multiHeader=[],
  header,
  data,
  filename,
  merges=[],
  autoWidth=true,
  bookType='xlsx'}={}){/* original data */
  filename= filename||'excel-list'
  data=[...data]
  data.unshift(header);for(let i= multiHeader.length-1; i>-1; i--){
    data.unshift(multiHeader[i])}var ws_name="SheetJS";var wb=newWorkbook(),
    ws=sheet_from_array_of_arrays(data);if(merges.length>0){if(!ws['!merges']) ws['!merges']=[];
    merges.forEach(item=>{
      ws['!merges'].push(XLSX.utils.decode_range(item))})}if(autoWidth){/*设置worksheet每列的最大宽度*/const colWidth= data.map(row=> row.map(val=>{/*先判断是否为null/undefined*/if(val==null){return{'wch':10};}/*再判断是否为中文*/elseif(val.toString().charCodeAt(0)>255){return{'wch': val.toString().length*2};}else{return{'wch': val.toString().length};}}))/*以第一行为初始值*/let result= colWidth[0];for(let i=1; i< colWidth.length; i++){for(let j=0; j< colWidth[i].length; j++){if(result[j]['wch']< colWidth[i][j]['wch']){
          result[j]['wch']= colWidth[i][j]['wch'];}}}
    ws['!cols']= result;}/* add worksheet to workbook */
  wb.SheetNames.push(ws_name);
  wb.Sheets[ws_name]= ws;var wbout=XLSX.write(wb,{
    bookType: bookType,
    bookSST:false,
    type:'binary'});saveAs(newBlob([s2ab(wbout)],{
    type:"application/octet-stream"}),`${filename}.${bookType}`);}

3.导入并使用组件组件

直接在事件函数里使用：懒加载方法只有再触发事件后再去找封装好的组件。

import('@/vendor/Export2Excel').then(excel=>{
  excel.export_json_to_excel({
    header: tHeader,//表头 必填
    data,//具体数据 必填
    filename:'excel-list',//非必填
    autoWidth:true,//非必填
    bookType:'xlsx'//非必填})})

案例代码：

实际使用一般表头和内容都是要做转换：

<template><el-button type="danger" size="small" @click="exporExcel">excel导出</el-button><template><script>asynctransExcer(){const map={'id':'编号','password':'密码','mobile':'手机号','username':'姓名','timeOfEntry':'入职日期','formOfEmployment':'聘用形式','correctionTime':'转正日期','workNumber':'工号','departmentName':'部门','staffPhoto':'头像地址'}// 枚举const hireTypEnmu={1:'正式','2':'非正式'}// 枚举const res=awaitgetUserList(this.pageObj)
      console.log('excel转换需要的数据', res)const enKeys= Object.keys(res.data.rows[0])// 得到英文表头const header= enKeys.map(item=> map[item])// 得到要导出中文表头const data= res.data.rows.map(item=>{
        item.formOfEmployment= hireTypEnmu[item.formOfEmployment]return Object.values(item)})// 得到要导出的具体数据
      console.log(header, data)return{ header, data}},// 转换导出需要的数据asyncexporExcel(){const{ header, data}=awaitthis.transExcer()// 转换excel数据import('@/components/exportExcel/Export2Excel').then(excel=>{
        excel.export_json_to_excel({
          header,// 表头 必填
          data,// 具体数据 必填
          filename:'excel-list',// 非必填
          autoWidth:true,// 非必填
          bookType:'xlsx'// 非必填})})}// 导出excel</script>