分页导出，防止内存溢出

分页导出，防止内存溢出

1.定义模板方法类

/**
 * 分页导出模板
 * <p>
 * T 为查询参数 需要有 PageNo 和 PageSize
 * R 为查询结果，导出的对象
 */
public abstract class PagingExportTemplate<T extends BasicQueryEntity, R> {


    protected static final Logger log = LoggerFactory.getLogger(PagingExportTemplate.class);


    /**
     * 处理查询参数的回调方法（例如校验，解析）
     * @param param
     */
    protected abstract void handleParam(T param);

    /**
     * 处理总条数的回调方法（例如最大数据限制）
     * @param total
     */
    protected abstract void handleTotal(long total,HttpServletResponse response);

    /**
     * 获取查询总数
     *
     * @param param
     * @return
     */
    protected abstract long getTotal(T param);


    /**
     * 获取一页的数据
     *
     * @param param
     * @return
     */
    protected abstract List<R> getPageData(T param);


    /**
     * 分页导出
     *
     * @param param                   查询参数
     * @param pageSize                每页的数量
     * @param response
     * @param fileName                文件名
     * @param includeColumnFiledNames 导出对象的表头字段
     * @param clazz                   导出对象类
     * @param strategy                导出策略
     */
    public void pagingExport(T param, Integer pageSize, HttpServletResponse response,
                             String fileName, Set<String> includeColumnFiledNames, Class clazz, HorizontalCellStyleStrategy strategy) {
        //处理查询参数的回调方法（例如校验，解析）
        handleParam(param);

        long total = getTotal(param);
        log.info("导出的数量为：{}", total);
        //处理总条数的回调方法（例如最大数据限制）
        handleTotal(total,response);

        //分页导出的总页数
        long pageNum = total % pageSize==0? (total/pageSize) : (total/pageSize)+1;

        param.setPageNo(1);
        param.setPageSize(pageSize);

        ExcelWriter excelWriter = null;
        try {
            response.setContentType("application/octet-stream;charset=UTF-8");
            response.setHeader("Content-Disposition", "attachment;filename*=utf-8'zh_cn'" + URLEncoder.encode(fileName, "UTF-8"));
            ServletOutputStream outputStream = response.getOutputStream();
            excelWriter = EasyExcel.write(outputStream, clazz)
                    .registerWriteHandler(strategy)
                    .includeColumnFiledNames(includeColumnFiledNames)
                    .build();
            WriteSheet writeSheet = EasyExcel.writerSheet(fileName).build();

            while (true) {
                log.info("开始查询第{}页：", param.getPageNo());
                List<R> dto = getPageData(param);
                log.info("完成查询第{}页：", param.getPageNo());
                param.setPageNo(param.getPageNo() + 1);
                excelWriter.write(dto, writeSheet);
                if (param.getPageNo() > pageNum ) {
                    break;
                }
            }
        } catch (Exception e) {
            log.error("导出失败：", e);
        } finally {
            if (excelWriter != null) {
                try {
                    excelWriter.finish();
                } catch (Exception e) {
                    log.error("关闭导出流失败：", e);
                }
            }
        }
    }
}

2.具体实现

@Component
public class ExamplePagingExport extends PagingExportTemplate<ExampleQuery, ExampleDTO> {

    @Resource
    ExampleService exampleService;

    @Override
    protected void handleParam(ExampleQuery param) {
        exampleService.handleQuery(param);
    }

    @Override
    protected void handleTotal(long total, HttpServletResponse response) {
        log.info("导出的数量为：{}", total);
    }

    @Override
    protected long getTotal(ExampleQuery param) {
        return exampleService.getCount(param);
    }

    @Override
    protected List<ExampleDTO> getPageData(ExampleQuery param) {
        return exampleService.listData(param);
    }

}

3.Service层调用

@Service
public class ExampleServiceImpl {
    @Resource
    ExamplePagingExport pagingExport;


    public void exportByCondition(ExampleQuery query, HttpServletResponse response) {
        String fileName = "文件名"+".xlsx";
        Set<String> includeColumnFiledNames = getIncludeColumnFiledNames();
        HorizontalCellStyleStrategy strategy = getCellStyleStrategy(false);
        Class<ExampleDTO> clazz = ExampleDTO.class;

        pagingExport.pagingExport(query,10000, response,fileName,includeColumnFiledNames, clazz,strategy);
    }


    private static Set<String> getIncludeColumnFiledNames() {
        Set<String> includeColumnFiledNames = new HashSet<String>();
        includeColumnFiledNames.add("filedA");
        includeColumnFiledNames.add("filedB");
        return includeColumnFiledNames;
    }

    private static HorizontalCellStyleStrategy getCellStyleStrategy(boolean isSchedule) {
        // 3、合成样式策略
        return new HorizontalCellStyleStrategy(getHeadWriteCellStyle(), getContentWriteCellStyle(isSchedule));
    }

    private static WriteCellStyle getHeadWriteCellStyle() {
        // 1、头的策略
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        // 设置头背景色
        headWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
        // 设置字体
        WriteFont headWriteFont = new WriteFont();
        headWriteFont.setFontHeightInPoints((short) 11);
        headWriteFont.setBold(true);
        headWriteCellStyle.setWriteFont(headWriteFont);
        // 设置边框
        headWriteCellStyle.setBorderTop(BorderStyle.THIN);
        headWriteCellStyle.setBorderLeft(BorderStyle.THIN);
        headWriteCellStyle.setBorderRight(BorderStyle.THIN);
        headWriteCellStyle.setBorderBottom(BorderStyle.THIN);
        // 设置内容位置
        headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        headWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        return headWriteCellStyle;
    }

    private static WriteCellStyle getContentWriteCellStyle(boolean isSchedule) {
        // 2、内容的策略
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        // 这里需要指定 FillPatternType 为FillPatternType.SOLID_FOREGROUND 不然无法显示背景颜色。头默认了 FillPatternType所以可以不指定
        contentWriteCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
        // 设置内容背景色
        contentWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
        // 设置字体
        WriteFont contentWriteFont = new WriteFont();
        if (isSchedule) {
            contentWriteFont.setFontHeightInPoints((short) 11);
            contentWriteFont.setBold(true);
        } else {
            contentWriteFont.setFontHeightInPoints((short) 10);
        }
        contentWriteCellStyle.setWriteFont(contentWriteFont);
        // 设置边框
        contentWriteCellStyle.setBorderTop(BorderStyle.THIN);
        contentWriteCellStyle.setBorderLeft(BorderStyle.THIN);
        contentWriteCellStyle.setBorderRight(BorderStyle.THIN);
        contentWriteCellStyle.setBorderBottom(BorderStyle.THIN);
        // 设置内容位置
        contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);

        //设置单元格为文本格式
        HSSFWorkbook demoWorkBook = new HSSFWorkbook();
        HSSFDataFormat format = demoWorkBook.createDataFormat();
        contentWriteCellStyle.setDataFormat(format.getFormat("@"));
        return contentWriteCellStyle;
    }
}