Javscript Exporting table to excel in HTML

Tag:else    need    document   extra   head    net   func   var   false   

<script language= "JavaScript" type= "Text/javascript" >/* * default conversion implementation function, if additional functionality is required, you need to expand * Parameters: * tableid:html in Table object ID  Attribute values * For detailed usage see the following Tabletoexcel object definitions */function Saveasexcel (TableID) {var TB = new Tabletoexcel (TableID);  Tb.setfontstyle ("Courier New");  Tb.setfontsize (10);  Tb.settableborder (2);  Tb.setcolumnwidth (7);  Tb.islinewrap (TRUE); Tb.getexcelfile ();  }/** function: The Table object in HTML is converted to an Excel generic object. * Parameters: TableID The ID property value of Table object in HTML * Description: * Can adapt to complex HTML table object automatic conversion, can automatically according to the column extension information * merge cells in Excel, the client needs to install Excel * Detailed properties, method reference  Description See: Microsoft Excel Visual Basic Reference * Demo for Excel: * var tb = new Tabletoexcel (' demotable ');  * Tb.setfontstyle ("Courier New"); * Tb.setfontsize (10); Recommended Value: * Tb.setfontcolor (6); The general situation does not need to set * Tb.setbackground (4); The general situation does not need to set * Tb.settableborder (2); Recommended Value 2 * tb.setcolumnwidth (10);  Recommended Value: * Tb.islinewrap (FALSE);  * Tb.isautofit (TRUE);  * * Tb.getexcelfile (); * If cell adaptive is set, the cell width is invalid * version: 1.0*/function Tabletoexcel (tableID) {this.tableborder =-1;//Border type,-1 No border desirable 1/2/3/4 this.background = 0;   Background color: White preferable color number in palette 1/2/3/4 .... This.fontcolor = 1; Font Color: Black this.fontsize = 10; Font size This.fontstyle = "Song Body"; Font type this.rowheight = 20; Row height this.columnwidth =-1; Column width this.linewrap = true; Whether to wrap line this.textalign =-4108; The content alignment defaults to center This.autofit = true;   Whether the adaptive width This.tableid = TableID;   } TableToExcel.prototype.setTableBorder = function (excelborder) {this.tableborder = Excelborder;    };   TableToExcel.prototype.setBackGround = function (excelcolor) {this.background = Excelcolor;    };   TableToExcel.prototype.setFontColor = function (excelcolor) {this.fontcolor = Excelcolor;    };   TableToExcel.prototype.setFontSize = function (excelfontsize) {this.fontsize = excelfontsize;    };   TableToExcel.prototype.setFontStyle = function (excelfont) {this.fontstyle = Excelfont;    };   TableToExcel.prototype.setRowHeight = function (excelrowheight) {this.rowheight = Excelrowheight;    }; TableTOExcel.prototype.setColumnWidth = function (excelcolumnwidth) {this.columnwidth = Excelcolumnwidth;    }; TableToExcel.prototype.isLineWrap = function (linewrap) {if (Linewrap = = False | | linewrap = = true) {This.linewra    p = linewrap;    }   };   TableToExcel.prototype.setTextAlign = function (textAlign) {this.textalign = textAlign;    };   TableToExcel.prototype.isAutoFit = function (autoFit) {if (AutoFit = = True | | autoFit = = FALSE) This.autofit = AutoFit;  }//File conversion main function TableToExcel.prototype.getExcelFile = function () {var jxls, MyWorkbook, Myworksheet, Myhtmltablecell,   Myexcelcell, MyExcelCell2;    var mycellcolspan, Mycellrowspan;   try {Jxls = new ActiveXObject (' Excel.Application '); }catch (e) {alert ("Unable to start excel!\n\n if you are sure that Excel is already installed on your computer," + "then adjust the security level of IE.)    \ n: \ n "+" tool →internet options → security → custom level → Initialize and script the ActiveX without marking as secure → enable ");   return false;   } jxls.visible = true;   MyWorkbook = JXls.Workbooks.Add ();   Jxls.displayalerts = false;Myworkbook.worksheets (3).   Delete (); Myworkbook.worksheets (2).   Delete ();   Jxls.displayalerts = true;    Myworksheet = Myworkbook.activesheet;   var readrow = 0, Readcol = 0;var totalrow = 0, totalcol = 0;    var tabnum = 0;   Set row height, column width if (this.columnwidth! =-1) myWorksheet.Columns.ColumnWidth = This.columnwidth;   else MyWorksheet.Columns.ColumnWidth = 7;    if (this.rowheight! =-1) myWorksheet.Rows.RowHeight = This.rowheight;   Search for Table object to convert, get corresponding row, number of columns var obj = document.all.tags ("table");     for (x = 0; x < obj.length; + +) {if (obj[x].id = = This.tableid) {tabnum = x;     TotalRow = Obj[x].rows.length;      for (i = 0; i < obj[x].rows[0].cells.length; i++) {Myhtmltablecell = obj[x].rows (0). Cells (i);      Mycellcolspan = Myhtmltablecell.colspan;     Totalcol = Totalcol + Mycellcolspan;   }}}//Start widget simulation table var exceltable = new Array ();    for (i = 0; I <= totalrow; i++) {Exceltable[i] = new Array (); for (t = 0; t <= totalcol; t+ +) {Exceltable[i][t] = false;    }}//Start conversion table for (z = 0; z < obj[tabnum].rows.length; z++) {Readrow = z + 1;    Readcol = 0;     for (c = 0; c < obj[tabnum].rows (z). cells.length; C + +) {Myhtmltablecell = Obj[tabnum].rows (z). Cells (c);     Mycellcolspan = Myhtmltablecell.colspan;     Mycellrowspan = Myhtmltablecell.rowspan;       for (y = 1; y <= totalcol; y++) {if (exceltable[readrow][y] = = False) {Readcol = y;      Break      }} if (Mycellcolspan * mycellrowspan > 1) {Myexcelcell = Myworksheet.cells (Readrow, Readcol);      MyExcelCell2 = myworksheet.cells (Readrow + myCellRowSpan-1, Readcol + myCellColSpan-1); Myworksheet.range (Myexcelcell, myExcelCell2).      Merge ();      Myexcelcell.horizontalalignment = this.textalign;      MyExcelCell.Font.Size = this.fontsize;      MyExcelCell.Font.Name = This.fontstyle;      Myexcelcell.wraptext = This.linewrap;      MyExcelCell.Interior.ColorIndex = This.background; MyexcelcelL.font.colorindex = This.fontcolor; if (this.tableborder! =-1) {Myworksheet.range (Myexcelcell, myExcelCell2). Borders (1).       Weight = This.tableborder; Myworksheet.range (Myexcelcell, myExcelCell2). Borders (2).       Weight = This.tableborder; Myworksheet.range (Myexcelcell, myExcelCell2). Borders (3).       Weight = This.tableborder; Myworksheet.range (Myexcelcell, myExcelCell2). Borders (4).      Weight = This.tableborder;      } myexcelcell.value = Myhtmltablecell.innertext; for (row = Readrow, row <= Mycellrowspan + readRow-1; row++) {for (col = readcol; col <= Mycellcolspan + re ADCOL-1;       col++) {Exceltable[row][col] = true;     }} readcol = Readcol + Mycellcolspan;      } else {Myexcelcell = Myworksheet.cells (Readrow, Readcol);      Myexcelcell.value = Myhtmltablecell.innertext;      Myexcelcell.horizontalalignment = this.textalign;      MyExcelCell.Font.Size = this.fontsize;      MyExcelCell.Font.Name = This.fontstyle; MyExcelcell.wraptext = This.linewrap;      MyExcelCell.Interior.ColorIndex = This.background;      MyExcelCell.Font.ColorIndex = This.fontcolor; if (this.tableborder! =-1) {myexcelcell.borders (1).       Weight = This.tableborder; Myexcelcell.borders (2).       Weight = This.tableborder; Myexcelcell.borders (3).       Weight = This.tableborder; Myexcelcell.borders (4).      Weight = This.tableborder;     } Exceltable[readrow][readcol] = True;readcol = Readcol + 1;    }}} if (This.autofit = = true) myWorksheet.Columns.AutoFit;   Jxls.usercontrol = true;   Jxls = null;   MyWorkbook = null;  Myworksheet = null; }; The following is a network collection, convenient for their own use. The source is unknown (I forgot where to find it, hehe). --------------------------------------------------------------------------------------------------------------- ----------------------populate and Format Excel with ActiveXObject in JavaScript 1. Create an instance and create a worksheet var xlobj = new ActiveXObject ("                         Excel.Application ");//Create an instance of Excel var xlbook = XLObj.Workbooks.Add; New Workbook Var ExcelSheet= Xlbook.worksheets (1); Create a worksheet 2. Save the Table Excelsheet.saveas ("C:\\test. XLS "); 3. Make Excel visible through the Application object ExcelSheet.Application.Visible = true; or excelsheet.visible=true;4. Print Xlbook.printout, or: excelsheet.printout;5. Close Xlbook.close (savechanges=false) or Excelsheet.close (savechanges= FALSE); 6. End Process ExcelSheet.Application.Quit () or xlobj.quit ();         Xlobj=null;7. Page Setup excelsheet.activesheet.pagesetup.leftmargin= 2/0.035;      Page margin left 2 cm ExcelSheet.ActiveSheet.PageSetup.RightMargin = 3/0.035;        Margins right 3 cm, ExcelSheet.ActiveSheet.PageSetup.TopMargin = 4/0.035;   Margin 4 cm, ExcelSheet.ActiveSheet.PageSetup.BottomMargin = 5/0.035;   Margin below 5 cm ExcelSheet.ActiveSheet.PageSetup.HeaderMargin = 1/0.035;    Page margin Header 1 cm ExcelSheet.ActiveSheet.PageSetup.FooterMargin = 2/0.035; Page margin Footer 2 cm ExcelSheet.ActiveSheet.PageSetup.CenterHeader = "content in the middle of the header"; ExcelSheet.ActiveSheet.PageSetup.LeftHeader = "Left content of header"; ExcelSheet.ActiveSheet.PageSetup.RightHeader = "Right content of the header"; ExcelSheet.ActiveSheet.PageSetup.CenterFooter = "footer middle content"; ExcelSheet.ActiveSheet.PageSetup.LeftFooter = "Left content of footer"; ExcelSheet.ActiveSheet.PageSetup.RightFooter = "footer right"; 8. For cell operations, with * section for rows, columns, and areas have corresponding properties ExcelSheet.ActiveSheet.Cells ( Row,col).                Value = "Content"; Sets the cell content ExcelSheet.ActiveSheet.Cells (row,col).        Borders.Weight = 1; Sets the cell border * () ExcelSheet.ActiveSheet.Cells (Row,col).    Interior.ColorIndex = 1; Set Cell background * (1-black, 2-white, 3-red, 4-green, 5-blue, 6-yellow, 7-pink, 8-turquoise, 9-Clay color. Can try more) ExcelSheet.ActiveSheet.Cells (Row,col).         Interior.Pattern = 1; Set cell background style * (1-None, 2-fine grid, 3-coarse mesh, 4-spotted, 5-horizontal, 6-vertical) ... Can try more) ExcelSheet.ActiveSheet.Cells (Row,col).        Font.ColorIndex = 1; Set the font color * (same as above) ExcelSheet.ActiveSheet.Cells (Row,col).                Font.Size = 10; Set to 10th-character *excelsheet.activesheet.cells (Row,col).        Font.Name = "Blackbody"; Set to Bold *excelsheet.activesheet.cells (Row,col).             Font.Italic = true; Set to Italic *excelsheet.activesheet.cells (Row,col).             Font.Bold = true; Set to Bold *excelsheet.activesheet.cells (Row,col). clearcontents;               Clears the content *excelsheet.activesheet.cells (Row,col).               Wraptext=true; Set to wrap *excelsheet.activesheet.cells (row,col). HorizontalAlignment = 3; Horizontal Alignment Enumeration * (n, 2-left, 3-centered, 4-right, 5-padding 6-justified, 7-centered across columns, 8-distributed) ExcelSheet.ActiveSheet.Cells (Row,col).      VerticalAlignment = 2;    Vertical Alignment Enumeration * (1-top, 2-centered, 3-bottom, 4-justified, 5-distributed)//row, column has the appropriate action: ExcelSheet.ActiveSheet.Rows (Row).    ExcelSheet.ActiveSheet.Columns (COL).                   ExcelSheet.ActiveSheet.Rows (startrow+ ":" +endrow).                such as rows ("1:5") is 1 to 5 lines ExcelSheet.ActiveSheet.Columns (startcol+ ":" +endcol). such as columns ("1:5") that is, 1 to 5 columns//area has the corresponding operation: Xlobj.range (startcell+ ":" +endcell).        Select;    such as Range ("A2:h8") is the entire area of column 2nd to column 8th Xlobj.selection. Merge cell Xlobj.range (startcell+ ":" +endcell).        MergeCells = true; If Range ("A2:h8") is going to merge the entire area of column a 2nd to columns 8th into one cell or: Xlobj.range ("A2", Xlobj.cells (8, 8)). MergeCells = true;9. Sets the row height and column width ExcelSheet.ActiveSheet.Columns (startcol+ ":" +endcol). ColumnWidth = 22;//set from Firstcol to StopcThe OL column has a width of 22excelsheet.activesheet.rows (startrow+ ":" +endrow). RowHeight = 22;//Set the width of the line from FirstRow to Stoprow to 22Var myrange Osheet.range (Osheet.cells (2,1), Osheet.cells (2,6)); MyRange.Columns.AutoFit () MyRange.Rows.AutoFit ()//doesn't seem to work well/ Set the range to adaptive height and width using JavaScript to export tabular content to Excel 1. Entire table copied into Excel function copytable (tableid) {//entire table copied to excel var CURTB      L = document.getElementById (TableID);      var OXL = new ActiveXObject ("Excel.Application");      Create an Ax object in Excel var oWB = OXL.Workbooks.Add ();          Get Workbook object var osheet = Owb.activesheet;      Activates the current sheet var sel = Document.body.createTextRange ();      Sel.movetoelementtext (CURTBL);      Move the contents of the table into the TextRange sel.select ();      Full selection TextRange content Sel.execcommand ("Copy");      Copy the contents of TextRange osheet.paste ();      Paste into the active Excel oxl.visible = true; Set Excel Visible Properties} 2. Copy the contents of the cell to Excel and format the cell. function Method2 (tableid)//read each cell in the table into Excel {var curtbl = Document.geteleme     Ntbyid (TableID); var OXL =New ActiveXObject ("Excel.Application");     Create an Ax object in Excel var oWB = OXL.Workbooks.Add ();     Get Workbook object var osheet = Owb.activesheet;     Activates the current sheet var lenr = curTbl.rows.length;     var Y=document.getelementbyid (' Dropyear ');     var M=document.getelementbyid (' Dropmonth ');     Number of table rows obtained Osheet.cells (1, 1). value= ' Merchant name: ICBC Mall '; Osheet.range (Osheet.cells), Osheet.cells (1,6). Merge () osheet.cells (2,1). value= date;     ' +y.options[y.selectedindex].value+ '-' +m.options[m.selectedindex].value; Osheet.range (Osheet.cells (2,1), Osheet.cells (2,6)). Merge () Osheet.cells (3, 1). value= ' Sales status ' Osheet.range (Osheet.cells (3,1), Osheet.cells (3,6)). Merge () Osheet.cells (3, 1).     horizontalalignment=3; Osheet.cells (3, 1).     font.size=14; Osheet.cells (3, 1).     Font.Bold = true; Osheet.range (Osheet.cells (4,1), Osheet.cells (4,6)).     Merge () osheet.cells (4,1). Value=document.getelementbyid (' Lbmessage '). InnerText; for (i = 0; i < lenr; i++) {var lenc = cuRtbl.rows (i). cells.length;//gets the number of columns per row for (j = 0; J < Lenc, J + +) {osheet.cells (i + 5, j + 1)             . Value = Curtbl.rows (i). Cells (j). InnerText;     Assignment}} osheet.cells (i+6,2). Value=document.getelementbyid (' Hidsum '). Value;     OSheet.Columns.AutoFit ();     OSheet.Rows.AutoFit (); Osheet.rows (4).     RowHeight = 30;     oXL.Visible = true;     Set Excel Visible Property Oxl.quit ();     OXL = null;     IDTMR = Window.setinterval ("Cleanup ();", 1);//force release of Resources}function Cleanup ()//Clear Excel Resource {window.clearinterval (IDTMR); CollectGarbage ();} Use JavaScript to copy content to the Clipboard in tabular format 1. Paste Function copytable () {var content= ' var tb=document.getelementbyid (' Gvmain ') in Excel    );    var rows=tb.rows;          for (Var i=0;i<rows.length;i++) {var cells=rows[i].cells;          for (Var j=0;j<cells.length;j++) {content +=cells[j].innertext.tostring () + ' \ t ';     } content + = ' \ n '; } if (content!= ') {ClipboarddatA.setdata ("text", content);     Alert ("Copy succeeded!") }}

This article is selected from http://www.cnblogs.com/lhws/archive/2012/03/15/2397952.html

Javscript Exporting table to excel in HTML