哪位大哥有现成的代码或者案列啊 ,小弟在这里谢了
或者发到我邮件上asdex1999@yahoo.com.cn
这是我写的一个财务经理考核系统其中生成EXCEL报表的部分,你参考一下吧。最后生成的EXCEL为了免去别人在浏览器中
直接打开而不是提示保存的麻烦我用了smartupload.
<%@page contentType="text/html; charset=gb2312"%>
<%@ page import="java.util.Date"%>
<%@ page import="java.lang.Integer"%>
<%@ page import="java.io.File"%>
<%@ page import="jxl.*"%>
<%@ page import="jxl.write.*"%>
<%@ page import="jxl.format.*"%>
<%@ page import="java.sql.*" %>
<jsp:useBean id="dbConn" scope="page" class="dbclass.dbConn" />//这里要换成你自己的数据库连接Bean
<%
request.setCharacterEncoding("GBK");
boolean bExportSucc = false;
String sErrorStr="";
String sHead="/admin/tongji/excel/";
String sFileName="Report("+request.getParameter("period")+").xls";
String sCurrPath=application.getRealPath(sHead);
if(!sCurrPath.substring(sCurrPath.length()-1).equals("/"))sCurrPath=sCurrPath+"/";
sCurrPath=sCurrPath+sFileName;
//获取文件的URL地址
String sAttachServer="127.0.0.1";
if(sAttachServer.length()==0)
sAttachServer=request.getServerName()+":"+request.getServerPort();
String sUrlPath="http://"+sAttachServer+"/system"+sHead+sFileName;
File file=new File(sCurrPath);
//以下开始输出到EXCEL
try {
/************创建工作簿*************/
WritableWorkbook workbook = Workbook.createWorkbook(file);
/************创建工作表*************/
WritableSheet sheet = workbook.createSheet("财务经理考核排名("+request.getParameter("period")+")", 0);
/************设置页眉、页脚******************/
sheet.setHeader("页眉","","第 &P 页,共 &N 页"); //设置页眉
sheet.setFooter("","","&D &T"); //设置页脚
/******注:以上这些&P、&D等参数可以从EXCEL的宏录制取得********/
/***********设置列宽*****************/
sheet.setColumnView(0,5); //第1列
sheet.setColumnView(1,15); //第2列
sheet.setColumnView(2,15);
sheet.setColumnView(3,15);
sheet.setColumnView(4,15);
sheet.setColumnView(5,15);
sheet.setColumnView(6,15);
sheet.setColumnView(7,15);
sheet.setColumnView(8,15);
/**************设置单元格字体***************/
WritableFont NormalFont = new WritableFont(WritableFont.ARIAL,10);
WritableFont BoldFont = new WritableFont(WritableFont.ARIAL,14,WritableFont.BOLD);
/**************以下设置几种格式的单元格*************/
//用于标题
WritableCellFormat wcf_title = new WritableCellFormat(BoldFont);
wcf_title.setBorder(Border.NONE, BorderLineStyle.THIN); //线条
wcf_title.setVerticalAlignment(VerticalAlignment.CENTRE); //垂直对齐
wcf_title.setAlignment(Alignment.CENTRE); //水平对齐
wcf_title.setWrap(false); //是否换行
//用于正文左
WritableCellFormat wcf_left = new WritableCellFormat(NormalFont);
wcf_left.setBorder(Border.ALL, BorderLineStyle.THIN); //线条
wcf_left.setVerticalAlignment(VerticalAlignment.CENTRE); //垂直对齐
wcf_left.setAlignment(Alignment.CENTRE);
wcf_left.setWrap(false); //是否换行
//用于正文右
WritableCellFormat wcf_right = new WritableCellFormat(NormalFont);
wcf_right.setBorder(Border.ALL, BorderLineStyle.THIN); //线条
wcf_right.setVerticalAlignment(VerticalAlignment.CENTRE); //垂直对齐
wcf_right.setAlignment(Alignment.CENTRE);
wcf_right.setWrap(false); //是否换行
//用于跨行
WritableCellFormat wcf_merge = new WritableCellFormat(NormalFont);
wcf_merge.setBorder(Border.ALL, BorderLineStyle.THIN); //线条
wcf_merge.setVerticalAlignment(VerticalAlignment.TOP); //垂直对齐
wcf_merge.setAlignment(Alignment.LEFT);
wcf_merge.setWrap(true); //是否换行
/**************单元格格式设置完成*******************/
/*****************以下是标题***************************/
sheet.mergeCells(0, 0, 8, 0); //合并单元格
sheet.addCell(new Label(0,0,"财务经理考核排名",wcf_title));
sheet.mergeCells(0, 1, 8, 1); //合并单元格
sheet.addCell(new Label(0,1,"考核期间:"+request.getParameter("period"),wcf_title));
/*****************以下是定单内容**********************/
sheet.addCell(new Label(0,2,"名次",wcf_left));
sheet.addCell(new Label(1,2,"被考核人",wcf_left));
sheet.addCell(new Label(2,2,"被考核人机构",wcf_left));
sheet.addCell(new Label(3,2,"会计核算",wcf_left));
sheet.addCell(new Label(4,2,"财务系统管理",wcf_left));
sheet.addCell(new Label(5,2,"财务综合管理",wcf_left));
sheet.addCell(new Label(6,2,"资金管理",wcf_left));
sheet.addCell(new Label(7,2,"预算管理",wcf_left));
sheet.addCell(new Label(8,2,"合计",wcf_left));
/*************数据库处理部分***************************/
String realname="";
String department="";
double[] mark=new double[7];
double added=0.00;
String sqlname="select name,realname,department from sys_admin where `group`='0'";
dbConn.dbConn();
ResultSet rsname=dbConn.exeQuery(sqlname);
while(rsname.next()){
realname=rsname.getString("realname");
department=rsname.getString("department");
String sql1="drop table IF EXISTS tt;";
String sql2="CREATE temporary table tt select SUM(sys_mark.`mark`) as `t2`,sys_admin.`realname` as `realname`,sys_khxm.`xiangmu` as `xiangmu`,sys_zhibiao.`zhibiao` as `zhibiao`,sys_zhibiao.`standmark` as `standmark`,sys_khxm.`weight` as `weight`,sys_admin.`department` as `department` from sys_zhibiao,sys_mark,sys_khxm,sys_admin where sys_mark.`zhibiao`=sys_zhibiao.`Id` and sys_mark.parentname=sys_admin.name and sys_mark.`parentname`='"+rsname.getString("name")+"' AND sys_zhibiao.`parentid`=sys_khxm.`Id` AND sys_mark.`period`='"+request.getParameter("period")+"' group by sys_zhibiao.`zhibiao`;";
String sql3="update tt set t2=standmark where t2>standmark;";
String sql4="select (100-SUM(t2))*weight as mark,realname,xiangmu,department,weight from tt where xiangmu='会计核算考核' group by xiangmu;";
String sql5="select (100-SUM(t2))*weight as mark,realname,xiangmu,weight from tt where xiangmu='财务系统管理考核' group by xiangmu;";
String sql6="select (100-SUM(t2))*weight as mark,realname,xiangmu,weight from tt where xiangmu='财务综合管理考核' group by xiangmu;";
String sql7="select (100-SUM(t2))*weight as mark,realname,xiangmu,weight from tt where xiangmu='资金管理考核' group by xiangmu;";
String sql8="drop table IF EXISTS tt2;";
String sql9="Create temporary table tt2 select t2,realname,department,xiangmu,zhibiao,standmark,weight from tt where xiangmu='预算管理考核';";
String sql10="select t2*0.24 as mark from tt2 where zhibiao='管理费用执行率';";
String sql11="select t2*0.56 as mark from tt2 where zhibiao='业务费用执行率';";
String sql12="select SUM(t2)*0.2 as mark from tt2 where zhibiao='预算编制' OR zhibiao='预算调整' OR zhibiao='预算追加' OR zhibiao='预算报告' group by xiangmu;";
boolean b1=dbConn.exeUpdate(sql1);
boolean b2=dbConn.exeUpdate(sql2);
boolean b3=dbConn.exeUpdate(sql3);
ResultSet rs=dbConn.exeQuery(sql4);
if(rs.next()){
mark[0]=Double.parseDouble(rs.getString("mark"));
}else
mark[0]=25.00;
rs=dbConn.exeQuery(sql5);
if(rs.next()){
mark[1]=Double.parseDouble(rs.getString("mark"));
}else
mark[1]=10.00;
rs=dbConn.exeQuery(sql6);
if(rs.next()){
mark[2]=Double.parseDouble(rs.getString("mark"));
}else
mark[2]=25.00;;
rs=dbConn.exeQuery(sql7);
if(rs.next()){
mark[3]=Double.parseDouble(rs.getString("mark"));
}else
mark[3]=15.00;
try{
boolean b4=dbConn.exeUpdate(sql8);
boolean b5=dbConn.exeUpdate(sql9);
rs=dbConn.exeQuery(sql10);
if(rs.next()){
mark[4]=24-Double.parseDouble(rs.getString("mark"));
}else
mark[4]=24.00;
rs=dbConn.exeQuery(sql11);
if(rs.next()){
mark[5]=56-Double.parseDouble(rs.getString("mark"));
}else
mark[5]=56.00;
rs=dbConn.exeQuery(sql12);
if(rs.next()){
mark[6]=20-Double.parseDouble(rs.getString("mark"));
}else
mark[6]=20.00;
mark[4]=(Double.parseDouble(new java.text.DecimalFormat("0.00").format(mark[4]+mark[5]+mark[6])))*0.25;
added=mark[0]+mark[1]+mark[2]+mark[3]+mark[4];
}catch(Exception e){}
String sqlcreate="CREATE TEMPORARY TABLE `mark_HZ` (`id` INT NOT NULL AUTO_INCREMENT ,`realname` VARCHAR( 30 ) NOT NULL ,`department` VARCHAR( 30 ) NOT NULL ,`hesuan` DOUBLE( 16, 2 ) DEFAULT '0.00' NOT NULL ,`xitong` DOUBLE( 16, 2 ) DEFAULT '0.00' NOT NULL ,`zonghe` DOUBLE( 16, 2 ) DEFAULT '0.00' NOT NULL ,`zijin` DOUBLE( 16, 2 ) DEFAULT '0.00' NOT NULL ,`yusuan` DOUBLE( 16, 2 ) DEFAULT '0.00' NOT NULL ,`added` DOUBLE( 16, 2 ) DEFAULT '0.00' NOT NULL ,PRIMARY KEY ( `id` ) ) TYPE = MYISAM;";
String sqlinsert="insert into mark_HZ (`realname`,`department`,`hesuan`,`xitong`,`zonghe`,`zijin`,`yusuan`,`added`) values ('"+realname+"','"+department+"','"+mark[0]+"','"+mark[1]+"','"+mark[2]+"','"+mark[3]+"','"+mark[4]+"','"+added+"');";
//String sqlselect="select * from mark_HZ order by `added` DESC;";
//out.println(sqlinsert+"<br>");
boolean b6=dbConn.exeUpdate(sqlcreate);
boolean b7=dbConn.exeUpdate(sqlinsert);
}
String sqlselect="select * from mark_HZ order by `added` DESC;";
int i=1;
int iRow=3;
//dbConn.dbConn();
ResultSet rs=dbConn.exeQuery(sqlselect);
while(rs.next()){
String a=Integer.toString(i);
sheet.addCell(new Label(0,iRow,a,wcf_right));
sheet.addCell(new Label(1,iRow,rs.getString(2),wcf_right));
sheet.addCell(new Label(2,iRow,rs.getString(3),wcf_right));
sheet.addCell(new Label(3,iRow,rs.getString(4),wcf_right));
sheet.addCell(new Label(4,iRow,rs.getString(5),wcf_right));
sheet.addCell(new Label(5,iRow,rs.getString(6),wcf_right));
sheet.addCell(new Label(6,iRow,rs.getString(7),wcf_right));
sheet.addCell(new Label(7,iRow,rs.getString(8),wcf_right));
sheet.addCell(new Label(8,iRow,rs.getString(9),wcf_right));
iRow++;
i=Integer.parseInt(a);
i++;
}
sheet.mergeCells(0, iRow, 8, iRow); //合并单元格
sheet.addCell(new Label(0,iRow,"制表人:"+request.getParameter("operator"),wcf_title));
/*************详细内容***************/
//sheet.mergeCells(0, 4, 3, 4);
//sheet.setRowView(4,50); //设置行高
//sheet.addCell(new Label(0,4,"跨行内容",wcf_merge));
/****************联系/回复方式等*******************/
//int iRow = 4;
//sheet.addCell(new Label(0,iRow+1,"正文左",wcf_right));
//sheet.addCell(new Label(0,iRow+2,"正文左1",wcf_right));
//sheet.addCell(new Label(0,iRow+3,"正文左2",wcf_right));
//sheet.addCell(new Label(2,iRow+1,"正文左3",wcf_right));
//sheet.addCell(new Label(2,iRow+2,"正文左4",wcf_right));
//sheet.addCell(new Label(2,iRow+3,"正文左5",wcf_right));
//sheet.addCell(new Label(1,iRow+1,"正文右",wcf_left));
// sheet.addCell(new Label(1,iRow+2,"正文右1",wcf_left));
//sheet.addCell(new Label(1,iRow+3,"正文右2",wcf_left));
// sheet.addCell(new Label(3,iRow+1,"正文右3",wcf_left));
//sheet.addCell(new Label(3,iRow+2,"正文右4",wcf_left));
//sheet.addCell(new Label(3,iRow+3,"正文右5",wcf_left));
//sheet.mergeCells(0,iRow+4,3,iRow+4);
//sheet.setRowView(iRow+4,50);
//sheet.addCell(new Label(0,iRow+4,"跨行内容1",wcf_merge));
/************以上所写的内容都是写在缓存中的,下一句将缓存的内容写到文件中*********/
workbook.write();
/***********关闭文件**************/
workbook.close();
bExportSucc = true;
} catch(Exception e){
sErrorStr = e.toString();
System.out.println("在输出到EXCEL的过程中出现错误,错误原因:"+e.toString());
}
%>
<%@ page import="com.jspsmart.upload.*" %>
<%
// 新建一个SmartUpload对象
SmartUpload su = new SmartUpload();
// 初始化
su.initialize(pageContext);
// 设定contentDisposition为null以禁止浏览器自动打开文件,
//保证点击链接后是下载文件。若不设定,则下载的文件扩展名为
//doc时,浏览器将自动用word打开它。扩展名为pdf时,
//浏览器将用acrobat打开。
su.setContentDisposition(null);
// 下载文件
//su.downloadFile("./excel/"+sFileName);
su.downloadFile("D:/system/admin/tongji/excel/"+sFileName);
%>
<%
if(file.exists()){
file.delete();
}
%>
<%
dbConn.dbClose();
%>
不好意思,有一点要补充一下,里面的Border.ALL、BorderLineStyle.THIN、VerticalAlignment.CENTRE、Alignment.CENTRE要指明为:jxl.format.Border.ALL、jxl.format.BorderLineStyle.THIN、jxl.format.VerticalAlignment.CENTRE、jxl.format.Alignment.CENTRE
否则运行时会报引用不明确的错误