Vue 下载 Excel 文件
Vue前端将List列表下载为Excel文件
安装依赖包
首先前端将List列表生成Excel文件需要下载几个依赖包。
npm install -S file-saver（生产依赖，则为-s）
npm install -S xlsx
npm install -D script-loader （开发依赖，则为-d）
引入文件
在 src 目录下面新建一个文件夹，例如 vendor，然后把：Blob.js 和 Export2Excel.js 两个文件放在该文件夹下，在Export2Excel.js 文件中我们引入。这两个文件在文末我会直接贴出来。
require('script-loader!file-saver'); // 保存文件
require('script-loader!@/vendor/Blob'); // 转二进制
下面这步好像不写也可以
由于这几个文件不支持 import 引入，所以我们需要 script-loader 来将他们挂载到全局环境下
import XLSX from 'xlsx' // xlsx核心
使用
然后就可以在我们需要的地方将List转成文件下载下来。
比如我有一个 List 数组 mData：
data() {
return {
mData: [
{
name: 'wjw',
age: 18,
sex: '1'
}, {
name: 'tcz',
age: 22,
sex: '1'
}, {
name: 'zx',
age: 23,
sex: '1'
}, {
name: 'qy',
age: 21,
sex: '1'
}, {
name: 'lxh',
age: 22,
sex: '1'
},
]
}
},
然后有一个按钮，点击按钮就会把 mData 列表转成 Excel 文件下载下来。
这个按钮我用了 ant-d 组件库，其实无所谓，只要是个点击事件触发就行。
<a-button @click="down" type="primary">
下载
</a-button>
然后会执行这个函数。
methods: {
down() {
import('@/vendor/Export2Excel').then(excel => {
const tHeader = ['姓名', '年龄', '性别'] // excel的表头标题，这个根据自己需要改
const filterVal = ['name', 'age', 'sex'] // 需要导出对应自己列表中的每项数据，这个就是根据自己的需要改
const list = this.mData // 整个列表的数据
const data = this.formatJson(filterVal, list)
excel.export_json_to_excel(
tHeader,
data,
'table-list' // 文件名称,若不写导出文件可能不识别
)
})
},
formatJson(filterVal, jsonData) { // 在整个列表的数据中过滤导出自己需要的数据
return jsonData.map(v => filterVal.map(j => v[j]))
},
}
文件生成成功！
后台返回blob文件流，前端实现下载文件
看这篇文章 ：
https://www.cnblogs.com/tcz1018/p/14073558.html
Blob.js
/* Blob.js
* A Blob, File, FileReader & URL implementation.
* 2018-08-09
*
* By Eli Grey, http://eligrey.com
* By Jimmy Wärting, https://github.com/jimmywarting
* License: MIT
* See https://github.com/eligrey/Blob.js/blob/master/LICENSE.md
*/
;(function(){
var global = typeof window === 'object'
? window : typeof self === 'object'
? self : this
var BlobBuilder = global.BlobBuilder
|| global.WebKitBlobBuilder
|| global.MSBlobBuilder
|| global.MozBlobBuilder;
global.URL = global.URL || global.webkitURL || function(href, a) {
a = document.createElement('a')
a.href = href
return a
}
var origBlob = global.Blob
var createObjectURL = URL.createObjectURL
var revokeObjectURL = URL.revokeObjectURL
var strTag = global.Symbol && global.Symbol.toStringTag
var blobSupported = false
var blobSupportsArrayBufferView = false
var arrayBufferSupported = !!global.ArrayBuffer
var blobBuilderSupported = BlobBuilder
&& BlobBuilder.prototype.append
&& BlobBuilder.prototype.getBlob;
try {
// Check if Blob constructor is supported
blobSupported = new Blob(['ä']).size === 2
// Check if Blob constructor supports ArrayBufferViews
// Fails in Safari 6, so we need to map to ArrayBuffers there.
blobSupportsArrayBufferView = new Blob([new Uint8Array([1,2])]).size === 2
} catch(e) {}
/**
* Helper function that maps ArrayBufferViews to ArrayBuffers
* Used by BlobBuilder constructor and old browsers that didn't
* support it in the Blob constructor.
*/
function mapArrayBufferViews(ary) {
return ary.map(function(chunk) {
if (chunk.buffer instanceof ArrayBuffer) {
var buf = chunk.buffer;
// if this is a subarray, make a copy so we only
// include the subarray region from the underlying buffer
if (chunk.byteLength !== buf.byteLength) {
var copy = new Uint8Array(chunk.byteLength);
copy.set(new Uint8Array(buf, chunk.byteOffset, chunk.byteLength));
buf = copy.buffer;
}
return buf;
}
return chunk;
});
}
function BlobBuilderConstructor(ary, options) {
options = options || {};
var bb = new BlobBuilder();
mapArrayBufferViews(ary).forEach(function(part) {
bb.append(part);
});
return options.type ? bb.getBlob(options.type) : bb.getBlob();
};
function BlobConstructor(ary, options) {
return new origBlob(mapArrayBufferViews(ary), options || {});
};
if (global.Blob) {
BlobBuilderConstructor.prototype = Blob.prototype;
BlobConstructor.prototype = Blob.prototype;
}
function FakeBlobBuilder() {
function toUTF8Array(str) {
var utf8 = [];
for (var i=0; i < str.length; i++) {
var charcode = str.charCodeAt(i);
if (charcode < 0x80) utf8.push(charcode);
else if (charcode < 0x800) {
utf8.push(0xc0 | (charcode >> 6),
0x80 | (charcode & 0x3f));
}
else if (charcode < 0xd800 || charcode >= 0xe000) {
utf8.push(0xe0 | (charcode >> 12),
0x80 | ((charcode>>6) & 0x3f),
0x80 | (charcode & 0x3f));
}
// surrogate pair
else {
i++;
// UTF-16 encodes 0x10000-0x10FFFF by
// subtracting 0x10000 and splitting the
// 20 bits of 0x0-0xFFFFF into two halves
charcode = 0x10000 + (((charcode & 0x3ff)<<10)
| (str.charCodeAt(i) & 0x3ff));
utf8.push(0xf0 | (charcode >>18),
0x80 | ((charcode>>12) & 0x3f),
0x80 | ((charcode>>6) & 0x3f),
0x80 | (charcode & 0x3f));
}
}
return utf8;
}
function fromUtf8Array(array) {
var out, i, len, c;
var char2, char3;
out = "";
len = array.length;
i = 0;
while (i < len) {
c = array[i++];
switch (c >> 4)
{
case 0: case 1: case 2: case 3: case 4: case 5: case 6: case 7:
// 0xxxxxxx
out += String.fromCharCode(c);
break;
case 12: case 13:
// 110x xxxx 10xx xxxx
char2 = array[i++];
out += String.fromCharCode(((c & 0x1F) << 6) | (char2 & 0x3F));
break;
case 14:
// 1110 xxxx 10xx xxxx 10xx xxxx
char2 = array[i++];
char3 = array[i++];
out += String.fromCharCode(((c & 0x0F) << 12) |
((char2 & 0x3F) << 6) |
((char3 & 0x3F) << 0));
break;
}
}
return out;
}
function isDataView(obj) {
return obj && DataView.prototype.isPrototypeOf(obj)
}
function bufferClone(buf) {
var view = new Array(buf.byteLength)
var array = new Uint8Array(buf)
var i = view.length
while(i--) {
view[i] = array[i]
}
return view
}
function encodeByteArray(input) {
var byteToCharMap = 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789'
var output = [];
for (var i = 0; i < input.length; i += 3) {
var byte1 = input[i];
var haveByte2 = i + 1 < input.length;
var byte2 = haveByte2 ? input[i + 1] : 0;
var haveByte3 = i + 2 < input.length;
var byte3 = haveByte3 ? input[i + 2] : 0;
var outByte1 = byte1 >> 2;
var outByte2 = ((byte1 & 0x03) << 4) | (byte2 >> 4);
var outByte3 = ((byte2 & 0x0F) << 2) | (byte3 >> 6);
var outByte4 = byte3 & 0x3F;
if (!haveByte3) {
outByte4 = 64;
if (!haveByte2) {
outByte3 = 64;
}
}
output.push(
byteToCharMap[outByte1], byteToCharMap[outByte2],
byteToCharMap[outByte3], byteToCharMap[outByte4])
}
return output.join('')
}
var create = Object.create || function (a) {
function c() {}
c.prototype = a;
return new c
}
if (arrayBufferSupported) {
var viewClasses = [
'[object Int8Array]',
'[object Uint8Array]',
'[object Uint8ClampedArray]',
'[object Int16Array]',
'[object Uint16Array]',
'[object Int32Array]',
'[object Uint32Array]',
'[object Float32Array]',
'[object Float64Array]'
]
var isArrayBufferView = ArrayBuffer.isView || function(obj) {
return obj && viewClasses.indexOf(Object.prototype.toString.call(obj)) > -1
}
}
/********************************************************/
/* Blob constructor */
/********************************************************/
function Blob(chunks, opts) {
chunks = chunks || []
for (var i = 0, len = chunks.length; i < len; i++) {
var chunk = chunks[i]
if (chunk instanceof Blob) {
chunks[i] = chunk._buffer
} else if (typeof chunk === 'string') {
chunks[i] = toUTF8Array(chunk)
} else if (arrayBufferSupported && (ArrayBuffer.prototype.isPrototypeOf(chunk) || isArrayBufferView(chunk))) {
chunks[i] = bufferClone(chunk)
} else if (arrayBufferSupported && isDataView(chunk)) {
chunks[i] = bufferClone(chunk.buffer)
} else {
chunks[i] = toUTF8Array(String(chunk))
}
}
this._buffer = [].concat.apply([], chunks)
this.size = this._buffer.length
this.type = opts ? opts.type || '' : ''
}
Blob.prototype.slice = function(start, end, type) {
var slice = this._buffer.slice(start || 0, end || this._buffer.length)
return new Blob([slice], {type: type})
}
Blob.prototype.toString = function() {
return '[object Blob]'
}
/********************************************************/
/* File constructor */
/********************************************************/
function File(chunks, name, opts) {
opts = opts || {}
var a = Blob.call(this, chunks, opts) || this
a.name = name
a.lastModifiedDate = opts.lastModified ? new Date(opts.lastModified) : new Date
a.lastModified = +a.lastModifiedDate
return a
}
File.prototype = create(Blob.prototype);
File.prototype.constructor = File;
if (Object.setPrototypeOf)
Object.setPrototypeOf(File, Blob);
else {
try {File.__proto__ = Blob} catch (e) {}
}
File.prototype.toString = function() {
return '[object File]'
}
/********************************************************/
/* FileReader constructor */
/********************************************************/
function FileReader() {
if (!(this instanceof FileReader))
throw new TypeError("Failed to construct 'FileReader': Please use the 'new' operator, this DOM object constructor cannot be called as a function.")
var delegate = document.createDocumentFragment()
this.addEventListener = delegate.addEventListener
this.dispatchEvent = function(evt) {
var local = this['on' + evt.type]
if (typeof local === 'function') local(evt)
delegate.dispatchEvent(evt)
}
this.removeEventListener = delegate.removeEventListener
}
function _read(fr, blob, kind) {
if (!(blob instanceof Blob))
throw new TypeError("Failed to execute '" + kind + "' on 'FileReader': parameter 1 is not of type 'Blob'.")
fr.result = ''
setTimeout(function(){
this.readyState = FileReader.LOADING
fr.dispatchEvent(new Event('load'))
fr.dispatchEvent(new Event('loadend'))
})
}
FileReader.EMPTY = 0
FileReader.LOADING = 1
FileReader.DONE = 2
FileReader.prototype.error = null
FileReader.prototype.onabort = null
FileReader.prototype.onerror = null
FileReader.prototype.onload = null
FileReader.prototype.onloadend = null
FileReader.prototype.onloadstart = null
FileReader.prototype.onprogress = null
FileReader.prototype.readAsDataURL = function(blob) {
_read(this, blob, 'readAsDataURL')
this.result = 'data:' + blob.type + ';base64,' + encodeByteArray(blob._buffer)
}
FileReader.prototype.readAsText = function(blob) {
_read(this, blob, 'readAsText')
this.result = fromUtf8Array(blob._buffer)
}
FileReader.prototype.readAsArrayBuffer = function(blob) {
_read(this, blob, 'readAsText')
this.result = blob._buffer.slice()
}
FileReader.prototype.abort = function() {}
/********************************************************/
/* URL */
/********************************************************/
URL.createObjectURL = function(blob) {
return blob instanceof Blob
? 'data:' + blob.type + ';base64,' + encodeByteArray(blob._buffer)
: createObjectURL.call(URL, blob)
}
URL.revokeObjectURL = function(url) {
revokeObjectURL && revokeObjectURL.call(URL, url)
}
/********************************************************/
/* XHR */
/********************************************************/
var _send = global.XMLHttpRequest && global.XMLHttpRequest.prototype.send
if (_send) {
XMLHttpRequest.prototype.send = function(data) {
if (data instanceof Blob) {
this.setRequestHeader('Content-Type', data.type)
_send.call(this, fromUtf8Array(data._buffer))
} else {
_send.call(this, data)
}
}
}
global.FileReader = FileReader
global.File = File
global.Blob = Blob
}
if (strTag) {
File.prototype[strTag] = 'File'
Blob.prototype[strTag] = 'Blob'
FileReader.prototype[strTag] = 'FileReader'
}
function fixFileAndXHR() {
var isIE = !!global.ActiveXObject || (
'-ms-scroll-limit' in document.documentElement.style &&
'-ms-ime-align' in document.documentElement.style
)
// Monkey patched
// IE don't set Content-Type header on XHR whose body is a typed Blob
// https://developer.microsoft.com/en-us/microsoft-edge/platform/issues/6047383
var _send = global.XMLHttpRequest && global.XMLHttpRequest.prototype.send
if (isIE && _send) {
XMLHttpRequest.prototype.send = function(data) {
if (data instanceof Blob) {
this.setRequestHeader('Content-Type', data.type)
_send.call(this, data)
} else {
_send.call(this, data)
}
}
}
try {
new File([], '')
} catch(e) {
try {
var klass = new Function('class File extends Blob {' +
'constructor(chunks, name, opts) {' +
'opts = opts || {};' +
'super(chunks, opts || {});' +
'this.name = name;' +
'this.lastModifiedDate = opts.lastModified ? new Date(opts.lastModified) : new Date;' +
'this.lastModified = +this.lastModifiedDate;' +
'}};' +
'return new File([], ""), File'
)()
global.File = klass
} catch(e) {
var klass = function(b, d, c) {
var blob = new Blob(b, c)
var t = c && void 0 !== c.lastModified ? new Date(c.lastModified) : new Date
blob.name = d
blob.lastModifiedDate = t
blob.lastModified = +t
blob.toString = function() {
return '[object File]'
}
if (strTag)
blob[strTag] = 'File'
return blob
}
global.File = klass
}
}
}
if (blobSupported) {
fixFileAndXHR()
global.Blob = blobSupportsArrayBufferView ? global.Blob : BlobConstructor
} else if (blobBuilderSupported) {
fixFileAndXHR()
global.Blob = BlobBuilderConstructor;
} else {
FakeBlobBuilder()
}
})();
Export2Excel.js
/* eslint-disable */
require('script-loader!file-saver');
require('script-loader!@/vendor/Blob');
require('script-loader!xlsx/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);
console.log('a')
var oo = generateArray(theTable);
var ranges = oo[1];
/* original data */
var data = oo[0];
var ws_name = "SheetJS";
console.log(data);
var wb = new Workbook(), 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")
}
function formatJson(jsonData) {
console.log(jsonData)
}
export function export_json_to_excel(th, jsonData, defaultTitle) {
/* original data */
var data = jsonData;
data.unshift(th);
var ws_name = "SheetJS";
var wb = new Workbook(), ws = sheet_from_array_of_arrays(data);
/* 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'});
var title = defaultTitle || '列表'
saveAs(new Blob([s2ab(wbout)], {type: "application/octet-stream"}), title + ".xlsx")
}