前端实现将json数据转换为Excel文件下载
发布网友
发布时间：2024-10-02 14:23
共1个回答
热心网友
时间：2024-10-09 07:28
Blob.js和Export2Excel.js文件
可以百度下载这两个文件，也可以直接copy下面的代码。
Blob.js代码如下：
/*eslint-disable*//*Blob.js*ABlobimplementation.**ByEliGrey,http://eligrey.com*ByDevinSamarin,https://github.com/eboyjr*License:X11/MIT*SeeLICENSE.md*//*globalself,unescape*//*jslintbitwise:true,regexp:true,confusion:true,es5:true,vars:true,white:true,plusplus:true*//*!@sourcehttp://purl.eligrey.com/github/Blob.js/blob/master/Blob.js*/(function(view){"usestrict";view.URL=view.URL||view.webkitURL;if(view.Blob&&view.URL){try{newBlob;return;}catch(e){}}//InternallyweuseaBlobBuilderimplementationtobaseBloboffof//inordertosupportolderbrowsersthatonlyhaveBlobBuildervarBlobBuilder=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_ERRSECURITY_ERRABORT_ERRNOT_READABLE_ERRENCODING_ERR"+"NO_MODIFICATION_ALLOWED_ERRINVALID_STATE_ERRSYNTAX_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){vartype=blob.type,data_URI_header;if(type===null){type="application/octet-stream";}if(blobinstanceofFakeBlob){data_URI_header="data:"+type;if(blob.encoding==="base64"){returndata_URI_header+";base64,"+blob.data;}elseif(blob.encoding==="URI"){returndata_URI_header+","+decodeURIComponent(blob.data);}if(btoa){returndata_URI_header+";base64,"+btoa(blob.data);}else{returndata_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*/){varbb=this.data;//decodedatatoabinarystringif(Uint8Array&&(datainstanceofArrayBuffer||datainstanceofUint8Array)){varstr="",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){varfr=newFileReaderSync;bb.push(fr.readAsBinaryString(data));}else{//asyncFileReaderwon'tworkasBlobBuilderissyncthrownewFileException("NOT_READABLE_ERR");}}elseif(datainstanceofFakeBlob){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(typeofdata!=="string"){data+="";//convertunsupportedtypestostrings}//decodeUTF-16tobinarystringbb.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"[objectBlobBuilder]";};FB_proto.slice=function(start,end,type){varargs=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"[objectBlob]";};FB_proto.close=function(){this.size=this.data.length=0;};returnFakeBlobBuilder;}(view));view.Blob=functionBlob(blobParts,options){vartype=options?(options.type||""):"";varbuilder=newBlobBuilder();if(blobParts){for(vari=0,len=blobParts.length;i<len;i++){builder.append(blobParts[i]);}}returnbuilder.getBlob(type);};}(typeofself!=="undefined"&&self||typeofwindow!=="undefined"&&window||this.content||this));Export2Excel.js代码如下：
/*eslint-disable*/require('script-loader!file-saver');require('script-loader!@/vendor/Blob');//实际Blob引入地址importXLSXfrom'xlsx'functiongenerateArray(table){varout=[];varrows=table.querySelectorAll('tr');varranges=[];for(varR=0;R<rows.length;++R){varoutRow=[];varrow=rows[R];varcolumns=row.querySelectorAll('td');for(varC=0;C<columns.length;++C){varcell=columns[C];varcolspan=cell.getAttribute('colspan');varrowspan=cell.getAttribute('rowspan');varcellValue=cell.innerText;if(cellValue!==""&&cellValue==+cellValue)cellValue=+cellValue;//Skiprangesranges.forEach(function(range){if(R>=range.s.r&&R<=range.e.r&&outRow.length>=range.s.c&&outRow.length<=range.e.c){for(vari=0;i<=range.e.c-range.s.c;++i)outRow.push(null);}});//HandleRowSpanif(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}});};//HandleValueoutRow.push(cellValue!==""?cellValue:null);//HandleColspanif(colspan)for(vark=0;k<colspan-1;++k)outRow.push(null);}out.push(outRow);}return[out,ranges];};functiondatenum(v,date1904){if(date1904)v+=1462;varepoch=Date.parse(v);return(epoch-newDate(Date.UTC(1899,11,30)))/(24*60*60*1000);}functionsheet_from_array_of_arrays(data,opts){varws={};varrange={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;varcell={v:data[R][C]};if(cell.v==null)continue;varcell_ref=XLSX.utils.encode_cell({c:C,r:R});if(typeofcell.v==='number')cell.t='n';elseif(typeofcell.v==='boolean')cell.t='b';elseif(cell.vinstanceofDate){cell.t='n';cell.z=XLSX.SSF._table[14];cell.v=datenum(cell.v);}elsecell.t='s';ws[cell_ref]=cell;}}if(range.s.c<10000000)ws['!ref']=XLSX.utils.encode_range(range);returnws;}functionWorkbook(){if(!(thisinstanceofWorkbook))returnnewWorkbook();this.SheetNames=[];this.Sheets={};}functions2ab(s){varbuf=newArrayBuffer(s.length);varview=newUint8Array(buf);for(vari=0;i!=s.length;++i)view[i]=s.charCodeAt(i)&0xFF;returnbuf;}exportfunctionexport_table_to_excel(id){vartheTable=document.getElementById(id);varoo=generateArray(theTable);varranges=oo[1];/*originaldata*/vardata=oo[0];varws_name="SheetJS";varwb=newWorkbook(),ws=sheet_from_array_of_arrays(data);/*addrangestoworksheet*///ws['!cols']=['apple','banan'];ws['!merges']=ranges;/*addworksheettoworkbook*/wb.SheetNames.push(ws_name);wb.Sheets[ws_name]=ws;varwbout=XLSX.write(wb,{bookType:'xlsx',bookSST:false,type:'binary'});saveAs(newBlob([s2ab(wbout)],{type:"application/octet-stream"}),"test.xlsx")}exportfunctionexport_json_to_excel(th,jsonData,defaultTitle){/*originaldata*/vardata=jsonData;//console.log('th',th);//console.log('jsonData',jsonData);//console.log('defaultTitle',defaultTitle)data.unshift(th);varws_name="SheetJS";varwb=newWorkbook(),ws=sheet_from_array_of_arrays(data);/*设置worksheet每列的最大宽度*/constcolWidth=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};}}))/*以第一行为初始值*/letresult=colWidth[0];for(leti=1;i<colWidth.length;i++){for(letj=0;j<colWidth[i].length;j++){if(result[j]['wch']<colWidth[i][j]['wch']){result[j]['wch']=colWidth[i][j]['wch'];}}}ws['!cols']=result;/*addworksheettoworkbook*/wb.SheetNames.push(ws_name);wb.Sheets[ws_name]=ws;varwbout=XLSX.write(wb,{bookType:'xlsx',bookSST:false,type:'binary'});vartitle=defaultTitle||'excel-list'saveAs(newBlob([s2ab(wbout)],{type:"application/octet-stream"}),title+".xlsx")}第三步：在src目录下创建vendor文件夹，引入Blob.js和Export2Excel.js文件.如果是按上面的方式创建并引入的，Export2Excel文件的引入目录就可以直接使用。
第四步，在vue中的使用方式:
如果你的数据就是完全下载的，不需要进行处理，则可以直接看代码，忽略下面的内容。
如果需要在数组中取出部分数据进行文件下载，可以查看该内容：https://blog.csdn.net/friend_ship/article/details/100560795
下面是我的使用的代码
因为我的是获取单个对象数组来生成Excel表格，和原博主的用法不太一样
letrowXlsx=[]rowXlsx.push(JSON.parse(row[j].data))//把对象加到数组里面去letkeys=[]letvalue=[]rowXlsx.forEach((v,i)=>{//获取key和valueObject.keys(v).forEach(v=>{keys.push(v)value.push(rowXlsx[i][v])})})console.log(keys)//取到了keyconsole.log(value)//取到了值import("@/vendor/Export2Excel").then(excel=>{//记得引入两个js文件consttHeader=keys;//这个是行头constfilterVal=keys;//因为我的数组键值是用中文来做的，所以和行头是一样的，各人对应不一样的用法constcontent=rowXlsx;constdata=this.formatJson(filterVal,content);excel.export_json_to_excel(tHeader,data,Date.parse(newDate()))})formatJson(filterVal,jsonData){returnjsonData.map(v=>filterVal.map(j=>v[j]))}