tempLate里面的方法直接让表格加一行 template sheets

最近开发项目要求导出excel表格，之前用Jq做过导出。这次用vue，记录一下经过

装依赖并且引入

安装依赖（如果用vue-admin-template基础版本，需要自己安装）, 并且把 集成版 vendor以及下面的Export2Excel.js复制过来

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

//如果想自定义样式安装xlsx-style
npm install --save xlsx-style

注意安装xlsx-style启动可能会报错（按照下面方式修改即可）：
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找到 var cpt = require(’./cpt’ + ‘able’); 并改成 var cpt = cptable;

引入依赖

import { saveAs } from 'file-saver'
import XLSX from "xlsx-style";

修改Export2Excel文件

Export2Excel源文件 export_json_to_excel 方法

/* eslint-disable */
import { saveAs } from 'file-saver'
import XLSX from 'xlsx'
…………
export function export_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 = new Workbook(),
    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
        };
      }
      /*再判断是否为中文*/
      else if (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(new Blob([s2ab(wbout)], {
    type: "application/octet-stream"
  }), `${filename}.${bookType}`);
}

修改为：

/* eslint-disable */
import { saveAs } from 'file-saver'
import XLSX from "xlsx-style";  //修改引入样式
…………
export function export_json_to_excel({
  title = [], // 新增的参数，表格标题
  multiHeader = [],
  SheetNames = [] ,// 新增的参数，sheet名称
  header,
  data,
  filename,
  merges = [],
  autoWidth = true,
  bookType = 'xlsx'
} = {}) {
  /* original data */
  filename = filename || 'excel-list'
  data = [...data]
  var wb = new Workbook();
  data.forEach((item,index) => {
     let sheet = oneSheet(item,title[index],header[index],merges[index],autoWidth)
     wb.SheetNames.push(SheetNames[index])
     wb.Sheets[SheetNames[index]] = sheet
     setExcelStyle(sheet,data.length)
  })
  var wbout = XLSX.write(wb, {
    bookType: bookType,
    bookSST: false,
    type: 'binary'
  });
  saveAs(new Blob([s2ab(wbout)], {
    type: "application/octet-stream"
  }), `${filename}.${bookType}`);
}

//渲染一个sheet
function oneSheet(data,title,header,merges,autoWidth){
  data.unshift(header);
  //写死的二级标题以及倒数两行（空行和合计）
  data.unshift(['资料区间：2021-08-04~2021-08-05']); // 表格标题
  data.unshift([title]); // 表格标题
  data.push(['']); // 接一行
  data.push(['份数：4 金额：96911.76元 税额：12557.32元']); // 接一行
  //最后两行合并单元格
  let merges_prev_last = 'A' + String(data.length-1)+':M'+ String(data.length-1);
  let merges_last = 'A' + String(data.length)+':M'+ String(data.length);
  merges.push(merges_prev_last)
  merges.push(merges_last)
  //调用上面的合并单元格函数
  var 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
        };
      }
      /*再判断是否为中文*/
      else if (val.toString().charCodeAt(0) > 255) {
        return {
          'wch': val.toString().length * 2
        };
      } else {
        return {
          'wch': val.toString().length
        };
      }
    }))
    /*以主表第三行为初始值，因为我的第一行是表格标题，第二行是副标题时间，所以以主表第三行表头为初始值*/
    let result = colWidth[3];
    for (let i = 2; i < colWidth.length-1; i++) {  //从第二行（i=2）开始到倒数第二行（i<colWidth.length-1）进行比较，调整列宽
      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;
  }
  return ws;
}

//设置表格样式
function setExcelStyle(dataInfo,length){
    // 设置单元格框线
  const borderAll = {
    top: {
      style: "thin"
    },
    bottom: {
      style: "thin"
    },
    left: {
      style: "thin"
    },
    right: {
      style: "thin"
    }
  };
  // 给所有单元格加上边框，内容居中，字体，字号，标题表头特殊格式部分后面替换
  for (var i in dataInfo) {
    if (i == "!ref" || i == "!merges" || i == "!cols" || i == "!rows" || i == "A1"|| i == "A2"|| i == "A"+String(length+1) || i == "A"+String(length+2)) {  //第一行A1，第二行A2以及倒数第二行，倒数第一行不加border
      //不做处理
     } else {
      dataInfo[i + ""].s = {
        border: borderAll,
        alignment: {
          horizontal: "center",
          vertical: "center"
        },
        font: {
          name: "微软雅黑",
          sz: 10
        }
      };
    }
  }

  // 设置表格样式，数字不够可以再往后面加AA，AB……
  const arrabc = ["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"]

  // 给标题、表格描述信息、表头等部分加上特殊格式
  arrabc.some(function (v) {
    for (let j = 1; j < 4; j++) {
      const _v = v + j
      if (dataInfo[_v]) {
        dataInfo[_v].s = {};
        // 标题部分A1-Z1
        if (j == 1) { //A1第一行居中加粗
          dataInfo[v + j].s = {
            font: {
              name: "微软雅黑",
              sz: 11,
              color: {
                rgb: "000000"
              },
              bold: true,
              italic: false,
              underline: false
            },
            alignment: {
              horizontal: "center",
              vertical: "center"
            }
          };
        }else if (j == 2) { //A1第一行居左
          dataInfo[v + j].s = {
            font: {
              name: "微软雅黑",
              sz: 10,
              bold: false,
              italic: false,
              underline: false
            },
            alignment: {
              horizontal: "left",
              vertical: "center"
            }
          };
        } else { //表头  加背景加粗
          const fv = v + 3
          dataInfo[fv].s = {
            border: borderAll,
            font: {
              name: "微软雅黑",
              sz: 10,
              bold: true
            },
            alignment: {
              horizontal: "center",
              vertical: "center"
            },
            fill: {
              fgColor: {
                rgb: "f0f0f0"
              },
            },
          }
        }
      }
    }
  });
}

