Java导出大量数据到Excel解决方案

ResultHandler流式查询导出Excel数据

// 导出Excel头部名称
List<String> headerArray = Arrays.asList("编码","姓名");
// 数据库对应字段
List<String> fieldArray = Arrays.asList("id", "name");
// 定义Excel文件名，不带“xlsx”后缀。
String fileName = getToday()+"导出数据-共计("+count+")"+"条";
//每次导出New 一个 handler 对象。将headerArray,fieldArray,fileName 传入参数。
ExcelResultHandler<查询对象> handler = new ExcelResultHandler<查询对象>(headerArray,fieldArray,fileName) {
            public void tryFetchDataAndWriteToExcel() {
                // this 指的是 New 对象，可添加查询 条件参数。
        		// mapper的方法需要是void返回，并且参数中含ResultHandler(流式查询遍历的条件)，可以加条件参数，
                // fetchSize参数必填。
                //见下图
                //<select id="export" parameterType="" resultType="" fetchSize="-2147483648">
                ***Mapper.export(this);
            }
        };
//开始调用方法。原理：调用开始后，表头写入Excel，遍历结果集，一条一条数据写入Excel，最后关闭资源。
handler.startExportExcel();

源码会判断方法返回值是否void,是否含有ResultHandler

ExcelResultHandler工具类：

按需修改

public abstract class ExcelResultHandler<T> implements ResultHandler<T>{
   
   private final Logger logger = LoggerFactory.getLogger(this.getClass());
   private AtomicInteger currentRowNumber = new AtomicInteger(0);
   private Sheet sheet = null;
   private List<String> headerArray ;
   private List<String> fieldArray ;
   private int totalCellNumber;
   private boolean isExportZip = true;
   private SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
   private String exportFileName = UUID.randomUUID().toString().replace("-", "");
   public ExcelResultHandler(List<String> headerArray,List<String> fieldArray){
      this.headerArray = headerArray;
      this.fieldArray = fieldArray;
      this.totalCellNumber = headerArray.size();
   }
   public ExcelResultHandler(List<String> headerArray,List<String> fieldArray,boolean isExportZip){
      this(headerArray,fieldArray);
      this.isExportZip = isExportZip;

   }
   public ExcelResultHandler(List<String> headerArray,List<String> fieldArray,String exportFileName){
      this(headerArray,fieldArray);
      this.exportFileName = exportFileName;
   }
   public ExcelResultHandler(List<String> headerArray,List<String> fieldArray,String exportFileName,boolean isExportZip){
      this(headerArray,fieldArray,exportFileName);
      this.isExportZip = isExportZip;
   }
   public abstract void tryFetchDataAndWriteToExcel();
   public void handleResult(ResultContext<? extends T> resultContext) {
      Object aRowData = resultContext.getResultObject();
      callBackWriteRowdataToExcel(aRowData);
      
   }
   public void startExportExcel() {
      HttpServletResponse response = ((ServletRequestAttributes) RequestContextHolder.getRequestAttributes()).getResponse();
      ZipOutputStream zos = null;
      OutputStream os = null;
      try {
         logger.info("--------->>>>写入Excel开始.." );
         //写入文件
         response.setContentType("application/octet-stream");
         response.setHeader("Content-Disposition", "attachment;filename=" + new String((exportFileName+".zip").replaceAll(" ", "").getBytes("utf-8"),"iso8859-1"));
         os = new BufferedOutputStream(response.getOutputStream());
         if(isExportZip){
            zos = new ZipOutputStream(os);
            ZipEntry zipEntry = new ZipEntry(new String((exportFileName+".xlsx").replaceAll(" ", "")));
            zos.putNextEntry(zipEntry);
         }
         SXSSFWorkbook wb = new SXSSFWorkbook();
         wb.setCompressTempFiles(false);
            sheet = wb.createSheet("Sheet 1");
           Row row = sheet.createRow(0);
           for (int cellNumber = 0; cellNumber < totalCellNumber; cellNumber++) {
              Cell cell = row.createCell(cellNumber);
                cell.setCellValue(headerArray.get(cellNumber));
           }
           tryFetchDataAndWriteToExcel();
         logger.info("--------->>>> write to excel size now is {}", currentRowNumber.get() );
         if(isExportZip){
            wb.write(zos);
         }else{
            wb.write(os);
         }
           if (wb != null) {
               wb.dispose();// 删除临时文件，很重要，否则磁盘可能会被写满
            }
           wb.close();
         logger.info("--------->>>>全部数据写入Excel完成.." );
      } catch (Exception e) {
         logger.error("系统异常，请联系管理员",e);
      } finally {
         logger.info("--------->>>>关闭流系统.." );
         if(isExportZip){
            try {if(zos!=null) zos.close();} catch (IOException e1) {logger.error("关闭流系统异常，请联系管理员",e1);    }
         }else{
            try {if(os!=null) os.close();} catch (IOException e1) {logger.error("关闭流系统异常，请联系管理员",e1);  }
         }
            
      }
   }
   @SuppressWarnings("rawtypes")
   public void callBackWriteRowdataToExcel(Object aRowData) {
      MethodAccessor methodAccessor = Reflector.getMethodAccessor(aRowData.getClass());
      currentRowNumber.incrementAndGet();
      Row row = sheet.createRow(currentRowNumber.get());
      for (int cellNumber = 0; cellNumber < totalCellNumber; cellNumber++) {
         Object value = null;
         if(aRowData instanceof Map){
            value = ((Map)aRowData).get(fieldArray.get(cellNumber));
         }else {
            value = methodAccessor.getFieldValue(aRowData, fieldArray.get(cellNumber));
         }
         Cell cell = row.createCell(cellNumber);
            if (value!=null && value instanceof Date){
               cell.setCellValue(sdf.format(value));//
            }else {
               cell.setCellValue(value==null?"":value.toString());//写入数据
            }
      }
      if(currentRowNumber.get() % 5000 == 0 ){
         logger.info("--------->>>> write to excel size now is {}", currentRowNumber.get() );
      }
   }

}

