js导出table中的EXCEL总结
导出EXCEL一般是用PHP做,但是项目中,有时候PHP后端工程师返回的数据不是我们想要的,作为前端开发工程师,把对应的数据编号转换为文字后,展示给用户,但是,需求要把数据同时导出一份EXCEl。无奈之下,我只能用js导出table中的数据了。
导出EXCEl一般是自己人用的,所以用js导出,因为js导出EXCEL一般情况下兼容性不是很好,很多只是兼容IE浏览器,还要设置在工具栏中进行设置才能导出,因为会相对比较烦。下面介绍几种方法:
一、js导出EXCEl带单元格合并【已验证,比较好用】
// JavaScript Document //调用方法 // var test=new PageToExcel("data",0,255,"测试.xls");//table id , 第几行开始,最后一行颜色 ,保存的文件名 // test.CreateExcel(false); // test.Exec(); // test.SaveAs(); // test.CloseExcel(); //LastRowColor 0黑色 255红色 // function PageToExcel(TableID,FirstRow,LastRowColor,SaveAsName){ this.lastRowColor=LastRowColor==""?0:LastRowColor; var today=new Date(); this.saveAsName=(SaveAsName==""?today.getYear()+"年"+(today.getMonth()+1)+"月"+today.getDate()+"日.xls":SaveAsName); this.tableId=TableID; this.table=document.getElementById(this.tableId);//导出的table 对象 this.rows=this.table.rows.length;//导出的table总行数 this.colSumCols=this.table.rows[0].cells.length;//第一行总列数 this.fromrow=FirstRow; this.beginCol=0; //起始列数 this.cols=this.colSumCols; this.oXL=null; this.oWB=null; this.oSheet=null; this.rowSpans=1; //行合并 this.colSpans=1; //列合并 this.colsName={0:"A",1:"B", 2:"C", 3:"D", 4:"E", 5:"F", 6:"G", 7:"H", 8:"I",9:"J", 10:"K", 11:"L", 12:"M", 13:"N", 14:"O", 15:"P", 16:"Q", 16:"R" ,18:"S", 19:"T", 20:"U", 21:"V", 22:"W", 23:"X", 24:"Y", 25:"Z"}; } PageToExcel.prototype.DeleteExcelCols=function(NotShowColList){//数组NotShowColList //this.notShowColList=NotShowColList;//不显示列集合,1,2,3,1 //删除excel中的列 var m=0; for(var i=0;i<NotShowColList.length;i++){ if(i>0){ m++; } var temp=NotShowColList[i]- m; var index=this.colsName[temp]; this.oSheet.Columns(index).Delete;//删除 } m=0; } PageToExcel.prototype.CreateExcel=function(ExcelVisible) { try{ this.oXL = new ActiveXObject("Excel.Application"); //创建应该对象 this.oXL.Visible = ExcelVisible; this.oWB = this.oXL .Workbooks.Add();//新建一个Excel工作簿 this.oSheet = this.oWB.ActiveSheet;//指定要写入内容的工作表为活动工作表 //不显示网格线 this.oXL.ActiveWindow.DisplayGridlines=false; }catch(e){ alert("请确认安装了非绿色版本的excel!"+e.description); CloseExcel(); } } PageToExcel.prototype.CloseExcel=function() { this.oXL.DisplayAlerts = false; this.oXL.Quit(); this.oXL = null; this.oWB=null; this.oSheet=null; } PageToExcel.prototype.ChangeElementToLabel=function (ElementObj){ var GetText=""; try{ var childres=ElementObj.childNodes; }catch(e){ return GetText} if(childres.length<=0) return GetText; for(var i=0;i<childres.length;i++){ try{if(childres[i].style.display=="none"||childres[i].type.toLowerCase()=="hidden"){continue;}} catch(e){} try{ switch (childres[i].nodeName.toLowerCase()){ case "#text" : GetText +=childres[i].nodeValue ; break; case "br" : GetText +="\n"; break; case "img" : GetText +=""; break; case "select" : GetText +=childres[i].options[childres[i].selectedIndex].innerText ; break; case "input" : if(childres[i].type.toLowerCase()=="submit"||childres[i].type.toLowerCase()=="button"){ GetText +=""; }else if(childres[i].type.toLowerCase()=="textarea"){ GetText +=childres[i].innerText; }else{ GetText +=childres[i].value; } break; default : GetText += this.ChangeElementToLabel(childres[i]); break; } }catch(e){} } return GetText; } PageToExcel.prototype.SaveAs=function (){ //保存 try{ this.oXL.Visible =true; var fname = this.oXL.Application.GetSaveAsFilename(this.saveAsName, "Excel Spreadsheets (*.xls), *.xls"); if(fname){ this.oWB.SaveAs(fname); this.oXL.Visible =false; } }catch(e){}; } PageToExcel.prototype.Exec=function() { //寻找列数,考虑到第一行可能存在 for (var i=0; i<this.colSumCols;i++) { var tmpcolspan = this.table.rows(0).cells(i).colSpan; if ( tmpcolspan>1 ) { this.cols += tmpcolspan-1; } } //定义2维容器数据,1:行;2:列;值(0 可以填充,1 已被填充) var container=new Array(this.rows); for (var i=0;i<this.rows;i++) { container[i]=new Array(this.cols); for (j=0;j<this.cols;j++) { container[i][j]=0; } } //将所有单元置为文本,避免非数字列被自动变成科学计数法和丢失前缀的0 this.oSheet.Range(this.oSheet.Cells(this.fromrow+1,1), this.oSheet.Cells(this.fromrow+this.rows,this.cols)).NumberFormat = "@"; // 循环行 for (i=0;i<this.rows;i++){ //循环列 for (j=0;j<this.cols;j++){ //寻找开始列 for (k=j;k<this.cols;k++){ if (container[i][k]==0) { this.beginCol=k; k=this.cols; //退出循环 } } //try{ //赋值 //此处相应跟改 根据 标签的类型,替换相关参数 this.oSheet.Cells(i+1+this.fromrow,this.beginCol+1).value = this.ChangeElementToLabel(this.table.rows(i).cells(j)); //计算合并列 try{ this.colSpans = this.table.rows(i).cells(j).colSpan; }catch(e){ this.colSpans=0 } if (this.colSpans>1) { //合并 this.oSheet.Range(this.oSheet.Cells(i+1+this.fromrow,this.beginCol+1),this.oSheet.Cells(i+1+this.fromrow,this.beginCol+this.colSpans)).Merge(); } //将当前table位置填写到对应的容器中 for (k=0; k<this.colSpans;k++) { container[i][this.beginCol+k]= 1; } // 计算合并行 try{ this.rowSpans = this.table.rows(i).cells(j).rowSpan; }catch(e){ this.rowSpans = 0; } if (this.rowSpans>1) { //行合并 this.oSheet.Range(this.oSheet.Cells(i+1+this.fromrow,this.beginCol+1),this.oSheet.Cells(i+this.rowSpans+this.fromrow,this.beginCol+this.colSpans)).Merge(); //将当前table位置填写到对应的容器中 for (k=1; k<this.rowSpans;k++) { //由于第0行已经被colSpans对应的代码填充了,故这里从第1行开始 for (l=0;l<this.colSpans;l++) { container[i+k][this.beginCol+l]=1; } } } //如果开始列+合并列已经等于列数了,故不需要再循环html table if (this.beginCol+this.colSpans>=this.cols) j=this.cols; } if(i==0) { //标题栏 this.oSheet.Range(this.oSheet.Cells(1,1), this.oSheet.Cells(1,1)).Font.Size=20; this.oSheet.Range(this.oSheet.Cells(1,1), this.oSheet.Cells(1,1)).Font.Bold = true; this.oSheet.Range(this.oSheet.Cells(1,1), this.oSheet.Cells(1,1)).HorizontalAlignment = -4108; //居中 this.oSheet.Range(this.oSheet.Cells(1,1), this.oSheet.Cells(1,1)).Rows.RowHeight = 40; } //自动调整行高 } //最后一行是否空色 try{ this.oSheet.Range(this.oSheet.Cells(this.rows,1), this.oSheet.Cells(this.rows,1)).Font.Color=this.lastRowColor; }catch(e){} this.oSheet.Range(this.oSheet.Cells(this.fromrow+2,1), this.oSheet.Cells(this.fromrow+this.rows,this.cols)).Rows.RowHeight=20; this.oSheet.Range(this.oSheet.Cells(this.fromrow+2,1), this.oSheet.Cells(this.fromrow+this.rows,this.cols)).Font.Size=10; //自动换行 this.oSheet.Range(this.oSheet.Cells(this.fromrow+2,1), this.oSheet.Cells(this.fromrow+this.rows,this.cols)).WrapText = true; //自动调整列宽 this.oSheet.Range(this.oSheet.Cells(this.fromrow+1,1), this.oSheet.Cells(this.fromrow+this.rows,this.cols)).Columns.AutoFit(); //点虚线 this.oSheet.Range(this.oSheet.Cells(this.fromrow+1,1), this.oSheet.Cells(this.fromrow+this.rows,this.cols)).Borders.LineStyle = -4118; return this.rows; }
注意:要改IE浏览器安全设置
二、js导出table中的EXCEL.该方法只能在IE内核下运行,相比其他方法的好处是,不
用再设置什么属性或者安装什么插件了,思路如下:
function getXlsFromTbl(inTblId, inWindow) { try { var allStr = ""; var curStr = ""; //alert("getXlsFromTbl"); if (inTblId != null && inTblId != "" && inTblId != "null") { curStr = getTblData(inTblId, inWindow); } if (curStr != null) { allStr += curStr; } else { alert("你要导出的表不存在!"); return; } var fileName = getExcelFileName(); doFileExport(fileName, allStr); } catch(e) { alert("导出发生异常:" + e.name + "->" + e.description + "!"); } } function getTblData(inTbl, inWindow) { var rows = 0; //alert("getTblData is " + inWindow); var tblDocument = document; if (!!inWindow && inWindow != "") { if (!document.all(inWindow)) { return null; } else { tblDocument = eval(inWindow).document; } } var curTbl = tblDocument.getElementById(inTbl); var outStr = ""; if (curTbl != null) { for (var j = 0; j < curTbl.rows.length; j++) { for (var i = 0; i < curTbl.rows[j].cells.length; i++) { if (i == 0 && rows > 0) { outStr += " \t"; rows -= 1; } outStr += curTbl.rows[j].cells[i].innerText + "\t"; if (curTbl.rows[j].cells[i].colSpan > 1) { for (var k = 0; k < curTbl.rows[j].cells[i].colSpan - 1; k++) { outStr += " \t"; } } if (i == 0) { if (rows == 0 && curTbl.rows[j].cells[i].rowSpan > 1) { rows = curTbl.rows[j].cells[i].rowSpan - 1; } } } outStr += "\r\n"; } } else { outStr = null; alert(inTbl + "不存在!"); } return outStr; } function getExcelFileName() { var d = new Date(); var curYear = d.getYear(); var curMonth = "" + (d.getMonth() + 1); var curDate = "" + d.getDate(); var curHour = "" + d.getHours(); var curMinute = "" + d.getMinutes(); var curSecond = "" + d.getSeconds(); if (curMonth.length == 1) { curMonth = "0" + curMonth; } if (curDate.length == 1) { curDate = "0" + curDate; } if (curHour.length == 1) { curHour = "0" + curHour; } if (curMinute.length == 1) { curMinute = "0" + curMinute; } if (curSecond.length == 1) { curSecond = "0" + curSecond; } var fileName = "91zaojia" + "_" + curYear + curMonth + curDate + "_" + curHour + curMinute + curSecond + ".xls"; return fileName; } function doFileExport(inName, inStr) { var xlsWin = null; if (!!document.all("glbHideFrm")) { xlsWin = glbHideFrm; } else { var width = 6; var height = 4; var openPara = "left=" + (window.screen.width / 2 - width / 2) + ",top=" + (window.screen.height / 2 - height / 2) + ",scrollbars=no,width=" + width + ",height=" + height; xlsWin = window.open("", "_blank", openPara); } xlsWin.document.write(inStr); xlsWin.document.close(); xlsWin.document.execCommand('Saveas', true, inName); xlsWin.close(); }
改代码已经验证,可以使用。调用很简单,直接用就可以
郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。