js导出table中的EXCEL总结

   导出EXCEL一般是用PHP做，但是项目中，有时候PHP后端工程师返回的数据不是我们想要的，作为前端开发工程师，把对应的数据编号转换为文字后，展示给用户，但是，需求要把数据同时导出一份EXCEl。无奈之下，我只能用js导出table中的数据了。

   导出EXCEl一般是自己人用的，所以用js导出，因为js导出EXCEL一般情况下兼容性不是很好，很多只是兼容IE浏览器，还要设置在工具栏中进行设置才能导出，因为会相对比较烦。下面介绍几种方法：

一、js导出EXCEl带单元格合并【已验证，比较好用】

// JavaScript Document//调用方法//   var test=new PageToExcel("data",0,255,"测试.xls");//table id , 第几行开始，最后一行颜色 ，保存的文件名//   test.CreateExcel(false);//   test.Exec();//   test.SaveAs();//   test.CloseExcel();//LastRowColor 0黑色 255红色//function PageToExcel(TableID,FirstRow,LastRowColor,SaveAsName){this.lastRowColor=LastRowColor==""?0:LastRowColor;var today=new Date();this.saveAsName=(SaveAsName==""?today.getYear()+"年"+(today.getMonth()+1)+"月"+today.getDate()+"日.xls":SaveAsName);this.tableId=TableID;this.table=document.getElementById(this.tableId);//导出的table 对象this.rows=this.table.rows.length;//导出的table总行数this.colSumCols=this.table.rows[0].cells.length;//第一行总列数this.fromrow=FirstRow;this.beginCol=0; //起始列数this.cols=this.colSumCols;this.oXL=null;this.oWB=null;this.oSheet=null;this.rowSpans=1; //行合并    this.colSpans=1; //列合并    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){//数组NotShowColList    //this.notShowColList=NotShowColList;//不显示列集合,1,2,3,1    //删除excel中的列   var m=0;   for(var i=0;i<NotShowColList.length;i++){         if(i>0){            m++;         }        var temp=NotShowColList[i]- m;        var index=this.colsName[temp];   this.oSheet.Columns(index).Delete;//删除   }   m=0;}    PageToExcel.prototype.CreateExcel=function(ExcelVisible){   try{   this.oXL = new ActiveXObject("Excel.Application"); //创建应该对象   this.oXL.Visible = ExcelVisible;   this.oWB = this.oXL .Workbooks.Add();//新建一个Excel工作簿    this.oSheet = this.oWB.ActiveSheet;//指定要写入内容的工作表为活动工作表   //不显示网格线   this.oXL.ActiveWindow.DisplayGridlines=false;   }catch(e){    alert("请确认安装了非绿色版本的excel！"+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<childres.length;i++){   try{if(childres[i].style.display=="none"||childres[i].type.toLowerCase()=="hidden"){continue;}}   catch(e){}         try{      switch (childres[i].nodeName.toLowerCase()){        case "#text" :         GetText +=childres[i].nodeValue ;         break;        case "br" :         GetText +="\n";         break;        case "img" :         GetText +="";         break;        case "select" :         GetText +=childres[i].options[childres[i].selectedIndex].innerText ;         break;        case "input" :         if(childres[i].type.toLowerCase()=="submit"||childres[i].type.toLowerCase()=="button"){          GetText +="";         }else if(childres[i].type.toLowerCase()=="textarea"){          GetText +=childres[i].innerText;         }else{          GetText +=childres[i].value;         }         break;        default :         GetText += this.ChangeElementToLabel(childres[i]);         break;      }          }catch(e){}   }   return GetText;}PageToExcel.prototype.SaveAs=function (){   //保存   try{    this.oXL.Visible =true;    var fname = this.oXL.Application.GetSaveAsFilename(this.saveAsName, "Excel Spreadsheets (*.xls), *.xls");     if(fname){     this.oWB.SaveAs(fname);     this.oXL.Visible =false;    }   }catch(e){}; }PageToExcel.prototype.Exec=function(){     //寻找列数，考虑到第一行可能存在   for (var i=0; i<this.colSumCols;i++) {    var tmpcolspan = this.table.rows(0).cells(i).colSpan;    if ( tmpcolspan>1 ) {     this.cols += tmpcolspan-1;    }   }     //定义2维容器数据，1：行；2：列；值（0 可以填充，1 已被填充）   var container=new Array(this.rows);   for (var i=0;i<this.rows;i++) {    container[i]=new Array(this.cols);    for (j=0;j<this.cols;j++) {     container[i][j]=0;    }   }     //将所有单元置为文本，避免非数字列被自动变成科学计数法和丢失前缀的0   this.oSheet.Range(this.oSheet.Cells(this.fromrow+1,1), this.oSheet.Cells(this.fromrow+this.rows,this.cols)).NumberFormat = "@";   // 循环行   for (i=0;i<this.rows;i++){    //循环列    for (j=0;j<this.cols;j++){     //寻找开始列     for (k=j;k<this.cols;k++){      if (container[i][k]==0) {       this.beginCol=k;       k=this.cols; //退出循环      }     }//try{      //赋值      //此处相应跟改 根据 标签的类型，替换相关参数      this.oSheet.Cells(i+1+this.fromrow,this.beginCol+1).value = this.ChangeElementToLabel(this.table.rows(i).cells(j));                //计算合并列      try{     this.colSpans = this.table.rows(i).cells(j).colSpan;      }catch(e){     this.colSpans=0        }     if (this.colSpans>1) {      //合并      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();     }     //将当前table位置填写到对应的容器中     for (k=0; k<this.colSpans;k++) {      container[i][this.beginCol+k]= 1;     }     // 计算合并行         try{      this.rowSpans = this.table.rows(i).cells(j).rowSpan;       }catch(e){       this.rowSpans = 0;     }         if (this.rowSpans>1) { //行合并      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();      //将当前table位置填写到对应的容器中      for (k=1; k<this.rowSpans;k++) { //由于第0行已经被colSpans对应的代码填充了，故这里从第1行开始       for (l=0;l<this.colSpans;l++) {        container[i+k][this.beginCol+l]=1;       }      }     }     //如果开始列＋合并列已经等于列数了，故不需要再循环html table     if (this.beginCol+this.colSpans>=this.cols) j=this.cols;       }    if(i==0)    {     //标题栏     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; //居中     this.oSheet.Range(this.oSheet.Cells(1,1), this.oSheet.Cells(1,1)).Rows.RowHeight = 40;    }     //自动调整行高   }       //最后一行是否空色   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;   //自动换行   this.oSheet.Range(this.oSheet.Cells(this.fromrow+2,1), this.oSheet.Cells(this.fromrow+this.rows,this.cols)).WrapText = true;   //自动调整列宽   this.oSheet.Range(this.oSheet.Cells(this.fromrow+1,1), this.oSheet.Cells(this.fromrow+this.rows,this.cols)).Columns.AutoFit();   //点虚线   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;}

注意：要改IE浏览器安全设置

二、js导出table中的EXCEL.该方法只能在IE内核下运行，相比其他方法的好处是，不

用再设置什么属性或者安装什么插件了，思路如下：

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("你要导出的表不存在！");            return;        }        var fileName = getExcelFileName();        doFileExport(fileName, allStr);    }    catch(e) {        alert("导出发生异常:" + 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).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 + "不存在!");    }    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 = "91zaojia" + "_" + 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.document.write(inStr);    xlsWin.document.close();    xlsWin.document.execCommand('Saveas', true, inName);    xlsWin.close();}

改代码已经验证，可以使用。调用很简单，直接用就可以

onclick="getXlsFromTbl('functionclickExcel',null);就可以了！