导出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比较神秘，不过当自己要做这个事情的时候查找一些资料，也能很快的搞定。想到朋友经常说的一句话---just do it 。

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