java中excel百万级数据导出（服务器cpu和内存的稳定，数据库的cpu的稳定）-526互联

@Slf4j
public abstract class ExcelUtil {

    /**
     * 导出
     * @param response
     * @param total
     * @param sheetCount
     * @param size
     * @param excelName
     */
    public void exportExcel(HttpServletResponse response, int total, int sheetCount, int size, String excelName, String excelSecret) {
        ServletOutputStream outputStream = null;
        try {
            Integer sheetNum = NumberUtil.ceilDiv(total, sheetCount);

            //必须放到循环外，否则会刷新流
            outputStream = response.getOutputStream();
            ExcelWriter excelWriter = EasyExcel.write(outputStream).build();
            if (StringUtils.isNotBlank(excelSecret)) {
                excelWriter = EasyExcel.write(outputStream).password(excelSecret).build();
            }
            //开始分批查询分次写入
            Long id = 0L;
            for (int i = 0; i < sheetNum; i++) {
                //创建Sheet
                WriteSheet sheet = new WriteSheet();
                sheet.setSheetName(excelName + i);
                sheet.setSheetNo(i);
                Integer whileCount = NumberUtil.ceilDiv(sheetCount, size);
                for (int m = 0; m< whileCount; m++) {
                    List list = getSubList(id, size);
                    if (CollectionUtils.isEmpty(list)) {
                        break;
                    }
                    Object lastObject = list.get(list.size() - 1);
                    id = ObjectUtil.getIdByObject(lastObject);
                    if (null == id) {
                        break;
                    }
                    WriteSheet writeSheet = EasyExcel.writerSheet(i, sheet.getSheetName())
                            .head(lastObject.getClass())
                            .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
                            .build();
                    excelWriter.write(list, writeSheet);
                    list.clear();
                }
            }
            // 下载EXCEL，返回给前段stream流
            response.setContentType("application/octet-stream");
            response.setCharacterEncoding("utf-8");
            response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + URLEncoder.encode(excelName, "UTF-8")
            + ".xlsx");
            excelWriter.finish();
            log.info("===export=end==");
        } catch (Exception e) {
            log.error("exportExcel=export=is=error={}", e);
        } finally {
            IoUtil.close(outputStream);
        }
    }

    /**
     * @param id
     * @return
     */
    public abstract List getSubList(Long id, int size);

}