javascript导出EXCEl方法总结

javascript导出excel，一般用于后台开发，主要是公司内部人员使用是用js直接导出excel,原因是，javascript必须在IE内核的浏览器下面才能成功导出，chrome浏览器不支持。

应用场景：1、公司内部后台 2、IE浏览器 3、导出比较复杂，通常是带合并单元格等 4、后端数据导出比较难处理的情况。

PS：导出EXCEL还是强烈建议打击用PHPEXCEl，因为PHP导出excel兼容性更好！格式也可以调整，也很强到，具体PHP导出excel请见后面文章！

方法一：js导出EXCEl带单元格合并

首先，要改IE浏览器安全设置，如下图：

然后请看如下函数：

函数参数说明：

// 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浏览器做参数设置，但是不能导出带有合并单元格的EXCEl，兼容性相对上一个函数要好一些！但是也是只能在IE浏览器下面运行，该方法只能导出table中的excel。

原理：循环获取table中的数据，然后把数据存放到一个自定义网页窗口中，再把该数据 xlsWin.document.execCommand保存到excel中。

代码如下：

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);就可以了！