使用需要引入这些js

在src目录下创建一个文件(vendor)进入Blob.js和Export2Excel.js
npm install -S file-saver 用来生成文件的web应用程序
npm install -S xlsx 电子表格格式的解析器
npm install -D script-loader 将js挂在在全局下

Blob.js

/* eslint-disable */
/* Blob.js*/

/*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

  , origin = /^\[\\w-\]+:\\/\*\\\[?\[\\w\\.:-\]+\\\]?(?::\[0-9\]+)?/  
;  
FakeBlob.fake = FB\_proto.fake = true;  
while (file\_ex\_code--) {  
  FileException.prototype\[file\_ex\_codes\[file\_ex\_code\]\] = file\_ex\_code + 1;  
}  
// Polyfill URL  
if (!real\_URL.createObjectURL) {  
  URL = view.URL = function (uri) {  
    var  
      uri\_info = document.createElementNS("http://www.w3.org/1999/xhtml", "a")  
      , uri\_origin  
    ;  
    uri\_info.href = uri;  
    if (!("origin" in uri\_info)) {  
      if (uri\_info.protocol.toLowerCase() === "data:") {  
        uri\_info.origin = null;  
      } else {  
        uri\_origin = uri.match(origin);  
        uri\_info.origin = uri\_origin && uri\_origin\[1\];  
      }  
    }  
    return uri\_info;  
  };  
}  
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 = 0;  
  delete this.data;  
};  
return FakeBlobBuilder;  

}(view));

view.Blob = function (blobParts, options) {
var type = options ? (options.type || "") : "";
var builder = new BlobBuilder();
if (blobParts) {
for (var i = 0, len = blobParts.length; i < len; i++) {
if (Uint8Array && blobParts[i] instanceof Uint8Array) {
builder.append(blobParts[i].buffer);
}
else {
builder.append(blobParts[i]);
}
}
}
var blob = builder.getBlob(type);
if (!blob.slice && blob.webkitSlice) {
blob.slice = blob.webkitSlice;
}
return blob;
};

var getPrototypeOf = Object.getPrototypeOf || function (object) {
return object.__proto__;
};
view.Blob.prototype = getPrototypeOf(new view.Blob());
}(
typeof self !== "undefined" && self
|| typeof window !== "undefined" && window
|| this
));

Export2Excel.js

require('script-loader!file-saver')
// vue 2.6必须要使用这种载入其他js文件的方式才行
import '@/vendor/Blob'
require('script-loader!xlsx/dist/xlsx.core.min')
require('script-loader!xlsx-style/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)
var oo = generateArray(theTable)
var ranges = oo[1]

/* original data */
var data = oo[0]
var ws_name = 'SheetJS'

var wb = new Workbook()
var 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'
)
}

// 主要修改此函数内的方法

