<!-- excel word 相关的jar包 -->
<dependency>
<groupId>jxl</groupId>
<artifactId>jxl</artifactId>
<version>${jar-no-version-redefined-version}</version>
<scope>${jar.scope}</scope>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>${pom-version}</version>
<scope>${jar.scope}</scope>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>${pom-version}</version>
<scope>${jar.scope}</scope>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>${pom-version}</version>
<scope>${jar.scope}</scope>
</dependency>
1 /** 2 * 导出结果到Excel文件 3 * 4 * @author zhanghanqing 2015年8月15日 下午4:04:09 5 * @param wbook 6 * @param condition 7 * @throws WriteException 8 * @throws RowsExceededException 9 */ 10 private void exportRandomPatrolResultExcel(WritableWorkbook wbook, RmpRandomPatrolResultCondition condition, UserLoginPojo userLogin) 11 throws RowsExceededException, WriteException { 12 int pageSize = ConstParamRmp.MAX_PAGE_SIZE; 13 int page = 1; 14 int totoalPage = 1; 15 PageBean<RmpRandomPatrolResultDTO> origBeans = null; 16 WritableSheet sheet1 = wbook.createSheet("sheet1", 0); 17 WritableSheet sheet = sheet1; 18 WritableSheet titleSheet = sheet; 19 int index = 0; // 序号 20 int sheetNum = 1; 21 WritableFont font = new WritableFont(WritableFont.createFont(ConstParamRmp.LS_SongTypeface), 11, WritableFont.BOLD); 22 WritableCellFormat format = new WritableCellFormat(font); 23 while (page <= totoalPage) { 24 // 每页取6000条 25 condition.setPageNo(page); 26 condition.setPageSize(pageSize); 27 origBeans = this.getRandomPatrolResultPage(condition,userLogin, new ArrayList<Integer>(){{add(0);}}); 28 if (!origBeans.getRows().isEmpty()) { 29 int row = (page - 1) * pageSize % ConstParamRmp.ONE_SHEET_NUMBER; 30 int col = 0; 31 sheet.addCell(new Label(col++, row, "序号")); 32 sheet.addCell(new Label(col++, row, "考评门店")); 33 sheet.addCell(new Label(col++, row, "考评人")); 34 sheet.addCell(new Label(col++, row, "考评时间")); 35 sheet.addCell(new Label(col++, row, "状态")); 36 sheet.addCell(new Label(col++, row, "图片地址")); 37 int maxLength = 0; // 图片地址列宽度 38 HttpServletRequest request = RequestResponseContext.getRequest(); 39 String ip = HttpRequestUtils.getLocalAddr(request); 40 Integer port = HttpRequestUtils.getLocalPort(request); 41 PssServerDto pss = baseRefService.getPssServerConfig(ip, port, userLogin.getUserId()); 42 String picServerPrefix; 43 if(pss != null && Strings.isNotEmpty(pss.getIp()) && Strings.isNotEmpty(pss.getHttpPort())) { 44 picServerPrefix = "http://" + pss.getIp() + ":" + pss.getHttpPort(); 45 } else { 46 picServerPrefix = ""; 47 } 48 for (RmpRandomPatrolResultDTO dto : origBeans.getRows()) { 49 col = 0; 50 row++;// 第一行为标题信息 51 sheet.addCell(new jxl.write.Number(col++, row, ++index)); 52 sheet.addCell(new Label(col++, row, dto.getStoreName())); 53 sheet.addCell(new Label(col++, row, dto.getPatrolPersonName())); 54 Date date = null; 55 if (dto.getCreateTime() != null) { 56 date = new Date(dto.getCreateTime().getTime()); 57 } 58 sheet.addCell(new Label(col++, row, date != null ? "" + RmpDateTimeUtil.dateToString(date,RmpDateTimeUtil.DATA_FORMAT_yyyy_MM_dd_HH_mm) : "")); 59 sheet.addCell(new Label(col++, row, dto.getStatusDesc())); 60 // 多个抓图时，url在单元格中换行 61 String picUrlLabelContent; 62 if (!CollectionUtils.isEmpty(dto.getPictureList())) { 63 StringBuilder sb = new StringBuilder(); 64 int length = dto.getPictureList().size(); 65 for (int i = 0; i < length; i++) { 66 RmpPatrolPictureDTO pictureDTO = dto.getPictureList().get(i); 67 final int size = pictureDTO.getPicUrl().length(); 68 if (size > maxLength) { 69 maxLength = size; 70 } 71 sb.append(i + 1).append(". ").append(picServerPrefix).append(pictureDTO.getPicUrl()); 72 if(i != length -1) { 73 sb.append("\n"); 74 } 75 } 76 picUrlLabelContent = sb.toString(); 77 } else { 78 picUrlLabelContent = ""; 79 } 80 WritableCell picUrlCell = new Label(col++, row, picUrlLabelContent); 81 WritableCellFormat cellFormat = new WritableCellFormat(); 82 cellFormat.setWrap(true); 83 picUrlCell.setCellFormat(cellFormat); 84 sheet.addCell(picUrlCell); 85 if (row >=ConstParamRmp.MAX_PAGE_SIZE) { 86 col = 0; 87 row= 0; 88 sheet.addCell(new Label(col++, row, "序号")); 89 sheet.addCell(new Label(col++, row, "考评门店")); 90 sheet.addCell(new Label(col++, row, "考评人")); 91 sheet.addCell(new Label(col++, row, "考评时间")); 92 sheet.addCell(new Label(col++, row, "状态")); 93 sheet.addCell(new Label(col++, row, "图片地址")); 94 sheet = wbook.createSheet(titleSheet.getName() + sheetNum, sheetNum); 95 sheet.addCell(new Label(col, row, titleSheet.getCell(col++, row).getContents(), format)); 96 sheet.addCell(new Label(col, row, titleSheet.getCell(col++, row).getContents(), format)); 97 sheet.addCell(new Label(col, row, titleSheet.getCell(col++, row).getContents(), format)); 98 sheet.addCell(new Label(col, row, titleSheet.getCell(col++, row).getContents(), format)); 99 sheet.addCell(new Label(col, row, titleSheet.getCell(col++, row).getContents(), format)); 100 sheet.addCell(new Label(col, row, titleSheet.getCell(col++, row).getContents(), format)); 101 sheetNum++; 102 } 103 } 104 if(maxLength != 0) { 105 sheet.setColumnView(5, maxLength); 106 } 107 } 108 // 导出本页 109 page++; 110 totoalPage = origBeans.getTotalPage(); 111 } 112 }
1 public ActionResult exportCameraStatusNum(String regionId,HttpServletResponse response,UserLoginPojo userLogin) { 2 ActionResult result = new ActionResult(false, "导出失败"); 3 String exportFileName=null; 4 try { 5 exportFileName= URLEncoder.encode("监控点在线离线占比", "UTF-8"); 6 } catch (UnsupportedEncodingException e1) { 7 RLogUtils.logException(e1); 8 exportFileName="cameraStatusNum"; 9 } 10 OutputStream os = ExcelUtils.getOutputStreamForExcelExport(response, exportFileName); 11 WritableWorkbook wbook = null; 12 try { 13 long createWorkbookStartTime = System.currentTimeMillis(); 14 wbook = Workbook.createWorkbook(os); 15 long createWorkbookEndTime = System.currentTimeMillis(); 16 RLogUtils.logInfo("createWorkbook总时间："+(createWorkbookEndTime-createWorkbookStartTime) + "ms"); 17 18 19 this.exportCameraStatusNumExcel(wbook, regionId, userLogin); 20 long exportCameraStatusNumExcelTime = System.currentTimeMillis(); 21 RLogUtils.logInfo("exportCameraStatusNumExcel："+(exportCameraStatusNumExcelTime-createWorkbookEndTime) + "ms"); 22 23 result.setMessage("导出成功"); 24 result.setSuccess(true); 25 } catch (Exception e) { 26 RLogUtils.logException(e); 27 } finally { 28 try { 29 if (wbook != null) { 30 wbook.write(); 31 wbook.close(); 32 } 33 if (os != null) { 34 os.flush(); 35 os.close(); 36 } 37 } catch (Exception e) { 38 RLogUtils.logException(e); 39 } 40 } 41 return result; 42 43 44 }
private void exportCameraStatusNumExcel(WritableWorkbook wbook, String regionId, UserLoginPojo userLogin) throws Exception { List<Integer> regionIds = rmpPassengerUtilDao.getRegionIdsByParentNode(regionId); CameraStatusDto result = cameraDao.queryCameraOnlineStatus(regionIds); //查询各个门店的摄像头在线率 List<CameraOnlineRateDto> onlineRateDtos = cameraDao.queryLostOnlineRateStoreNew(regionIds,true); //查询各个门店的摄像头离线率 List<CameraOnlineRateDto> offlineRateDtos = cameraDao.queryLostOnlineRateStoreNew(regionIds,false); exportCameraStatusNumExcelOnline(onlineRateDtos,wbook,true); exportCameraStatusNumExcelOnline(offlineRateDtos,wbook,false); } private void exportCameraStatusNumExcelOnline(List<CameraOnlineRateDto> onlineRateDtos, WritableWorkbook wbook,boolean isOnline) throws Exception { //字体 // WritableFont wf_title = new WritableFont(WritableFont.createFont(ConstParamRmp.LS_SongTypeface), 25, WritableFont.BOLD); //WritableCellFormat format = new WritableCellFormat(font); WritableFont wf_title = new WritableFont(WritableFont.ARIAL, 11, WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.BLACK); WritableCellFormat wcf_title1 = new WritableCellFormat(wf_title); // 单元格定义 wcf_title1.setBackground(jxl.format.Colour.LIGHT_GREEN); // 设置单元格的背景颜色 wcf_title1.setAlignment(jxl.format.Alignment.CENTRE); // 设置对齐方式 wcf_title1.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN,jxl.format.Colour.BLACK); //设置边框 WritableSheet onlineSheet = null; //在线或离线 if(isOnline){ onlineSheet = wbook.createSheet("在线监控点占比" ,0); }else{ onlineSheet = wbook.createSheet("离线监控点占比" ,1); } WritableCellFormat cellFormat = new WritableCellFormat(); cellFormat.setWrap(true); //生成第一行 onlineSheet.addCell(new Label(0,0,"门店",wcf_title1)); onlineSheet.addCell(new Label(1,0,"监控总数",wcf_title1)); onlineSheet.addCell(new Label(2,0,isOnline?"在线数":"离线数",wcf_title1)); onlineSheet.addCell(new Label(3,0,isOnline?"在线率":"离线率",wcf_title1)); onlineSheet.setColumnView(0, 15); // 设置列的宽度 onlineSheet.setColumnView(1, 15); // 设置列的宽度 onlineSheet.setColumnView(2, 15); // 设置列的宽度 onlineSheet.setColumnView(3, 15); // 设置列的宽度 DecimalFormat df1 = new DecimalFormat("##.0%"); //生成其他行 if(CollectionUtils.isNotEmpty(onlineRateDtos)){ for(int i =0;i<onlineRateDtos.size();i++){ onlineSheet.addCell(new Label(0,i+1,onlineRateDtos.get(i).getStore_name(),cellFormat)); onlineSheet.addCell(new Label(1,i+1,String.valueOf(onlineRateDtos.get(i).getTotal_num()),cellFormat)); onlineSheet.addCell(new Label(2,i+1,String.valueOf(onlineRateDtos.get(i).getOnline_Num()),cellFormat)); onlineSheet.addCell(new Label(3,i+1,String.valueOf(df1.format(onlineRateDtos.get(i).getOnline_rate())),cellFormat)); } } }
import java.io.File; import java.io.FileInputStream; import java.io.IOException; import java.io.InputStream; import java.io.OutputStream; import java.io.PushbackInputStream; import java.text.SimpleDateFormat; import java.util.Date; import javax.servlet.http.HttpServletResponse; import org.apache.poi.POIXMLDocument; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.openxml4j.exceptions.InvalidFormatException; import org.apache.poi.openxml4j.exceptions.OpenXML4JException; import org.apache.poi.poifs.filesystem.POIFSFileSystem; import org.apache.poi.ss.usermodel.Workbook; import org.apache.poi.ss.usermodel.WorkbookFactory; import org.apache.poi.xssf.usermodel.XSSFSheet; import com.hikvision.cms.rmp.common.util.RLogUtils; public class ExcelUtils { /** * @description 取得Excel输出流 * @author chenzhiqin 2013-7-19 上午11:51:38 * @param response * @param fileType * @return OutputStream */ public static OutputStream getOutputStreamForExcelExport(HttpServletResponse response, String fileType) { if (response == null) { return null; } OutputStream os = null; // HttpServletResponse response = ServletActionContext.getResponse(); try { os = response.getOutputStream(); } catch (IOException e) { RLogUtils.logException(e); } response.reset(); SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH_mm_ss"); String dateString = sdf.format(new Date()); response.setHeader("Content-disposition", "attachment; filename=\"" + fileType + dateString + ".xls\""); response.setContentType("application/msexcel"); return os; } /** * 报表专用 * @author wanghuaming 2015年8月21日 下午3:57:06 * @param response * @param fileName * @return * @modificationHistory=========================逻辑或功能性重大变更记录 * @modify by user: {修改人} 2015年8月21日 * @modify by reason:{方法名}:{原因} */ public static OutputStream getOutputStreamForReportExcelExport(HttpServletResponse response, String fileName) { if (response == null) { return null; } OutputStream os = null; try { os = response.getOutputStream(); fileName = new String(fileName.getBytes("GB2312"), "ISO8859-1"); response.addHeader("Content-Disposition", "attachment;filename=" + fileName + ".xls"); response.setCharacterEncoding("utf-8"); response.setContentType("application/octet-stream"); } catch (IOException e) { RLogUtils.logException(e); } return os; } /** * @description 针对2007版本excel文件，取得OOXML格式的Excel输出流 * @author fanyingru 2014-7-19 上午11:51:38 * @param response * @param fileType * @return OutputStream */ public static OutputStream getOutputStreamForOOXMLExcelExport(HttpServletResponse response, String fileType) { if (response == null) { return null; } OutputStream os = null; try { os = response.getOutputStream(); } catch (IOException e) { RLogUtils.logException(e); } response.reset(); SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH_mm_ss"); String dateString = sdf.format(new Date()); response.setHeader("Content-disposition", "attachment; filename=\"" + fileType + dateString + ".xlsx\""); response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"); return os; } /** * 返回excel的版本类型，其中： 97-2003版本："application/vnd.ms-excel" 2007版本："application/vnd.openxmlformats-officedocument.spreadsheetml.sheet" * @param in * @return * @throws IOException * @throws OpenXML4JException */ public static String getContentType(InputStream in) throws IOException { // InputStream in = new FileInputStream(excel); String contentType = null; if (!in.markSupported()) { in = new PushbackInputStream(in, 8); } if (POIFSFileSystem.hasPOIFSHeader(in)) { contentType = "application/vnd.ms-excel"; } else if (POIXMLDocument.hasOOXMLHeader(in)) { contentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"; } if (in != null) { try { in.close(); } catch (IOException e) { RLogUtils.logException(e); } } return contentType; } /** * 返回excel的版本类型，其中： 97-2003版本："application/vnd.ms-excel" 2007版本："application/vnd.openxmlformats-officedocument.spreadsheetml.sheet" * @author fanyingru 2014-7-19 * @param excel * @return * @throws IOException * @throws OpenXML4JException */ public static String getContentType(File excel) throws IOException { InputStream in = new FileInputStream(excel); String contentType = null; if (!in.markSupported()) { in = new PushbackInputStream(in, 8); } if (POIFSFileSystem.hasPOIFSHeader(in)) { contentType = "application/vnd.ms-excel"; } else if (POIXMLDocument.hasOOXMLHeader(in)) { contentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"; } if (in != null) { try { in.close(); } catch (IOException e) { RLogUtils.logException(e); } } return contentType; } /** * 判断excel文件内容是否为空 * @author zhangguanghui 2014年9月15日 下午4:01:32 * @param in excel文件文件流 * @return true:excel文件内容为空 false:excel文件内容不为空 */ public static boolean isNull(InputStream in) throws Exception { // InputStream in=null; boolean isNull = true; try { // in = new FileInputStream(excelFile); if (!in.markSupported()) { in = new PushbackInputStream(in, 8); } if (POIFSFileSystem.hasPOIFSHeader(in)) { // 进excel03的处理函数 isNull = isNullFor03(in); } else if (POIXMLDocument.hasOOXMLHeader(in)) { // 进excel07的处理函数 isNull = isNullFor07(in); } } catch (Exception e) { throw e; } finally { if (in != null) { try { in.close(); } catch (IOException e) { throw e; } } } return isNull; } /** * 判断excel文件内容是否为空 * @author zhangguanghui 2014年9月15日 下午4:01:32 * @param excelFile excel文件 * @return true:excel文件内容为空 false:excel文件内容不为空 * @throws Exception */ public static boolean isNull(File excelFile) throws Exception { InputStream in = null; boolean isNull = true; try { in = new FileInputStream(excelFile); if (!in.markSupported()) { in = new PushbackInputStream(in, 8); } if (POIFSFileSystem.hasPOIFSHeader(in)) { // 进excel03的处理函数 isNull = isNullFor03(in); } else if (POIXMLDocument.hasOOXMLHeader(in)) { // 进excel07的处理函数 isNull = isNullFor07(in); } } catch (Exception e) { throw e; } finally { if (in != null) { try { in.close(); } catch (IOException e) { RLogUtils.logException(e); } } } return isNull; } /** * 判断03版本的excel文件内容是否为空 * @author zhangguanghui 2014年9月15日 下午4:44:57 * @param in * @return true:空 false:不为空 * @throws IOException */ public static boolean isNullFor03(InputStream in) throws IOException { HSSFWorkbook workbook; workbook = new HSSFWorkbook(in); HSSFSheet st = workbook.getSheetAt(0); if (st.iterator().hasNext()) { return false; } return true; } /** * 判断07版本的excel文件内容是否为空 * @author zhangguanghui 2014年9月15日 下午4:00:59 * @param in * @return true:空 false:不为空 * @throws IOException * @throws InvalidFormatException */ public static boolean isNullFor07(InputStream in) throws InvalidFormatException, IOException { Workbook workbook; workbook = WorkbookFactory.create(in); XSSFSheet st = (XSSFSheet)workbook.getSheetAt(0); if (st.iterator().hasNext()) { return false; } return true; } }
366
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
