前端实现导出Excel

一、准备文件

1. 创建excel文件夹

├─excel
   ├─Blob.js
   ├─Export2Excel.js

2.

Blob.js

文件夹内容 ↓

/* eslint-disable *//* Blob.js*//*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{newBlob;return;}catch(e){}}// Internally we use a BlobBuilder implementation to base Blob off of// in order to support older browsers that only have BlobBuildervar BlobBuilder = view.BlobBuilder || view.WebKitBlobBuilder || view.MozBlobBuilder ||(function(view){varget_class=function(object){returnObject.prototype.toString.call(object).match(/^\[object\s(.*)\]$/)[1];},FakeBlobBuilder=functionBlobBuilder(){this.data =[];},FakeBlob=functionBlob(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
  
        , origin =/^[\w-]+:\/*\[?[\w\.:-]+\]?(?::[0-9]+)?/;
      FakeBlob.fake = FB_proto.fake =true;while(file_ex_code--){FileException.prototype[file_ex_codes[file_ex_code]]= file_ex_code +1;}// Polyfill URLif(!real_URL.createObjectURL){URL= view.URL=function(uri){var
            uri_info = document.createElementNS("http://www.w3.org/1999/xhtml","a"), uri_origin
          ;
          uri_info.href = uri;if(!("origin"in uri_info)){if(uri_info.protocol.toLowerCase()==="data:"){
              uri_info.origin =null;}else{
              uri_origin = uri.match(origin);
              uri_info.origin = uri_origin && uri_origin[1];}}return uri_info;};}URL.createObjectURL=function(blob){var
          type = blob.type
          , data_URI_header
        ;if(type ===null){
          type ="application/octet-stream";}if(blob instanceofFakeBlob){
          data_URI_header ="data:"+ type;if(blob.encoding ==="base64"){return data_URI_header +";base64,"+ blob.data;}elseif(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);}}elseif(real_create_object_URL){returnreal_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 stringif(Uint8Array &&(data instanceofArrayBuffer|| data instanceofUint8Array)){var
            str ="", buf =newUint8Array(data), i =0, buf_len = buf.length
          ;for(; i < buf_len; i++){
            str += String.fromCharCode(buf[i]);}
          bb.push(str);}elseif(get_class(data)==="Blob"||get_class(data)==="File"){if(FileReaderSync){var fr =newFileReaderSync;
            bb.push(fr.readAsBinaryString(data));}else{// async FileReader won't work as BlobBuilder is syncthrownewFileException("NOT_READABLE_ERR");}}elseif(data instanceofFakeBlob){if(data.encoding ==="base64"&& atob){
            bb.push(atob(data.data));}elseif(data.encoding ==="URI"){
            bb.push(decodeURIComponent(data.data));}elseif(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;}returnnewFakeBlob(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;}returnnewFakeBlob(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 =0;deletethis.data;};return FakeBlobBuilder;}(view));
  
    view.Blob=function(blobParts, options){var type = options ?(options.type ||""):"";var builder =newBlobBuilder();if(blobParts){for(var i =0, len = blobParts.length; i < len; i++){if(Uint8Array && blobParts[i]instanceofUint8Array){
            builder.append(blobParts[i].buffer);}else{
            builder.append(blobParts[i]);}}}var blob = builder.getBlob(type);if(!blob.slice && blob.webkitSlice){
        blob.slice = blob.webkitSlice;}return blob;};var getPrototypeOf = Object.getPrototypeOf ||function(object){return object.__proto__;};
    view.Blob.prototype =getPrototypeOf(newview.Blob());}(typeof self !=="undefined"&& self
    ||typeof window !=="undefined"&& window
    ||this));

Export2Excel.js

文件夹内容 ↓

/* eslint-disable */require('script-loader!file-saver');// require('script-loader!src/vendor/Blob');require('./Blob');require('script-loader!xlsx/dist/xlsx.core.min');functiongenerateArray(table){var out =[];var rows = table.querySelectorAll('tr');var ranges =[];for(varR=0;R< rows.length;++R){var outRow =[];var row = rows[R];var columns = row.querySelectorAll('td');for(varC=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 Spanif(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 Colspanif(colspan)for(var k =0; k < colspan -1;++k) outRow.push(null);}
    out.push(outRow);}return[out, ranges];};functiondatenum(v, date1904){if(date1904) v +=1462;var epoch = Date.parse(v);return(epoch -newDate(Date.UTC(1899,11,30)))/(24*60*60*1000);}functionsheet_from_array_of_arrays(data, opts){var ws ={};var range ={s:{c:10000000,r:10000000},e:{c:0,r:0}};for(varR=0;R!= data.length;++R){for(varC=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';elseif(typeof cell.v ==='boolean') cell.t ='b';elseif(cell.v instanceofDate){
        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;}functionWorkbook(){if(!(thisinstanceofWorkbook))returnnewWorkbook();this.SheetNames =[];this.Sheets ={};}functions2ab(s){var buf =newArrayBuffer(s.length);var view =newUint8Array(buf);for(var i =0; i != s.length;++i) view[i]= s.charCodeAt(i)&0xFF;return buf;}exportfunctionexport_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 =newWorkbook(), 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(newBlob([s2ab(wbout)],{type:"application/octet-stream"}),"test.xlsx")}functionformatJson(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")// }exportfunctionexport_json_to_excel(th, jsonData, defaultTitle, autoWidth=true, bookType ='xlsx', myRowFont ='1'){/* original data */var data = jsonData;
  data.unshift(th);var ws_name ="SheetJS";var wb =newWorkbook(), ws =sheet_from_array_of_arrays(data);// console.log(autoWidth)if(autoWidth){/*设置worksheet每列的最大宽度*/const colWidth = data.map(row=> row.map(val=>{/*先判断是否为null/undefined*/if(val ==null){return{'wch':10};}/*再判断是否为中文*/elseif(val.toString().charCodeAt(0)>255){return{'wch': val.toString().length *2};}else{return{'wch': val.toString().length
          };}}))/*以第一行为初始值*/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 wbout =XLSX.write(wb,{bookType:'xlsx',bookSST:false,type:'binary'});var title = defaultTitle ||'列表'saveAs(newBlob([s2ab(wbout)],{type:"application/octet-stream"}), title +".xlsx")}

二、导出Excel文件

// 导出写好的文件const{
    export_json_to_excel
  }=require('/excel/Export2Excel');// 定义Excel文件的表头const tHeader =['工號','姓名','課程系列','課程名稱','課程類別'];// 表头对应的字段const filterVal =['empID','empName','courseSeriesName','courseName','courseType'];const data = list.map(v=> filterVal.map(j=> v[j]))export_json_to_excel(tHeader,data,'文件名');