export function export_json_to_excel({
multiHeader = [],
header,
data,
sheetname,
filename,
merges = [],
autoWidth = true,
bookType = 'xlsx'
} = {}) {
/* original data */
filename = filename || 'excel-list'
data = […data]

for (var i = 0; i < header.length; i++) {
data[i].unshift(header[i])
}

// data.unshift(header)

for (let i = multiHeader.length - 1; i > -1; i--) {
data[i].unshift(multiHeader[i])
}

var ws_name = sheetname
var wb = new Workbook()
var ws = []
for (var j = 0; j < header.length; j++) {
ws.push(sheet_from_array_of_arrays(data[j]))
}

if (merges.length > 0) {
for (var j = 0; j < header.length; j++) { if (!ws[j]['!merges']) ws[j]['!merges'] = [] merges[j].forEach(item => {
ws[j]['!merges'].push(XLSX.utils.decode_range(item))
})
}
}
// console.log("width", autoWidth)
if (autoWidth) {
/* 设置worksheet每列的最大宽度*/
var colWidth = []
for (var k = 0; k < header.length; k++) { colWidth.push( data[k].map(row =>
row.map(val => {
/* 先判断是否为null/undefined*/
if (val == null) {
return {
wch: 10
}
} else if (val.toString().charCodeAt(0) > 255) {
/* 再判断是否为中文*/
return {
wch: val.toString().length * 2
}
} else {
return {
wch: val.toString().length
}
}
})
)
)
}

/\* 以第一行为初始值\*/  
const result = \[\]  
for (var k = 0; k < colWidth.length; k++) {  
  result\[k\] = colWidth\[k\]\[0\]  
  for (let i = 1; i < colWidth\[k\].length; i++) {  
    for (let j = 0; j < colWidth\[k\]\[i\].length; j++) {  
      if (!result\[k\]\[j\]) {  
        continue  
      }  
      if (result\[k\]\[j\]\['wch'\] < colWidth\[k\]\[i\]\[j\]\['wch'\]) {  
        result\[k\]\[j\]\['wch'\] = colWidth\[k\]\[i\]\[j\]\['wch'\]  
      }  
    }  
  }  
}  
// 分别给sheet表设置宽度  
for (var l = 0; l < result.length; l++) {  
  ws\[l\]\['!cols'\] = result\[l\]  
}  

}

/* add worksheet to workbook */
for (var k = 0; k < header.length; k++) {
wb.SheetNames.push(ws_name[k])
wb.Sheets[ws_name[k]] = ws[k]
}

// 设置单元格框线
const borderAll = {
top: {
style: 'thin'
},
bottom: {
style: 'thin'
},
left: {
style: 'thin'
},
right: {
style: 'thin'
}
}
for (var k = 0; k < header.length; k++) {
var dataInfo = wb.Sheets[wb.SheetNames[k]]
// 给所有单元格加上边框，内容居中，字体，字号
for (var i in dataInfo) {
if (
i === '!ref' ||
i === '!merges' ||
i === '!cols' ||
i === '!rows' ||
i === 'A1'
) {

  } else {  
    dataInfo\[i + ''\].s = {  
      border: borderAll,  
      alignment: {  
        horizontal: 'left',  
        vertical: 'left'  
      },  
      font: {  
        name: '微软雅黑',  
        sz: 10  
      }  
    }  
  }  
}  

}
if (merges.length > 0) {
// 表头合并
const titleArr = ['A1', 'B1', 'C1', 'D1', 'E1', 'F1', 'G1', 'H1', 'I1', 'J1', 'K1', 'L1', 'M1', 'N1', 'O1', 'P1', 'Q1', 'R1', 'S1', 'T1', 'U1', 'V1', 'W1', 'X1', 'Y1', 'Z1']
for (var k = 0; k < header.length; k++) {
var dataInfo = wb.Sheets[wb.SheetNames[k]]
titleArr.some(function(v) {
const a = merges[k][0].split(':')
if (v === a[1]) {
if (!dataInfo[v]) {
dataInfo[v] = {}
}
dataInfo[v].s = {}
return true
} else {
if (!dataInfo[v]) {
dataInfo[v] = {}
}
dataInfo[v].s = {}
}
})
// 设置主标题样式
dataInfo['A1'].s = {
font: {
name: '微软雅黑',
sz: 18,
color: { rgb: '000000' },
bold: true,
italic: false,
underline: false
},
alignment: {
horizontal: 'center',
vertical: 'center'
}
}
}
}
var wbout = XLSX.write(wb, {
bookType: bookType,
bookSST: false,
type: 'binary'
})
saveAs(
new Blob([s2ab(wbout)], {
type: 'application/octet-stream'
}),
`${filename}.${bookType}`
)
}

setMethods.js

import { parseTime } from '@/utils/index'
import { export_json_to_excel } from '@/vendor/Export2Excel'
/**
* Parse the json to excel
* tableJson 导出数据 ; filenames导出表的名字; autowidth表格宽度自动 true or false; bookTypes xlsx & csv & txt
* @param {(Object)} tableJson
* @param {string} filenames
* @param {boolean} autowidth
* @param {string} bookTypes
*/
export function json2excel(tableJson, filenames, autowidth, bookTypes) {
require.ensure([], () => {
var tHeader = []
var dataArr = []
var sheetnames = []
// 合并列对象
var multiHeader = []
　　　// 表格合并位置
var merges = []
for (var i in tableJson) {
tHeader.push(tableJson[i].tHeader)
dataArr.push(formatJson(tableJson[i].filterVal, tableJson[i].tableDatas))
sheetnames.push(tableJson[i].sheetName)
var multiHeaderData = [filenames]
if (tableJson[i].multiHeaderData && tableJson[i].multiHeaderData.length > 0) {
multiHeaderData = tableJson[i].multiHeaderData
}
// 表格标题空白处使用标题填充
for (var j = 1; j < tableJson[i].tHeader.length; j++) {
multiHeaderData.push('')
}
multiHeader.push(multiHeaderData)

　　　　// 表格合并位置 从A1->H1

  var mergesData = \['A1:H1'\]  
  merges.push(mergesData)  
}  
export\_json\_to\_excel({  
  header: tHeader,  
  data: dataArr,  
  sheetname: sheetnames,  
  filename: filenames,  
  autoWidth: autowidth,  
  bookType: bookTypes,  
  merges: merges,  
  multiHeader: multiHeader  
})  

})
}
// 数据过滤，时间过滤
function formatJson(filterVal, jsonData) {
return jsonData.map(v =>
filterVal.map(j => {
if (j === 'timestamp') {
return parseTime(v[j])
} else {
// 如果 undefine,返回空字符串
if (!v[j]) {
return ''
}
return v[j]
}
})
)
}
// 剪切标题
export function cutValue(target, name) {
var arr = []
for (var i = 0; i < target.length; i++) {
arr.push(target[i][name])
}
return arr
}