使用

列表数据

tableHeaderArray:[
        {label:"发票类型",prop:'fplx'},
        {label:"发票代码",prop:'fpdm'},
        {label:"发票号码",prop:'fphm'},
        {label:"购方名称",prop:'gfmc'},
        {label:"主要商品名称",prop:'zyspmc'},
        {label:"开票日期",prop:'kprq'},
        {label:"发票状态",prop:'fpzt'},
        {label:"金额（不含税）",prop:'jebhs'},
        {label:"税率",prop:'sl'},
        {label:"税额",prop:'se'},
        {label:"价税合计",prop:'sjhj'},
        {label:"明细状态",prop:'mxzt'},
       /* {label:"作废状态",prop:'zfzt'}, */
        {label:"凭证号",prop:'pzh'}
      ],
      tableData2: [{'fplx':'机动车销售统一发票','fpdm':'91130100061','fphm':'91130100061','gfmc':'XXXXX有限公司','zyspmc':'XX盘','kprq':'2021-06-23',
      'fpzt':'','jebhs':'136.223.332.00','sl':'13%','se':'332.00','sjhj':'332.00','mxzt':'有','zsxm':'货物','jsfs':'一般计税','jzjt':'一般项目','zfzt':'是','pzh':'--'},
      {'fplx':'机动车销售统一发票','fpdm':'91130100061','fphm':'91130100061','gfmc':'XXXXX有限公司','zyspmc':'XX盘','kprq':'2021-06-23',
      'fpzt':'','jebhs':'136.223.332.00','sl':'13%','se':'332.00','sjhj':'332.00','mxzt':'有','zsxm':'货物','jsfs':'一般计税','jzjt':'一般项目','zfzt':'是','pzh':'--'},
      {'fplx':'机动车销售统一发票','fpdm':'91130100061','fphm':'91130100061','gfmc':'XXXXX有限公司','zyspmc':'XX盘','kprq':'2021-06-23',
      'fpzt':'','jebhs':'136.223.332.00','sl':'13%','se':'332.00','sjhj':'332.00','mxzt':'有','zsxm':'货物','jsfs':'一般计税','jzjt':'一般项目','zfzt':'是','pzh':'--'},
      {'fplx':'机动车销售统一发票','fpdm':'91130100061','fphm':'91130100061','gfmc':'XXXXX有限公司','zyspmc':'XX盘','kprq':'2021-06-23',
      'fpzt':'','jebhs':'136.223.332.00','sl':'13%','se':'332.00','sjhj':'332.00','mxzt':'有','zsxm':'货物','jsfs':'一般计税','jzjt':'一般项目','zfzt':'是','pzh':'--'},
      {'fplx':'机动车销售统一发票','fpdm':'91130100061','fphm':'91130100061','gfmc':'XXXXX有限公司','zyspmc':'XX盘','kprq':'2021-06-23',
      'fpzt':'','jebhs':'136.223.332.00','sl':'13%','se':'332.00','sjhj':'332.00','mxzt':'有','zsxm':'货物','jsfs':'一般计税','jzjt':'一般项目','zfzt':'是','pzh':'--'}]
    }

点击导出按钮，执行以下方法

handleDownload() {
       this.downloadLoading = true
       import('@/vendor/Export2ExcelMoreSheet').then(excel => {
        const tHeader= this.tableHeaderArray.map((item,index) => {return item.label})
        const filterVal= this.tableHeaderArray.map((item,index) => {return item.prop})
        const data = [this.formatJson(filterVal, this.tableData2),this.formatJson(filterVal, this.tableData2),this.formatJson(filterVal, this.tableData2),this.formatJson(filterVal, this.tableData2),this.formatJson(filterVal, this.tableData2),this.formatJson(filterVal, this.tableData2),this.formatJson(filterVal, this.tableData2),this.formatJson(filterVal, this.tableData2)]
        const merges = [["A1:M1","A2:M2"],["A1:M1","A2:M2"],["A1:M1","A2:M2"],["A1:M1","A2:M2"],["A1:M1","A2:M2"],["A1:M1","A2:M2"],["A1:M1","A2:M2"],["A1:M1","A2:M2"]]; // 根据Excel确定要合并的单元格
        excel.export_json_to_excel({
          title:['销项发票数据','销项发票数据','销项发票数据','销项发票数据','销项发票数据','销项发票数据','销项发票数据','销项发票数据'],
          SheetNames:["普通发票","电子普通发票","普通发票卷票","专用发票","电子专用发票","收购发票","机动车销售统一发票","二手车销售统一发票"],
          header: [tHeader,tHeader,tHeader,tHeader,tHeader,tHeader,tHeader,tHeader],
          data,
          filename: '发表列表'+(this.formInline.timeValue?('-'+this.formInline.timeValue[0]+'~'+this.formInline.timeValue[1]):''),
          merges,
          autoWidth: true,
          bookType: 'xlsx'
        })
        this.downloadLoading = false
      })
    },

最后实现效果如下图所示：

以上，如有小伙伴问题和意见可以随时提出。

感谢以下博主提供思路：
在VUE中使用Export2Excel导出表格，导出多个sheetvue导出excel，Export2Excel样式修改，复杂表头，多级表头