poi分步导出(将导出数据与下载分离)
来源:互联网 发布:如何清空手机数据 编辑:程序博客网 时间:2024/06/05 03:37
**
poi分步导出(将导出数据与下载分离)
在利用poi进行导出数据时会遇到因数据量太大而导出时间较长的情况,这时候就需要预约导出,即把导出的表存到数据库,待导出成功后随时可以下载,这样做相当于对导出做了异步处理,当数据量太大导致导出时间过长时用户点击导出后不用等待导出完成,就可以在系统中进行其他操作,且用户在导出同一张表时也可以直接下载上次导出的数据表,不需反复进行导出操作。
前端页面点击“生成Excel”给出正在导出提示到后台执行导出操作,若上次导出未完成也会给出相应提示,导出成功后的文件名将显示在右方,点击文件名即可进行下载。
代码块
//controller层导出方法@RequestMapping(value="navigationLossData.do",params="exportExcelFile") @ResponseBody public Map<String,Object> exportExcelFile(HttpServletRequest request,SessionQuery sq) throws IOException, ParseException{ String userId=this.getLoginUser(request).getId(); int pageIndex = this.getLigerUIPageIndex(request); int pageSize = this.getLigerUIPageSize(request); Map<String, Object> paramMap = this.queryParam(request); paramMap.put("exportExcel","exportExcel"); Map<String,Object> resultMap=new HashMap<String, Object>(); //在逻辑层中处理 resultMap=cscExportLogService.exportNavigationLossData(userId,paramMap,pageIndex,pageSize); return resultMap; }
//service实现层@Overridepublic Map<String, Object> exportNavigationLossData(String userId,Map<String, Object> paramMap, int pageIndex, int pageSize) { //根据用户id向数据库中存入一张空表 OcspCscExportLog ocsp=this.insertNewLog(userId,"navigationLossData"); //获取要导出的数据 PagedResult<OcspCscLossData> pr=this.cscSessionService.queryNavigationLossData(paramMap, pageIndex, pageSize); //导出数据集合 List<OcspCscLossData> lossDataList =pr.getData(); //用于导出到Excel的集合 List<List<String>> lossDataCollection=this.queryNavigationLossCollection(paramMap,lossDataList); Map<String,Object> resultMap=new HashMap<String, Object>(); try { //导出类型,决定Excel列宽 Map<String,Object> excelSign=new HashMap<String, Object>(); excelSign.put("navigationLoss", "navigationLoss"); ocsp=ExcelUtils.exportLossDataExcel(lossDataCollection, ocsp,excelSign); resultMap.put("success", true); } catch (Exception e) { log.info("流导入库错误原因"+e.getMessage()); ocsp.setStatus("2"); resultMap.put("success", false); }finally{ this.updateData(ocsp); resultMap.put("fileTime", ocsp.getExportTime()); } return resultMap; }
//导出工具类方法public static OcspCscExportLog exportLossDataExcel( List<List<String>> collection, OcspCscExportLog ocsp,Map<String,Object> excelSign) { // 创建excel工作簿 HSSFWorkbook wb = new HSSFWorkbook(); String fileName=ocsp.getFileName(); HSSFSheet sheet = wb.createSheet(fileName); sheet.setDefaultColumnWidth(17); //表头字体 Font f = wb.createFont(); f.setFontHeightInPoints((short) 20); f.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); f.setColor(IndexedColors.BLACK.getIndex()); f.setFontName("微软雅黑"); //标题行字体 Font f1 = wb.createFont(); f1.setFontHeightInPoints((short) 12); f1.setColor(IndexedColors.BLACK.getIndex()); f1.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); f1.setFontName("微软雅黑"); HSSFCellStyle cs6 = wb.createCellStyle(); //内容字体 Font f2 = wb.createFont(); f2.setFontHeightInPoints((short)12); f2.setColor(IndexedColors.BLACK.getIndex()); f2.setFontName("微软雅黑"); cs6.setFont(f2); //单元格样式 HSSFCellStyle cs= wb.createCellStyle(); cs.setFont(f); cs.setAlignment(HSSFCellStyle.ALIGN_CENTER); HSSFCellStyle cs1= wb.createCellStyle(); cs1.setFont(f1); cs1.setAlignment(HSSFCellStyle.ALIGN_CENTER); HSSFCellStyle cs2= wb.createCellStyle(); cs2.setFont(f2); cs2.setAlignment(HSSFCellStyle.ALIGN_CENTER); //创建表头 HSSFRow row1= sheet.createRow((short) 0); List<String> rs1=collection.get(0); //添加列 HSSFCell cell1 =row1.createCell(0); cell1.setCellValue(rs1.get(0)); cell1.setCellStyle(cs); //创建标题行 HSSFRow row2= sheet.createRow((short) 1); List<String> rs2=collection.get(1); int len1=len1=rs2.size(); for(int i=0; i<len1; i++){ HSSFCell cell =row2.createCell(i); cell.setCellValue(rs2.get(i)); cell.setCellStyle(cs1); } Region region = new Region(0, (short) 0, 0, (short) (len1-1)); sheet.addMergedRegion(region); //添加内容 int len=collection.size(); for(int j=2;j<len; j++){ //添加行 HSSFRow row = sheet.createRow((short) j); List<String> rs=collection.get(j); for (int k=0,len2=rs.size();k<len2; k++) { HSSFCell cell =row.createCell(k); String value=rs.get(k); if(value==null||"".equals(value)){ cell.setCellValue(" "); }else{ cell.setCellValue(value); } if(value!=null&&value.equals("总计:")){ cell.setCellStyle(cs1); }else{ cell.setCellStyle(cs2); } } } //判断流失数据类型 if(excelSign.containsKey("navigationLoss")){ sheet.setColumnWidth(0, 40 * 256); sheet.setColumnWidth(1, 25 * 256); sheet.setColumnWidth(6, 18 * 256); sheet.setColumnWidth(8, 30 * 256); sheet.setColumnWidth(9, 30 * 256); sheet.setColumnWidth(10, 30 * 256); }else{ sheet.setColumnWidth(0, 25 * 256); sheet.setColumnWidth(1, 50 * 256); sheet.setColumnWidth(6, 18 * 256); sheet.setColumnWidth(7, 18 * 256); sheet.setColumnWidth(8, 19 * 256); sheet.setColumnWidth(10, 30 * 256); sheet.setColumnWidth(11, 30 * 256); sheet.setColumnWidth(12, 30 * 256); sheet.setColumnWidth(13, 30 * 256); } ByteArrayOutputStream os = new ByteArrayOutputStream(); try { wb.write(os); } catch (IOException e) { e.printStackTrace(); } byte[] content = os.toByteArray(); ocsp.setFileContent(content); ocsp.setStatus("2"); return ocsp; }
//controller下载方法@RequestMapping(value="navigationLossData.do",params="downloadExcelFile") @ResponseBody public void downloadExcelFile(HttpServletRequest request,HttpServletResponse response) throws IOException{ String userId=this.getLoginUser(request).getId(); OcspCscExportLog ocsp=cscExportLogService.getNewestExport("navigationLossData",userId); try { ExcelUtils.downloadExcel(ocsp, response); } catch (Exception e) { System.out.println("下载出错"+e.getMessage()); e.printStackTrace(); } }
//工具类下载方法public static void downloadExcel( OcspCscExportLog ocspCscExportLog, HttpServletResponse response){ String fileName=ocspCscExportLog.getFileName(); InputStream is = new ByteArrayInputStream(ocspCscExportLog.getFileContent()); BufferedInputStream bis = null; BufferedOutputStream bos = null; try { // 设置response参数,可以打开下载页面 response.reset(); response.setContentType("application/x-xls ; charset=UTF-8"); response.setHeader("Content-Disposition", "attachment;filename="+URLEncoder.encode(FileOperateUtils.checkFileName(fileName), "UTF-8")); ServletOutputStream out = response.getOutputStream(); bis = new BufferedInputStream(is); bos = new BufferedOutputStream(out); byte[] buff = new byte[2048]; int bytesRead; while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) { bos.write(buff, 0, bytesRead); } } catch (final Exception e) { e.printStackTrace(); } finally { try { if (bis != null) bis.close(); if (bos != null) bos.close(); } catch (Exception e) { e.printStackTrace(); } } }
阅读全文
0 0
- poi分步导出(将导出数据与下载分离)
- poi方式将数据导出到execl并提供下载
- poi方式将数据导出到execl并提供下载
- poi将数据库数据导出excel文件
- 使用POI将数据导入导出数据库。
- 使用poi将数据导出到excel
- POI导出下载
- POI EXCEL 数据导出
- poi导出excel数据
- POI批量导出数据
- POI-数据导入导出
- POI-数据导入导出
- POI 大数据导出
- poi导出数据
- POI导出excle数据
- poi的导出excel(直接下载)
- 使用Apache的POI,将数据导出至EXCEL
- POI 将数据导出到Word的实例
- Mysql:优化数据库的8个方法
- LeetCode 598. Range Addition II
- 使用 PHPExcel 读取\修改 xls文件
- servlet请求转发与重定向的区别
- ORACLE分页查询SQL语法——最高效的分页
- poi分步导出(将导出数据与下载分离)
- hdu3584(三维树状数组,区间修改,点查询)
- 文章标题
- Java中Filter、Listener,拦截器的学习
- USACO-Section2.2 Party Lamps【深度优先搜索】
- 3——FFMPEG之解复用器-----AVInputFormat
- Qt开发: 添加程序图标ico -【图文并茂】
- 20170725使用linux的GDB打印STL(vector,map,set..................)
- JavaWeb做导出功能时直接跳转新页面并显示乱码?