本文實例講述了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程序設計有所幫助。
