Vue实现导出excel

最近接到一个需求，要求在页面中导出excel表格，以前没有接触过，经过一番学习，也有所收获。 从网上查:前端如何导出excel表格，大致是有两种方式:

1. 后端制作好表格，甩给前端一个链接即可

2. 后端把表格数据传给前端，前端制作表格并导出

这里选择了第二种👀

导出单sheet

首先需要安装依赖:

npm install file-saver xlsx --save

npm install script-loader --save-dev

接下来需要两个js文件

1. Blob.js

/* eslint-disable */
/* Blob.js
 * A Blob implementation.
 * 2014-05-27
 *
 * By Eli Grey, http://eligrey.com
 * By Devin Samarin, https://github.com/eboyjr
 * License: X11/MIT
 *   See LICENSE.md
 */

/*global self, unescape */
/*jslint bitwise: true, regexp: true, confusion: true, es5: true, vars: true, white: true,
 plusplus: true */

/*! @source http://purl.eligrey.com/github/Blob.js/blob/master/Blob.js */

(function (view) {
  "use strict";

  view.URL = view.URL || view.webkitURL;

  if (view.Blob && view.URL) {
    try {
      new Blob;
      return;
    } catch (e) { }
  }

  // Internally we use a BlobBuilder implementation to base Blob off of
  // in order to support older browsers that only have BlobBuilder
  var BlobBuilder = view.BlobBuilder || view.WebKitBlobBuilder || view.MozBlobBuilder || (function (view) {
    var
      get_class = function (object) {
        return Object.prototype.toString.call(object).match(/^\[object\s(.*)\]$/)[1];
      }
      , FakeBlobBuilder = function BlobBuilder() {
        this.data = [];
      }
      , FakeBlob = function Blob(data, type, encoding) {
        this.data = data;
        this.size = data.length;
        this.type = type;
        this.encoding = encoding;
      }
      , FBB_proto = FakeBlobBuilder.prototype
      , FB_proto = FakeBlob.prototype
      , FileReaderSync = view.FileReaderSync
      , FileException = function (type) {
        this.code = this[this.name = type];
      }
      , file_ex_codes = (
        "NOT_FOUND_ERR SECURITY_ERR ABORT_ERR NOT_READABLE_ERR ENCODING_ERR "
        + "NO_MODIFICATION_ALLOWED_ERR INVALID_STATE_ERR SYNTAX_ERR"
      ).split(" ")
      , file_ex_code = file_ex_codes.length
      , real_URL = view.URL || view.webkitURL || view
      , real_create_object_URL = real_URL.createObjectURL
      , real_revoke_object_URL = real_URL.revokeObjectURL
      , URL = real_URL
      , btoa = view.btoa
      , atob = view.atob

      , ArrayBuffer = view.ArrayBuffer
      , Uint8Array = view.Uint8Array
      ;
    FakeBlob.fake = FB_proto.fake = true;
    while (file_ex_code--) {
      FileException.prototype[file_ex_codes[file_ex_code]] = file_ex_code + 1;
    }
    if (!real_URL.createObjectURL) {
      URL = view.URL = {};
    }
    URL.createObjectURL = function (blob) {
      var
        type = blob.type
        , data_URI_header
        ;
      if (type === null) {
        type = "application/octet-stream";
      }
      if (blob instanceof FakeBlob) {
        data_URI_header = "data:" + type;
        if (blob.encoding === "base64") {
          return data_URI_header + ";base64," + blob.data;
        } else if (blob.encoding === "URI") {
          return data_URI_header + "," + decodeURIComponent(blob.data);
        } if (btoa) {
          return data_URI_header + ";base64," + btoa(blob.data);
        } else {
          return data_URI_header + "," + encodeURIComponent(blob.data);
        }
      } else if (real_create_object_URL) {
        return real_create_object_URL.call(real_URL, blob);
      }
    };
    URL.revokeObjectURL = function (object_URL) {
      if (object_URL.substring(0, 5) !== "data:" && real_revoke_object_URL) {
        real_revoke_object_URL.call(real_URL, object_URL);
      }
    };
    FBB_proto.append = function (data/*, endings*/) {
      var bb = this.data;
      // decode data to a binary string
      if (Uint8Array && (data instanceof ArrayBuffer || data instanceof Uint8Array)) {
        var
          str = ""
          , buf = new Uint8Array(data)
          , i = 0
          , buf_len = buf.length
          ;
        for (; i < buf_len; i++) {
          str += String.fromCharCode(buf[i]);
        }
        bb.push(str);
      } else if (get_class(data) === "Blob" || get_class(data) === "File") {
        if (FileReaderSync) {
          var fr = new FileReaderSync;
          bb.push(fr.readAsBinaryString(data));
        } else {
          // async FileReader won't work as BlobBuilder is sync
          throw new FileException("NOT_READABLE_ERR");
        }
      } else if (data instanceof FakeBlob) {
        if (data.encoding === "base64" && atob) {
          bb.push(atob(data.data));
        } else if (data.encoding === "URI") {
          bb.push(decodeURIComponent(data.data));
        } else if (data.encoding === "raw") {
          bb.push(data.data);
        }
      } else {
        if (typeof data !== "string") {
          data += ""; // convert unsupported types to strings
        }
        // decode UTF-16 to binary string
        bb.push(unescape(encodeURIComponent(data)));
      }
    };
    FBB_proto.getBlob = function (type) {
      if (!arguments.length) {
        type = null;
      }
      return new FakeBlob(this.data.join(""), type, "raw");
    };
    FBB_proto.toString = function () {
      return "[object BlobBuilder]";
    };
    FB_proto.slice = function (start, end, type) {
      var args = arguments.length;
      if (args < 3) {
        type = null;
      }
      return new FakeBlob(
        this.data.slice(start, args > 1 ? end : this.data.length)
        , type
        , this.encoding
      );
    };
    FB_proto.toString = function () {
      return "[object Blob]";
    };
    FB_proto.close = function () {
      this.size = this.data.length = 0;
    };
    return FakeBlobBuilder;
  }(view));

  view.Blob = function Blob(blobParts, options) {
    var type = options ? (options.type || "") : "";
    var builder = new BlobBuilder();
    if (blobParts) {
      for (var i = 0, len = blobParts.length; i < len; i++) {
        builder.append(blobParts[i]);
      }
    }
    return builder.getBlob(type);
  };
}(typeof self !== "undefined" && self || typeof window !== "undefined" && window || this.content || this));

