导出Execl表格
/** * 导出集合方法封装 * @param list 需要导出的数据 * @return srModel 前台需要的uploadFile */ private BatchEduceDetailPMSrModel EduceDetail(List<OasPlMainDedail> list){ String srcPath = "D:"+FileUtil.separator; //ftp地址 String DescFile = AppConfig.getProperty("Location_Default_Dir")+FileUtil.separator +"oas_dtplmain"; BatchEduceDetailPMSrModel srModel = new BatchEduceDetailPMSrModel(); //excel操作类 HSSFWorkbook workbook = new HSSFWorkbook(); //excel创建一个sheet HSSFSheet sheet = workbook.createSheet(); //sheet.setDefaultColumnWidth(100); //sheet.addMergedRegion(new Region(0,(short)0,0,(short)3)); HSSFCellStyle cellStyle = workbook.createCellStyle();//创建一个样式 cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); HSSFFont cellStyleFont = workbook.createFont();//创建字体样式 cellStyleFont.setBoldweight((short)4);//加粗 cellStyleFont.setFontName("新宋体"); cellStyleFont.setFontHeightInPoints((short) 10);//设置字体大小 cellStyle.setFont(cellStyleFont);//字体样式添加到样式 /* HSSFRow rHssfRow0 = sheet.createRow(0);//创建第一行 rHssfRow0.setHeight((short)(35.7*14));//设置高度 * 第一行跨行列格式 sheet.addMergedRegion(new Region(0,(short)(0),0,(short)(1))); sheet.addMergedRegion(new Region(0,(short)(2),0,(short)(4))); sheet.addMergedRegion(new Region(0,(short)(5),0,(short)(6))); sheet.addMergedRegion(new Region(0,(short)(7),0,(short)(9))); sheet.addMergedRegion(new Region(0,(short)(10),0,(short)(11))); sheet.addMergedRegion(new Region(0,(short)(12),0,(short)(14))); rHssfRow0.createCell(0).setCellValue("会议计划名称"); rHssfRow0.getCell(0).setCellStyle(cellStyle); rHssfRow0.createCell(5).setCellValue("会议时间"); rHssfRow0.getCell(5).setCellStyle(cellStyle); rHssfRow0.createCell(10).setCellValue("创建者"); rHssfRow0.getCell(10).setCellStyle(cellStyle); rHssfRow0.createCell(2).setCellValue(oasPlMain.getPlanName()); rHssfRow0.createCell(7).setCellValue(oasPlMain.getPlanTime()); HSSFCellStyle cellDateStyleRow1 = CellStyleUtil.createDateFormat(workbook); rHssfRow0.getCell(7).setCellStyle(cellDateStyleRow1); //设置日期格式 rHssfRow0.createCell(12).setCellValue(oasPlMain.getTrueName()); */ HSSFRow rHssfRow = sheet.createRow(0);//创建第一行 rHssfRow.setHeight((short)(35.7*14));//设置高度 //rHssfRow.setRowStyle(cellStyle);//设置行样式 rHssfRow.createCell(0).setCellValue("序号"); //rHssfRow.getCell(0).setCellStyle(cellStyle);//引用列样式 sheet.setColumnWidth(0,(short)(2000));//宽度 rHssfRow.createCell(1).setCellValue("分类"); //rHssfRow.getCell(1).setCellStyle(cellStyle);//引用列样式 sheet.setColumnWidth(1,(short)(3000));//宽度 rHssfRow.createCell(2).setCellValue("项目名称"); //rHssfRow.getCell(2).setCellStyle(cellStyle);//引用列样式 sheet.setColumnWidth(2,(short)(6500));//宽度 rHssfRow.createCell(3).setCellValue("入住日期"); //rHssfRow.getCell(3).setCellStyle(cellStyle);//列样式 sheet.setColumnWidth(3,(short)(6400));//宽度 rHssfRow.createCell(4).setCellValue("预备会时间"); //rHssfRow.getCell(4).setCellStyle(cellStyle);//列样式 sheet.setColumnWidth(4,(short)(6400));//宽度 rHssfRow.createCell(5).setCellValue("住宿人数"); //rHssfRow.getCell(5).setCellStyle(cellStyle);//列样式 sheet.setColumnWidth(5,(short)(4000));//宽度 rHssfRow.createCell(6).setCellValue("第一步开标时间"); //rHssfRow.getCell(6).setCellStyle(cellStyle);//列样式 sheet.setColumnWidth(6,(short)(6400));//宽度 rHssfRow.createCell(7).setCellValue("第一步开标人数"); //rHssfRow.getCell(7).setCellStyle(cellStyle);//列样式 sheet.setColumnWidth(7,(short)(5300));//宽度 rHssfRow.createCell(8).setCellValue("第二步开标时间"); //rHssfRow.getCell(8).setCellStyle(cellStyle);//列样式 sheet.setColumnWidth(8,(short)(6400));//宽度 rHssfRow.createCell(9).setCellValue("第二步开标人数"); //rHssfRow.getCell(9).setCellStyle(cellStyle);//列样式 sheet.setColumnWidth(9,(short)(5300));//宽度 rHssfRow.createCell(10).setCellValue("评标会议室需求"); //rHssfRow.getCell(10).setCellStyle(cellStyle);//列样式 sheet.setColumnWidth(10,(short)(5500));//宽度 rHssfRow.createCell(11).setCellValue("会议室落实情况"); //rHssfRow.getCell(11).setCellStyle(cellStyle);//列样式 sheet.setColumnWidth(11,(short)(5400));//宽度 rHssfRow.createCell(12).setCellValue("离店日期"); //rHssfRow.getCell(12).setCellStyle(cellStyle);//列样式 sheet.setColumnWidth(12,(short)(6400));//宽度 rHssfRow.createCell(13).setCellValue("项目经理"); //rHssfRow.getCell(13).setCellStyle(cellStyle);//列样式 sheet.setColumnWidth(13,(short)(4500));//宽度 rHssfRow.createCell(14).setCellValue("开标地点"); //rHssfRow.getCell(14).setCellStyle(cellStyle);//列样式 sheet.setColumnWidth(14,(short)(10000));//宽度 rHssfRow.createCell(15).setCellValue("评标地点"); //rHssfRow.getCell(15).setCellStyle(cellStyle);//列样式 sheet.setColumnWidth(15,(short)(10000));//宽度 rHssfRow.createCell(16).setCellValue("会务负责人"); sheet.setColumnWidth(16,(short)(4500));//宽度 CellStyle cell=workbook.createCellStyle(); HSSFFont font = workbook.createFont();//创建字体样式 font.setBoldweight((short)4);//加粗 font.setFontName("新宋体"); font.setFontHeightInPoints((short) 10);//设置字体大小 cell.setAlignment(HSSFCellStyle.ALIGN_CENTER); cell.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); cell.setFont(font); cell.setWrapText(true); rHssfRow.getCell(16).setCellStyle(cell);//列样式 for (int i = 0; i <= 16; i++) { rHssfRow.getCell(i).setCellStyle(CellStyleUtil.createTopBorder(workbook)); //第一行 每一列设置边框 } //创建行id 从1开始 int i = 1; //内容写入excel HSSFRow row = null; HSSFCellStyle cellDateStyle = CellStyleUtil.createDateFormat(workbook); for(OasPlMainDedail oas : list){ //创建行 row = sheet.createRow(i); //写入内容 row.createCell(0).setCellValue(i); row.getCell(0).setCellStyle(cellStyle); row.createCell(1).setCellValue(dao.getPlanType(oas.getPlanType())); row.createCell(2).setCellValue(oas.getBidName()); if(null==oas.getCheckIn()){ row.createCell(3).setCellValue(""); row.getCell(3).setCellStyle(cellDateStyle); //设置日期格式 }else{ row.createCell(3).setCellValue(oas.getCheckIn()); row.getCell(3).setCellStyle(cellDateStyle); //设置日期格式 } if(null==oas.getReadinessTime()){ row.createCell(4).setCellValue(""); row.getCell(4).setCellStyle(cellDateStyle); }else{ row.createCell(4).setCellValue(oas.getReadinessTime()); row.getCell(4).setCellStyle(cellDateStyle); } row.createCell(5).setCellValue(oas.getStayPopulation()); if(null==oas.getFirstBidOpeningDate()){ row.createCell(6).setCellValue(""); row.getCell(6).setCellStyle(cellDateStyle); }else{ row.createCell(6).setCellValue(oas.getFirstBidOpeningDate()); row.getCell(6).setCellStyle(cellDateStyle); } row.createCell(7).setCellValue(oas.getFirstBidOpeningPopulation()); if(null==oas.getFirstBidOpeningDate()){ row.createCell(8).setCellValue(""); row.getCell(8).setCellStyle(cellDateStyle); }else{ row.createCell(8).setCellValue(oas.getFirstBidOpeningDate()); row.getCell(8).setCellStyle(cellDateStyle); } row.createCell(9).setCellValue(oas.getSecondBidOpeningPopulation()); row.createCell(10).setCellValue(oas.getEvaluationOfBidNeed()); row.createCell(11).setCellValue(oas.getEvaluationOfBidEndInfo()); if(null==oas.getDepartureDate()){ row.createCell(12).setCellValue(""); row.getCell(12).setCellStyle(cellDateStyle); }else{ row.createCell(12).setCellValue(oas.getDepartureDate()); row.getCell(12).setCellStyle(cellDateStyle); } row.createCell(13).setCellValue(oas.getProjectManager()); row.createCell(14).setCellValue(oas.getOpenAddress()); row.createCell(15).setCellValue(oas.getEvaAddress()); row.createCell(16).setCellValue(oas.getOptUserId()); for (int j = 0; j <= 16; j++) { if(j!=12&&j!=8&&j!=6&&j!=3&&j!=4) row.getCell(j).setCellStyle(CellStyleUtil.createBodyBorder(workbook)); //给非日期列设置边框 } i++; } java.io.FileOutputStream _fos = null; try{ //初始路径D:/flowDedail.xls srcPath = srcPath+"flowDedail.xls"; //输出流 设置输出位置 _fos = new java.io.FileOutputStream(srcPath); //创建的HSSFWorkbook对象写入输出流 workbook.write(_fos); //创建文件 File file=new File(srcPath); //设置id 在前台下载 srModel.setUpLoadFileId(FileUtil.uploadFile(file.getAbsolutePath(),DescFile)); logger.info("文件id: "+srModel.getUpLoadFileId()); } catch (Exception e) { logger.info("导出失败.."); e.printStackTrace(); } finally { try { _fos.close(); } catch (IOException e) { e.printStackTrace(); } } return srModel; }