导出Excel文件

导出Excel工具类

public static<T> void export(HttpServletResponse response, String fileName, String[] headerNames, String[] headerKeys, List<T> list) {
   // 清空response
   response.reset();
   // 设置response的Header
   response.addHeader("Content-Disposition", "attachment;filename="
         + encodingFileName(fileName));
   response.setContentType("application/octet-stream");
   OutputStream out = null;
   try {
      out = response.getOutputStream();
      Workbook workbook = null;
      if(fileName.endsWith(".xls")){
          // 声明一个工作薄
         workbook = new HSSFWorkbook();
      }else {
         workbook = new SXSSFWorkbook(10000);
      }
      
        // 生成一个表格
      Sheet sheet = workbook.createSheet("Sheet1");
        // 设置表格默认列宽度为18个字节
        sheet.setDefaultColumnWidth(18);
       
        Row row = sheet.createRow(0);
        for(int colIndex = 0; colIndex < headerNames.length; colIndex++){
           Cell cell = row.createCell(colIndex);
           cell.setCellValue(headerNames[colIndex]);
        }
        for (int rowIndex = 0; rowIndex < list.size() ; rowIndex++) {
         row = sheet.createRow(rowIndex + 1);
         T t = list.get(rowIndex);
         Reflector reflector = Reflector.forClass(t.getClass());
         
         for(int colIndex = 0; colIndex < headerKeys.length; colIndex++){
              Cell cell = row.createCell(colIndex);
            Invoker invoker = reflector.getGetInvoker(headerKeys[colIndex]);
            Object fieldValue = invoker.invoke(t, new Object[] {});
            
            if(fieldValue == null) {
               continue;
            }
            Object type = invoker.getType();
            if (type.equals(BigDecimal.class)) {      
               cell.setCellValue(((BigDecimal)fieldValue).doubleValue());
            } else if (type.equals(Integer.class)) {
               cell.setCellValue((Integer)fieldValue);
            }else if (type.equals(Long.class)) {
               cell.setCellValue(((Long)fieldValue).toString());
            } else if (type.equals(Double.class)) {
               cell.setCellValue(((Double)fieldValue).doubleValue());
            } else if (type.equals(Date.class)) {
               cell.setCellValue(DateUtils.format((Date)fieldValue));
            }else {
               cell.setCellValue((String)fieldValue);
            }
              
           }        
      }        
        workbook.write(out);
   } catch (Exception e) {
      e.printStackTrace();
   }finally{
      IOUtils.closeQuietly(out);
       }
}

实际使用

this.interviewListFileExport(request,
                response, 
                params,"面签列表.xlsx",
                new String[]{"贷款编号","客户姓名","证件类型","证件号码","合作银行","贷款金额","期数","汽车类型","汽车品牌+车系+车型","面签方式","生成时间", "客户经理"},
                new String[]{"projectNo","customerName","cardType","cardNo","bankName","loanAmount","loanTerm","carType","completeCarInfo","interviewType","createTime","realname"});

 /**
     * 针对不同数量的记录做数据导出处理
     * @author xuesk
     * @date 2018-11-12
     * @param request
     * @param response
     * @param fileName Excel文件名
     * @param params 查询参数
     * @param headerNames 文档列名
     * @param headerKeys 属性名,用于获取数据
     */
private void interviewListFileExport(HttpServletRequest request, HttpServletResponse response, HashMap params, String fileName, String[] headerNames, String[] headerKeys) {
        //由于导出记录的数量未知 因此采用分批次查询的方法
        //初始化 当前批次为1 每批次查询数量为30000
        int currentBatch = 1;
        int batchSize = 30000;
        //通过分页查询来实现分批次读写流
        Paging paging = new Paging();
        paging.setCurrentPage(currentBatch);
        paging.setPageSize(batchSize);
        //清空response
        response.reset();
        //设置response的Header
        response.addHeader("Content-Disposition", "attachment;filename="
                + ExcelUtil.encodingFileName(fileName));
        response.setContentType("application/octet-stream");
        OutputStream out = null;
        //文件读写
        try {
            out = response.getOutputStream();
            //声明一个工作簿 内存中只创建5000个对象，写临时文件，当超过5000条，就将内存中不用的对象释放。
            SXSSFWorkbook workbook = new SXSSFWorkbook(5000);
            //生成一个表格
            Sheet sheet = workbook.createSheet("Sheet1");
            //设置表格默认列宽度为18个字节
            sheet.setDefaultColumnWidth(18);
            //第一行写标题
            Row row = sheet.createRow(0);
            for (int colIndex = 0; colIndex < headerNames.length; colIndex++) {
                Cell cell = row.createCell(colIndex);
                cell.setCellValue(headerNames[colIndex]);
            }
            //循环标志位 默认为true
            boolean flag = true;
            //当前数据记录行数游标
            int currentRowIndex = 1;
            while (flag) {
                logger.info("视频面签管理-面签记录列表导出：写入数据开始");
                List<Map> interviewList = imVideoInterviewService.selectCompleteAssociateInterviewInfo(params, paging);
                if (CollectionUtils.isEmpty(interviewList)) {
                    //如果列表为空 终止循环
                    flag = false;
                } else {
                    for (int recordIndex = 0; recordIndex < interviewList.size(); recordIndex++) {
                        row = sheet.createRow(currentRowIndex);
                        Map interviewInfo = interviewList.get(recordIndex);
                        //将枚举值转化为中文
                        this.convertEnumValueToChinese(interviewInfo);
                        for (int colIndex = 0; colIndex < headerKeys.length; colIndex++) {
                            Cell cell = row.createCell(colIndex);
                            Object fieldValue = interviewInfo.get(headerKeys[colIndex]);
                            if (fieldValue == null) {
                                continue;
                            }
                            Class<?> clazz = fieldValue.getClass();
                            if (clazz.equals(BigDecimal.class)) {
                                cell.setCellValue(((BigDecimal) fieldValue).doubleValue());
                            } else if (clazz.equals(Integer.class)) {
                                cell.setCellValue((Integer) fieldValue);
                            } else if (clazz.equals(Long.class)) {
                                cell.setCellValue(((Long) fieldValue));
                            } else if (clazz.equals(Double.class)) {
                                cell.setCellValue(((Double) fieldValue));
                            } else if (clazz.equals(Date.class)) {
                                cell.setCellValue(DateUtils.format((Date) fieldValue, "yyyy-MM-dd"));
                            } else if (clazz.equals(Timestamp.class)) {
                                cell.setCellValue(fieldValue.toString());
                            }else {
                                cell.setCellValue((String) fieldValue);
                            }
                        }
                        //游标自增
                        currentRowIndex++;
                    }
                    //判断是否要进行下一次循环
                    if (interviewList.size() < batchSize) {
                        //如果列表大小小于每批次查询数量 说明当前已经是符合筛选条件的记录的最后一页 终止循环
                        flag = false;
                    } else {
                        //批次数量自增
                        paging.setCurrentPage(++currentBatch);
                    }
                    interviewList.clear();
                }
            }
            workbook.write(out);
            out.flush();
            logger.info("视频面签管理-面签记录列表导出：写入数据结束");
        } catch (Exception e) {
            e.printStackTrace();
            logger.error("视频面签管理-面签记录列表导出：输出流读写出错" + e.getMessage());
        } finally {
            IOUtils.closeQuietly(out);
        }
    }