导出excel报表

前段时间把报表导出成excel，把代码分享给大家

/** * * @Title: doExport 
* @Description: 数据导出 
* @param @param service 报表管理使用服务类 
* @param @param category 报表类型 
* @param @param json 报表统计条件 
* @param @param mimeType 导出文件类型 如：xls、cvs 等 
* @param @throws Exception 
* @return void * @throws 
*/ 
private void doExport(ReportManagementService service, String category, String json,UserVo user, String mimeType) throws Exception
{ 
   JSONObject jsonObject = JSONObject.fromObject(json); 
   String plantId = user.getPlantID();
   AbstractVo jsonVo = null; 
   List<AbstractVo> outputVoList = null; 
   List<DL2ReportOutputVo> DL2ListVo = new ArrayList<DL2ReportOutputVo>(); 
   try{ 
      
      jsonVo = (DL2ReportInputVo)JSONObject.toBean(jsonObject, DL2ReportInputVo.class); 
      DL2ReportInputVo inputVo = (DL2ReportInputVo)JSONObject.toBean(jsonObject, DL2ReportInputVo.class); 
      //获得报表的数据 
     DL2ListVo = service.statDL2ReportVo(this.getOsp(), inputVo, plantId);
    }catch(Exception ex){ 
       log.debug("Get report data error: "+ ex); 
       throw new Exception("Get report data error: "+ ex); 
    } 
    OutputStream os = null; 
     try{
         os = getResponse().getOutputStream(); 
      }catch(IOException ioex){ 
        throw new Exception("Get response OutputStream Error: " + ioex);
      } 
      Calendar calendar = Calendar.getInstance(); 
      SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmmss"); 
      String currentDatetime = sdf.format(calendar.getTime()); 
       getResponse().reset(); 
       getResponse().setHeader("Content-disposition", "attachment; filename=" + category + currentDatetime+ ".xls");        getResponse().setContentType("application/msexcel"); 
       WritableWorkbook workbook = null; 
       WritableSheet sheet = null; 
      try{ 
          workbook = Workbook.createWorkbook(os); 
          sheet = workbook.createSheet("Sheet 1", 0); 
            sheet.getSettings().setDefaultColumnWidth(15); 
         //create header 
          WritableFont headerFont = new WritableFont(WritableFont.ARIAL,10,WritableFont.BOLD,false,UnderlineStyle.NO_UNDERLINE,jxl.format.Colour.BLACK);           WritableCellFormat headerFormat = new WritableCellFormat(headerFont); if("DL2".equals(category)){ DL2ReportInputVo inputVo = (DL2ReportInputVo)jsonVo;    
           //设定excel标题的名称以及位置    
    Label titleLabel = new Label(0, 0, "行政发文查询清单", headerFormat);
    Label sendDateDurationLabel = new Label(0, 1, "发文日期："+inputVo.getSendDateBegin()+" 至 "+inputVo.getSendDateEnd(), headerFormat);
    Label serialNumberLabel = new Label(0,2,"序号",headerFormat); 
    Label fileNumberLabel = new Label(1,2,"文件编号",headerFormat);
    Label fileTitleLabel = new Label(2,2,"文件标题",headerFormat);
    Label fileTypeLabel = new Label(3,2,"公文类别",headerFormat);
    Label pageNumberLabel = new Label(4,2,"页数",headerFormat);
    Label drafterLabel = new Label(5,2,"拟稿人",headerFormat);
    Label signerLabel = new Label(6,2,"签发人",headerFormat);
    Label approveDateLabel = new Label(7,2,"批准日期",headerFormat);
    Label sendDateLabel = new Label(8,2,"发文日期",headerFormat);
    Label sendWayLabel = new Label(9,2,"外发方式",headerFormat);
    Label toUnitLabel = new Label(10,2,"主送单位",headerFormat);
    //添加到excel sheet中
    sheet.addCell(titleLabel);
    sheet.addCell(sendDateDurationLabel);
    sheet.addCell(serialNumberLabel);
    sheet.addCell(fileNumberLabel);
    sheet.addCell(fileTitleLabel);
    sheet.addCell(fileTypeLabel);
    sheet.addCell(pageNumberLabel);
    sheet.addCell(drafterLabel);
    sheet.addCell(signerLabel);
    sheet.addCell(approveDateLabel);
    sheet.addCell(sendDateLabel);
    sheet.addCell(sendWayLabel);
    sheet.addCell(toUnitLabel);
    if (DL2ListVo != null && !DL2ListVo.isEmpty()){
     Label serialNumber = null;
     Label fileNumber = null;
     Label fileType = null;
     Label fileTitle = null;
     Label pageNumber = null;
     Label drafter = null;
     Label signer = null;
     Label approveDate = null;
     Label sendDate = null;
     Label sendWay = null;
     Label toUnit =null;
     for ( int i = 0 ;i<DL2ListVo.size() ; i++){
      DL2ReportOutputVo outPut = DL2ListVo.get(i);
      serialNumber = new Label(0,3 + i,outPut.getSerialNumber());
      fileNumber = new Label(1,3 +i,judgeNull(outPut.getFileNumber()));
      fileTitle = new Label(2,3+i,outPut.getFileTitle());
      fileType = new Label(3,3+i,outPut.getFileType());
      pageNumber = new Label(4,3+i,judgeNull(outPut.getPageNumber()));
      drafter = new Label(5,3+i,judgeNull(outPut.getDrafter()));
      signer = new Label(6,3+i,outPut.getSigner());
      approveDate = new Label(7,3 + i,outPut.getApproveDate());
      sendDate = new Label(8,3+i,outPut.getSendDate());
      sendWay = new Label(9,3+i,judgeNull(outPut.getSendWay()));
      toUnit = new Label(10,3+i,outPut.getToUnit());
     // 把相应的数据放入到excel中      
      sheet.addCell(serialNumber);
      sheet.addCell(fileNumber);
      sheet.addCell(fileTitle);
      sheet.addCell(fileType);
      sheet.addCell(pageNumber);
      sheet.addCell(drafter);
      sheet.addCell(signer);
      sheet.addCell(approveDate);
      sheet.addCell(sendDate);
      sheet.addCell(sendWay);
      sheet.addCell(toUnit);
     }
       
    }
   }
  }catch(Exception ex){
   
   log.debug("Generate excel error: " + ex);
   throw new Exception("Generate excel error: " + ex);
   
  }finally{
   try{
    workbook.write();
    workbook.close();
    os.close();
   }catch(Exception ex){
    log.debug("Close WritableWorkbook error: " + ex);
    throw new Exception("Close WritableWorkbook error: " + ex);
   }
  }
  
  
 }
 //导出报表时判断是否为空，为空不显示为null
 public String judgeNull(String str){
  String judgeStr ="";
  if(str != "null"){
   judgeStr = str;
  }
  return judgeStr;
 }

以前觉得报表导出excel比较神秘，不过当自己要做这个事情的时候查找一些资料，也能很快的搞定。想到朋友经常说的一句话---justdo it 。

其实有时候害怕做什么事情，找各种借口说做不好，其实就是在给自己的懒惰找借口。拒绝懒惰，走向成功.