调用代码例1:

// 在vue页面引入插件
import { json2excel } from '@/vendor/setMethods'
// 例
// tHeader= [[表头名称1,表头名称1,表头名称1],[表头名称2]]
// filterVal= [[columnName1,columnName1,columnName1],[columnName2]]
// tableDatas= [[{columnName1:1,columnName2:2},{},{}],[{}]]
// sheetName= ['sheet1','sheet2','sheet3']
methods: {
// 导出报表
handleDownload(sheetLen) {
var that = this
var excelDatas = []
for (let i = 0; i <= sheetLen; i++) {
var data = {
tHeader: that.downIndexData.tHeaders[i], // sheet表一头部
filterVal: that.downIndexData.filterVals[i], // 表一的数据字段
tableDatas: that.downIndexData.datas[i], // 表一的整体json数据
sheetName: that.downIndexData.sheetNames[i] // 表一的sheet名字
}
excelDatas.push(data)
}
const fileName = '月度报表'
json2excel(excelDatas, fileName, true, 'xlsx')
}
}

调用代码例2(导出单个sheet):

　　　　var fileName = data.year + '年' + data.timeRange + '月' + data.reportName + data.orgName
const tHeader = [ '排序', '指标名称', '单位', '填报值', '上期数据', '指标说明', '备注' ]
const filterVal = [ 'indexSort', 'indexName', 'unit', 'value', 'termValue', 'describe', 'remark' ]
var excelDatas = []
var sheet = {
tHeader: tHeader, // sheet表一头部
filterVal: filterVal, // 表一的数据字段
tableDatas: that.downData, // 表一的整体json数据
sheetName: fileName // 表一的sheet名字
}
excelDatas.push(sheet)
json2excel(excelDatas, fileName, true, 'xlsx')

调用代码例3(导出多个sheet):

downReportMony: async function() {
var that = this
if (that.crud) {
that.crud.loading = true
}
that.downLoading = true
var excelDatas = []
var fileName = that.multipleSelection[0].orgName + '报表'
for (const data of that.multipleSelection) {
await that.getTableData(data)
await that.setDataIndex()
　　　　　// 合并表头名称
var sheetTitleName = data.year + '年' + data.timeRange + '月' + data.orgName + data.reportName
const tHeader = ['排序', '指标名称', '单位', '填报值', '上期数据', '指标说明', '备注']
const filterVal = ['indexSort', 'indexName', 'unit', 'value', 'termValue', 'describe', 'remark']
var sheet = {
tHeader: tHeader, // sheet表一头部
filterVal: filterVal, // 表一的数据字段
tableDatas: that.downData, // 表一的整体json数据
multiHeaderData: [sheetTitleName],
sheetName: data.timeRange + '月' // 表一的sheet名字
}
excelDatas.push(sheet)
}
json2excel(excelDatas, fileName, true, 'xlsx')
if (that.crud) {
that.crud.loading = false
}
that.downLoading = false
that.onlineReportVisible = false
}

最终合并列导出效果

vue2.5版本的例子,其他版本的vue,引入方式可能会不一样

@font-face { font-family: "yourDictFontAwesome"; src: url("chrome-extension://nnijdpomjcmgahgjcdgimjldkopghaef/lib/fontawesome-webfont.ttf") format("truetype"); font-weight: normal; font-style: normal }