EasyExcel导入、导出Excel

一、引入 jar 包

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>3.0.5</version>
</dependency>

EasyExcel支持对实体类中的属性进行注解，方便后续进行读写操作。 id 为 表头，index 代表的是 第0行

@ExcelProperty(value="id",index=0)

二、编写excel导入对应实体类

@Data
public class ImportTradeDetailExcelMode implements Serializable {

    @ApiModelProperty(value = "贝壳楼盘名")
    @ExcelProperty("贝壳楼盘名")
    private String bkGardenName;

    @ApiModelProperty(value = "贝壳小区ID")
    @ExcelProperty("贝壳小区ID")
    private String bkGardenId;

    @ApiModelProperty(value = "城市区域")
    @ExcelProperty("区域")
    private String regionName;

    @ApiModelProperty(value = "城市名称")
    @ExcelProperty("城市")
    private String cityName;

    @ApiModelProperty(value = "成交日期")
    @ExcelProperty("成交时间")
    @DateTimeFormat("yyyy-MM-dd")
    private String tradeDate;

    @ApiModelProperty(value = "房源价格")
    @ExcelProperty("房源价格")
    private String roomPrice;

    @ApiModelProperty(value = "楼层")
    @ExcelProperty("楼层")
    private String floor;

    @ApiModelProperty(value = "房源面积(单位M2)")
    @ExcelProperty("房源面积")
    private BigDecimal roomArea;

    @ApiModelProperty(value = "朝向")
    @ExcelProperty("朝向")
    private String towards;

    @ApiModelProperty(value = "户型")
    @ExcelProperty("户型")
    private String roomType;

    @ApiModelProperty(value = "房屋用途")
    @ExcelProperty("房屋用途")
    private String roomUse;

    @ExcelProperty("错误消息")
    private String errorMsg;
}

三、导入

3.1、编写导入监听类

3.1.1 导入数据抽象数据监听类

/**
 * @author: huangyibo
 * @Date: 2022/3/24 16:03
 * @Description: 导入数据抽象类明细数据监听类
 */

@Slf4j
public abstract class ImportDataAbstractListener<T> extends AnalysisEventListener<T> {

    /**
     * 每隔3000条存储数据库， 然后清理successList ，方便内存回收
     */
    private static final int BATCH_COUNT = 3000;

    public static int getBatchCount() {
        return BATCH_COUNT;
    }

    /**
     * 获取导入结果
     * @return
     */
    public abstract ImportDataReturnVo<T> getResult();
}

3.1.2 编写具体监听类

/**
 * @author: huangyibo
 * @Date: 2022/3/24 16:03
 * @Description: 交易明细数据监听类
 */

@Slf4j
public class TradeDetailImportListener extends ImportDataAbstractListener<ImportTradeDetailExcelMode> {

    /**
     * 解析成功的数据
     */
    private List<DictImportTradeDetailDTO> successList = new ArrayList<>();

    /**
     * 格式有问题的数据
     */
    private List<ImportTradeDetailExcelMode> failList = new ArrayList<>();

    /**
     * 导入返回结果
     */
    private ImportDataReturnVo<ImportTradeDetailExcelMode> returnVo;

    private DictImportTradeDetailRemote dictImportTradeDetailRemote;

    private String userId;

    public TradeDetailImportListener(DictImportTradeDetailRemote dictImportTradeDetailRemote, String userId) {
        this.dictImportTradeDetailRemote = dictImportTradeDetailRemote;
        this.returnVo = new ImportDataReturnVo<>();
        this.returnVo.setImportId(IdUtils.getUuid());
        this.userId = userId;
    }

