vue通过xlsx-style导出数据
1、安装依赖
npm install -S file-saver
npm install -S xlsx
npm install -S xlsx-style
npm install -D script-loader
注:xlsx-style组件报错Can‘t resolve ‘./cptable‘ in ‘xxx\node_modules_xlsx
vue2x版本
就是有webpack配置
就是在webpack.base.conf.js中加入下面这段
module.exports = {
externals: {
'./cptable': 'var cptable'
}
}
vue3x版本
在根目录下的vue.config.js(没有的话自己创建一个)
加入下面的代码
module.exports = {
configureWebpack: {
externals: {
'./cptable': 'var cptable'
}
}
}
2、创建文件
3、主要代码
Blob.js
/* eslint-disable */
/* Blob.js
* A Blob implementation.
*
* 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));
exportExcel.js
// Export2Excel.js
/* eslint-disable */
import 'script-loader!file-saver';
import './Blob.js'; //转二进制用 这边要写你的blob的实际地址
import 'script-loader!xlsx/dist/xlsx.core.min';
import XLSXS 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 = XLSXS.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 = XLSXS.SSF._table[14];
cell.v = datenum(cell.v);
} else cell.t = 's';
ws[cell_ref] = cell;
}
}
if (range.s.c < 10000000) ws['!ref'] = XLSXS.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 exportjsontoexcel({
data, //格式后的数据
filename, //表名
merges = [], //合并单元格格式
autoWidth = true,
bookType = 'xlsx',
formStyle, // 样式修改
} = {}) {
/* original data */
//判断是否有表名、没有则赋予固定表名
filename = filename || 'excel-list'
var ws_name = "SheetJS";
var wb = new Workbook(),
//将data转化格式 用于接下来动态宽度
ws = sheet_from_array_of_arrays(data);
//合并单元格
ws['!merges'] = merges
//动态设置宽度
if (autoWidth) {
/*设置worksheet每列的最大宽度*/
const colWidth = data.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
};
}
}))
//判断合并单元格中是否有同行合并,有则将其长度设为0
if (merges.length > 0) {
if (!ws['!merges']) ws['!merges'] = [];
merges.forEach(a => {
let w1 = a.s.c
let e1 = a.s.r
let w2 = a.e.c
let e2 = a.e.r
if (e1 == e2) {
if (+w1 > +w2) {
let jh = w1
w1 = w2
w2 = jh
}
for (let i = +w1; i <= +w2; i++) {
colWidth[e1][i]['wch'] = 0;
}
}
})
}
/*以第一行为初始值 判断对应每一列的最大长度*/
let result = colWidth[0];
for (let i = 1; i < colWidth.length; i++) {
for (let j = 0; j < colWidth[i].length; j++) {
if (result[j]['wch'] < colWidth[i][j]['wch']) {
result[j]['wch'] = colWidth[i][j]['wch'];
}
}
}
ws['!cols'] = result;
}
/* add worksheet to workbook */
wb.SheetNames.push(ws_name);
wb.Sheets[ws_name] = ws;
var dataInfo = wb.Sheets[wb.SheetNames[0]];
const borderAll = { //单元格外侧框线
top: {
style: 'thin'
},
bottom: {
style: 'thin'
},
left: {
style: 'thin'
},
right: {
style: 'thin'
}
};
//给所以单元格加上边框
const letter = {
"A": 0,
"B": 1,
"C": 2,
"D": 3,
"E": 4,
"F": 5,
"G": 6,
"H": 7,
"I": 8,
"J": 9,
"K": 10,
"L": 11,
"M": 12,
"N": 13,
"O": 14,
"P": 15,
"Q": 16,
"R": 17,
"S": 18,
"T": 19,
"U": 20,
"V": 21,
"W": 22,
"X": 23,
"Y": 24,
"Z": 25
}
const range = dataInfo['!ref'].split(':') // 表格范围区域
let a1 = range[0].slice(0, 1) // A
let b1 = +range[0].slice(1) // 1
let a2 = range[1].slice(0, 1) // 最大为Z(26) 表格的列数
let b2 = +range[1].slice(1) // 表格的行数
for (let i = letter[a1]; i <= letter[a2]; i++) {
for (let j = b1; j <= b2; j++) {
let value = ''
for (let key in letter) {
if (letter[key] == i) {
value = key
}
}
if (!dataInfo[value + j]) {
dataInfo[value + j] = {
s: {
border: borderAll,
}
}
}
}
}
for (var i in dataInfo) {
if (i == '!ref' || i == '!merges' || i == '!cols') {
} else {
dataInfo[i + ''].s = {
border: borderAll,
//居中属性
alignment: {
horizontal: "center",
vertical: "center"
},
}
}
}
//设置主标题样式
for (let key in formStyle) {
dataInfo[key].s = formStyle[key]
dataInfo[key].s.border = borderAll
}
var wbout = XLSXS.write(wb, {
bookType: bookType,
bookSST: false,
type: 'binary'
});
saveAs(new Blob([s2ab(wbout)], {
type: "application/octet-stream"
}), `${filename}.${bookType}`);
};
// 导出多个表格 下方带tab切换
export function exportjsontoexcelMore({
dataList,
filename,
bookType = 'xlsx',
}) {
//判断是否有表名、没有则赋予固定表名
filename = filename || 'excel-list'
var wb = new Workbook()
dataList.forEach((item, index) => {
let {
Sheet,
data,
merges,
autoWidth,
formStyle
} = item
let ws = sheet_from_array_of_arrays(data)
//合并单元格
ws['!merges'] = merges
//动态设置宽度
if (autoWidth) {
/*设置worksheet每列的最大宽度*/
const colWidth = data.map(row => row.map(val => {
/*先判断是否为null/undefined*/
if (val == null) {
return {
'wch': 15
};
}
/*再判断是否为中文*/
else if (val.toString().charCodeAt(0) > 255) {
return {
'wch': val.toString().length * 2
};
} else {
return {
'wch': val.toString().length
};
}
}))
//判断合并单元格中是否有同行合并,有则将其长度设为0
if (merges.length > 0) {
if (!ws['!merges']) ws['!merges'] = [];
merges.forEach(a => {
let w1 = a.s.c
let e1 = a.s.r
let w2 = a.e.c
let e2 = a.e.r
if (e1 == e2) {
if (+w1 > +w2) {
let jh = w1
w1 = w2
w2 = jh
}
for (let i = +w1; i <= +w2; i++) {
colWidth[e1][i]['wch'] = 0;
}
}
})
}
/*以第一行为初始值 判断对应每一列的最大长度*/
let result = colWidth[0];
for (let i = 1; i < colWidth.length; i++) {
for (let j = 0; j < colWidth[i].length; j++) {
if (result[j]['wch'] < colWidth[i][j]['wch']) {
result[j]['wch'] = colWidth[i][j]['wch'];
}
}
}
ws['!cols'] = result;
}
/* add worksheet to workbook */
wb.SheetNames.push(Sheet);
wb.Sheets[Sheet] = ws;
let dataInfo = wb.Sheets[wb.SheetNames[index]];
const borderAll = { //单元格外侧框线
top: {
style: 'thin'
},
bottom: {
style: 'thin'
},
left: {
style: 'thin'
},
right: {
style: 'thin'
}
};
//给所以单元格加上边框
const letter = {
"A": 0,
"B": 1,
"C": 2,
"D": 3,
"E": 4,
"F": 5,
"G": 6,
"H": 7,
"I": 8,
"J": 9,
"K": 10,
"L": 11,
"M": 12,
"N": 13,
"O": 14,
"P": 15,
"Q": 16,
"R": 17,
"S": 18,
"T": 19,
"U": 20,
"V": 21,
"W": 22,
"X": 23,
"Y": 24,
"Z": 25
}
const range = dataInfo['!ref'].split(':') // 表格范围区域
let a1 = range[0].slice(0, 1) // A
let b1 = +range[0].slice(1) // 1
let a2 = range[1].slice(0, 1) // 最大为Z(26) 表格的列数
let b2 = +range[1].slice(1) // 表格的行数
for (let i = letter[a1]; i <= letter[a2]; i++) {
for (let j = b1; j <= b2; j++) {
let value = ''
for (let key in letter) {
if (letter[key] == i) {
value = key
}
}
if (!dataInfo[value + j]) {
dataInfo[value + j] = {
s: {
border: borderAll,
}
}
}
}
}
for (var i in dataInfo) {
if (i == '!ref' || i == '!merges' || i == '!cols') {
} else {
dataInfo[i + ''].s = {
border: borderAll,
//居中属性
alignment: {
horizontal: "center",
vertical: "center"
},
}
}
}
//设置主标题样式
for (let key in formStyle) {
dataInfo[key].s = formStyle[key]
dataInfo[key].s.border = borderAll
}
})
var wbout = XLSXS.write(wb, {
bookType: bookType,
bookSST: false,
type: 'binary'
});
saveAs(new Blob([s2ab(wbout)], {
type: "application/octet-stream"
}), `${filename}.${bookType}`);
}
vue
import { exportjsontoexcel,exportjsontoexcelMore } from '@/libs/exportExcel'
handleExport () {
// s-start-开始;e-end-结束;r-row-行;c-column-列
let title = 'xxx标题'
let timeText = `统计日期:2020到2021`
let headList = [
[title, null, null, null, null, null, null, null, null, null, null, null, null, null, null],
[timeText, null, null, null, null, null, null, null, null, null, null, null, null, null, null],
['工号', '姓名', '提成1', null, null, null, null, null, '提成2', null, '提成3', null, '提成4', null, '合计(元)'],
['工号', '姓名', 'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L','M' ]
] // 导出的表头数据
let contentList = [] //表格内容导出的数据
let merges1 = [ // 设置表头单元格合
{ s: { r: 0, c: 0 }, e: { r: 0, c: 14 } },
{ s: { r: 1, c: 0 }, e: { r: 1, c: 14 } },
{ s: { r: 2, c: 2 }, e: { r: 2, c: 7 } },
{ s: { r: 2, c: 8 }, e: { r: 2, c: 9 } },
{ s: { r: 2, c: 10 }, e: { r: 2, c: 11 } },
{ s: { r: 2, c: 12 }, e: { r: 2, c: 13 } },
{ s: { r: 2, c: 0 }, e: { r: 3, c: 0 } },
{ s: { r: 2, c: 1 }, e: { r: 3, c: 1 } },
{ s: { r: 2, c: 14 }, e: { r: 3, c: 14 } }
]
let merges2 = [] // 设置表格内容单元格合并
let aoa = [...headList, ...contentList] // 导出的数据
let merges = [...merges1, ...merges2] // 合并单元格
const filename = `文件名.xlsx`
// 样式修改
let formStyle = {}
formStyle['A1'] = {
font: {
name: '宋体',
bold: true,
italic: false,
underline: false
},
alignment: {
horizontal: 'center',
vertical: 'center'
}
}
exportjsontoexcel ({
data: aoa, // 表格数据
merges, // 合并单元格
filename, // 导出文件名
autoWidth: true, // 自适应宽度
bookType: 'xlsx', // 导出类型
formStyle: formStyle // 特殊行或列样式
})
},
handleExportMore () {
let list = []
for (let i = 0; i < 3; i++) {
let title = `第${i + 1}个表格`
let timeText = `统计日期:2020到2021`
let headList = [
[title, null, null, null, null, null, null, null, null, null, null, null, null, null, null],
[timeText, null, null, null, null, null, null, null, null, null, null, null, null, null, null],
['工号', '姓名', '提成1', null, null, null, null, null, '提成2', null, '提成3', null, '提成4', null, '合计(元)'],
['工号', '姓名', '大哥', '大哥', '大哥', '大哥', '大哥', '大哥', '大哥', '大哥', '大哥', '大哥', '大哥', '大哥', '大哥']
] // 导出的表头数据
let contentList = []
let merges1 = [ // 设置表头单元格合
{ s: { r: 0, c: 0 }, e: { r: 0, c: 14 } },
{ s: { r: 1, c: 0 }, e: { r: 1, c: 14 } },
{ s: { r: 2, c: 2 }, e: { r: 2, c: 7 } },
{ s: { r: 2, c: 8 }, e: { r: 2, c: 9 } },
{ s: { r: 2, c: 10 }, e: { r: 2, c: 11 } },
{ s: { r: 2, c: 12 }, e: { r: 2, c: 13 } },
{ s: { r: 2, c: 0 }, e: { r: 3, c: 0 } },
{ s: { r: 2, c: 1 }, e: { r: 3, c: 1 } },
{ s: { r: 2, c: 14 }, e: { r: 3, c: 14 } }
]
let merges2 = [] // 设置表格内容单元格合并
let aoa = [...headList, ...contentList] // 导出的数据
let merges = [...merges1, ...merges2] // 合并单元格
// 样式修改
let formStyle = {}
formStyle['A1'] = {
font: {
name: '宋体',
bold: true,
italic: false,
underline: false
},
alignment: {
horizontal: 'center',
vertical: 'center'
}
}
list.push({
Sheet: `第${i}个`, // 下方tab切换名称
data: aoa, // 表格数据
merges, // 合并单元格
autoWidth: true, // 自适应宽度
formStyle: formStyle // 特殊行或列样式
})
}
exportjsontoexcelMore({
dataList: list,
bookType: 'xlsx', // 导出类型
filename: '我的测试导出' // 导出标题名
})
},