Vue 下载 Excel 文件

Vue 下载 Excel 文件

Vue前端将List列表下载为Excel文件

安装依赖包

首先前端将List列表生成Excel文件需要下载几个依赖包。

npm install -S file-saver（生产依赖，则为-s）

npm install -S xlsx

npm install -D script-loader （开发依赖，则为-d）

引入文件

在 src 目录下面新建一个文件夹，例如 vendor，然后把：Blob.js 和 Export2Excel.js 两个文件放在该文件夹下，在Export2Excel.js 文件中我们引入。这两个文件在文末我会直接贴出来。

require('script-loader!file-saver'); // 保存文件

require('script-loader!@/vendor/Blob'); // 转二进制

下面这步好像不写也可以

由于这几个文件不支持 import 引入，所以我们需要 script-loader 来将他们挂载到全局环境下

import XLSX from 'xlsx' // xlsx核心

使用

然后就可以在我们需要的地方将List转成文件下载下来。

比如我有一个 List 数组 mData：

data() {
      return {
        mData: [
          {
            name: 'wjw',
            age: 18,
            sex: '1'
          }, {
            name: 'tcz',
            age: 22,
            sex: '1'
          }, {
            name: 'zx',
            age: 23,
            sex: '1'
          }, {
            name: 'qy',
            age: 21,
            sex: '1'
          }, {
            name: 'lxh',
            age: 22,
            sex: '1'
          },
        ]
      }
    },

然后有一个按钮，点击按钮就会把 mData 列表转成 Excel 文件下载下来。

这个按钮我用了 ant-d 组件库，其实无所谓，只要是个点击事件触发就行。

<a-button  @click="down" type="primary">
  下载
</a-button>

然后会执行这个函数。

    methods: {
      down() {
        import('@/vendor/Export2Excel').then(excel => {
          const tHeader = ['姓名', '年龄', '性别'] // excel的表头标题，这个根据自己需要改
          const filterVal = ['name', 'age', 'sex'] // 需要导出对应自己列表中的每项数据，这个就是根据自己的需要改 
          const list = this.mData // 整个列表的数据
          const data = this.formatJson(filterVal, list)
          excel.export_json_to_excel(
            tHeader,
            data,
            'table-list' // 文件名称,若不写导出文件可能不识别
          )
        })
      },
      formatJson(filterVal, jsonData) { // 在整个列表的数据中过滤导出自己需要的数据
        return jsonData.map(v => filterVal.map(j => v[j]))
      },
    }

文件生成成功！

后台返回blob文件流，前端实现下载文件

看这篇文章 ：

https://www.cnblogs.com/tcz1018/p/14073558.html

Blob.js

/* Blob.js
 * A Blob, File, FileReader & URL implementation.
 * 2018-08-09
 *
 * By Eli Grey, http://eligrey.com
 * By Jimmy Wärting, https://github.com/jimmywarting
 * License: MIT
 *   See https://github.com/eligrey/Blob.js/blob/master/LICENSE.md
 */