    @Override
    public void invoke(ImportTradeDetailExcelMode data, AnalysisContext analysisContext) {
        if("{}".equals(JSON.toJSONString(data))){
            //log.info("成交数据为空, 不做处理, data:{}", JSON.toJSONString(data));
            return;
        }
        DictImportTradeDetailDTO tradeDetailDTO = new DictImportTradeDetailDTO();
        BeanUtils.copyProperties(data, tradeDetailDTO);
        if (!validateEntity(data, tradeDetailDTO)) {
            log.info("该数据必传参数校验不通过, data:{}", JSON.toJSONString(data));
            return;
        }
        String tradeDate = data.getTradeDate();
        String newTread = tradeDate.replaceAll("\\.", "-");
        newTread = newTread.replaceAll("\\/", "-");
        Date date = Date.from(LocalDate.parse(newTread, DateTimeFormatter.ofPattern("yyyy-MM-dd")).atStartOfDay(ZoneOffset.ofHours(8)).toInstant());
        tradeDetailDTO.setTradeDate(date);
        tradeDetailDTO.setImportId(returnVo.getImportId());
        tradeDetailDTO.setCreateId(userId);
        tradeDetailDTO.setModifyId(userId);
        successList.add(tradeDetailDTO);

        //每隔3000条存储数据库， 然后清理successList ，方便内存回收
        if (successList.size() >= getBatchCount()) {
            //分批存储数据
            saveData();
            successList.clear();
        }
    }

    /**
     * 校验表头，表头必须严格按照此格式和顺序
     * @param headMap
     * @param context
     */
    @Override
    public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
        String[] headList = {"日期", "贝壳楼盘名", "寻渡小区ID", "贝壳小区ID", "区域", "商圈", "城市",
                "成交时间", "房源价格", "楼层", "房源面积", "朝向", "户型", "房屋用途", "房源ID"};
        Collection<String> values = headMap.values();
        for (String head : headList) {
            if (!values.contains(head)) {
                throw new RequestResultException("成交数据导入excel表头缺少[" + head + "]列");
            }
        }

        //表头顺序严格校验
        /*for (int i = 0; i < headList.length; i++) {
            if(!Objects.equals(headList[i], headMap.get(i))){
                throw new RequestResultException("成交数据导入excel表头["+headMap.get(i)+"]和解析格式excel表头["+headList[i]+"]不一致");
            }
        }*/
    }

    /**
     * 校验必传参数
     * @param data
     * @param tradeDetailDTO
     * @return
     */
    private boolean validateEntity(ImportTradeDetailExcelMode data, DictImportTradeDetailDTO tradeDetailDTO) {
        log.info("校验导入交易数据, data:{}", JSON.toJSONString(data));
        if(StringUtils.isEmpty(data.getBatchNum())){
            data.setErrorMsg("日期不能为空");
            failList.add(data);
            return false;
        }
        if(StringUtils.isEmpty(data.getKfGardenId())){
            data.setErrorMsg("看房网小区ID不能为空");
            failList.add(data);
            return false;
        }
        if(StringUtils.isEmpty(data.getRoomLink())){
            data.setErrorMsg("房源ID不能为空");
            failList.add(data);
            return false;
        }
        if(StringUtils.isEmpty(data.getRoomPrice())){
            data.setErrorMsg("房源价格不能为空");
            failList.add(data);
            return false;
        }else{
            try {
                BigDecimal roomPrice = new BigDecimal(String.valueOf(data.getRoomPrice()))
                        .multiply(new BigDecimal(10000));
                if(roomPrice.compareTo(new BigDecimal(0)) <= 0){
                    data.setErrorMsg("房源价格必须大于0");
                    failList.add(data);
                    return false;
                }
                tradeDetailDTO.setRoomPrice(roomPrice);
            } catch (Exception e) {
                data.setErrorMsg("房源价格格式错误");
                failList.add(data);
                return false;
            }
        }
        if(StringUtils.isEmpty(data.getRoomArea())){
            data.setErrorMsg("房源面积不能为空");
            failList.add(data);
            return false;
        }else{
            try {
                BigDecimal roomArea = new BigDecimal(String.valueOf(data.getRoomArea()));
                if(roomArea.compareTo(new BigDecimal(0)) <= 0){
                    data.setErrorMsg("房源面积必须大于0");
                    failList.add(data);
                    return false;
                }
                tradeDetailDTO.setRoomArea(roomArea);
            } catch (Exception e) {
                data.setErrorMsg("房源面积格式错误");
                failList.add(data);
                return false;
            }
        }
        if(StringUtils.isEmpty(data.getTradeDate())){
            data.setErrorMsg("成交时间不能为空");
            failList.add(data);
            return false;
        }
        return true;
    }

    /**
     * 所有数据读取完之后调用该方法
     * @param analysisContext
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        if(!CollectionUtils.isEmpty(successList)){
            if(returnVo.getSuccessNum() == null){
                returnVo.setSuccessNum(successList.size());
            }else {
                returnVo.setSuccessNum(returnVo.getSuccessNum() + successList.size());
            }
            log.info("存储成交明细数据, size:{}, successList:{}", successList.size(), JSON.toJSONString(successList));
            dictImportTradeDetailRemote.batchInsert(successList);
            log.info("存储成交明细数据SUCCESS, size:{}", successList.size());
        }
        returnVo.setFailList(failList);
        returnVo.setFailNum(failList.size());
        returnVo.setImportTotalNum(returnVo.getSuccessNum() + returnVo.getFailNum());
        log.info("存储成交明细数据导入成功, size:{}, returnVo:{}", successList.size(), JSON.toJSONString(returnVo));
    }

    /**
     * 分批存储数据库
     */
    private void saveData() {
        if(returnVo.getSuccessNum() == null){
            returnVo.setSuccessNum(successList.size());
        }else {
            returnVo.setSuccessNum(returnVo.getSuccessNum() + successList.size());
        }
        log.info("存储成交明细数据, size:{}, successList:{}", successList.size(), JSON.toJSONString(successList));
        dictImportTradeDetailRemote.batchInsert(successList);
        log.info("存储成交明细数据SUCCESS, size:{}", successList.size());
    }

    /**
     * 获取导入结果
     * @return
     */
    @Override
    public ImportDataReturnVo<ImportTradeDetailExcelMode> getResult() {
        return returnVo;
    }
}

