java jxl poi_【Java】JXL和POI操作Excel

public void outputExcel(HttpServletRequest request, HttpServletResponse response,String title)throwsIOException

{//获取输出流

OutputStream os =response.getOutputStream();

HttpSession session=request.getSession();

String oprator=(String)session.getAttribute("yhxm");//设置编码

response.setHeader("Content-disposition", "attachment; filename="+"cprdpxx"+".xls");//设定输出文件头

request.setCharacterEncoding("utf-8");//设置文件格式

response.setContentType("application/vnd.ms-excel;charset=utf-8");//加标题//标题字体//设置单元格字体，位置

jxl.write.WritableFont wfc = new jxl.write.WritableFont(WritableFont.COURIER, 18, WritableFont.BOLD, true);

jxl.write.WritableCellFormat wcfFC= newjxl.write.WritableCellFormat(wfc);try{

wcfFC.setAlignment(jxl.format.Alignment.CENTRE);

wcfFC.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);//字段字体

jxl.write.WritableFont wfc1 = new jxl.write.WritableFont(WritableFont.COURIER, 12, WritableFont.NO_BOLD, false,UnderlineStyle.NO_UNDERLINE, Colour.BLACK);

jxl.write.WritableCellFormat wcfFC1= newjxl.write.WritableCellFormat(wfc1);

wcfFC1.setBorder(Border.NONE,BorderLineStyle.THIN,Colour.GRAY_50);//设置字体位置

wcfFC1.setAlignment(jxl.format.Alignment.CENTRE);

wcfFC1.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);//

//2013/7/17//标题字体

jxl.write.WritableFont wfc3 = new jxl.write.WritableFont(WritableFont.COURIER, 14, WritableFont.BOLD, false,UnderlineStyle.NO_UNDERLINE, Colour.BLACK);

jxl.write.WritableCellFormat wcfFC3= newjxl.write.WritableCellFormat(wfc3);

wcfFC3.setBorder(Border.NONE,BorderLineStyle.THIN,Colour.GRAY_50);//设置字体位置

wcfFC3.setAlignment(jxl.format.Alignment.CENTRE);

wcfFC3.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);

//查询结果字体

jxl.write.WritableCellFormat wcfFC2 = newjxl.write.WritableCellFormat();

wcfFC2.setAlignment(jxl.format.Alignment.CENTRE);

wcfFC2.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);

WritableWorkbook wbook=Workbook.createWorkbook(os);//设置默认字体

WritableFont font=new WritableFont(WritableFont.COURIER, 12, WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE, Colour.BLACK);

WritableCellFormat cFormat= newWritableCellFormat(font);

cFormat.setAlignment(jxl.format.Alignment.CENTRE);

cFormat.setBorder(Border.ALL,BorderLineStyle.THIN,Colour.GRAY_50);

cFormat.setBackground(Colour.WHITE);//2013/7/17//设置信息头栏字体

WritableFont font11=new WritableFont(WritableFont.COURIER, 12, WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE, Colour.BLACK);

WritableCellFormat cFormat11= newWritableCellFormat(font11);

cFormat11.setAlignment(jxl.format.Alignment.CENTRE);

cFormat11.setBorder(Border.ALL,BorderLineStyle.THIN,Colour.GRAY_50);

cFormat11.setBackground(Colour.WHITE);//写sheet名称

WritableSheet wsheet = wbook.createSheet("长江大学职称评审投票信息统计表", 0);//设置单元格默认列宽

wsheet.getSettings().setDefaultColumnWidth(10);

wsheet.getSettings().setDefaultRowHeight(350);//设置sheet页面水平打印

wsheet.getSettings().setHorizontalCentre(true);//默认为横向打印//2013/7/17

wsheet.setPageSetup(PageOrientation.LANDSCAPE.LANDSCAPE,PaperSize.A4,0.5d,0.5d);/*** lrr 2014-05-27 BEGIN

* 功能描述：设置页码格式：第 X 页(共 X 页)*/

//wsheet.setFooter("", "&P", "");//过时的方法

··HeaderFooter footer = newHeaderFooter();

··Contents contentsFooter=footer.getCentre();

