jsp匯出excel並支援分sheet匯出的方法_JSP編程

本文執行個體講述了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程式設計有所協助。