1. Directly copy the entire table to excel
2. traverse the table and assign values to the corresponding cells in Excel.
3. Extract the content from the table, and use the. CSV format for IE.
Benefits of each method:
1. Directly copy the table to retain the original format of the table, such as column and row merging, alignment, and background color,
2. You can traverse some required content of a table flexibly by traversing the table.
3. Save ie as an activexobject object. You can solve table merging problems without creating activexobject objects.
Disadvantages of each method:
1. A script error may pop up: automation cannot create objects.
Solution: Enable: Initialize ActiveX controls that are not marked as secure and run scripts in IE security settings.
As the entire table is copied to excel, adding a title to the table and adding it to excel may cause problems.
Solution: First add the first row to the table.
<Tr> <TD colspan = "X" align = "center"> & </TD> </tr>
X indicates the number of columns in the entire table. After copying the entire table, add the following code. osheet is the currently active sheet.
Osheet. Range (osheet. cells (1, 1), osheet. cells (1, x). value = "Table title"; // set the title
Osheet. Rows (1). Font. size = 16; // set the text size
Osheet. Rows (1). Font. Name = ""; // set the text font
Note: The following attributes may be useful or report errors.
Osheet. Range (osheet. cells (), osheet. cells (). mergecells = true; // merge Cells
Osheet. Range (osheet. cells (), osheet. cells (). Interior. colorindex = 6; // set the background color?
Osheet. Range (osheet. cells (), osheet. cells (). Font. colorindex = 5; // set the font color?
Osheet. Rows (1). rowheight = 20; // you can specify the column height.
Osheet. cells (irow, icol). halignment = '2' // set the font to center
2. A script error may pop up: automation cannot create objects (the solution is as above ).
No table lines are written into the Excel worksheet (not resolved)
There is also a problem when merging cells. Solution: Merge cells and then write data.
Osheet. Range (osheet. cells (), osheet. cells (). mergecells = true; // merge Cells
3. No table line is displayed when the table content is written to the Excel file (not resolved)
When the table format is complex, there will be problems (rowspan> 1 or colspan> 1). Solution: Generally, the table Header Format is complicated. You can first write the table header to death, and then recycle and write other data.
The Code is as follows:
<! Doctype HTML public "-// W3C // dtd html 4.0 transitional // en">
<HTML>
<Head>
<Title> new document </title>
<Meta name = "generator" content = "editplus">
<Meta name = "author" content = "">
<Meta name = "keywords" content = "">
<Meta name = "Description" content = "">
</Head>
<Body>
<Table id = "tableexcel" width = "100%" border = "1" cellspacing = "0" cellpadding = "0">
<Tr>
<TD colspan = "5" align = "center"> How to export an Excel document from a Web page </TD>
</Tr>
<Tr>
<TD> Column Title 1 </TD>
<TD> Column Title 2 </TD>
<TD> Column Title 3 </TD>
<TD> Column Title 4 </TD>
<TD> Column Title 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>
<Input type = "button" onclick = "javascript: Method1 ('tableexcel ');" value = "method 1 import to excel">
<Input type = "button" onclick = "javascript: method2 ('tableexcel ');" value = "method 2 import to excel">
<Input type = "button" onclick = "javascript: getxlsfromtbl ('tableexcel ', null);" value = "method 3">
<Script language = "JavaScript">
Function Method1 (tableid)
{// Copy the entire table to excel
VaR curtbl = Document. getelementbyid (tableid );
VaR oxl = new activexobject ("Excel. application ");
// Create an ax object Excel
VaR owb = oxl. workbooks. Add ();
// Obtain the workbook object
VaR osheet = owb. activesheet;
// Activate the current sheet
VaR sel = Document. Body. createTextRange ();
Sel. movetoelementtext (curtbl );
// Move the table content to textrange
Sel. Select ();
// Select all content in textrange
Sel.exe ccommand ("copy ");
// Copy the content in textrange
Osheet. paste ();
// Paste it to the Excel file of the activity
Oxl. Visible = true;
// Set the Excel visible attribute
}
Function method2 (tableid) // read each unit in the table to excel.
{
VaR curtbl = Document. getelementbyid (tableid );
VaR oxl = new activexobject ("Excel. application ");
// Create an ax object Excel
VaR owb = oxl. workbooks. Add ();
// Obtain the workbook object
VaR osheet = owb. activesheet;
// Activate the current sheet
VaR lenr = curtbl. Rows. length;
// Obtain the number of rows in the table
For (I = 0; I <lenr; I ++)
{
VaR lenc = curtbl. Rows (I). cells. length;
// Obtain the number of columns in each row
For (j = 0; j <lenc; j ++)
{
Osheet. cells (I + 1, J + 1). value = curtbl. Rows (I). cells (j). innertext;
// Assign a value
}
}
Oxl. Visible = true;
// Set the Excel visible attribute
}
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 ("the table you want to export does not exist! ");
Return;
}
VaR filename = getexcelfilename ();
Dofileexport (filename, allstr );
}
Catch (e ){
Alert ("Export exception:" + 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).doc ument;
}
}
VaR curtbl = tbldocument. getelementbyid (intbl );
VaR outstr = "";
If (curtbl! = NULL ){
For (var j = 0; j <curtbl. Rows. length; j ++ ){
// Alert ("J is" + J );
For (VAR I = 0; I <curtbl. Rows [J]. cells. length; I ++ ){
// Alert ("I is" + I );
If (I = 0 & rows> 0 ){
Outstr + = "";
Rows-= 1;
}
Outstr + = curtbl. Rows [J]. cells [I]. innertext + "";
If (curtbl. Rows [J]. cells [I]. colspan> 1 ){
For (var k = 0; k <curtbl. Rows [J]. cells [I]. colspan-1; k ++ ){
Outstr + = "";
}
}
If (I = 0 ){
If (rows = 0 & curtbl. Rows [J]. cells [I]. rowspan> 1 ){
Rows = curtbl. Rows [J]. cells [I]. rowspan-1;
}
}
}
Outstr + = "";
}
}
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 = "leo_zhang" + "_" + curyear + curmonth + curdate + "_"
+ Curhour + curminute + cursecond + ". CSV ";
// Alert (filename );
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.doc ument. Write (instr );
Xlswin.doc ument. Close ();
Xlswin.document.exe ccommand ('saveas', true, inname );
Xlswin. Close ();
}
</SCRIPT>
</Body>
</Html>
This article from the csdn blog, reproduced please indicate the source: http://blog.csdn.net/zhongzhengfeng/archive/2009/04/20/4094831.aspx
Start building with 50+ products and up to 12 months usage for Elastic Compute Service
1 on 1 presale consultation
24/7 Technical Support 6 Free Tickets per Quarter Faster Response
Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.
A comprehensive suite of global cloud computing services to power your business
Payment Methods We Support