3.2、编写导入工具类

/**
 * @author: huangyibo
 * @Date: 2022/3/24 20:11
 * @Description:
 */

public class EasyExcelUtil {

    /**
     * 下载文件时，针对不同浏览器，进行附件名的编码
     *
     * @param filename 下载文件名
     *
     * @param agent 客户端浏览器
     *
     * @return 编码后的下载附件名
     * @throws IOException
     */
    public static String encodeDownloadFilename(String filename, String agent)
            throws IOException {
        if (agent.contains("Firefox")) { // 火狐浏览器
            filename = "=?UTF-8?B?"
                    + Base64.getEncoder().encode(filename.getBytes("utf-8"))
                    + "?=";
            filename = filename.replaceAll("\r\n", "");
        } else { // IE及其他浏览器
            filename = URLEncoder.encode(filename, "utf-8");
            filename = filename.replace("+"," ");
        }
        return filename;
    }


    /**
     * 设置导出Excel格式
     * @param response
     * @param request
     * @param filename
     * @throws IOException
     */
    public static void setExportExcelFormat(HttpServletResponse response, HttpServletRequest request, String filename) throws IOException {
        String agent = request.getHeader("user-agent");//获得游览器
        filename = filename + ".xlsx";
        String downloadFilename = encodeDownloadFilename(filename, agent); //使用工具类解决文件乱码的问题
        response.setCharacterEncoding("UTF-8");
        // 设置响应输出的头类型
        response.setHeader("content-Type", "application/vnd.ms-excel");
        // 下载文件的默认名称
        response.setHeader("Content-Disposition", "attachment;filename="+downloadFilename);
        response.setHeader("Pragma", "public");
        response.setHeader("Cache-Control", "no-store");
        response.addHeader("Cache-Control", "max-age=0");
    }


