vue三种方式导出报表至excel

1、序言

1.1、源码

源码在下方，复制运行，安装相应的插件即可

1.2、坑

方式一、方式二安装相同插件：

npm install xlsx xlsx-style file-saver

导入 xlsx-style 会报如下的错误

解决办法：

（1）去node_modules下面找到xlsx/dist/ 修改相应配置，这样非常不好，每次npm install，都要跑去node_modules下面修改，非常麻烦！

（2）新建vue.config.js，复制如下代码，重启项目即可

module.exports = {
    configureWebpack: (config) => {
        // 解决xlsx-style报错的问题
        return {
            externals: [{
                './cptable': 'var cptable'
            }]
        }
    }
}

还有一个坑

解决办法：

1.3、实现功能说明

未实现：

（1）单元格样式修改还未实现，后期找到合适的方法会更新此博客

（2）不能导出图片至excel表中，只把表格数据导出至excel表

（3）导出的excel报表没有多个Sheet

已实现：

（1）表格正常导出

（2）多级表头导出

（3）单元格自定义内容、合计行导出

2、方式一（xlsx）（不推荐）

2.1、工具方法

Export2Excel.js

export function export_json_to_excel({
    multiHeader = [], // 第一行表头
    multiHeader2 = [],	// 第二行表头
    data,
    filename, //文件名
    merges = [], // 合并
    bookType = 'xlsx'
} = {}) {
    /* original data */
    filename = filename || '列表';
    data = [...data]
    data.unshift(multiHeader2)
    data.unshift(multiHeader)
    console.log('data:', data)
    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))
        })
    }

    /* add worksheet to workbook */
    wb.SheetNames.push(ws_name);
    wb.Sheets[ws_name] = ws;

    var wbout = XLSXStyle.write(wb, {
        bookType: bookType,
        bookSST: false,
        type: 'binary'
    });

    saveAs(new Blob([s2ab(wbout)], {
        type: "application/octet-stream"
    }), `${filename}.${bookType}`);
}

2.2、使用

// multiHeader: 一级表头
// multiHeader2：二级表头
// data：表格数据
// merges：需要合并的单元格
// filename：导出的excel文件名
import('@/utils/Export2Excel').then((excel) => {
        excel.export_json_to_excel({
          multiHeader,
          multiHeader2,
          data,
          merges,
          filename: '报表导出一',
        });
      })

2.3、目标效果

2.4、缺点

（1）没有合计行

（2）表格插槽自定义的内容，如添加的单位: kg、cm、岁不能一起导出来

（3）最痛苦的是多级表头导出的时候还要一个个算单元格哪些需要合并、多级表头数组的字段排放，字段少还好，多的时候就痛苦面具了！

3、方式二（xlsx结合dom）（推荐）

3.1、工具方法

Export2Excel.js

