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

本文实例讲述了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);
}

希望本文所述对大家jsp程序设计有所帮助。