2. Export2Excel.js(上传不了，可自行下载)

将Blob.js和Export2Excel.js放在一个vendor的文件夹里，然后将vendor文件夹放在合适的地方，比如src/util/下,然后将vendor的路径配在webpack.base.conf.js下，并写上别名：

resolve: {
    extensions: ['.js', '.vue', '.json'],
    alias: {
      'vue$': 'vue/dist/vue.esm.js',
      '@': resolve('src'),
      "vendor": path.resolve(__dirname, '../src/util/vendor') // 加上这行代码
    }
  }

接下来就是具体的实现导出表格了

 <button @click="exportExcel">导出</button>

methods: {
    exportExcel() {
      require.ensure([], () => {
        const { export_json_to_excel } = require("vendor/Export2Excel");
        const tHeader = ["编号", "标题", "作者", "回顾", "时间"];
        const filterVal = [
          "id",
          "title",
          "author",
          "pageviews",
          "display_time"
        ];
        const list = [
          { id: 1, title: 2, author: 3, pageviews: 4, time: 5 },
          { id: 6, title: 7, author: 8, pageviews: 9, time: 10 },
          { id: 11, title: 12, author: 13, pageviews: 14, time: 15 }
        ]
        const data = this.formatJson(filterVal, list);
        export_json_to_excel(
          tHeader,
          data,
          "账单报表" + moment(new Date()).format("YYYYMMDDHHmmss")
        );
      });
    },
    formatJson(filterVal, jsonData) {
      return jsonData.map(v => filterVal.map(j => v[j]));
    }
  }

    这里我们可以看到主要是调用了Export2Excel.js里的export_json_to_excel方法,并且将tHeader(表格头部),以及将filterVal(头部字段)和l  ist(表格数据)通过formatJson方法映射后的数据传给了export_json_to_excel。

    这样点击按钮，表格就导出成功了😊！(moment为格式化时间的库，需要引入)

但是如何导出一张表格里面包含两个sheet呢？

导出多个sheet

  这时候需要对export_json_to_excel方法进行研究了

