Js export table EXCEL Summary

Exporting EXCEL is generally done using PHP, but in projects, sometimes the data returned by the PHP backend engineer is not what we want. As a front-end development engineer, after converting the corresponding data number into text, the data must be exported to an EXCEl file. In desperation, I can only use js to export table data.

EXCEl export is generally used by people, so js export is used, because js export EXCEL is generally not very compatible, many are only compatible with IE browsers, but also set in the toolbar to export, because it will be relatively annoying. The following describes several methods:

1. js export EXCEl with cells merged [verified, easy to use]

// JavaScript Document // call method // var test = new PageToExcel ("data", 0,255, "test .xls"); // table id, starting from the row, last line color, saved file name // test. 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 () + "sun.xls": SaveAsName); this. tableId = TableID; this. table = document. getElementById (this. tableId); // The exported table object this. rows = this. table. rows. length; // The total number of rows of the exported table this. colSumCols = this. table. rows [0]. cells. length; // The total number of columns in the first row this. fromrow = FirstRow; this. beginCol = 0; // start column this. cols = this. colSumCols; this. oXL = null; this. oWB = null; this. oSheet = null; this. rowSpans = 1; // merge rows this. colSpans = 1; // merge columns this. colsName = {0: "A", 1: "B", 2: "C", 3: "D", 4: "E", 5: "F ", 6: "G", 7: "H", 8: "I", 9: "J", 10: "K", 11: "L", 12: "M", 13: "N", 14: "O", 15: "P", 16: "Q", 16: "R", 18: "S ", 19: "T", 20: "U", 21: "V", 22: "W", 23: "X", 24: "Y", 25: "Z" };} PageToExcel. prototype. deleteExcelCols = function (NotShowColList) {// array NotShowColList // this. notShowColList = NotShowColList; // The column set is not displayed. for example, 1, 2, 3, and 1, delete the column var m = 0 in excel. for (var I = 0; 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 the object this. oXL. visible = ExcelVisible; this. oWB = this. oXL. workbooks. add (); // create an Excel Workbook this. oSheet = this. oWB. activeSheet; // specify the worksheet to write content as the active worksheet // do not display gridlines this. oXL. ActiveWindow. DisplayGridlines = false;} catch (e) {alert ("check that non-green excel version 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
  
   
1) {this. cols + = tmpcolspan-1;} // defines 2-dimensional container data, 1: Row; 2: column; value (0 can be filled, 1 has been filled) var container = new Array (this. rows); for (var I = 0; I
   
    
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 () ;}// enter the current table location in the corresponding container for (k = 0; k
    
     
1) {// merge rows 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 (); // enter the current table location in the corresponding container for (k = 1; k
     
      
= This. cols) j = this. cols;} if (I = 0) {// Title bar this. oSheet. range (this. oSheet. cells (1, 1), this. oSheet. cells (1, 1 )). font. size = 20; this. oSheet. range (this. oSheet. cells (1, 1), this. oSheet. cells (1, 1 )). font. bold = true; this. oSheet. range (this. oSheet. cells (1, 1), this. oSheet. cells (1, 1 )). horizontalAlignment =-4108; // center this. oSheet. range (this. oSheet. cells (1, 1), this. oSheet. cells (1, 1 )). rows. rowHeight = 40;} // automatically adjust the Row Height} // whether the last row is null. try {this. oSheet. range (this. oSheet. cells (this. rows, 1), this. oSheet. cells (this. rows, 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; // automatically wrap this. oSheet. range (this. oSheet. cells (this. fromrow + 2, 1), this. oSheet. cells (this. fromrow + this. rows, this. cols )). wrapText = true; // automatically adjust the column width this. oSheet. range (this. oSheet. cells (this. fromrow + 1, 1), this. oSheet. cells (this. fromrow + this. rows, this. cols )). columns. autoFit (); // dotted line 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: You must change the security settings of IE.

<喎?http: www.bkjia.com kf ware vc " target="_blank" class="keylink"> VcD4KPHA + CjxpbWcgc3JjPQ = "http://www.2cto.com/uploadfile/Collfiles/20140628/20140628085243161.jpg" alt = "\">

2. js exports the EXCEL in the table. This method can only be run in the IE kernel. The advantage of other methods is that you do not need to set any attributes or install any plug-ins. 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, 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 ++) {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. l Ength = 1) {curHour = "0" + curHour;} if (curMinute. length = 1) {curMinute = "0" + curMinute;} if (curSecond. length = 1) {curSecond = "0" + curSecond ;} var fileName = "91 zaojia" + "_" + 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.doc ument. write (inStr); xlsWin.doc ument. close (); xlsWin.document.exe cCommand ('saveas', true, inName); xlsWin. close ();}

The Code has been verified and can be used. The call is simple and can be used directly.
Onclick = "getXlsFromTbl ('functionclickexcel ', null!