首先声明 非原创 原创地址
一、 第一步先安装3个依赖包:
npm install -S file-saver
npm install -S xlsx
npm install -D script-loader
二、在项目中新建一个文件夹(名字自取,这里命名excel),然后在文件夹中放入Blob.js 和 export2Excel.js 两个文件(我把地址贴在这,可以直接下载解压)
链接地址:https://pan.baidu.com/s/1itpx7GqPhp44CWdK-NT5qw
提取码:kzlr
下载解压完建好文件夹就长这样:
三、在你要导出表格的"xxx.vue"页面给按钮绑定点击事件:
<Button type="primary" @click="exportData" style="display:block; margin:5px auto;">导出Excel表格</Button>
在标签的methods方法中调用下载表格的事件
methods: {
//触发按钮点击下载事件
exportData() {
this.excelData = this.historyList; //将你要导出的数组数据(historyList)赋值给excelDate
this.export2Excel(); //调用export2Excel函数,填写表头(clomns里的type)和对应字段(historyList里的属性名)
},
//表格数据写入excel
export2Excel() {
var that = this;
require.ensure([], () => {
const {
export_json_to_excel
} = require("@/assets/excel/Export2Excel");
//这里使用绝对路径( @表示src文件夹 ),使用@/+存放export2Excel的路径【也可以写成相对于你当前"xxx.vue"文件的相对路径,例如我的页面放在assets文件夹同级下的views文件夹下的“home.vue”里,那这里路径也可以写成"../assets/excel/Export2Excel"】
const tHeader = ["报名日期", "姓名", "班级", "学号","性别","邮箱","联系方式","所选导师"]; // 导出的excel表头名信息
const filterVal = [
"date",
"name",
"class",
"studentId",
"sex",
"email",
"telephone",
"teacher"
]; // 导出的excel表头字段名,需要导出表格字段名
const list = that.excelData;
const data = that.formatJson(filterVal, list);
export_json_to_excel(tHeader, data, "学生报名信息汇总"); // 导出的表格名称,根据需要自己命名
});
},
//格式转换,直接复制即可,不需要修改什么
formatJson(filterVal, jsonData) {
return jsonData.map(v => filterVal.map(j => v[j]));
}
}
四、总结一下这段代码需要按照个人需求修改的几个小点:
高亮部分就是改动点 ——
this.excelData = this.historyList;
require("@/assets/excel/Export2Excel");
const tHeader = [“报名日期”, “姓名”, “班级”, “学号”,“性别”,“邮箱”,“联系方式”,“所选导师”];
const filterVal = [
“date”,
“name”,
“class”,
“studentId”,
“sex”,
“email”,
“telephone”,
“teacher”
];
export_json_to_excel(tHeader, data, “学生报名信息汇总”);
附两个js文件的源码:
Blob.js:
/*! @source http://purl.eligrey.com/github/Blob.js/blob/master/Blob.js */
(function(view){view.URL=view.URL||view.webkitURL;if(view.Blob&&view.URL){try{new Blob;return}catch(e){}}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){var bb=this.data;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{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+=""}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));
Export2Excel.js:
/* eslint-disable */
require('script-loader!file-saver');
require('script-loader!./../excel/Blob');
require('script-loader!xlsx/dist/xlsx.core.min');
function generateArray(table) {
let out = [];
let rows = table.querySelectorAll('tr');
let ranges = [];
for (let R = 0; R < rows.length; ++R) {
let outRow = [];
let row = rows[R];
let columns = row.querySelectorAll('td');
for (let C = 0; C < columns.length; ++C) {
let cell = columns[C];
let colspan = cell.getAttribute('colspan');
let rowspan = cell.getAttribute('rowspan');
let 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 (let 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 (let k = 0; k < colspan - 1; ++k) outRow.push(null);
}
out.push(outRow);
}
return [out, ranges];
};
function datenum(v, date1904) {
if (date1904) v += 1462;
let 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) {
let ws = {};
let range = {s: {c: 10000000, r: 10000000}, e: {c: 0, r: 0}};
for (let R = 0; R !== data.length; ++R) {
for (let 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;
let cell = {v: data[R][C]};
if (cell.v == null) continue;
let 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) {
let buf = new ArrayBuffer(s.length);
let view = new Uint8Array(buf);
for (let i = 0; i !== s.length; ++i) view[i] = s.charCodeAt(i) & 0xFF;
return buf;
}
export function export_table_to_excel(id) {
let theTable = document.getElementById(id);
let oo = generateArray(theTable);
let ranges = oo[1];
/* original data */
let data = oo[0];
let ws_name = "SheetJS";
console.log(data);
let 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;
let 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 */
let data = jsonData;
data.unshift(th);
let ws_name = "SheetJS";
let wb = new Workbook(), ws = sheet_from_array_of_arrays(data);
/* add worksheet to workbook */
wb.SheetNames.push(ws_name);
wb.Sheets[ws_name] = ws;
let wbout = XLSX.write(wb, {bookType: 'xlsx', bookSST: false, type: 'binary'});
let title = defaultTitle || '列表';
saveAs(new Blob([s2ab(wbout)], {type: "application/octet-stream"}), title + ".xlsx")
}