// 根据dom导出表格
export function exportToExcel(idSelector, fileName, titleNum = 1) {
    // 设置导出的内容是否只做解析，不进行格式转换     false：要解析， true:不解析
    const xlsxParam = { raw: true }
    let table = document.querySelector(idSelector).cloneNode(true);

    // 因为element-ui的表格的fixed属性导致多出一个table，会下载重复内容，这里删除掉
    if (table.querySelector('.el-table__fixed-right'))
        table.removeChild(table.querySelector('.el-table__fixed-right'));
    if (table.querySelector('.el-table__fixed'))
        table.removeChild(table.querySelector('.el-table__fixed'));

    const wb = XLSX.utils.table_to_book(table, xlsxParam)
    let range = XLSX.utils.decode_range(wb.Sheets['Sheet1']['!ref']);
    let cWidth = [];
    for (let C = range.s.c; C < range.e.c; ++C) {   //SHEET列
        let len = 100; //默认列宽
        let len_max = 400; //最大列宽
        for (let R = range.s.r; R <= range.e.r; ++R) {  //SHEET行
            let cell = { c: C, r: R };                    //二维 列行确定一个单元格
            let cell_ref = XLSX.utils.encode_cell(cell);  //单元格 A1、A2
            if (wb.Sheets['Sheet1'][cell_ref]) {
                // if (R == 0){
                if (R < titleNum) {
                    wb.Sheets['Sheet1'][cell_ref].s = {  //设置第一行单元格的样式 style
                        alignment: {
                            horizontal: 'center',
                            vertical: 'center',
                        },
                    };
                } else {
                    wb.Sheets['Sheet1'][cell_ref].s = {
                        alignment: {
                            horizontal: 'center',
                            vertical: 'center',
                        },
                    };
                }
                //动态自适应：计算列宽
                let va = JSON.parse(JSON.stringify(wb.Sheets['Sheet1'][cell_ref].v));
                var card1 = JSON.parse(JSON.stringify(va)).match(/[\u4e00-\u9fa5]/g); //匹配中文
                var card11 = "";
                if (card1) {
                    card11 = card1.join("")
                }
                var card2 = JSON.parse(JSON.stringify(va)).replace(/([^\u0000-\u00FF])/g, "");  //剔除中文
                let st = 0;
                if (card11) {
                    // st += card11.length * 16  //中文字节码长度
                    st += card11.length * 20  //中文字节码长度
                }
                if (card2) {
                    // st += card2.length * 8  //非中文字节码长度
                    st += card2.length * 10  //非中文字节码长度
                }
                if (st > len) {
                    len = st;
                }
            }
        }
        if (len > len_max) {//最大宽度
            len = len_max;
        }

        cWidth.push({ 'wpx': len });     //列宽
    }
    wb.Sheets['Sheet1']['!cols'] = cWidth
    const wbout = XLSXStyle.write(wb, { bookType: 'xlsx', bookSST: false, type: 'binary' })
    try {
        saveAs(new Blob([s2ab(wbout)], { type: '' }), `${fileName}.xlsx`)
    } catch (e) {
        if (typeof console !== 'undefined') {
            console.log(e, wbout)
        }
    }
    return wbout
}

3.2、使用

import { exportToExcel } from '@/utils/Export2Excel'
methods: {
    // 方式二导出报表
    handleExport2() {
      // exportToExcel(表格id选择器, 导出文件名称);
      exportToExcel('#table', '报表导出二');
    },
  }

3.2、效果

除了合计行有点小小的瑕疵，其他部分非常完美了，代码少，实现的功能又多，相当于导出el-table了！

4、方式三（vue-json-excel）（不推荐）

# 安装插件

npm i vue-json-excel

# 插件官网：GitHub - jecovier/vue-json-excel

4.1、使用

字段说明

字段名称	字段类型	字段说明
data	Array	表格数据
fields	Object	不填写的话，导出的excel表头名称为 data数据里面的属性名称，其实就是字段名称映射成中文
type	string	导出的文件类型，默认xls
name	string	导出的文件名称
title	string	excel表格标题

main.js

import Vue from 'vue'
import App from './App.vue'
import ElementUI from 'element-ui';
import 'element-ui/lib/theme-chalk/index.css';

import JsonExcel from "vue-json-excel";
Vue.component("downloadExcel", JsonExcel);
Vue.use(ElementUI);

new Vue({
  render: h => h(App),
}).$mount('#app')

页面使用

每个字段可以自定义单元格格式化输出内容，比如加上单位

4.2、效果

4.3、缺点

（1）多级表头导不出来，官方文档写的不够详细

（2）没有合计行

（3）配置稍显繁琐

5、源码

vue.config.js

module.exports = {
    configureWebpack: (config) => {
        // 解决xlsx-style报错的问题
        return {
            externals: [{
                './cptable': 'var cptable'
            }]
        }
    }
}

main.js

import Vue from 'vue'
import App from './App.vue'
import ElementUI from 'element-ui';
import 'element-ui/lib/theme-chalk/index.css';
import JsonExcel from "vue-json-excel";
Vue.component("downloadExcel", JsonExcel);
Vue.use(ElementUI);
Vue.config.productionTip = false

