vue前端实现导出excel功能组件

1.安装exceljs

cnpm install exceljs@4.2.1 --save

npm安装很难成功

2.组件代码

<template>
    <div style="background-color: skyblue;width: 100px;height: 50px;
    line-height: 50px;text-align: center;cursor: pointer;" @click="exportExcel">导出</div>
</template>
<script>
import Excel from 'exceljs'
export default {
  props: {
    exportExcelParams: {
      default: {
        data: [],
        fileName: 'file',
        header: [],
        sheetName: 'sheet1',
        imageKeys: [],
        creator:'me',
        lastModifiedBy:'her',
      },
      required: true,
    }
  },
  mounted () {

  },
  methods: {
    initWorkbook() {
      const workbook = new Excel.Workbook();
      // 设置属性
      const { creator = 'demo', lastModifiedBy = 'demo' } = this.exportExcelParams
      workbook.creator = creator;
      workbook.lastModifiedBy = lastModifiedBy;
      workbook.created = new Date();
      workbook.modified = new Date();
      workbook.lastPrinted = new Date();
      // 设置打开时候的视图
      workbook.views = [
        {
            x: 0,
            y: 0,
            width: 10000,
            height: 20000,
            firstSheet: 0,
            activeTab: 1,
            visibility: 'visible'
        }
      ]
      return workbook;
    },
    setWorksheet(worksheet, header, data) {
      //  Excel行高所使用单位为磅，列宽使用单位为0.1英寸
      //  行高：1毫米＝2.7682个单百位，1厘米＝27.682个单位（磅）；1个单位＝0.3612毫米
      //  列宽：1毫米＝0.4374个单位，1厘米＝4.374 个单位（英寸*0.1）；1个单位＝2.2862毫米
      //  单位（磅） 96像素/英寸,则 96 像素 = 1 英寸 = 2.54 厘米；  1像素 = 1/96*2.54*27.682磅
      let rowHeight = Math.ceil(108/96*2.54*27.682) + 1;

      worksheet.state = 'visible';
      //  设置列
      worksheet.columns = header;
      for (let i = 1; i <= header.length; i++) {
          worksheet.getColumn(i).alignment = { vertical: 'middle', horizontal: 'center' };
          worksheet.getColumn(i).font = { name: 'Arial Unicode MS' };
      }

      //  设置行
      worksheet.addRows(data);
      // 设置表头样式
      worksheet.getRow(1).font = { name: 'Arial Unicode MS', family: 4, size: 13 };
      worksheet.getRow(1).fill = { type: 'pattern', pattern: 'solid', fgColor: { argb: 'FF8DB4E2' } }
      data.forEach((item, i) => {
        worksheet.getRow(i + 2).height = rowHeight
      })
    },
    async exportExcel() {
      const workbook = this.initWorkbook();
      const { sheetName = 'sheet1' } = this.exportExcelParams
      const worksheet = workbook.addWorksheet(sheetName);
      const { imageKeys = [], data = [], header = [] } = this.exportExcelParams
      const getIndexByKey = (arr, key) => {
          for (let i = 0; i < arr.length; i++) {
              const element = arr[i];
              if (element.key == key) {
                  return i
              }
          }
      }
      let imageList = imageKeys.map(key => {
        return data.map((item, index) => {
          return {
            key,
            url: item[key],
            col: getIndexByKey(header, key) + 1,
            row: index + 2,
            width: 164,
            height: 108
          }
        })
      })
      this.setWorksheet(worksheet, header, data);
      //  图片处理
      await Promise.all(imageList.reduce((arr, list) => {
        return arr.concat(list.map(item => {
          return this.getImageId(workbook, item.url).then(imageId => {
            worksheet.getRow(item.row).getCell(item.key).value = ''; 
            worksheet.addImage(imageId, {
                tl: { col: item.col - 1, row: item.row - 1},
                ext: { width: item.width, height: item.height}
            });
          })
        }))
      }, []))
      let bufferContent = await workbook.xlsx.writeBuffer();
      this.saveFile(bufferContent)
    },
    getImageId(workbook, url) {
      return this.getBase64(url).then(res => {
        const imageId = workbook.addImage({
            base64: res.data.url,
            extension: res.data.type.split('/')[1],
        });
        return imageId
      })
    },
    fileAsBase64(file) {
      return new Promise((resolve, reject) => {
        let reader = new FileReader();
        reader.readAsDataURL(file);
        reader.onload = function (e) {
          resolve({
            code: 1, 
            data: {
              name: file.name,
              type: file.type,
              originSize: file.size,
              url: e.target.result
            }
          })
        }
        reader.onerror = function () {
          reject({code: 0, msg : 'can not readFile'})
        }
      })
    },
    getStaticFile(url, type = '') {
      return new Promise((resolve, reject) => {
        let xhr = this.createXMLHttp();
        xhr.onload = function () {
          if (this.response) {
              resolve({code: 1, data: this.response})
          } else {
              reject({code: 0, data: url, msg: '服务器所返回的类型和你所设置的返回值类型不兼容'})
          }
        }
        xhr.onerror = function (error) {
          reject(error)
        }
        xhr.open('GET', url, true);
        xhr.responseType = type;
        xhr.send();
      })
    },
    saveFile(file) {
      const { fileName = 'file' } = this.exportExcelParams
      let ieKit = /(?:ms|\()(ie)\s([\w\.]+)|trident|
      (edge|edgios|edga|edg)/i.test(window.navigator.userAgent);
      let blobData = new Blob([file], {type:
      'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'});
      if (ieKit) {
        navigator.msSaveBlob && navigator.msSaveBlob(blobData, fileName)
      } else {
        let objectURL = window.URL.createObjectURL(blobData),
        save_link = document.createElementNS('http://www.w3.org/1999/xhtml', 'a'),
        event = document.createEvent('MouseEvents');
        save_link.href = objectURL;
        save_link.download = fileName;
        event.initMouseEvent('click', true, false, window, 0, 0, 0, 0, 0, false, 
        false, false, false, 0, null);
        save_link.dispatchEvent(event)
        window.URL.revokeObjectURL(objectURL);
      }
    },

    getBase64(url, type = 'blob') {
      return this.getStaticFile(url, type).then(res => this.fileAsBase64(res.data))
    },

    createXMLHttp() {
      let xmlhttp;
      try {
        xmlhttp = new XMLHttpRequest(); //尝试创建 XMLHttpRequest 对象，
        除 IE 外的浏览器都支持这个方法。
      } catch (e) {
        try {
          xmlhttp = ActiveXobject('Msxml12.XMLHTTP'); //使用较新版本的
          IE 创建 IE 兼容的对象（Msxml2.XMLHTTP）。
        } catch (ex) {
          try {
            xmlhttp = ActiveXobject('Microsoft.XMLHTTP'); //使用较老版本的 
            IE 创建 IE 兼容的对象（Microsoft.XMLHTTP）。
          } catch (failed) {
            xmlhttp = false; //如果失败了还保持false
          }
        }
      }
      return xmlhttp;
    }
  }
}
</script>

3.使用

 data() {
    return {
      exportExcelParams: {
        header: [
          { header: 'Id', key: 'id', width: 10 },
          { header: 'avatar', key: 'avatar', type: 'image', width: Math.ceil(164/96*2.54*4.374) + 2},
          { header: 'cover', key: 'cover', type: 'image', width: Math.ceil(164/96*2.54*4.374) + 2},
          { header: 'D.O.B.', key: 'DOB', width: 10 }
        ],
        data: [
          { id: 1, avatar: 'http://p0.meituan.net/tuanpic/3df525af5a3f7fe04077567d2a6caf794904.png', 
          cover: 'http://p0.meituan.net/tuanpic/3df525af5a3f7fe04077567d2a6caf794904.png', 
          DOB: 'dddddd' },
          { id: 2, avatar: 'http://p0.meituan.net/tuanpic/3df525af5a3f7fe04077567d2a6caf794904.png', 
          cover: 'http://p0.meituan.net/tuanpic/3df525af5a3f7fe04077567d2a6caf794904.png', 
          DOB: 'dddddd1' },
          { id: 3, avatar: 'http://p0.meituan.net/tuanpic/3df525af5a3f7fe04077567d2a6caf794904.png',
          cover: 'http://p0.meituan.net/tuanpic/3df525af5a3f7fe04077567d2a6caf794904.png',
          DOB: 'dddddd2' },
          { id: 4, avatar: 'http://p0.meituan.net/tuanpic/3df525af5a3f7fe04077567d2a6caf794904.png',
          cover: 'http://p0.meituan.net/tuanpic/3df525af5a3f7fe04077567d2a6caf794904.png',
          DOB: 'dddddd3' },
          { id: 5, avatar: 'http://p0.meituan.net/tuanpic/3df525af5a3f7fe04077567d2a6caf794904.png',
          cover: 'http://p0.meituan.net/tuanpic/3df525af5a3f7fe04077567d2a6caf794904.png',
          DOB: 'dddddd4' },
          { id: 6, avatar: 'http://p0.meituan.net/tuanpic/3df525af5a3f7fe04077567d2a6caf794904.png',
          cover: 'http://p0.meituan.net/tuanpic/3df525af5a3f7fe04077567d2a6caf794904.png', 
          DOB: 'dddddd5' },
        ],
        imageKeys: ['avatar', 'cover'],
        sheetName: '小橘子的sheet',
        fileName: '小橘子的file',
      }
    }
  },

4.显示

5.服务端实现导出

参考: node.js 服务端实现excel导出以及vue前端本地保存文件并导出