;(function(){

  var global = typeof window === 'object'
    ? window : typeof self === 'object'
      ? self : this

  var BlobBuilder = global.BlobBuilder
    || global.WebKitBlobBuilder
    || global.MSBlobBuilder
    || global.MozBlobBuilder;

  global.URL = global.URL || global.webkitURL || function(href, a) {
    a = document.createElement('a')
    a.href = href
    return a
  }

  var origBlob = global.Blob
  var createObjectURL = URL.createObjectURL
  var revokeObjectURL = URL.revokeObjectURL
  var strTag = global.Symbol && global.Symbol.toStringTag
  var blobSupported = false
  var blobSupportsArrayBufferView = false
  var arrayBufferSupported = !!global.ArrayBuffer
  var blobBuilderSupported = BlobBuilder
    && BlobBuilder.prototype.append
    && BlobBuilder.prototype.getBlob;

  try {
    // Check if Blob constructor is supported
    blobSupported = new Blob(['ä']).size === 2

    // Check if Blob constructor supports ArrayBufferViews
    // Fails in Safari 6, so we need to map to ArrayBuffers there.
    blobSupportsArrayBufferView = new Blob([new Uint8Array([1,2])]).size === 2
  } catch(e) {}

  /**
   * Helper function that maps ArrayBufferViews to ArrayBuffers
   * Used by BlobBuilder constructor and old browsers that didn't
   * support it in the Blob constructor.
   */
  function mapArrayBufferViews(ary) {
    return ary.map(function(chunk) {
      if (chunk.buffer instanceof ArrayBuffer) {
        var buf = chunk.buffer;

        // if this is a subarray, make a copy so we only
        // include the subarray region from the underlying buffer
        if (chunk.byteLength !== buf.byteLength) {
          var copy = new Uint8Array(chunk.byteLength);
          copy.set(new Uint8Array(buf, chunk.byteOffset, chunk.byteLength));
          buf = copy.buffer;
        }

        return buf;
      }

      return chunk;
    });
  }

  function BlobBuilderConstructor(ary, options) {
    options = options || {};

    var bb = new BlobBuilder();
    mapArrayBufferViews(ary).forEach(function(part) {
      bb.append(part);
    });

    return options.type ? bb.getBlob(options.type) : bb.getBlob();
  };

  function BlobConstructor(ary, options) {
    return new origBlob(mapArrayBufferViews(ary), options || {});
  };

  if (global.Blob) {
    BlobBuilderConstructor.prototype = Blob.prototype;
    BlobConstructor.prototype = Blob.prototype;
  }

  function FakeBlobBuilder() {
    function toUTF8Array(str) {
      var utf8 = [];
      for (var i=0; i < str.length; i++) {
        var charcode = str.charCodeAt(i);
        if (charcode < 0x80) utf8.push(charcode);
        else if (charcode < 0x800) {
          utf8.push(0xc0 | (charcode >> 6),
            0x80 | (charcode & 0x3f));
        }
        else if (charcode < 0xd800 || charcode >= 0xe000) {
          utf8.push(0xe0 | (charcode >> 12),
            0x80 | ((charcode>>6) & 0x3f),
            0x80 | (charcode & 0x3f));
        }
        // surrogate pair
        else {
          i++;
          // UTF-16 encodes 0x10000-0x10FFFF by
          // subtracting 0x10000 and splitting the
          // 20 bits of 0x0-0xFFFFF into two halves
          charcode = 0x10000 + (((charcode & 0x3ff)<<10)
            | (str.charCodeAt(i) & 0x3ff));
          utf8.push(0xf0 | (charcode >>18),
            0x80 | ((charcode>>12) & 0x3f),
            0x80 | ((charcode>>6) & 0x3f),
            0x80 | (charcode & 0x3f));
        }
      }
      return utf8;
    }
    function fromUtf8Array(array) {
      var out, i, len, c;
      var char2, char3;

      out = "";
      len = array.length;
      i = 0;
      while (i < len) {
        c = array[i++];
        switch (c >> 4)
        {
          case 0: case 1: case 2: case 3: case 4: case 5: case 6: case 7:
          // 0xxxxxxx
          out += String.fromCharCode(c);
          break;
          case 12: case 13:
          // 110x xxxx   10xx xxxx
          char2 = array[i++];
          out += String.fromCharCode(((c & 0x1F) << 6) | (char2 & 0x3F));
          break;
          case 14:
            // 1110 xxxx  10xx xxxx  10xx xxxx
            char2 = array[i++];
            char3 = array[i++];
            out += String.fromCharCode(((c & 0x0F) << 12) |
              ((char2 & 0x3F) << 6) |
              ((char3 & 0x3F) << 0));
            break;
        }
      }
      return out;
    }
    function isDataView(obj) {
      return obj && DataView.prototype.isPrototypeOf(obj)
    }
    function bufferClone(buf) {
      var view = new Array(buf.byteLength)
      var array = new Uint8Array(buf)
      var i = view.length
      while(i--) {
        view[i] = array[i]
      }
      return view
    }
    function encodeByteArray(input) {
      var byteToCharMap = 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789'

      var output = [];

      for (var i = 0; i < input.length; i += 3) {
        var byte1 = input[i];
        var haveByte2 = i + 1 < input.length;
        var byte2 = haveByte2 ? input[i + 1] : 0;
        var haveByte3 = i + 2 < input.length;
        var byte3 = haveByte3 ? input[i + 2] : 0;

        var outByte1 = byte1 >> 2;
        var outByte2 = ((byte1 & 0x03) << 4) | (byte2 >> 4);
        var outByte3 = ((byte2 & 0x0F) << 2) | (byte3 >> 6);
        var outByte4 = byte3 & 0x3F;

        if (!haveByte3) {
          outByte4 = 64;

          if (!haveByte2) {
            outByte3 = 64;
          }
        }

        output.push(
          byteToCharMap[outByte1], byteToCharMap[outByte2],
          byteToCharMap[outByte3], byteToCharMap[outByte4])
      }

      return output.join('')
    }

    var create = Object.create || function (a) {
      function c() {}
      c.prototype = a;
      return new c
    }

    if (arrayBufferSupported) {
      var viewClasses = [
        '[object Int8Array]',
        '[object Uint8Array]',
        '[object Uint8ClampedArray]',
        '[object Int16Array]',
        '[object Uint16Array]',
        '[object Int32Array]',
        '[object Uint32Array]',
        '[object Float32Array]',
        '[object Float64Array]'
      ]

      var isArrayBufferView = ArrayBuffer.isView || function(obj) {
        return obj && viewClasses.indexOf(Object.prototype.toString.call(obj)) > -1
      }
    }



    /********************************************************/
    /*                   Blob constructor                   */
    /********************************************************/
    function Blob(chunks, opts) {
      chunks = chunks || []
      for (var i = 0, len = chunks.length; i < len; i++) {
        var chunk = chunks[i]
        if (chunk instanceof Blob) {
          chunks[i] = chunk._buffer
        } else if (typeof chunk === 'string') {
          chunks[i] = toUTF8Array(chunk)
        } else if (arrayBufferSupported && (ArrayBuffer.prototype.isPrototypeOf(chunk) || isArrayBufferView(chunk))) {
          chunks[i] = bufferClone(chunk)
        } else if (arrayBufferSupported && isDataView(chunk)) {
          chunks[i] = bufferClone(chunk.buffer)
        } else {
          chunks[i] = toUTF8Array(String(chunk))
        }
      }

      this._buffer = [].concat.apply([], chunks)
      this.size = this._buffer.length
      this.type = opts ? opts.type || '' : ''
    }

    Blob.prototype.slice = function(start, end, type) {
      var slice = this._buffer.slice(start || 0, end || this._buffer.length)
      return new Blob([slice], {type: type})
    }

    Blob.prototype.toString = function() {
      return '[object Blob]'
    }



    /********************************************************/
    /*                   File constructor                   */
    /********************************************************/
    function File(chunks, name, opts) {
      opts = opts || {}
      var a = Blob.call(this, chunks, opts) || this
      a.name = name
      a.lastModifiedDate = opts.lastModified ? new Date(opts.lastModified) : new Date
      a.lastModified = +a.lastModifiedDate

      return a
    }

    File.prototype = create(Blob.prototype);
    File.prototype.constructor = File;

    if (Object.setPrototypeOf)
      Object.setPrototypeOf(File, Blob);
    else {
      try {File.__proto__ = Blob} catch (e) {}
    }

    File.prototype.toString = function() {
      return '[object File]'
    }


    /********************************************************/
    /*                FileReader constructor                */
    /********************************************************/
    function FileReader() {
      if (!(this instanceof FileReader))
        throw new TypeError("Failed to construct 'FileReader': Please use the 'new' operator, this DOM object constructor cannot be called as a function.")

      var delegate = document.createDocumentFragment()
      this.addEventListener = delegate.addEventListener
      this.dispatchEvent = function(evt) {
        var local = this['on' + evt.type]
        if (typeof local === 'function') local(evt)
        delegate.dispatchEvent(evt)
      }
      this.removeEventListener = delegate.removeEventListener
    }

    function _read(fr, blob, kind) {
      if (!(blob instanceof Blob))
        throw new TypeError("Failed to execute '" + kind + "' on 'FileReader': parameter 1 is not of type 'Blob'.")

      fr.result = ''

      setTimeout(function(){
        this.readyState = FileReader.LOADING
        fr.dispatchEvent(new Event('load'))
        fr.dispatchEvent(new Event('loadend'))
      })
    }

    FileReader.EMPTY = 0
    FileReader.LOADING = 1
    FileReader.DONE = 2
    FileReader.prototype.error = null
    FileReader.prototype.onabort = null
    FileReader.prototype.onerror = null
    FileReader.prototype.onload = null
    FileReader.prototype.onloadend = null
    FileReader.prototype.onloadstart = null
    FileReader.prototype.onprogress = null

    FileReader.prototype.readAsDataURL = function(blob) {
      _read(this, blob, 'readAsDataURL')
      this.result = 'data:' + blob.type + ';base64,' + encodeByteArray(blob._buffer)
    }

    FileReader.prototype.readAsText = function(blob) {
      _read(this, blob, 'readAsText')
      this.result = fromUtf8Array(blob._buffer)
    }

    FileReader.prototype.readAsArrayBuffer = function(blob) {
      _read(this, blob, 'readAsText')
      this.result = blob._buffer.slice()
    }

    FileReader.prototype.abort = function() {}


    /********************************************************/
    /*                         URL                          */
    /********************************************************/
    URL.createObjectURL = function(blob) {
      return blob instanceof Blob
        ? 'data:' + blob.type + ';base64,' + encodeByteArray(blob._buffer)
        : createObjectURL.call(URL, blob)
    }

    URL.revokeObjectURL = function(url) {
      revokeObjectURL && revokeObjectURL.call(URL, url)
    }

    /********************************************************/
    /*                         XHR                          */
    /********************************************************/
    var _send = global.XMLHttpRequest && global.XMLHttpRequest.prototype.send
    if (_send) {
      XMLHttpRequest.prototype.send = function(data) {
        if (data instanceof Blob) {
          this.setRequestHeader('Content-Type', data.type)
          _send.call(this, fromUtf8Array(data._buffer))
        } else {
          _send.call(this, data)
        }
      }
    }

    global.FileReader = FileReader
    global.File = File
    global.Blob = Blob
  }

  if (strTag) {
    File.prototype[strTag] = 'File'
    Blob.prototype[strTag] = 'Blob'
    FileReader.prototype[strTag] = 'FileReader'
  }

  function fixFileAndXHR() {
    var isIE = !!global.ActiveXObject || (
      '-ms-scroll-limit' in document.documentElement.style &&
      '-ms-ime-align' in document.documentElement.style
    )

    // Monkey patched
    // IE don't set Content-Type header on XHR whose body is a typed Blob
    // https://developer.microsoft.com/en-us/microsoft-edge/platform/issues/6047383
    var _send = global.XMLHttpRequest && global.XMLHttpRequest.prototype.send
    if (isIE && _send) {
      XMLHttpRequest.prototype.send = function(data) {
        if (data instanceof Blob) {
          this.setRequestHeader('Content-Type', data.type)
          _send.call(this, data)
        } else {
          _send.call(this, data)
        }
      }
    }

    try {
      new File([], '')
    } catch(e) {
      try {
        var klass = new Function('class File extends Blob {' +
          'constructor(chunks, name, opts) {' +
          'opts = opts || {};' +
          'super(chunks, opts || {});' +
          'this.name = name;' +
          'this.lastModifiedDate = opts.lastModified ? new Date(opts.lastModified) : new Date;' +
          'this.lastModified = +this.lastModifiedDate;' +
          '}};' +
          'return new File([], ""), File'
        )()
        global.File = klass
      } catch(e) {
        var klass = function(b, d, c) {
          var blob = new Blob(b, c)
          var t = c && void 0 !== c.lastModified ? new Date(c.lastModified) : new Date

          blob.name = d
          blob.lastModifiedDate = t
          blob.lastModified = +t
          blob.toString = function() {
            return '[object File]'
          }

          if (strTag)
            blob[strTag] = 'File'

          return blob
        }
        global.File = klass
      }
    }
  }

  if (blobSupported) {
    fixFileAndXHR()
    global.Blob = blobSupportsArrayBufferView ? global.Blob : BlobConstructor
  } else if (blobBuilderSupported) {
    fixFileAndXHR()
    global.Blob = BlobBuilderConstructor;
  } else {
    FakeBlobBuilder()
  }

})();