export function export_json_to_excel(th, jsonData, defaultTitle) {
  var data = jsonData;
  data.unshift(th);
  var ws_name = "SheetJS";
  var wb = new Workbook(), ws = sheet_from_array_of_arrays(data);
  /* 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' });
  var title = defaultTitle || '列表'
  saveAs(new Blob([s2ab(wbout)], { type: "application/octet-stream" }), title + ".xlsx")
}

    这里我们不妨将wb打印出来就一目了然了。

  我们发现SheetNames是一个数组，因此只需要往里面加其他sheet就可以实现一个表格多个sheet，同样的，Sheets是一个对象，而对象的每一条数据就是一个sheet。因此我们对export_json_to_excel进行改造一番。

export function export_json_to_excel(obj) {
  const { th, jsonData, defaultTitle, SheetName } = obj
  var wb = new Workbook();
  let ws = [];
  /* add worksheet to workbook */
   for (let i = 0; i < SheetName.length; i++) {
    var dataItem = jsonData[i];
    dataItem.unshift(th[i])
    ws.push(sheet_from_array_of_arrays(dataItem))
    wb.SheetNames.push(SheetName[i]);
    wb.Sheets[SheetName[i]] = ws[i]
  }
  var wbout = XLSX.write(wb, { bookType: 'xlsx', bookSST: false, type: 'binary' });
  var title = defaultTitle || '列表'
  saveAs(new Blob([s2ab(wbout)], { type: "application/octet-stream" }), title + ".xlsx")
}

因此在exportExcel方法也需要做相应改变

exportExcel() {
  require.ensure([], () => {
    const { export_json_to_excel } = require("vendor/Export2Excel");
    const tHeader1 = ["编号", "标题", "作者", "回顾", "时间"];
    const tHeader2 = ["学号", "科目", "成绩", "时间"];
    const filterVal1 = ["id", "title", "author", "pageviews", "display_time"];
    const filterVal2 = ["number", "type", "grade", "display_time"];
    const list1 = [
      { id: 1, title: 2, author: 3, pageviews: 4, time: 5 },
      { id: 6, title: 7, author: 8, pageviews: 9, time: 10 },
      { id: 11, title: 12, author: 13, pageviews: 14, time: 15 }
    ]
    const list2 = [
      { number: 1, type: 2, grade: 3, display_time: 5 },
      { number: 6, type: 7, grade: 8, display_time: 10 },
      { number: 11, type: 12, grade: 13, display_time: 15 }
    ]
    const data1 = this.formatJson(filterVal1, list1);
    const data2 = this.formatJson(filterVal2, list2);
    export_json_to_excel({
      th: [tHeader1, tHeader2], 
      jsonData: [data1, data2], 
      defaultTitle: '报表'+ moment(new Date()).format('YYYYMMDDHHmmss'), 
      SheetName: ['sheet1', 'sheet2']
    })
  });
},

看看效果：

修改表格样式

  似乎已经很完美了，但是作为一个合格的前端切图仔，表格是不是不太美观？

这里我们采用:

xlsx-style

引入时需要注意，直接npm完成后运行报错，可以将node_modules/xlsx-style/dist/xlsx.full.min.js复制到static下，然后再在index.html下引入就可以了。

从文档中可以看出

sheet['!ref']：表示所有单元格的范围，例如从A1到F8则记录为A1:F8；

sheet[!merges]：存放一些单元格合并信息，是一个数组，每个数组由包含s和e构成的对象组成，s表示开始，e表示结束，r表示行，c表示列；如：

sheet['!merges'] = [
  {
    s: {r: 0, c: 0},
    e: {r: 0, c: 2}
  },
  {
    s: {r: 5, c: 9},
    e: {r: 0, c: 9}
  }
];

分别表示单元格A1 - C1和A10 - F10的合并

对于每一个单元格也有一些属性：

t：表示内容类型，s表示string类型，n表示number类型，b表示boolean类型，d表示date类型，等等

v：表示原始值；

f：表示公式，如B2+B3；

h：HTML内容

w：格式化后的内容

r：富文本内容rich text

s:  单元格样式

这样要设置表格样式，就可以对单元格的s属性下手了

如果有说的不对的地方，欢迎大神指出哦😀,同时感谢以下文章的作者：

1.如何使用JavaScript实现纯前端读取和导出excel文件

2.js-xlsx 一个实用的js 导出列表插件

3.Vue项目中使用xlsx-style导出有格式的表格