jsp导出excel并支持分sheet导出的方法

public downloadfile exporttoexcel() throws exception{ string config_value = systemconfigservice.getconfigvaluebykey("export.xls"); logger.info("获取的导出类型为execl表格"+",每页sheet导出的行数为:"+config_value+"行-------------------"); file file = new file(new simpledateformat("yyyy-mm-dd").format(new date())+".xls"); writableworkbook wbook = null ; writablesheet wsheet =null; wbook = workbook.createworkbook(file); //建立excel文件 long starttime = system.currenttimemillis(); try { exporttoexcelsearchcondition condittion = ((exporttoexcelsearchcondition) servletcontext.currentsession().getattribute("condittion")); list<contenttoexcel> cte = new arraylist<contenttoexcel>(); if(condittion == null){ throw new exception("session获取查询条件失败"); }else{ //根据条件查询需要导出的数据 cte = category2contentdao.searchbycondition(condittion.getcategoryid(), condittion.gethsdflag(), condittion.getexternalcontentid(),condittion.getcontentname(),condittion.getcontentid(), condittion.getsystemid(), condittion.getstate(), condittion.getcpid(),condittion.getcontenttype(),condittion.getbeforemodifytime(),condittion.getaftermodifytime()); } logger.info("开始导出excel表格--"); if(stringutils.isnotblank(config_value)){ int value = numberutils.toint(config_value); if( cte !=null && cte.size()>0 ){ int k = 0;//分sheet的个数 int i = 0;//用于循环excel的行号 iterator<contenttoexcel> it = cte.iterator(); map<string,string> contentidandcontenttypemap = new hashmap<string,string>(); map<string,string> contentidanddurationmap= new hashmap<string,string>(); for (contenttoexcel contenttoexcel : cte) { contentidandcontenttypemap.put(contenttoexcel.getcontentid(), contenttoexcel.getcontenttype()); } //计算时长 contentidanddurationmap = category2contentdao.getdurationbycontentidmap(contentidandcontenttypemap); /** * 1)采用iterator迭代器进行迭代，与for循环相比可优化迭代效率。 * 2)大量数据影响效率，求大数据量的解决办法。 * add by guohua.yuan 2013-06-08 */ while(it.hasnext()){ contenttoexcel contenttoexcel = it.next(); if( i % value == 0){ wsheet = wbook.createsheet("节目单导出("+(int)(i/value+1)+")", (int)(i/value+1)); //工作表名称 //设置excel字体 writablefont wfont = new writablefont(writablefont.arial, 10, writablefont.bold, false, jxl.format.underlinestyle.no_underline, jxl.format.colour.black); writablecellformat titleformat = new writablecellformat(wfont); string[] title = { "标识","名称", "类型", "码流分档", "时长","快编人员" ,"初审人员","复审人员","终审人员","最后更新时间","内容创建时间","入库时间","状态"}; //设置excel表头 for (int j = 0; j < title.length; j++) { label exceltitle = new label(j, 0, title[j], titleformat); wsheet.addcell(exceltitle); } k = k+1; } wsheet.addcell(new label(0, i+1 -value*(k-1), contenttoexcel.getcontentid())); wsheet.addcell(new label(1, i+1 - value*(k-1), contenttoexcel.getcontentname())); if(contenttoexcel.getcontenttype().equals("serie")){ wsheet.addcell(new label(2, i+1 - value*(k-1), "电视剧单集")); }else if(contenttoexcel.getcontenttype().equals("series")){ wsheet.addcell(new label(2, i+1 - value*(k-1), "连续剧")); }else if(contenttoexcel.getcontenttype().equals("movie")){ wsheet.addcell(new label(2, i+1 - value*(k-1), "电影")); } if(contenttoexcel.gethsdflag() == 1){ wsheet.addcell(new label(3, i+1 - value*(k-1), "标清")); }else if(contenttoexcel.gethsdflag() == 2){ wsheet.addcell(new label(3, i+1 -value*(k-1), "高清")); }else if(contenttoexcel.gethsdflag() == 3){ wsheet.addcell(new label(3, i+1 - value*(k-1), "超高清")); } //添加时长 wsheet.addcell(new label(4, i+1 - value*(k-1),contentidanddurationmap.get(contenttoexcel.getcontentid()))); wsheet.addcell(new label(5, i+1 - value*(k-1), contenttoexcel.getkbpersonner())); wsheet.addcell(new label(6, i+1 - value*(k-1), contenttoexcel.getcspersonner())); wsheet.addcell(new label(7, i+1 - value*(k-1), contenttoexcel.getfspersonner())); wsheet.addcell(new label(8, i+1 - value*(k-1), contenttoexcel.getzspersonner())); if(contenttoexcel.getmodifytime()==null){ wsheet.addcell(new label(9, i+1 - value*(k-1), "")); }else{ wsheet.addcell(new label(9, i+1 - value*(k-1), new simpledateformat("yyyy-mm-dd hh:mm:ss").format(contenttoexcel.getmodifytime()))); } if(contenttoexcel.getcreatetime() == null){ wsheet.addcell(new label(10, i+1 - value*(k-1), "")); }else{ wsheet.addcell(new label(10, i+1 - value*(k-1), new simpledateformat("yyyy-mm-dd hh:mm:ss").format(contenttoexcel.getcreatetime()))); } if(contenttoexcel.getinstoragetime() ==null){ wsheet.addcell(new label(11,i+1 - value*(k-1),"")); }else{ wsheet.addcell(new label(11,i+1 - value*(k-1),new simpledateformat("yyyy-mm-dd hh:mm:ss").format(contenttoexcel.getinstoragetime()))); } if(stringutils.equals(contenttoexcel.getstatus(), "1500")){ wsheet.addcell(new label(12, i+1 - value*(k-1), "成品就绪")); }else if(stringutils.equals(contenttoexcel.getstatus(), "1700")){ wsheet.addcell(new label(12, i+1 - value*(k-1), "发布成功 ")); }else if(stringutils.equals(contenttoexcel.getstatus(), "1800")){ wsheet.addcell(new label(12, i+1 - value*(k-1), "发布失败")); }else if(stringutils.equals(contenttoexcel.getstatus(), "1901")){ wsheet.addcell(new label(12, i+1 - value*(k-1), "发布中")); } i++; } wbook.write();//写入文件 }else{ throw new exception("没有数据可导"); } }else{ throw new exception("请检查系统配置管理是否配置导出类型的数据"); } } catch (exception e) { throw new exception(e); }finally{ if(wbook!=null){ wbook.close(); } } logger.info("导出excel耗时："+(system.currenttimemillis() - starttime)+"ms"); servletcontext.currentsession().removeattribute("condittion"); return new downloadfile(file.getname()).readfrom(file); }