因为无返回值，前端如何知道是否下载完成？

前端用下载监听器。

var index = layer.load(3, {
    shade: [0.1, '#fff'],
    content: '<span class="loadtip">下载中</span>',
    success: function (layer) {
        layer.find('.layui-layer-content').css({
            'padding-top': '30px',
            'width': '100px',
        });
        layer.find('.loadtip').css({
            'font-size':'18px',
            'margin-left':'5px'
        });
    }
});
const xhr = new XMLHttpRequest();
xhr.open('GET', '下载接口');
xhr.send(null);
// 设置服务端的响应类型
xhr.responseType = "blob";
// 监听下载
xhr.addEventListener('progress', event => {
    // 计算出百分比
    const percent  = ((event.loaded / event.total) * 100).toFixed(2);
    console.log(`下载进度：${percent}`);
}, false);
xhr.onreadystatechange = event => {
    if (xhr.readyState == 4) {
        if (xhr.status == 200) {
            // 获取ContentType
            const contentType = xhr.getResponseHeader('Content-Type');
            // 文件名称 如果想和后端一样，需要处理编码
            const fileName = "名称.zip";
            // 创建一个a标签用于下载
            const donwLoadLink = document.createElement('a');
            donwLoadLink.download = fileName;
            donwLoadLink.href = URL.createObjectURL(xhr.response);
            // 触发下载事件，IO到磁盘
            donwLoadLink.click();
            // 释放内存中的资源
            URL.revokeObjectURL(donwLoadLink.href);
            // 关闭加载动画
            layer.close(index);
        } else if (response.status == 404) {
            alert(`文件：${file} 不存在`);
        } else if (response.status == 500) {
            alert('系统异常');
        }
    }
}