js匯出table中的EXCEL總結

標籤：seconds   man   ica   ++   版本號碼   function   add   .exe   otto   

   匯出EXCEL通常是用PHP做，可是項目中，有時候PHP後端project師返回的資料不是我們想要的，作為前端開發project師，把相應的資料編號轉換為文字後，展示給使用者。可是。需求要把資料同一時候匯出一份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();}

改代碼已經驗證，能夠使用。調用非常easy，直接用就能夠
onclick="getXlsFromTbl(‘functionclickExcel‘,null);就能夠了！

js匯出table中的EXCEL總結