Jsp中的table多表头导出excel文件具体实现

首先引入两份js:copyhtmltoexcel.js以及 tabletoexcel.js

/*
 * 默认转换实现函数，如果需要其他功能，需自行扩展
 * 参数：
 *      tableid : html中table对象id属性值
 * 详细用法参见以下 tabletoexcel 对象定义 
 */
function saveasexcel(tableid){
 var tb = new tabletoexcel(tableid);
  tb.setfontstyle("courier new");
  tb.setfontsize(10);
  tb.settableborder(2);
  tb.setcolumnwidth(7);
  tb.islinewrap(false);
  tb.isautofit(true);
  tb.getexcelfile();
}

/*
 *  功能：html中table对象转换为excel通用对象.
 *  作者：jeva
 *  时间：2006-08-09
 *  参数：tableid  html中table对象的id属性值
 *  说明：
 *       能适应复杂的html中table对象的自动转换，能够自动根据行列扩展信息
 *       合并excel中的单元格，客户端需要安装有excel
 *       详细的属性、方法引用说明参见：excel的microsoft excel visual basic参考
 *  示范：
 *       var tb = new tabletoexcel('demotable');
 *    tb.setfontstyle("courier new");
 *    tb.setfontsize(10);  //推荐取值10
 *    tb.setfontcolor(6);  //一般情况不用设置
 *    tb.setbackground(4);  //一般情况不用设置
 *    tb.settableborder(2);  //推荐取值2
 *    tb.setcolumnwidth(10);  //推荐取值10
 *    tb.islinewrap(false);
 *    tb.isautofit(true);
 *   
 *    tb.getexcelfile();
 *   如果设置了单元格自适应，则设置单元格宽度无效
 *  版本：1.0
 */
function tabletoexcel(tableid) {
    this.tableborder = -1; //边框类型，-1没有边框 可取1/2/3/4
    this.background = 0; //背景颜色：白色   可取调色板中的颜色编号 1/2/3/4....
    this.fontcolor = 1;  //字体颜色：黑色
    this.fontsize = 10;  //字体大小
    this.fontstyle = "宋体"; //字体类型
    this.rowheight = -1; //行高
    this.columnwidth = -1; //列宽
    this.linewrap = true; //是否自动换行
    this.textalign = -4108; //内容对齐方式   默认为居中
    this.autofit = false;  //是否自适应宽度
    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.linewrap = linewrap;
    }
};

tabletoexcel.prototype.settextalign = function (textalign) {
    this.textalign = textalign;
};

tabletoexcel.prototype.isautofit = function(autofit){
 if(autofit == true || autofit == false)
  this.autofit = autofit ;
}

//文件转换主函数
tabletoexcel.prototype.getexcelfile = function () {
    var jxls, myworkbook, myworksheet, myhtmltablecell, myexcelcell, myexcelcell2;
    var mycellcolspan, mycellrowspan;

    try {
        jxls = new activexobject('excel.application');
    }
    catch (e) {
        alert("无法启动excel!\n\n" + e.message +
           "\n\n如果您确信您的电脑中已经安装了excel，"+
           "那么请调整ie的安全级别。\n\n具体操作：\n\n"+
           "工具 → internet选项 → 安全 → 自定义级别 → activex控件和插件 \n\n" +
       "→ 启用 : 对没有标记为安全的activex控件进行初始化和脚本运行");
        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;

//设置行高、列宽
    if(this.columnwidth != -1)
     myworksheet.columns.columnwidth = this.columnwidth;
    else
     myworksheet.columns.columnwidth = 7;
    if(this.rowheight != -1)
     myworksheet.rows.rowheight = this.rowheight ;

//搜索需要转换的table对象，获取对应行、列数
    var obj = document.all.tags("table");
    for (x = 0; x < obj.length; x++) {
        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;
            }
        }
    }

//开始构件模拟表格
    var exceltable = new array();
    for (i = 0; i <= totalrow; i++) {
        exceltable[i] = new array();
        for (t = 0; t <= totalcol; t++) {
            exceltable[i][t] = false;
        }
    }

//开始转换表格   
    for (z = 0; z < obj[tabnum].rows.length; z++) {
        readrow = z + 1;
        readcol = 1;
        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 + readcol - 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;
};

copyhtmltoexcel.js

//eltalbeout 这个为导出内容的外层表格，主要是设置border之类的样式，eldiv则是整个导出的html部分
function onhtmltoexcel(eltableout,eldiv){
 try{
  //设置导出前的数据，为导出后返回格式而设置
  var eldivstrbak = eldiv.innerhtml;
  //设置table的border=1，这样到excel中就有表格线 ps:感谢双面提醒
  eltableout.border=1;
  //过滤eldiv内容
  var eldivstr = eldiv.innerhtml;
  eldivstr = replacehtml(eldivstr,"<a",">");
  eldivstr = replacehtml(eldivstr,"</a",">");
  eldiv.innerhtml=eldivstr; 

  var orangeref = document.body.createtextrange();
  orangeref.movetoelementtext( eldiv );
  orangeref.execcommand("copy");

  //返回格式变换以前的内容
  eldiv.innerhtml = eldivstrbak;
  //内容数据可能很大，所以赋空
  eldivstrbak = "";
  eldivstr = "";

  var oxl = new activexobject("excel.application")
  var owb = oxl.workbooks.add ;
  var osheet = owb.activesheet ;
  osheet.paste();
  osheet.cells.numberformatlocal = "@";
  osheet.columns("d:d").select
  oxl.selection.columnwidth = 20
  oxl.visible = true;  
  osheet = null;
  owb = null;
  appexcel = null;
 }catch(e){
  alert(e.description)
 }
}

function replacehtml(replacedstr,repstr,endstr){  
  var replacedstrf = "";  
  var replacedstrb = "";  
  var repstrindex = replacedstr.indexof(repstr);  
  while(repstrindex != -1){  
      replacedstrf = replacedstr.substring(0,repstrindex);  
      replacedstrb = replacedstr.substring(repstrindex,replacedstr.length);  
      replacedstrb = replacedstrb.substring(replacedstrb.indexof(endstr)+1,replacedstrb.length);  
      replacedstr = replacedstrf + replacedstrb;  
      repstrindex = replacedstr.indexof(repstr);  
  }  
  return replacedstr;
}

在jsp页面中写js方法

  //生成excel
  function ontabletoexcel(){
   var eltableout = document.getelementbyid("eltableout");
   var eldiv = document.getelementbyid("eldiv");
   onhtmltoexcel(eltableout,eldiv);
  }

注意事项：使用js导出excel需要设置ie的active。注意最佳的是ie8浏览器。至于在java后台实现excel还没去研究，希望各位大神指教。