欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

程序员文章站 2022-10-10 19:06:06
首先引入两份js:copyhtmltoexcel.js以及 tabletoexcel.js复制代码 代码如下:/*  * 默认转换实现函数,如果需要其他功能,需自...

首先引入两份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还没去研究,希望各位大神指教。