Spring Boot 使用EasyExcel分页查询下载示例

• 依赖

<dependency>
	<groupId>com.alibaba</groupId>
	<artifactId>easyexcel</artifactId>
	<version>3.2.1</version>
</dependency>
<dependency>
	<groupId>org.projectlombok</groupId>
	<artifactId>lombok</artifactId>
	<version>2.3.0.RELEASE</version>
</dependency>
<dependency>
	<groupId>org.springframework.boot</groupId>
	<artifactId>spring-boot-starter-web</artifactId>
	<version>2.3.0.RELEASE</version>
</dependency>

工具类

@Slf4j  
public class EasyExcelUtil {  
  
    public static <T> void writeByPage(HttpServletResponse response, Class<?> header, String fileName, Function<PageRequest, Page<T>> function, PageRequest pageRequest) {  
        try {  
            String name = URLEncoder.encode(fileName, "UTF-8").replaceAll("\\+", "%20");  
            response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + name + ".xlsx");  
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");  
            response.setCharacterEncoding("utf-8");  
  
            ExcelWriter writer = new ExcelWriterBuilder()  
                    .autoCloseStream(true)  
                    .automaticMergeHead(true) // 这里一定要设置为true，否则在无法实现表头列合并  
                    .excelType(ExcelTypeEnum.XLSX)  
                    .file(response.getOutputStream())  
                    .head(header)  
                    .build();  
            // xlsx文件单个工作簿上限是1048575行,这里如果超过104W需要分Sheet  
            long sheetMax = 1048575;  
            // 每一个工作簿可写入的数据量，需要把表头所占的行算上  
            long sheetTotal = 1;  
            // 工作簿编号  
            int sheetNum = 1;  
            WriteSheet writeSheet = EasyExcel.writerSheet("sheet" + sheetNum).head(header).build();  
            // 先查询第一页，得到总数  
            Page<T> page = function.apply(pageRequest);  
            writer.write(page.getContent(), writeSheet);  
            if (page.getTotalPages() <= 0 || page.getTotalElements() <= pageRequest.getPageSize()) {  
                // 没有数据或只有一页数据  
                writer.finish();  
                return;            }  
            sheetTotal += page.getContent().size();  
            long total = page.getTotalElements();  
            long fetch = page.getContent().size();  
            for (; ; ) {  
                // 只查询分页数据，不查询总数  
                pageRequest = PageRequest.of(pageRequest.getPageNumber() + 1, pageRequest.getPageSize());  
                page = function.apply(pageRequest);  
                List<T> list = page.getContent();  
                fetch += list.size();  
                sheetTotal += list.size();  
                if (sheetTotal >= sheetMax) {  
                    sheetTotal = 1 + list.size();  
                    sheetNum++;  
                    writeSheet = EasyExcel.writerSheet("sheet" + sheetNum).head(header).build();  
                }  
                writer.write(list, writeSheet);  
                if (fetch >= total) {  
                    writer.finish();  
                    break;                }  
            }  
            writer.finish();  
        } catch (Exception e) {  
            log.error(e.getMessage(), e);  
            response.reset();  
            response.setContentType("application/json");  
            response.setCharacterEncoding("utf-8");  
            try {  
                response.getOutputStream().write("{'code':200,'msg':'server error'}".getBytes(StandardCharsets.UTF_8));  
            } catch (IOException ex) {  
                throw new RuntimeException(ex);  
            }  
        }  
    }  
}

业务类

@RestController  
@Slf4j  
public class PageController {  
  
    @GetMapping("page")  
    public void page(HttpServletResponse response) {  
        PageRequest requestPage = PageRequest.of(1, 5000);  
        EasyExcelUtil.writeByPage(response, DownloadData.class, "测试分页查询下载文件", this::listPage, requestPage);  
    }  
  
    /**  
     * 模拟service查询数据  
     */  
    private Page<DownloadData> listPage(PageRequest requestPage) {  
        List<DownloadData> list = new ArrayList<>();  
        for (int i = (requestPage.getPageNumber() - 1) * requestPage.getPageSize(); i < requestPage.getPageNumber() * requestPage.getPageSize(); i++) {  
            list.add(new DownloadData("n" + i, new Date()));  
        }  
        return new PageImpl<>(list, requestPage, 1148575);  
    }  
  
    @Data  
    @AllArgsConstructor    static class DownloadData implements Serializable {  
        @ExcelProperty(value = "昵称", index = 0)  
        private String nickName;  
        @ExcelProperty(value = "注册时间", index = 1)  
        private Date createDate;  
    }  
}

参考

• 编程随笔