contentsFooter.setFontSize(10);

contentsFooter.append("第 ");

contentsFooter.appendPageNumber();

contentsFooter.append(" 页 ( 共 ");

contentsFooter.appendTotalPages();

contentsFooter.append(" 页 )");//设置打印标题行//wsheet.getSettings().setVerticalFreeze(3);

SheetSettings ss=wsheet.getSettings();

ss.setFooter(footer);//设置页脚

/*** lrr 2014-05-27 END

* 功能描述：设置页码格式：第 X 页(共 X 页)*/

//ss.setPrintTitles(0,2,0,10);

ss.setPrintTitlesRow(0,2);//设置固定打印标题

ss.setOrientation(PageOrientation.LANDSCAPE);

wsheet.setPageSetup(PageOrientation.LANDSCAPE);//标题

wsheet.mergeCells(0, 0, 10, 0);/*** lrr 2014-05-24 改 BEGIN

* 功能描述：修改投票信息统计表格式*/wsheet.addCell(new jxl.write.Label(0, 0, "长江大学职称评审投票信息统计表",wcfFC3));//设置统计表标题

WritableFont font1=new WritableFont(WritableFont.COURIER, 12, WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE, Colour.BLACK);

WritableCellFormat cFormat1= newWritableCellFormat(font1);

cFormat1.setAlignment(jxl.format.Alignment.RIGHT);

cFormat1.setBorder(Border.NONE,BorderLineStyle.THIN,Colour.GRAY_50);

cFormat1.setBackground(Colour.WHITE);

wsheet.addCell(new jxl.write.Label(0,1,"评审日期："+(new SimpleDateFormat("yyyy-MM-dd").format(new java.util.Date())) ,cFormat1));//合计列

/*** lrr 2014-05-24 改 END

* 功能描述：修改投票信息统计表格式*/

/*** 以下为从数据库中查询数据添加到wsheet中*/PersistenceManagerOfZcps pmoz=newPersistenceManagerOfZcps();

String zsql="select count(*) from view_cprdp where 1=1 "+title;//总人数//String xb_n_sql="select count(*) from view_cprxx where xb='1'";//性别男 人数

String hj="";//合计

String sql="select * from view_cprdp where 1=1 "+title;//String year=request.getParameter("year");

sql+="order by year_dm desc,zcbh asc,ls,ty_num desc,cpbh";// //System.out.println("sql="+sql);

ResultSet rs=pmoz.executeQuery(sql);

List cprdp_list=new ArrayList();int zs = 0;//参评人总人数

int ns=0;//男参评人数

try{while(rs.next())

{

zc_cprdpxx zp=newzc_cprdpxx();

PersistenceManagerOfZcps pm=newPersistenceManagerOfZcps();

zp.setYear(rs.getString("year_mc")==null?"":rs.getString("year_mc").trim());

zp.setCpbh(rs.getString("cpbh")==null?"":rs.getString("cpbh").trim());

zp.setCpxm(rs.getString("cpxm")==null?"":rs.getString("cpxm").trim());

zp.setLxdh(rs.getString("lxdh")==null?"":rs.getString("lxdh").trim());

zp.setTy_num(rs.getString("ty_num")==null?"":rs.getString("ty_num").trim());

zp.setBty_num(rs.getString("bty_num")==null?"":rs.getString("bty_num").trim());

zp.setQq_num(rs.getString("qq_num")==null?"":rs.getString("qq_num").trim());

zp.setSftg(rs.getString("sftg")==null?"":rs.getString("sftg").trim());

zp.setLs(rs.getString("ls")==null?"":rs.getString("ls").trim());

zp.setZwm(rs.getString("zwm_mc")==null?"":rs.getString("zwm_mc").trim());//2013/7/21

zp.setZcbh(pm.getPureCode_zcm(rs.getString("zcbh")==null?"":rs.getString("zcbh").trim()));

zp.setCprzt(pm.getPureCode("code_zczt", rs.getString("zcbh").substring(1, 2)));

pm.close();//zp.setNl(rs.getString("nl")==null?"":rs.getString("nl").trim());

cprdp_list.add(zp);

}

rs=pmoz.executeQuery(zsql);while(rs.next())

{

zs=rs.getInt(1);

}

rs.close();

}catch(SQLException e) {

os.close();//TODO Auto-generated catch block

this.cwts("导出失败，请稍后重试！", response);

e.printStackTrace();

}finally{

pmoz.close();//关闭数据连接

}//hj="评委总人数为："+String.valueOf(zs)+" 性别 男："+String.valueOf(ns)+" 女："+String.valueOf(zs-ns);

