在vue中将数据导出为excel表格

将一个JSON转换成Excel 酷炫啊，兄嘚~
首先我们要先输出一个export.js

import saveAs from './FileSaver.js'

function convertDSTo26BS (num) {
  let code=''
  let reg = /^\d+$/g
  if (!reg.test(num)) {
    return code
  }
  let m
  while (num > 0) {
    m = num % 26
    if (m === 0) {
      m = 26
    }
    code = String.fromCharCode(64 + parseInt(m)) + code
    num = (num - m) / 26
  }
  return code
}

// const types = {
//   'string': 's',
//   'number': 'n'
// }

function Export (sheets) {
  let result = {Sheets: {}, SheetNames: []}
  sheets.forEach(item => {
    result.SheetNames.push(item.name)

    let unitBlocks = {}
    let merges = []
    let minCol
    let minRow
    let maxCol
    let maxRow

    // 把单元格和合并的单元格参数格式化
    item.data.list.forEach((units, row) => {
      units.forEach((unit, col) => {
        let id = convertDSTo26BS(col + 1) + '' + (row + 1)
        if (unit.type !== 'empty') {
          unitBlocks[id] = {
            t: unit.type,
            v: unit.value,
            s: unit.style,
            z: unit.format,
            ...unit
            // w: unit.value
          }
          if (minCol === undefined || minCol > col + 1) {
            minCol = col + 1
          }
          if (maxCol === undefined || maxCol < col + 1) {
            maxCol = col + 1
          }
          if (minRow === undefined || minRow > row + 1) {
            minRow = row + 1
          }
          if (maxRow === undefined || maxRow < row + 1) {
            maxRow = row + 1
          }
        }
        if (unit.isMerged && unit.type !== 'empty') {

          let merge = {}
          merge.s = {
            c: col,
            r: row
          }
          merge.e = {
            c: col + unit.merge.width - 1,
            r: row + unit.merge.height - 1
          }
          merges.push(merge)
        }
      })
    })

    let range = convertDSTo26BS(minCol) + minRow + ':' + convertDSTo26BS(maxCol) + maxRow
    result.Sheets[item.name] = {
      '!merges': merges,
      '!ref': range,
      '!cols': item['cols'],
      ...unitBlocks
    }
  })
  return result
}

function main (sheets, opt) {
  if (!sheets || sheets.length <= 0) {
    console.log('数据格式错误')
    return false
  }
  let result = Export(sheets)
  console.log(result)

  opt = opt || {
    type: 'xlsx',
    name: 'exportExcel'
  }
  console.log(result)
  var wopts = { bookType: opt.type, bookSST: false, type: 'binary' }
  var wbout = XLSX.write(result, wopts)
  /* the saveAs call downloads a file on the local machine */
  // saveAs(new Blob([wbout], {type: "application/octet-stream"}), opt.name + '.' + opt.type)
  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;
  }

  if (opt['isReturnFile']) {
    return new Blob([s2ab(wbout)],{type:""})
  } else {
    /* the saveAs call downloads a file on the local machine */
    saveAs(new Blob([s2ab(wbout)],{type:""}), opt.name + '.' + opt.type)
  }

}

export default main

这一顿操作下来，我们基本上就能实现了转换成excel了，输出的话还需要另外一个文件

