HTML page table Export to Excel

Several ways to compare HTML page tables to Excel.

Examples of forms are as follows:

<table id= "Tableexcel" width= "100%" border= "1" cellspacing= "0" cellpadding= "0" >

<tr>

<TD colspan= "5" align= "Center" >html Table export channel exceltd>

Tr>

<tr>

<td> column Header 1td>

<td> column Header 2td>

Category <td> Title 3td>

<td> column Header 4td>

<td> column Header 5td>

Tr>

<tr>

<td>aaatd>

<td>bbbtd>

<td>ccctd>

<td>dddtd>

<td>eeetd>

Tr>

<tr>

<td>AAAtd>

<td>BBBtd>

<td>CCCtd>

<td>DDDtd>

<td>EEEtd>

Tr>

<tr>

<td>FFFtd>

<td>GGGtd>

<td>HHHtd>

<td>IIItd>

<td>JJJtd>

Tr>

Table>

1, The method of JS

A. Copy the entire table into EXCEL

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 into EXCEL :

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 table to another page, and then save it in CVS format

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 + = "/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 = "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, feeling the first method is more perfect, because this method compares the format of the complete output table. However, 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 method in section 3 does not use an ActiveX object, but uses a popup output, which is not available if a pop-up window is disabled.

For an issue where the Execl object cannot be closed, the following method is an expedient method:

function Cleanup () {

Window.clearinterval (IDTMR);

CollectGarbage ();

}

Call Method:

IDTMR = Window.setinterval ("Cleanup ();", 1);

2. Methods in ASP. NET (C #)

This method is similar to the above JS of the 3rd Chinese law (can also be implemented in other Web scripts, such as the ASP in VBScript, or PHP), the form of the file stream way

The output is Excel. The instance code is as follows:

public void Outputexcel (string title)

{

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 method is essentially not the standard Excel format, but the HTML format file is saved as Excel format, and then opened with Excel.

HTML page table Export to Excel