wsheet.mergeCells(0, 1, 10, 1);//合并0到10单元格//wsheet.addCell(new jxl.write.Label(0,1,hj ,cFormat));//合计列//设置固定的打印标题//2013/7/17wsheet.getSettings().setPrintArea(0, 2, 0,7);//SheetSettings setting = wsheet.getSettings();//setting.setPrintTitlesRow(0, 3);

int i = 0;int j=0;//String[] colum={"年度","参评人编号","单位名称","参评人姓名","性别","申报职称级别","申报职称名称"};//2013/7/17修改

String[] colum={"年度", "参评人编号","工作单位","参评人姓名", "申报职称","申报状态","轮数","同意数","不同意数","弃权数","是否通过"};for (i = 0; i <11; i++) {//加入行字段名

wsheet.addCell(new jxl.write.Label(i, 2, colum[i], cFormat11));

}for(i=3;i

{

wsheet.addCell(new jxl.write.Label(0,i, cprdp_list.get(i-3).getYear(), cFormat));

wsheet.addCell(new jxl.write.Label(1,i, cprdp_list.get(i-3).getCpbh(), cFormat));

wsheet.addCell(new jxl.write.Label(2,i, cprdp_list.get(i-3).getLxdh(), cFormat));

wsheet.addCell(new jxl.write.Label(3,i, cprdp_list.get(i-3).getCpxm(), cFormat));

wsheet.addCell(new jxl.write.Label(4,i, cprdp_list.get(i-3).getZwm(), cFormat));

wsheet.addCell(new jxl.write.Label(5,i, cprdp_list.get(i-3).getCprzt(), cFormat));

wsheet.addCell(new jxl.write.Label(6,i, cprdp_list.get(i-3).getLs(), cFormat));

wsheet.addCell(new jxl.write.Label(7,i, cprdp_list.get(i-3).getTy_num(), cFormat));

wsheet.addCell(new jxl.write.Label(8,i, cprdp_list.get(i-3).getBty_num(), cFormat));

wsheet.addCell(new jxl.write.Label(9,i, cprdp_list.get(i-3).getQq_num(), cFormat));

wsheet.addCell(new jxl.write.Label(10,i, cprdp_list.get(i-3).getSftg(), cFormat));

}

wsheet.mergeCells(0, i, 10, i);//合并0到10单元格

/*** lrr 2014-05-24 增 BEGIN

* 功能描述：增加四行：1.监票人 2.评审委员会主任*/wsheet.addCell(new jxl.write.Label(0,i,"制表人："+oprator ,cFormat1));//合计列

i = i+1;

wsheet.mergeCells(0, i, 10, i+1);//合并两行并同时合并单元格

wsheet.addCell(new jxl.write.Label(0,i,"监票人： " ,cFormat1));//合计列

i = i+2;

wsheet.mergeCells(0, i, 10, i+1);//合并两行并同时合并单元格

wsheet.addCell(new jxl.write.Label(0,i,"评审委员会主任： " ,cFormat1));//合计列

/*** lrr 2014-05-24 增 END

* 功能描述：增加两行：1.监票人 2.评审委员会主任*/wbook.write();

wbook.close();

}catch(WriteException e) {//TODO Auto-generated catch block

e.printStackTrace();//this.cwts("导出失败，请稍后重试！", response);

} catch(IOException e1) {//TODO Auto-generated catch block

e1.printStackTrace();//this.cwts("导出失败，请稍后重试！", response);

}finally{try{

os.close();

}catch(IOException e1) {//TODO Auto-generated catch block

e1.printStackTrace();//this.cwts("导出失败，请稍后重试！", response);

}

}

}