    /**
     * 读取导入excel数据(单sheet页读取)
     * @param file              上传文件
     * @param clazz             导入实体类型
     * @param excelListener     数据导入监听器
     * @return ImportDataReturnVo<?>
     * @throws IOException
     */
    public static ImportDataReturnVo<?> read(MultipartFile file, Class<?> clazz, ImportDataAbstractListener<?> excelListener) throws IOException {
        EasyExcel.read(file.getInputStream(), clazz, excelListener).sheet().doRead();
        return excelListener.getResult();
    }


    /**
     * 读取导入excel数据(读取全部sheet页)
     * @param file              上传文件
     * @param clazz             导入实体类型
     * @param excelListener     数据导入监听器
     * @return ImportDataReturnVo<?>
     * @throws IOException
     */
    public static ImportDataReturnVo<?> readAll(MultipartFile file, Class<?> clazz, ImportDataAbstractListener<?> excelListener) throws IOException {
        EasyExcel.read(file.getInputStream(), clazz, excelListener).doReadAll();
        return excelListener.getResult();
    }


    /**
     * 读取导入excel数据(读取指定sheet页)
     * @param file              上传文件
     * @param clazz             导入实体类型
     * @param excelListener     数据导入监听器
     * @param sheetNumList      指定的sheetNum
     * @return ImportDataReturnVo<?>
     * @throws IOException
     */
    public static ImportDataReturnVo<?> readSheet(MultipartFile file, Class<?> clazz, ImportDataAbstractListener<?> excelListener, List<Integer> sheetNumList) throws IOException {
        ExcelReader excelReader = EasyExcel.read(file.getInputStream()).build();
        // 读取不同的数据, 需要使用不同的head 和Listener
        List<ReadSheet> readSheetList = new ArrayList<>();
        sheetNumList.forEach(sheetNum -> {
            // readSheet参数设置读取sheet的序号
            ReadSheet readSheet = EasyExcel.readSheet(sheetNum).head(clazz).registerReadListener(excelListener).build();
            readSheetList.add(readSheet);
        });
        // 这里注意 一定要把readSheetList一起传进去，不然有个问题就是03版的excel 会读取多次，浪费性能
        excelReader.read(readSheetList);
        // 这里千万别忘记关闭，读的时候会创建临时文件，到时磁盘会崩的
        excelReader.finish();
        return excelListener.getResult();
    }

    /**
     * 将数据写入Excel并写入流中
     * @param outputStream  输出流
     * @param clazz         导出实体类型
     * @param list          导出数据集合
     * @param sheetName     sheet页名称
     */
    public static void writeExcel2Stream(ByteArrayOutputStream outputStream, Class<?> clazz, List<?> list, String sheetName){
        EasyExcel.write(outputStream, clazz).sheet(sheetName).doWrite(list);
    }

    /**
     * 单sheet页写入Excel
     * @param outputStream  输出流
     * @param clazz         导出实体类型
     * @param list          导出数据集合
     * @param sheetName     sheet页名称
     */
    public static void writeExcel(ServletOutputStream outputStream, Class<?> clazz, List<?> list, String sheetName) {
        EasyExcel.write(outputStream, clazz)
                .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
                .sheet(sheetName)
                .doWrite(list);
    }

    /**
     * 多sheet页写入Excel
     * @param excelWriter   excelWriter写出对象
     * @param clazz         导出实体类型
     * @param list          导出数据集合
     * @param num           sheet页码
     * @param sheetName     sheet页名称
     */
    public static void writerSheetExcel(ExcelWriter excelWriter, Class<?> clazz, List<?> list, Integer num, String sheetName) {
        WriteSheet writeSheet = EasyExcel.writerSheet(num, sheetName).head(clazz)
                .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()).build();
        excelWriter.write(list, writeSheet);
    }
}

3.3、业务层调用

/**
* 导入数据源记录 前端控制器
*
* @author huangyibo
* @since 2022-03-11
*/

