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
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));
5万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
