JS export Excel Summary in table

Export Excel is generally done with PHP, but in the project, sometimes the PHP back-end engineer returned data is not what we want, as a front-end development engineer, the corresponding data number converted to text, display to the user, but the need to export data at the same time a copy of Excel. Helpless, I can only use JS to export the data in the table.

Export Excel is usually used by one of our own, so with JS export, because JS export Excel General compatibility is not very good, a lot of just compatible with IE browser, but also set in the toolbar to set up in order to export, because it will be relatively annoying. Here are a few methods:

First, JS export Excel with cell merge "verified, easy to use"

JavaScript document//Call method/var test=new pagetoexcel ("Data", 0,255, "test. xls"),//table ID, first line start, last line color, saved filename/tes T.createexcel (false);//test. Exec ();//test. SaveAs ();//test. Closeexcel ();//lastrowcolor 0 Black 255 red//function pagetoexcel (tableid,firstrow,lastrowcolor,saveasname) { this.lastrowcolor=lastrowcolor== ""? 0:lastrowcolor;var today=new Date (); This.saveasname= (SaveAsName== ""? Today.getyear () + "year" + (Today.getmonth () +1) + "month" +today.getdate () + "Day xls": saveasname); This.tableid=tableid; This.table=document.getelementbyid (This.tableid);//Exported Table object this.rows=this.table.rows.length;// Exported table total number of rows this.colsumcols=this.table.rows[0].cells.length;//the first row totals the number of columns this.fromrow=firstrow;this.begincol=0; Number of starting columns This.cols=this.colsumcols;this.oxl=null;this.owb=null;this.osheet=null;this.rowspans=1; Merge This.colspans=1; Column merge this.colsname={0: "A", 1: "B", 2: "C", 3: "D", 4: "E", 5: "F", 6: "G", 7: "H", 8: "I", 9: "J", Ten: "K", One: "L", "M", and: "N" ,: "O", "P", +: "Q", "R", "S", "T", "U", 21: "V", "W", Max: "X", "Y", +: "Z"};} Pagetoexcel.prototype.deleteexcelcols=function (notshowcollist) {//array notshowcollist//this.notshowcollist=   notshowcollist;//do not display column collection, 1,2,3,1//delete columns in 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;//Delete} m=0;} Pagetoexcel.prototype.createexcel=function (excelvisible) {try{This.oxl = new ActiveXObject ("Excel.Application");//   Create should object this.oXL.Visible = excelvisible; THIS.OWB = This.oxl. Workbooks.Add ();//Create a new Excel workbook This.osheet = this.owb.activesheet;//Specifies that the worksheet you want to write to is the active sheet//Do not display gridlines This.oXL.ActiveWindo   W.displaygridlines=false; }catch (e) {alert ("Please confirm that a non-green version of the excel! is installed    "+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 () {//save 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 () {//search for the number of columns, taking into account that the first row may exist for (var i=0; i<this.colsumcols;i++) {var Tmpcolspa    n = this.table.rows (0). Cells (i). ColSpan;    if (tmpcolspan>1) {this.cols + = tmpcolspan-1;   }}//define 2-D container data, 1: Row, 2: column, value (0 can be populated, 1 has been populated) 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; }}//Set all cells to text to prevent non-numeric columns from being automatically changedinto scientific notation and the loss of the prefixes of 0 this.oSheet.Range (This.oSheet.Cells (this.fromrow+1,1), This.oSheet.Cells (This.fromrow+this.rows, This.cols)).   NumberFormat = "@"; Loop line for (i=0;i<this.rows;i++) {//Loop column for (j=0;j<this.cols;j++) {//Look for Start column for (k=j;k<this.cols;k       + +) {if (container[i][k]==0) {this.begincol=k; K=this.cols; Exit loop}}//try{//assignment//Here corresponding change according to the type of label, replace the relevant parameter This.oSheet.Cells (I+1+THIS.FROMROW,THIS.BEGINCOL+1) . Value = this.                Changeelementtolabel (This.table.rows (i). Cells (j));      Calculates the merged column try{This.colspans = this.table.rows (i). Cells (j). ColSpan; }catch (e) {this.colspans=0} if (this.colspans>1) {//merge 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 ();     }//Fill in the current table position into the corresponding container for (k=0; k<this.colspans;k++) {container[i][this.begincol+k]= 1; }//Calculate merged rows try{     This.rowspans = This.table.rows (i). Cells (j). RowSpan;     }catch (e) {This.rowspans = 0; } if (this.rowspans>1) {//Rows merge 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 (); Fills the current table position into the corresponding container for (k=1; k<this.rowspans;k++) {//because the No. 0 line has been populated with the corresponding code of Colspans, this starts from line 1th for (l=0;l<th       is.colspans;l++) {container[i+k][this.begincol+l]=1;       }}}//If the Start column + merge column is already equal to the number of columns, there is no need to recycle the HTML table if (this.begincol+this.colspans>=this.cols) J=this.cols; } if (i==0) {//title bar This.oSheet.Range (This.oSheet.Cells, This.oSheet.Cells ()).      font.size=20; This.oSheet.Range (This.oSheet.Cells), This.oSheet.Cells (()).      Font.Bold = true; This.oSheet.Range (This.oSheet.Cells), This.oSheet.Cells (()). HorizontalAlignment =-4108; Center This.oSheet.Range (this.oSheet.Cells), This.oSheet.Cells (1,1)).    Rows.rowheight = 40; }//AutoFit line height}//last row whether empty color try{this.oSheet.Range (This.oSheet.Cells (this.rows,1), This.oSheet.Cells (This.row s,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; Line Wrap This.oSheet.Range (This.oSheet.Cells (this.fromrow+2,1), This.oSheet.Cells (this.fromrow+this.rows,this.cols )).   WrapText = true; AutoFit Column Width this.oSheet.Range (this.oSheet.Cells (this.fromrow+1,1), This.oSheet.Cells (This.fromrow+this.rows, This.cols)).   Columns.AutoFit (); Dot Dashed 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;}

Note: To change the Internet Explorer security Settings

Second, JS to export excel in table. This method can only be run under IE kernel, compared to other methods, the advantage is that it does not to set what properties or install what plug-in, the idea is as follows :

function Getxlsfromtbl (intblid, Inwindow) {try {var allstr = "";         var curstr = "";         Alert ("Getxlsfromtbl"); if (intblid! = NULL && Intblid! = "" && intblid! = "null") {CURSTR = Gettbldata (Intblid, INWI         Ndow);        } if (curstr! = null) {allstr + = Curstr; } else {alert ("The table you are exporting does not exist!")            ");        Return        } var fileName = Getexcelfilename ();    Dofileexport (FileName, ALLSTR); } catch (e) {alert ("Export exception occurred:" + 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].rowspa                    n > 1) {rows = curtbl.rows[j].cells[i].rowspan-1;        }}} outstr + = "\ r \ n";        }} else {outstr = null;    Alert (intbl + "does not exist!"); } 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 ();}

the code has been validated and can be used. the call is simple and can be used directly
onclick= "getxlsfromtbl (' functionclickexcel ', null);