@Lazy
@RestController
@RequestMapping("/security/dictImportDataRecord")
@Api(tags = ApiSwaggerTags.DICT_IMPORT,value = "导入数据源记录")
@Slf4j
public class DictImportDataRecordController extends WebBaseController {

    @Resource
    private DictImportDataRecordRemote dictImportDataRecordRemote;

    @Resource
    private DictImportTradeDetailRemote dictImportTradeDetailRemote;

    @Resource
    private AliyunOss aliyunOss;

    @PostMapping("/importTrade")
    @ApiOperation(value = "导入成交明细数据")
    @ApiResponses({
            @ApiResponse(message = "导入成交明细数据", code = 200, response = ImportDataReturnVo.class)
    })
    public String importTradeData(@RequestParam("file") MultipartFile file) {
        JwtUserInfo userInfo = getCurrentUserInfo();
        DictImportDataRecordDTO dataRecordDTO = new DictImportDataRecordDTO();
        ImportDataReturnVo<?> result = new ImportDataReturnVo<>();
        try {
            TradeDetailImportListener excelListener = new TradeDetailImportListener(dictImportTradeDetailRemote, userInfo.getUserId());
            result = EasyExcelUtil.read(file, ImportTradeDetailExcelMode.class, excelListener);
            if(!CollectionUtils.isEmpty(result.getFailList())){
                ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
                //将格式错误数据写入Excel并写入流中
                EasyExcelUtil.writeExcel2Stream(outputStream, ImportTradeDetailExcelMode.class, result.getFailList(), "成交数据");
                MultipartFile multipartFile = FileUtil.getMultipartFileFromStream(outputStream, "成交数据错误数据.xlsx");
                //上传aliyun 对象存储 OSS
                AliyunUploadRequest uploadRequest = new AliyunUploadRequest(multipartFile);
                AliyunUploadResult uploadResult = aliyunOss.fileUpload(uploadRequest);
                log.info("导入成交明细数据, 校验不通过数据写入excel上传到阿里云oss成功, result:{}", JSON.toJSONString(result));
                if(uploadResult != null){
                    dataRecordDTO.setFailDataFileUrl(uploadResult.getUrl());
                }
            }
            BeanUtils.copyProperties(result, dataRecordDTO);
            dataRecordDTO.setDataType(DataTypeEnum.TRADE_DATA);
            dataRecordDTO.setCreateId(userInfo.getUserId());
            dataRecordDTO.setModifyId(userInfo.getUserId());
            dataRecordDTO.setId(result.getImportId());
            dictImportDataRecordRemote.insert(dataRecordDTO);
            result.setSuccess(Boolean.TRUE);
        } catch (Exception e) {
            result.setSuccess(Boolean.FALSE);
            result.setErrCode(DictReturnCodeEnum.F6000000.name());
            if(e instanceof RequestResultException){
                result.setErrMessage(e.getMessage());
            }else {
                result.setErrMessage(DictReturnCodeEnum.F6000000.getMessage());
            }
            log.info("导入成交明细数据 Exception, result:{}", JSON.toJSONString(result), e);
        }
        return successInfo(result);
    }
}

四、导出

• EASYEXCEL导出百万级连表查询XLSX数据方法实测102万105秒

4.1 编写excel导出对应实体类

@Data
@ApiModel(description="估价表")
public class DictGardenAppraisalExport implements Serializable {

    @ApiModelProperty(value = "城市名称")
    @ColumnWidth(20)
    @ExcelProperty(value = "城市", index = 0)
    private String cityName;

    @ApiModelProperty(value = "城市区域名称")
    @ColumnWidth(20)
    @ExcelProperty(value = "区域", index = 1)
    private String regionName;

    @ApiModelProperty(value = "商圈名称")
    @ColumnWidth(20)
    @ExcelProperty(value = "商圈", index = 2)
    private String businessAreaName;

    @ApiModelProperty(value = "楼盘名称")
    @ColumnWidth(20)
    @ExcelProperty(value = "楼盘名称", index = 3)
    private String gardenName;

