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还没去研究,希望各位大神指教。
上一篇: 利用PS快速改变各种商品颜色
下一篇: PS打造经典逼真的大众车标