EasyExcel分页导出数据

分页导出excel

前段时间用了esayExcel做了导入,最近业务中用到了导出,就再看了看github文档,发现版本升级了,好多方法都已经过时了,曾经十分复杂的导入导出优化了不少,导入导出都是一行代码直接搞定.但是数据量大的时候一次查询过多数据库耗时过长容易造成网关超时等等问题,所以都是分页查询之后导出到一个表上

直接上代码

		String fileName = "服务协议" + System.currentTimeMillis() + ExcelTypeEnum.XLS.getValue();

        // 文件存储位置
        String filePath = BeanTool.getProperty("file.client.storage.path") + "/" + fileName;

        ExcelWriter excelWriter = EasyExcel.write(filePath, ServiceAgreementExcelVO.class).build();
        WriteSheet writeSheet = EasyExcel.writerSheet(1).build();
        long pageIndex = 1L;
        while (true) {
         	// 一次分页查询500条
            PageInfo pageInfo = new PageInfo().setPageSize(500L).setPageIndex(pageIndex);
            serviceAgreementQueryVO.setPagingQuery(pageInfo);
            Result<ServiceAgreementVO> result = getServiceAgreementProductList(serviceAgreementQueryVO, userSessionVO);
            if (result.getPageInfo().getTotal() > 20000) {
                ExceptionUtil.rollback("导出最大不超过20000条", ErrorConstants.EXPORT_CANNOT_EXCEED_20000);
            }
            List<ServiceAgreementExcelVO> serviceAgreementExcelVOList = BeanCopyUtil.copy(result.getModels(), ServiceAgreementExcelVO.class);
            if (!CollectionUtils.isEmpty(serviceAgreementExcelVOList)) {
                // 第一次写入会创建头,第二次写入会在上一次写入的最后一行后面写入
                excelWriter.write(serviceAgreementExcelVOList, writeSheet);
            }

            PageInfo pageInformation = result.getPageInfo();
            if (pageInformation.getPageIndex() * pageInformation.getPageSize() >= pageInformation.getTotal()) {
                break;
            }
            pageIndex++;
        }
        excelWriter.finish();

        // 将生成的文件上传文件服务器 , 返回结果
        Result uploadResult = FileClientUtil.uploadFile(ExcelUtil.getAddress(), new File(filePath), fileName);
        // 上传失败抛出异常
        if (uploadResult.getSuccess() == Constants.NO) {
            ExceptionUtil.rollback("文件上传服务器失败", ErrorConstants.UPLOAD_FAILED);
        }
        // 将文件地址放入vo
        CommonFileVO commonFileVO = JsonUtil.parseObject(JsonUtil.toJsonString(uploadResult.getModels().get(0)), CommonFileVO.class);
        FileResultVO fileResultVO = new FileResultVO();
        fileResultVO.setSymbol(Constants.EXCEL_FILE);
        fileResultVO.setFileId(commonFileVO.getId());
        fileResultVO.setFileCode(commonFileVO.getCode());
        fileResultVO.setFileName(commonFileVO.getOriginalName());
        return Result.createWithModel(null, fileResultVO);

这次优化后分页导出只需要循环调用excelWriter.write(serviceAgreementExcelVOList, writeSheet)就会自动在上次的基础上自动添加

实体类也不需要继承任何其他类了,可以自定义列宽列高

/**
 * @author qiaomu@vichain.com
 * @date 2019/9/9
 */
@ColumnWidth(15)
public class ServiceAgreementExcelVO {

    @ExcelProperty(value = "业务类型", index = 0)
    private String businessTypeName;

    @ExcelProperty(value = "创客", index = 1)
    private String entrepreneursName;

    @ExcelProperty(value = "客户", index = 2)
    private String customerName;

    @ExcelProperty(value = "供应商", index = 3)
    private String supplierName;

    @ExcelProperty(value = "订单数量(ADMT)", index = 4)
    private BigDecimal orderQuantity;

    @ExcelProperty(value = "产品", index = 5)
    private String productName;

    @ExcelProperty(value = "采购合同", index = 6)
    private String purchaseOrderCode;

    @ExcelProperty(value = "销售合同", index = 7)
    private String saleOrderCode;

    @DateTimeFormat("yyyy/MM/dd")
    @ExcelProperty(value = "签约日期", index = 8)
    private Date contractDate;

实体类这里还有两个坑
第一:时间类型不能是LocalDateTime,所以导出时把原来的LocalDateTime转换成了Date类型;
第二:getter,setter方法必须是第一种的

@ExcelProperty(value = "业务类型", index = 0)
private String businessTypeName;
public String getBusinessTypeName() {
    return businessTypeName;
}

public void setBusinessTypeName(String businessTypeName) {
    this.businessTypeName = businessTypeName;
}

如果你用了下面带返回值的setter方法,导入导出也会失效

@ExcelProperty(value = "业务类型", index = 0)
private String businessTypeName;
public String getBusinessTypeName() {
    return businessTypeName;
}

public ServiceAgreementExcelVO setBusinessTypeName(String businessTypeName) {
    this.businessTypeName = businessTypeName;
    return this;
}

这是我踩到最大的坑,找了好几个小时才发现了这个问题