HTML page table Export to Excel file method

A recent project needs to import the form of the report into Excel, find some methods on the Internet, compare the research, remember here, the memo.

The table example is as follows:

The code is as follows

Copy Code

<table id= "Tableexcel" width= "100%" border= "1" cellspacing= "0" cellpadding= "0" > <tr> &LT;TD colspan= "5" align= "Center" >html form Export excel</td> </tr> <tr> <td> column Headings 1</td> <td> column Headings 2</td> <td> category title 3</td> <td> column Headings 4</td> <td> column Headings 5</td> </tr> <tr> <td>aaa</td> <td>bbb</td> <td>ccc</td> <td>ddd</td> <td>eee</td> </tr> <tr> <td>AAA</td> <td>BBB</td> <td>CCC</td> <td>DDD</td> <td>EEE</td> </tr> <tr> <td>FFF</td> <td>GGG</td> <td>HHH</td> <td>III</td> <td>JJJ</td> </tr> </table>

1, JS method

A, copy the entire table into Excel

code is as follows	copy code
function Method1 (TableID) {     var curtbl = document.getElementById (tableid);     var oXL = new ActiveXObject ("Excel.Application");     var owb = OXL.Workbooks.Add ();     var osheet = Owb.activesheet;     var sel = Document.body.createTextRange ();     Sel.movetoelementtext (CURTBL);     sel.select ();     Sel.execcommand ("Copy");     osheet.paste ();     oxl.visible = true; }

B, read each cell in the table to Excel:

The code is as follows

Copy Code

function Method2 (TableID) { var curtbl = document.getElementById (TableID); var oXL = new ActiveXObject ("Excel.Application"); var owb = OXL.Workbooks.Add (); var osheet = Owb.activesheet; var lenr = curTbl.rows.length; for (i = 0; i < lenr; i++) {var Lenc = curtbl.rows (i). Cells.length; for (j = 0; J < Lenc; J + +) { Osheet.cells (i + 1, j + 1). Value = Curtbl.rows (i). Cells (j). innertext; } } oXL.Visible = true; }

C, output the form to another page and save it as a CVS format

The code is as follows

Copy Code

function Getxlsfromtbl (intblid, Inwindow) { try { var allstr = ""; var curstr = ""; if (intblid!= null && intblid!= "" && intblid!= "null") { Curstr = Gettbldata (Intblid, Inwindow); } if (curstr!= null) { Allstr + = Curstr; } else { Alert ("The table you want to export 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; 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 + + "RN"; } } 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 = "Table" + "_" + curyear + curmonth + curdate + "_" + curhour + curminute + cursecond + ". csv"; 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 (); }

Summary: Compare the above 3 methods, feel the first method is more perfect, because this method is more complete output table format. However, both the first and second methods use ActiveX objects, which are required for client security, and one of the biggest problems is that Excel objects cannot be closed. The 3rd method does not use ActiveX objects, but it uses pop-up output and cannot be used if pop-up windows are disabled.

The following method is an expedient method for a problem where the Execl object cannot be closed:

The code is as follows	Copy Code
function Cleanup () { Window.clearinterval (IDTMR); CollectGarbage (); }

Call Method:

The code is as follows	Copy Code
IDTMR = Window.setinterval ("Cleanup ();", 1);

2, ASP.net (C #) method in fact, similar to the above JS 3rd Chinese law (can also be implemented in other Web scripts, such as VBScript in Asp, or PHP), the table file flow to the way output to Excel. The instance code is as follows: public void Outputexcel (string title)

The code is as follows

Copy Code

{ Response.Clear (); Response.Buffer = true; Response.Charset = "Utf-8"; Response.AddHeader ("Content-disposition", "attachment;filename=" + httputility.urlencode (title + ". xls")); response.contentencoding = System.Text.Encoding.GetEncoding ("Utf-8"); Response.ContentType = "Application/ms-excel"; Page.enableviewstate = false; System.IO.StringWriter ostringwriter = new System.IO.StringWriter (); System.Web.UI.HtmlTextWriter ohtmltextwriter = new System.Web.UI.HtmlTextWriter (ostringwriter); This. Page.rendercontrol (Ohtmltextwriter); String temp = ostringwriter.tostring (); Response.Write (temp); Response.End (); }

This approach is essentially not a standard Excel format, but save HTML files in Excel format and then open them in Excel. 3, using Excel application or MSOWC or ado.net