(function (global, factory) {
  if (typeof define === "function" && define.amd) {
    define([], factory);
  } else if (typeof exports !== "undefined") {
    factory();
  } else {
    var mod = {
      exports: {}
    };
    factory();
    global.FileSaver = mod.exports;
  }
})(this, function () {
  "use strict";

  /*
  * FileSaver.js
  * A saveAs() FileSaver implementation.
  *
  * By Eli Grey, http://eligrey.com
  *
  * License : https://github.com/eligrey/FileSaver.js/blob/master/LICENSE.md (MIT)
  * source  : http://purl.eligrey.com/github/FileSaver.js
  */
  // The one and only way of getting global scope in all environments
  // https://stackoverflow.com/q/3277182/1008999
  var _global = function () {
    // some use content security policy to disable eval
    try {
      return Function('return this')() || (42, eval)('this');
    } catch (e) {
      // every global should have circular reference
      // used for checking if someone writes var window = {}; var self = {}
      return typeof window === 'object' && window.window === window ? window : typeof self === 'object' && self.self === self ? self : typeof global === 'object' && global.global === global ? global : this;
    }
  }();

  function bom(blob, opts) {
    if (typeof opts === 'undefined') opts = {
      autoBom: false
    };else if (typeof opts !== 'object') {
      console.warn('Depricated: Expected third argument to be a object');
      opts = {
        autoBom: !opts
      };
    } // prepend BOM for UTF-8 XML and text/* types (including HTML)
    // note: your browser will automatically convert UTF-16 U+FEFF to EF BB BF

    if (opts.autoBom && /^\s*(?:text\/\S*|application\/xml|\S*\/\S*\+xml)\s*;.*charset\s*=\s*utf-8/i.test(blob.type)) {
      return new Blob([String.fromCharCode(0xFEFF), blob], {
        type: blob.type
      });
    }

    return blob;
  }

  function download(url, name, opts) {
    var xhr = new XMLHttpRequest();
    xhr.open('GET', url);
    xhr.responseType = 'blob';

    xhr.onload = function () {
      saveAs(xhr.response, name, opts);
    };

    xhr.onerror = function () {
      console.error('could not download file');
    };

    xhr.send();
  }

  function corsEnabled(url) {
    var xhr = new XMLHttpRequest(); // use sync to avoid popup blocker

    xhr.open('HEAD', url, false);
    xhr.send();
    return xhr.status >= 200 && xhr.status <= 299;
  } // `a.click()` doesn't work for all browsers (#465)


  function click(node) {
    try {
      node.dispatchEvent(new MouseEvent('click'));
    } catch (e) {
      var evt = document.createEvent('MouseEvents');
      evt.initMouseEvent('click', true, true, window, 0, 0, 0, 80, 20, false, false, false, false, 0, null);
      node.dispatchEvent(evt);
    }
  }

  var saveAs = _global.saveAs || // probably in some web worker
  typeof window !== 'object' || window !== _global ? function saveAs() {}
  /* noop */
  // Use download attribute first if possible (#193 Lumia mobile)
  : 'download' in HTMLAnchorElement.prototype ? function saveAs(blob, name, opts) {
    var URL = _global.URL || _global.webkitURL;
    var a = document.createElement('a');
    name = name || blob.name || 'download';
    a.download = name;
    a.rel = 'noopener'; // tabnabbing
    // TODO: detect chrome extensions & packaged apps
    // a.target = '_blank'

    if (typeof blob === 'string') {
      // Support regular links
      a.href = blob;

      if (a.origin !== location.origin) {
        corsEnabled(a.href) ? download(blob, name, opts) : click(a, a.target = '_blank');
      } else {
        click(a);
      }
    } else {
      // Support blobs
      a.href = URL.createObjectURL(blob);
      setTimeout(function () {
        URL.revokeObjectURL(a.href);
      }, 4E4); // 40s

      setTimeout(function () {
        click(a);
      }, 0);
    }
  } // Use msSaveOrOpenBlob as a second approach
  : 'msSaveOrOpenBlob' in navigator ? function saveAs(blob, name, opts) {
    name = name || blob.name || 'download';

    if (typeof blob === 'string') {
      if (corsEnabled(blob)) {
        download(blob, name, opts);
      } else {
        var a = document.createElement('a');
        a.href = blob;
        a.target = '_blank';
        setTimeout(function () {
          click(a);
        });
      }
    } else {
      navigator.msSaveOrOpenBlob(bom(blob, opts), name);
    }
  } // Fallback to using FileReader and a popup
  : function saveAs(blob, name, opts, popup) {
    // Open a popup immediately do go around popup blocker
    // Mostly only avalible on user interaction and the fileReader is async so...
    popup = popup || open('', '_blank');

    if (popup) {
      popup.document.title = popup.document.body.innerText = 'downloading...';
    }

    if (typeof blob === 'string') return download(blob, name, opts);
    var force = blob.type === 'application/octet-stream';

    var isSafari = /constructor/i.test(_global.HTMLElement) || _global.safari;

    var isChromeIOS = /CriOS\/[\d]+/.test(navigator.userAgent);

    if ((isChromeIOS || force && isSafari) && typeof FileReader === 'object') {
      // Safari doesn't allow downloading of blob urls
      var reader = new FileReader();

      reader.onloadend = function () {
        var url = reader.result;
        url = isChromeIOS ? url : url.replace(/^data:[^;]*;/, 'data:attachment/file;');
        if (popup) popup.location.href = url;else location = url;
        popup = null; // reverse-tabnabbing #460
      };

      reader.readAsDataURL(blob);
    } else {
      var URL = _global.URL || _global.webkitURL;
      var url = URL.createObjectURL(blob);
      if (popup) popup.location = url;else location.href = url;
      popup = null; // reverse-tabnabbing #460

      setTimeout(function () {
        URL.revokeObjectURL(url);
      }, 4E4); // 40s
    }
  };
  _global.saveAs = saveAs.saveAs = saveAs;

  if (typeof module !== 'undefined') {
    module.exports = saveAs;
  }
});

