欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

程序员文章站 2022-05-18 12:41:15
本文实例讲述了jsp导出excel并支持分sheet导出的方法。分享给大家供大家参考,具体如下: public downloadfile exporttoexce...

本文实例讲述了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程序设计有所帮助。