    @ApiModelProperty(value = "物业类型(冗余字段，存储格式：普通住宅/公寓/酒店)")
    @ColumnWidth(20)
    @ExcelProperty(value = "物业类型", index = 4)
    private String usageName;

    @ApiModelProperty(value = "委托套数")
    @ColumnWidth(20)
    @ExcelProperty(value = "委托套数", index = 5)
    private Integer trustNum;

    @ApiModelProperty(value = "委托均价")
    @ColumnWidth(20)
    @ExcelProperty(value = "委托均价", index = 6)
    private BigDecimal trustAvgPrice;

    @ApiModelProperty(value = "成交套数")
    @ColumnWidth(20)
    @ExcelProperty(value = "成交套数", index = 7)
    private Integer tradeNum;

    @ApiModelProperty(value = "成交均价")
    @ColumnWidth(20)
    @ExcelProperty(value = "成交均价", index = 8)
    private BigDecimal tradeAvgPrice;

    @ApiModelProperty(value = "外部参考价")
    @ColumnWidth(20)
    @ExcelProperty(value = "外部参考价", index = 9)
    private BigDecimal outerReferPrice;

    @ApiModelProperty(value = "参考价")
    @ColumnWidth(20)
    @ExcelProperty(value = "参考价", index = 10)
    private BigDecimal referPrice;

    @ApiModelProperty(value = "政府指导价")
    @ColumnWidth(20)
    @ExcelProperty(value = "政府指导价", index = 11)
    private BigDecimal governGuidePrice;

    @ApiModelProperty(value = "价差幅度")
    @ColumnWidth(20)
    @ExcelProperty(value = "价差幅度", index = 12)
    @NumberFormat("##0.##0%")
    private BigDecimal diffPriceRange;

    @ApiModelProperty(value = "参考价/第三方委托均价")
    @ColumnWidth(20)
    @ExcelProperty(value = "幅度1", index = 13)
    @NumberFormat("##0.##0%")
    private BigDecimal referThirdTrust;

    @ApiModelProperty(value = "参考价/第三方成交均价")
    @ColumnWidth(20)
    @ExcelProperty(value = "幅度2", index = 14)
    @NumberFormat("##0.##0%")
    private BigDecimal referThirdTrade;

    @ApiModelProperty(value = "参考价/第三方参考价")
    @ColumnWidth(20)
    @ExcelProperty(value = "幅度3", index = 15)
    @NumberFormat("##0.##0%")
    private BigDecimal referThirdRefer;

    @ApiModelProperty(value = "估价日期")
    @ColumnWidth(20)
    @ExcelProperty(value = "估价日期", index = 16)
    @DateTimeFormat("yyyy-MM-dd")
    private Date referDate;

    @ApiModelProperty(value = "发布状态：已发布=PUBLISHED、待发布=TOBE_PUBLISH、已撤回=REVOKE")
    @ColumnWidth(20)
    @ExcelProperty(value = "状态", index = 17)
    private String publishStatus;
}

4.2导入逻辑编写

