jsp導(dǎo)出excel并支持分sheet導(dǎo)出的方法

public DownloadFile exportToExcel() throws Exception{
 String config_value = systemConfigService.getConfigValueByKey("Export.Xls");
 logger.info("獲取的導(dǎo)出類型為execl表格"+",每頁sheet導(dǎo)出的行數(shù)為:"+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"));
  ListContentToExcel> cte = new ArrayListContentToExcel>();
  if(condittion == null){
   throw new Exception("session獲取查詢條件失敗");
  }else{
   //根據(jù)條件查詢需要導(dǎo)出的數(shù)據(jù)
   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("開始導(dǎo)出excel表格--");
  if(StringUtils.isNotBlank(config_value)){
    int value = NumberUtils.toInt(config_value);
   if( cte !=null  cte.size()>0 ){
   int k = 0;//分sheet的個(gè)數(shù)
   int i = 0;//用于循環(huán)Excel的行號(hào)
   IteratorContentToExcel> it = cte.iterator(); 
   MapString,String> contentidAndContentTypeMap = new HashMapString,String>(); 
   MapString,String> contentIdAndDurationMap= new HashMapString,String>();
   for (ContentToExcel contentToExcel : cte) {
    contentidAndContentTypeMap.put(contentToExcel.getContentId(), contentToExcel.getContentType());
   }
   //計(jì)算時(shí)長(zhǎng)
   contentIdAndDurationMap = category2ContentDAO.getDurationByContentIdMap(contentidAndContentTypeMap);
   /**
   * 1)采用iterator迭代器進(jìn)行迭代，與for循環(huán)相比可優(yōu)化迭代效率。
   * 2)大量數(shù)據(jù)影響效率，求大數(shù)據(jù)量的解決辦法。
   * add by guohua.yuan 2013-06-08
   */
   while(it.hasNext()){
    ContentToExcel contentToExcel = it.next();
    if( i % value == 0){
      wsheet = wbook.createSheet("節(jié)目單導(dǎo)出("+(int)(i/value+1)+")", (int)(i/value+1)); //工作表名稱 
      //設(shè)置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 = { "標(biāo)識(shí)","名稱", "類型", "碼流分檔", "時(shí)長(zhǎng)","快編人員" ,"初審人員","復(fù)審人員","終審人員","最后更新時(shí)間","內(nèi)容創(chuàng)建時(shí)間","入庫時(shí)間","狀態(tài)"}; 
      //設(shè)置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), "連續(xù)劇")); 
      }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), "標(biāo)清")); 
      }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), "超高清"));
      }
      //添加時(shí)長(zhǎng)
      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), "發(fā)布成功 ")); 
      }else if(StringUtils.equals(contentToExcel.getStatus(), "1800")){
      wsheet.addCell(new Label(12, i+1 - value*(k-1), "發(fā)布失敗")); 
      }else if(StringUtils.equals(contentToExcel.getStatus(), "1901")){
      wsheet.addCell(new Label(12, i+1 - value*(k-1), "發(fā)布中")); 
      }
      i++;
   }
   wbook.write();//寫入文件
   }else{
   throw new Exception("沒有數(shù)據(jù)可導(dǎo)");
   }
  }else{
   throw new Exception("請(qǐng)檢查系統(tǒng)配置管理是否配置導(dǎo)出類型的數(shù)據(jù)");
  }
 } catch (Exception e) {
  throw new Exception(e);
 }finally{
  if(wbook!=null){
   wbook.close();
  }
 }
 logger.info("導(dǎo)出excel耗時(shí)："+(System.currentTimeMillis() - startTime)+"ms");
 ServletContext.currentSession().removeAttribute("condittion");
 return new DownloadFile(file.getName()).readFrom(file);
}