new Vue({
  render: h => h(App),
}).$mount('#app')

App.vue

<template>
  <div id="app">
    <div style="display: flex; justify-content: center;">
      <el-button type="success" @click="handleExport">导出一</el-button>
      <el-button type="primary" @click="handleExport2">导出二</el-button>

      <download-excel :data="tableData" :fields="fields" name="报表三">
        <el-button type="warning">导出三</el-button>
      </download-excel>
    </div>
    <el-table :data="tableData" id="table" ref="table" border style="width: 100%; margin-top: 20px;" show-summary>
      <el-table-column prop="date" label="日期" width="180">
      </el-table-column>
      <el-table-column prop="name" label="姓名" width="180">
      </el-table-column>
      <el-table-column label="个人信息" align="center">
        <el-table-column prop="height" label="身高">
          <template slot-scope="{row}">
            {{ row.height + 'cm' }}
          </template>
        </el-table-column>
        <el-table-column prop="weight" label="体重">
          <template slot-scope="{row}">
            {{ row.weight + 'kg' }}
          </template>
        </el-table-column>
        <el-table-column prop="age" label="年龄">
          <template slot-scope="{row}">
            {{ row.age + '岁' }}
          </template>
        </el-table-column>
      </el-table-column>
    </el-table>
  </div>
</template>
 
<script>
import { exportToExcel } from '@/utils/Export2Excel'
export default {
  name: 'App',
  components: {
  },
  data() {
    return {
      tableData: [{
        date: '2016-05-03',
        name: '王大虎',
        age: 20,
        weight: 70,
        height: 188
      }, {
        date: '2016-05-02',
        name: '王二虎',
        age: 18,
        weight: 60,
        height: 177
      }, {
        date: '2016-05-04',
        name: '王小虎',
        age: 16,
        weight: 50,
        height: 166
      }],
      fields: {
        '日期': 'date',
        '姓名': 'name',
        '身高': {
          field: 'height',
          callback: (value) => {
            return `${value} cm`;
          },
        },
        '体重': {
          field: 'weight',
          callback: (value) => {
            return `${value} kg`;
          },
        },
        '年龄': {
          field: 'age',
          callback: (value) => {
            return `${value} 岁`;
          },
        },
      }
    }
  },
  methods: {
    // 方式一导出报表
    handleExport() {
      // 一级表头
      const multiHeader = [
        "日期",
        "姓名",
        "个人信息",
      ];

      // 二级表头
      const multiHeader2 = [
        "",
        "",
        "身高",
        "体重",
        "年龄",
      ];

      // 根据Excel确定要合并的单元格
      const merges = [
        "A1:A2",
        "B1:B2",
        "C1:E1",
      ];

      // const header = this.$refs.table.columns.map(item => item.label)
      const columns = this.$refs.table.columns.map(item => item.property)
      const data = this.tableData.map(v => columns.map(j => v[j]));

      import('@/utils/Export2Excel').then((excel) => {
        excel.export_json_to_excel({
          multiHeader,
          multiHeader2,
          data,
          merges,
          filename: '报表导出一',
        });
      })
    },
    // 方式二导出报表
    handleExport2() {
      exportToExcel('#table', '报表导出二');
    },
  }
}
</script>
 
<style>
#app {
  font-family: Avenir, Helvetica, Arial, sans-serif;
  -webkit-font-smoothing: antialiased;
  -moz-osx-font-smoothing: grayscale;
  text-align: center;
  color: #2c3e50;
}
</style>

utils/Export2Excel.js