@PostMapping(value = "/exportAppraisal")
@ApiOperation(value = "导出估价信息")
@ApiResponses({
        @ApiResponse(message = "导出估价信息", code = 200, response = DictGardenAppraisalVo.class)
})
public String exportAppraisal(@RequestBody DictGardenAppraisalQueryPageForm queryPageForm, HttpServletResponse response, HttpServletRequest request) {
    try {
        ServletOutputStream outputStream = response.getOutputStream();
        queryPageForm.setPageSize(PAGE_SIZE);
        Pagination<DictGardenAppraisalVo> page = dictGardenAppraisalRemote.queryDictGardenAppraisalPage(queryPageForm);
        EasyExcelUtil.setExportExcelFormat(response,request,"估价信息");
        //如果总数据量多余10万，分页导出
        if(page.getRecordCount() > PAGE_SIZE){
            //必须放到循环外，否则会刷新流
            ExcelWriter excelWriter = EasyExcel.write(outputStream).build();
            for (int i = 1; i <= page.getPageCount(); i++) {
                queryPageForm.setCurrentPage(i);
                Pagination<DictGardenAppraisalVo> appraisalPage = dictGardenAppraisalRemote.queryDictGardenAppraisalPage(queryPageForm);
                if (!CollectionUtils.isEmpty(appraisalPage.getItems())) {
                    List<DictGardenAppraisalExport> exportList = getDictGardenAppraisalExports(appraisalPage);
                    WriteSheet writeSheet = EasyExcel.writerSheet(i, "估价信息" + i).head(DictGardenAppraisalExport.class)
                            .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()).build();
                    excelWriter.write(exportList, writeSheet);
                }
            }
            //刷新流
            excelWriter.finish();
        }else {
            if (!CollectionUtils.isEmpty(page.getItems())) {
                List<DictGardenAppraisalExport> exportList = getDictGardenAppraisalExports(page);
                EasyExcel.write(outputStream,DictGardenAppraisalExport.class).registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()).sheet("估价信息").doWrite(exportList);
            }
        }
        outputStream.flush();
        response.getOutputStream().close();
        return successInfo(true);
    } catch (Exception e) {
        log.error("估价信息数据导出excel Exception",e);
        return successInfo(false);
    }
}

//@AuthorityAnnotation(AuthorityAnnotationEnums.ADMIN)
@PostMapping(value = "/exportAppraisal")
@ApiOperation(value = "导出楼盘估价信息")
@ApiResponses({
        @ApiResponse(message = "导出楼盘估价信息", code = 200, response = Boolean.class)
})
public String exportAppraisal(@RequestBody DictGardenAppraisalQueryPageForm queryPageForm, HttpServletResponse response, HttpServletRequest request) {
    try {
        ServletOutputStream outputStream = response.getOutputStream();
        queryPageForm.setPageSize(PAGE_SIZE);
        Pagination<DictGardenAppraisalVo> page = dictGardenAppraisalRemote.queryDictGardenAppraisalPage(queryPageForm);
        EasyExcelUtil.setExportExcelFormat(response, request,"KF楼盘估价");
        //如果总数据量多余10万，分页导出
        if(page.getRecordCount() > PAGE_SIZE){
            //必须放到循环外，否则会刷新流
            ExcelWriter excelWriter = EasyExcel.write(outputStream).build();
            for (int i = 1; i <= page.getPageCount(); i++) {
                queryPageForm.setCurrentPage(i);
                Pagination<DictGardenAppraisalVo> appraisalPage = dictGardenAppraisalRemote.queryDictGardenAppraisalPage(queryPageForm);
                if (!CollectionUtils.isEmpty(appraisalPage.getItems())) {
                    List<DictGardenAppraisalExport> exportList = getDictGardenAppraisalExports(appraisalPage);
                    EasyExcelUtil.writerSheetExcel(excelWriter, DictGardenAppraisalExport.class, exportList, i, "楼盘估价" + i);
                }
            }
            //刷新流
            excelWriter.finish();
        }else {
            //总数据量小余10万，单页导出
            if (!CollectionUtils.isEmpty(page.getItems())) {
                List<DictGardenAppraisalExport> exportList = getDictGardenAppraisalExports(page);
                EasyExcelUtil.writeExcel(outputStream, DictGardenAppraisalExport.class, exportList, "楼盘估价");
            }
        }
        outputStream.flush();
        outputStream.close();
        return successInfo(true);
    } catch (Exception e) {
        log.error("楼盘估价数据导出excel Exception",e);
        return successInfo(false);
    }
}

实测：导出用时105秒左右

每个表10万条

参考： https://www.yuque.com/easyexcel/doc/easyexcel

https://alibaba-easyexcel.github.io/

https://www.freesion.com/article/65771115640/

https://www.cnblogs.com/math-and-it/p/15465848.html

https://blog.51cto.com/alex4dream/4112300