Export2Excel.js

/* eslint-disable */
require('script-loader!file-saver');
require('script-loader!@/vendor/Blob');
require('script-loader!xlsx/dist/xlsx.core.min');
function generateArray(table) {
    var out = [];
    var rows = table.querySelectorAll('tr');
    var ranges = [];
    for (var R = 0; R < rows.length; ++R) {
        var outRow = [];
        var row = rows[R];
        var columns = row.querySelectorAll('td');
        for (var C = 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 Span
            if (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 Colspan
            if (colspan) for (var k = 0; k < colspan - 1; ++k) outRow.push(null);
        }
        out.push(outRow);
    }
    return [out, ranges];
};

function datenum(v, date1904) {
    if (date1904) v += 1462;
    var epoch = Date.parse(v);
    return (epoch - new Date(Date.UTC(1899, 11, 30))) / (24 * 60 * 60 * 1000);
}

function sheet_from_array_of_arrays(data, opts) {
    var ws = {};
    var range = {s: {c: 10000000, r: 10000000}, e: {c: 0, r: 0}};
    for (var R = 0; R != data.length; ++R) {
        for (var C = 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';
            else if (typeof cell.v === 'boolean') cell.t = 'b';
            else if (cell.v instanceof Date) {
                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;
}

function Workbook() {
    if (!(this instanceof Workbook)) return new Workbook();
    this.SheetNames = [];
    this.Sheets = {};
}

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;
}

export function export_table_to_excel(id) {
    var theTable = document.getElementById(id);
    console.log('a')
    var oo = generateArray(theTable);
    var ranges = oo[1];

    /* original data */
    var data = oo[0];
    var ws_name = "SheetJS";
    console.log(data);

    var wb = new Workbook(), 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(new Blob([s2ab(wbout)], {type: "application/octet-stream"}), "test.xlsx")
}

function formatJson(jsonData) {
    console.log(jsonData)
}
export function export_json_to_excel(th, jsonData, defaultTitle) {

    /* original data */

    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")
}