/* eslint-disable */
import { saveAs } from 'file-saver'
import * as XLSX from 'xlsx'
import * as XLSXStyle from "xlsx-style";


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_json_to_excel({
    multiHeader = [], // 第一行表头
    multiHeader2 = [],	// 第二行表头
    data,
    filename, //文件名
    merges = [], // 合并
    bookType = 'xlsx'
} = {}) {
    /* original data */
    filename = filename || '列表';
    data = [...data]
    data.unshift(multiHeader2)
    data.unshift(multiHeader)
    console.log('data:', data)
    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))
        })
    }

    /* add worksheet to workbook */
    wb.SheetNames.push(ws_name);
    wb.Sheets[ws_name] = ws;

    var wbout = XLSXStyle.write(wb, {
        bookType: bookType,
        bookSST: false,
        type: 'binary'
    });

    saveAs(new Blob([s2ab(wbout)], {
        type: "application/octet-stream"
    }), `${filename}.${bookType}`);
}

// 根据dom导出表格
export function exportToExcel(idSelector, fileName, titleNum = 1) {
    // 设置导出的内容是否只做解析，不进行格式转换     false：要解析， true:不解析
    const xlsxParam = { raw: true }
    let table = document.querySelector(idSelector).cloneNode(true);

    // 因为element-ui的表格的fixed属性导致多出一个table，会下载重复内容，这里删除掉
    if (table.querySelector('.el-table__fixed-right'))
        table.removeChild(table.querySelector('.el-table__fixed-right'));
    if (table.querySelector('.el-table__fixed'))
        table.removeChild(table.querySelector('.el-table__fixed'));

    const wb = XLSX.utils.table_to_book(table, xlsxParam)
    let range = XLSX.utils.decode_range(wb.Sheets['Sheet1']['!ref']);
    let cWidth = [];
    for (let C = range.s.c; C < range.e.c; ++C) {   //SHEET列
        let len = 100; //默认列宽
        let len_max = 400; //最大列宽
        for (let R = range.s.r; R <= range.e.r; ++R) {  //SHEET行
            let cell = { c: C, r: R };                    //二维 列行确定一个单元格
            let cell_ref = XLSX.utils.encode_cell(cell);  //单元格 A1、A2
            if (wb.Sheets['Sheet1'][cell_ref]) {
                // if (R == 0){
                if (R < titleNum) {
                    wb.Sheets['Sheet1'][cell_ref].s = {  //设置第一行单元格的样式 style
                        alignment: {
                            horizontal: 'center',
                            vertical: 'center',
                        },
                    };
                } else {
                    wb.Sheets['Sheet1'][cell_ref].s = {
                        alignment: {
                            horizontal: 'center',
                            vertical: 'center',
                        },
                    };
                }
                //动态自适应：计算列宽
                let va = JSON.parse(JSON.stringify(wb.Sheets['Sheet1'][cell_ref].v));
                var card1 = JSON.parse(JSON.stringify(va)).match(/[\u4e00-\u9fa5]/g); //匹配中文
                var card11 = "";
                if (card1) {
                    card11 = card1.join("")
                }
                var card2 = JSON.parse(JSON.stringify(va)).replace(/([^\u0000-\u00FF])/g, "");  //剔除中文
                let st = 0;
                if (card11) {
                    // st += card11.length * 16  //中文字节码长度
                    st += card11.length * 20  //中文字节码长度
                }
                if (card2) {
                    // st += card2.length * 8  //非中文字节码长度
                    st += card2.length * 10  //非中文字节码长度
                }
                if (st > len) {
                    len = st;
                }
            }
        }
        if (len > len_max) {//最大宽度
            len = len_max;
        }

        cWidth.push({ 'wpx': len });     //列宽
    }
    wb.Sheets['Sheet1']['!cols'] = cWidth
    const wbout = XLSXStyle.write(wb, { bookType: 'xlsx', bookSST: false, type: 'binary' })
    try {
        saveAs(new Blob([s2ab(wbout)], { type: '' }), `${fileName}.xlsx`)
    } catch (e) {
        if (typeof console !== 'undefined') {
            console.log(e, wbout)
        }
    }
    return wbout
}

utils/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));