我们看一下具体怎么应用

[{
  "name": "第1个sheet",
  "data": {
    "list": [
      [{
        "type": "s",
        "value": "标题加粗",
        "style": { "font": {"bold": true} }
      }, {
        "type": "s",
        "value": "居中 蓝背景",
        "style": {"alignment": {"horizontal": "center", "vertical": "center" },  "fill": { "bgColor": { "indexed": 64 }, "fgColor": { "rgb": "0000FF" } } }
      }, {
        "type": "s",
        "value": "左对齐",
        "style": { "alignment": {"horizontal": "top", "vertical": "bottom" } }
      }, {
        "type": "s",
        "value": "右对齐",
        "style": { "alignment": {"horizontal": "bottom", "vertical": "top" } }
      }, {
        "type": "n",
        "value": "-1234567.89111",
        "style": {"numFmt": "#,##0.00;-#,##0.00" }
      }, {
        "type": "s",
        "value": "字体大小10",
        "style": {"font": {"sz": "10"} }
      }]
    ]
  }
}, {
  "name": "第一个sheet",
  "data": {
    "list": [
      [{
        "type": "empty",
        "value": ""
      }, {
        "type": "empty",
        "value": ""
      }, {
        "type": "empty",
        "value": ""
      }, {
        "type": "empty",
        "value": ""
      }, {
        "type": "empty",
        "value": ""
      }],
      [{
        "type": "empty",
        "value": ""
      }, {
        "type": "s",
        "value": "第一列表头"
      }, {
        "type": "s",
        "value": "第二列表头红色字体",
        "style": { "font": { "sz": 14, "bold": true, "color": { "rgb": "FFFFAA00" } }, "fill": { "bgColor": { "indexed": 64 }, "fgColor": { "rgb": "FFFF00" } } }
      }, {
        "type": "s",
        "value": "第三列表头边框"
      }, {
        "type": "s",
        "value": "第四列背景色"
      }],
      [{
        "type": "empty",
        "value": ""
      }, {
        "type": "s",
        "value": "第一列第二行"
      }, {
        "type": "s",
        "value": "第二列第二行"
      }, {
        "type": "s",
        "value": "第三列第二行"
      }, {
        "type": "s",
        "value": "第四列第二行"
      }],
      [{
        "type": "empty",
        "value": ""
      }, {
        "type": "s",
        "value": "链接"
      }, {
        "type": "s",
        "value": "合并的单元格",
        "isMerged": true,
        "merge": {
          "height": 3,
          "width": 2
        }
      }, {
        "type": "empty",
        "value": "",
        "isMerged": true,
        "merge": {
          "height": 3,
          "width": 2
        }
      }, {
        "type": "empty",
        "value": ""
      }],
      [{
        "type": "empty",
        "value": ""
      }, {
        "type": "n",
        "value": 12344
      }, {
        "type": "empty",
        "value": "",
        "isMerged": true,
        "merge": {
          "height": 3,
          "width": 2
        }
      }, {
        "type": "empty",
        "value": "",
        "isMerged": true,
        "merge": {
          "height": 3,
          "width": 2
        }
      }, {
        "type": "empty",
        "value": ""
      }],
      [{
        "type": "empty",
        "value": ""
      }, {
        "type": "n",
        "value": 0.5416666666666666
      }, {
        "type": "empty",
        "value": "",
        "isMerged": true,
        "merge": {
          "height": 3,
          "width": 2
        }
      }, {
        "type": "empty",
        "value": "",
        "isMerged": true,
        "merge": {
          "height": 3,
          "width": 2
        }
      }, {
        "type": "empty",
        "value": ""
      }],
      [{
        "type": "empty",
        "value": ""
      }, {
        "type": "n",
        "value": 666
      }, {
        "type": "empty",
        "value": ""
      }, {
        "type": "s",
        "value": "合并的单元格2",
        "isMerged": true,
        "merge": {
          "height": 2,
          "width": 2
        }
      }, {
        "type": "empty",
        "value": "",
        "isMerged": true,
        "merge": {
          "height": 2,
          "width": 2
        }
      }],
      [{
        "type": "empty",
        "value": ""
      }, {
        "type": "n",
        "value": 13010.541666666666
      }, {
        "type": "empty",
        "value": ""
      }, {
        "type": "empty",
        "value": "",
        "isMerged": true,
        "merge": {
          "height": 2,
          "width": 2
        }
      }, {
        "type": "empty",
        "value": "",
        "isMerged": true,
        "merge": {
          "height": 2,
          "width": 2
        }
      }]
    ]
  }
}, {
  "name": "第二个sheet",
  "data": {
    "list": [
      [{
        "type": "s",
        "value": "日期",
        "isMerged": true,
        "merge": {
          "height": 3,
          "width": 1
        }
      }, {
        "type": "s",
        "value": "配送信息",
        "isMerged": true,
        "merge": {
          "height": 1,
          "width": 5
        }
      }, {
        "type": "empty",
        "value": "配送信息",
        "isMerged": true,
        "merge": {
          "height": 1,
          "width": 5
        }
      }, {
        "type": "empty",
        "value": "配送信息",
        "isMerged": true,
        "merge": {
          "height": 1,
          "width": 5
        }
      }, {
        "type": "empty",
        "value": "配送信息",
        "isMerged": true,
        "merge": {
          "height": 1,
          "width": 5
        }
      }, {
        "type": "empty",
        "value": "配送信息",
        "isMerged": true,
        "merge": {
          "height": 1,
          "width": 5
        }
      }],
      [{
        "type": "empty",
        "value": "日期",
        "isMerged": true,
        "merge": {
          "height": 3,
          "width": 1
        }
      }, {
        "type": "s",
        "value": "姓名",
        "isMerged": true,
        "merge": {
          "height": 2,
          "width": 1
        }
      }, {
        "type": "s",
        "value": "地址",
        "isMerged": true,
        "merge": {
          "height": 1,
          "width": 4
        }
      }, {
        "type": "empty",
        "value": "地址",
        "isMerged": true,
        "merge": {
          "height": 1,
          "width": 4
        }
      }, {
        "type": "empty",
        "value": "地址",
        "isMerged": true,
        "merge": {
          "height": 1,
          "width": 4
        }
      }, {
        "type": "empty",
        "value": "地址",
        "isMerged": true,
        "merge": {
          "height": 1,
          "width": 4
        }
      }],
      [{
        "type": "empty",
        "value": "日期",
        "isMerged": true,
        "merge": {
          "height": 3,
          "width": 1
        }
      }, {
        "type": "empty",
        "value": "姓名",
        "isMerged": true,
        "merge": {
          "height": 2,
          "width": 1
        }
      }, {
        "type": "s",
        "value": "省份"
      }, {
        "type": "s",
        "value": "市区"
      }, {
        "type": "s",
        "value": "地址"
      }, {
        "type": "s",
        "value": "邮编"
      }]
    ]
  }
}]

可以看到 写完后支持多个sheet ， 并且能够控制单元格的样式。 是不是很棒呢？