poi分步导出（将导出数据与下载分离）

**

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实现层
@Override
public 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();
              }
      }
    }