前端导出Excel，让后端刮目相看

　　前言
　　这几天在开发中遇到一个事情，做一个导出的功能，但是后台是新来的菜鸟，等了三天没反应，接口做不出来，我内心一万个…，于是乎我就打算前端做导出了，网上复制了一些代码就出来了。导入依赖// npm npm install -S file-saver xlsx npm install -D script-loader 复制代码导入Bolb.js与Export2Excel.js
　　首先拷贝以下两个文件代码： 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＂) } 复制代码
　　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(/^[objects(.*)]$/)[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)); 复制代码
　　因为我们在Export2Excel.js中设置的Blob路径为vendor，所以我们要在src下建立相关文件夹并放入这两个js。
　　设置导出表格数据
　　假设我们需要导出一个这样的表格
　　数据// data dataList: [     { name: ＂张明＂, loginTime: 16, id: 1, department: ＂生产部＂, sex: ＂男＂ },     { name: ＂小金＂, loginTime: 11, id: 2, department: ＂生产部＂, sex: ＂女＂ },     { name: ＂小凌＂, loginTime: 21, id: 3, department: ＂生产部＂, sex: ＂男＂ },     { name: ＂盖伦＂, loginTime: 5, id: 4, department: ＂测试部＂, sex: ＂男＂ } ] 复制代码
　　html
       
         
ID
         
名称
         
登陆次数
         
部门
       
       
         
{{ item.id }}
         
{{ item.name }}
         
{{ item.loginTime }}
         
{{ item.department }}
       
     
     导出信息 复制代码
　　methods // 导出excel exportExcel() {   // 引入文件   const { export_json_to_excel } = require(＂vendor/Export2Excel.js＂);   // 表头   const tHeader = [＂ID＂, ＂名称＂, ＂登陆次数＂, ＂部门＂];   // table表格中对应的属性名   const filterVal = [＂id＂, ＂name＂, ＂loginTime＂, ＂department＂];   // 表格绑定数据转json   const data = this.formatJson(filterVal, this.dataList);   export_json_to_excel(     tHeader,     data,     ＂部门登陆信息＂ + new Date().toLocaleDateString()   ); // 对应下载文件的名字 }, // 导出列表格式化数据的方法 formatJson(filterVal, jsonData) {   return jsonData.map(v => filterVal.map(j => v[j])); }  复制代码
　　展示效果
　　到此，前端导出功能实现。这种方式的优势在于:1.不用请求后端获取文件。2.所见即所得，前端可有灵活地把控输出的数据，不用因为导出的数据有差错而拉着后端一起联调。多sheet导出
　　我们可能有时候会遇到对Excel多sheet的操作，比如部门的多季度绩效分多sheet导出。
　　我们需要在Export2Excel.js中添加一个方法:/**  * 多sheet导出  * @param {Array} th 表头  * @param {Array} jsonDatas 数据集   * @param {String} defaultTitle 导出的excel名称  * @param {Array} sheetNames sheet名称集  */ export function export_season_to_excel(th, jsonDatas, defaultTitle, sheetNames) {   var wb = new Workbook()   jsonDatas.forEach((item, index) => {     var data = item;     data.unshift(th);     var ws_name = sheetNames[index];     var 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＂) } 复制代码
　　页面中的data内容：// data 季度绩效数据 seasonDatas: [         [           { name: ＂张明＂, score: 72 },           { name: ＂小金＂, score: 21 },           { name: ＂小凌＂, score: 16 },           { name: ＂盖伦＂, score: 84 }         ],         [           { name: ＂张明＂, score: 32 },           { name: ＂小金＂, score: 54 },           { name: ＂小凌＂, score: 45 },           { name: ＂盖伦＂, score: 26 }         ],         [           { name: ＂张明＂, score: 67 },           { name: ＂小金＂, score: 87 },           { name: ＂小凌＂, score: 45 },           { name: ＂盖伦＂, score: 78 }         ],         [           { name: ＂张明＂, score: 54 },           { name: ＂小金＂, score: 34 },           { name: ＂小凌＂, score: 26 },           { name: ＂盖伦＂, score: 34 }         ]       ] 复制代码
　　html                 导出绩效信息 复制代码
　　执行方法     // methods 导出绩效信息excel     exportSeason() {       // 引入文件       const { export_season_to_excel } = require(＂vendor/Export2Excel.js＂);       // 表头       const tHeader = [＂名称＂, ＂绩效分＂];       // table表格中对应的属性名       const filterVal = [＂name＂, ＂score＂];       let datas = [];       let sheets = [];       this.seasonDatas.forEach((item, index) => {         // 表格绑定数据转json         datas.push(this.formatJson(filterVal, item));         sheets.push(`第${index + 1}季度绩效`);       });       export_season_to_excel(         tHeader,         datas,         ＂部门季度绩效＂ + new Date().toLocaleDateString(),         sheets       ); // 对应下载文件的名字     }, 复制代码
　　展示效果：
　　至此，多Sheet导出我们也完成了。项目地址
　　项目地址：https://github.com/FireSmallPanda/vuexDemo.git
　　存疑解析大数据导出和自定义表头名称
　　在文章发出后大家对大数据导出和怎么自定义表头存在疑惑，我这边设置了一个可以自定义导出规模的例子。
　　html部分    
行     
列     导出自定义大小表格信息 复制代码
　　js部分// data rows: 100, // 行 cols: 100 // 列 // methods  // 导出自定义数据 outPutBigData() {   // 引入文件   const { export_json_to_excel } = require(＂vendor/Export2Excel.js＂);   // 表头   let tHeader = [];   // table表格中对应的属性名   let filterVal = [];   // 需要导出的内容   let pushData = [];   // 自定义生成列   for (let i = 0; i < this.cols * 1; i++) {     tHeader.push(`第${i + 1}列数据`);     filterVal.push(i);   }   // 生成自定义数据   for (let j = 0; j < this.rows * 1; j++) {     let pushObj = {};     for (let i = 0; i < this.cols * 1; i++) {       // 这边为展示数据不一致性设置为随机数       pushObj[i] = Math.random();     }     // 插入一条数据     pushData.push(pushObj);   }   // ---至此模拟后端请求数据结束---   // 表格绑定数据转json   const data = this.formatJson(filterVal, pushData);   export_json_to_excel(     tHeader,     data,     ＂自定义导出数据＂ + new Date().toLocaleDateString()   ); // 对应下载文件的名字 }, 复制代码
　　演示效果:
　　为方便大家直观的看到，我们这次导出10000行10列的随机数据.可以看到，其中的列名称我也是自动生成的。
　　作者：有趣的老凌
　　链接：https://juejin.cn/post/7030291455243452429
　　来源：稀土掘金
　　著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。