easyExcel Web导出

使用:

   @Autowired
    private ExcelExport export;

    @PostMapping("/export")
    public void export(PageParam param,HttpServletResponse response) throws IOException {
        export.export(ExcelParam.init("测试导出",response), dataSourceParam -> {
            Pager<PageVo> page = service.queryPage(param);
            param.setPageNum(dataSourceParam.getNumber());
            param.setPageSize(dataSourceParam.getSize());
            //分页查出的vo实体转换为excel导出的实体
            return PageUtils.convert(page, PageExcel.class);
        });
    }

接口层:

public interface ExcelExport {

    <T> void export(ExcelParam excelParam, DataSource<T> dataSource);

}

接口层实现:
这里采用的是查出所有的数据,放入List中,然后全部导出,也可采用查出分页数据,分批次写入excel,这两种使用JMC测试内存占用,cpu占用情况基本相同
入参ExcelParam包含excel名称信息以及OutputStream,DataSource接口指定调用者实现查询分页数据的逻辑

后期,可修改为异步导出,可去除OutputStream入参,引入线程池,异步导出excel写入文件,写入完成上传文件至文件服务器,并返回url连接,url连接写入数据库表,实现异步excel导出功能

@Slf4j
@Component
public class ExcelExportImpl implements ExcelExport {


    /**
    *写法1
    */
    @Override
    public <T> void export(ExcelParam excelParam, DataSource<T> dataSource) {
        try {
            log.info("fileName:{} start", excelParam.getExcelName());
            List<T> res = new ArrayList<>();
            Class<?> clazz = null;
            int currentPageNum = 1;
            int totalPageNum;
            do {
                DataSourceParam dataSourceParam = new DataSourceParam(currentPageNum);
                Pager<T> page = dataSource.get(dataSourceParam);
                if (CollectionUtils.isNotEmpty(page.getRows())) {
                    clazz = page.getRows().get(0).getClass();
                    res.addAll(page.getRows());
                }

                //总页数
                totalPageNum = (page.getTotal() - 1) / dataSourceParam.getSize() + 1;
                currentPageNum++;
            } while (currentPageNum <= totalPageNum);
            EasyExcel.write(excelParam.getOutputStream(), clazz).sheet("sheet1").doWrite(res);
            log.info("fileName:{} end", excelParam.getExcelName());
        } catch (Exception e) {
            log.error("fileName:{} ==>exception:{}", excelParam.getExcelName(), e);
        }
    }

    /**
    *写法2
    */
    @Override
    public <T> void export2(ExcelParam excelParam, DataSource<T> dataSource) {
        log.info("fileName:{} start", excelParam.getExcelName());
        ExcelWriter excelWriter = EasyExcel.write(excelParam.getOutputStream(), excelParam.getClazz()).build();
        WriteSheet writeSheet = EasyExcel.writerSheet(1).build();
        try {
            int currentPageNum = 1;
            int totalPageNum;
            do {
                DataSourceParam dataSourceParam = new DataSourceParam(currentPageNum);
                Pager<T> page = dataSource.get(dataSourceParam);
                if (CollectionUtils.isNotEmpty(page.getRows())) {
                    excelWriter.write(page.getRows(), writeSheet);
                }

                //总页数
                totalPageNum = (page.getTotal() - 1) / dataSourceParam.getSize() + 1;
                currentPageNum++;
            } while (currentPageNum <= totalPageNum);
            log.info("fileName:{} end", excelParam.getExcelName());
        } catch (Exception e) {
            log.error("fileName:{} ==>exception:{}", excelParam.getExcelName(), e);
        } finally {
            if (excelWriter != null) {
                excelWriter.finish();
            }
        }
    }





}

参数ExcelParam:

@Data
@AllArgsConstructor
@NoArgsConstructor
public class ExcelParam implements Serializable {

    private String excelName;

    private OutputStream outputStream;

    public static ExcelParam init(String excelName, HttpServletResponse response) throws IOException {
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding("utf-8");
        // 这里URLEncoder.encode可以防止中文乱码
        String fileName = URLEncoder.encode(excelName, "UTF-8").replaceAll("\\+", "%20");
        response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");

        return new ExcelParam(excelName, response.getOutputStream());
    }
}

参数DataSource:

@FunctionalInterface
public interface DataSource<T> {

    Pager<T> get(DataSourceParam param);

}

导出实体:

@EqualsAndHashCode()
@Data
@ContentRowHeight(20)
@HeadRowHeight(50)
@ColumnWidth(20)
public class PageExcel implements Serializable {
    @ExcelProperty(value = "字符串标题")
    private String string;
    @ExcelProperty(value = "日期标题")
    private Date date;
    @ExcelProperty(value = "数字标题")
    private Double doubleData;
}