easyexcel官网
https://www.yuque.com/easyexcel/doc/easyexcel

测试用poi版本 3.15 3.17存在 如果用户不想等待中断会导致内存泄漏 4.1.0没有该问题-

        <!--引用相关依赖-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>2.2.6</version>
        </dependency>
      
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>4.1.0</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml-schemas</artifactId>
            <version>4.1.0</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>4.1.0</version>
        </dependency>
        <dependency>
            <groupId>cn.hutool</groupId>
            <artifactId>hutool-all</artifactId>
            <version>5.5.1</version>
            <scope>compile</scope>
        </dependency>

第一个思路：分页导出 查询数据库一批写入一批

@RestController
@RequestMapping("api")
@Slf4j
public class TestController {

    private static AtomicInteger exportNumber = new AtomicInteger();
    @RequestMapping(name = "数据导出接口", value = "exportList.json", method = RequestMethod.POST)
    public void getInsList(@RequestBody QueryCondition bean, HttpServletResponse response) {
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + "查验列表数据导出" + ".xlsx");
        //限制导出的次数  分布式的话 可以选用中间件去操作
        if(exportNumber.incrementAndGet() > 5){
            errorHandle(response,"超出同时导出的数量 请稍后导出");
            return;
        }
        ExcelWriter excelWriter = null;
        try {
            excelWriter = EasyExcel.write(response.getOutputStream(), TestDto.class).build();
            int i = 0;
            while (true){
                // 分页去数据库查询数据 这里可以去数据库查询每一页的数据 这里你根据自身的分页去实现就行
                List<TestDto> data =  getPageList(bean);
                if(data == null){
                    return;
                }
                //这方法封装数据用 这个可以根据你自身业务处理
                WriteSheet writeSheet = EasyExcel.writerSheet(i,"列表数据导出"+i).build();
                excelWriter.write(data,writeSheet);
                i++;
                data.clear();
            }
        }catch (Exception e){
            log.error("查验列表数据导出异常 ",e);
            errorHandle(response,"导出异常"+e.getMessage());
        }finally {
            exportNumber.decrementAndGet();
            if (excelWriter != null) {
                excelWriter.finish();
            }
        }
    }

    /**  分页去数据库查询数据 这里可以去数据库查询每一页的数据 这里你根据自身的分页去实现就行*/
    private List<TestDto> getPageList(QueryCondition bean) {
        return new ArrayList<>();
    }

    /** 错误处理 */
    public void errorHandle(HttpServletResponse response,String errMsg)  {
        TestDto testDto = new TestDto();
        testDto.setInsid(errMsg);
        try {
            EasyExcel.write(response.getOutputStream(), TestDto.class).sheet("查验列表数据导出").doWrite(Arrays.asList(testDto));
        } catch (IOException e) {
            log.error("errorHandle 异常{}",e.getMessage());
        }
    }

    // 头背景设置成红色 IndexedColors.RED.getIndex()  导出的对象
    @HeadStyle(fillPatternType = FillPatternType.SOLID_FOREGROUND, fillForegroundColor = 17)
    // 头字体设置成20
    @HeadFontStyle(fontHeightInPoints = 13)
    @ColumnWidth(10)
    @HeadRowHeight(30)
    // 内容字体设置成20
    @ContentFontStyle(fontHeightInPoints = 13)
    @ExcelIgnoreUnannotated()
    @Data
    public class TestDto implements Serializable {
        @ExcelProperty(value = "查验编号",index = 0)
        @ColumnWidth(28)
        private String insid;

        @ExcelProperty(value = "测试1",index = 1)
        private String licenseplate;

        @ExcelProperty(value = "测试2",index = 2)
        private String licensecolorName;
        private String licensecolor;

        @ExcelProperty(value = "测试3",index = 3)
        private String vehicletypeName;
        private Integer vehicletype;

        @ExcelProperty(value = "编号",index = 4)
        private String id;

        @ExcelProperty(value = "类型",index = 5)
        private String freighttypesName;
        private String freighttypes;

        /** 查验结果  1合格  2 不合格*/
        @ExcelProperty(value = "测试结果",index = 6,converter = CheckConverter.class)
        private Integer checkresult;

        @ExcelProperty(value = "测试省份",index = 7)
        private String enprovincialName;

        @ExcelProperty(value = "出口测试",index = 8)
        @ColumnWidth(20)
        private String exmanagername;

        @ExcelProperty(value = "出口测试9",index = 9)
        @ColumnWidth(20)
        private String exroadname;
        @ExcelProperty(value = "出口测试10",index = 10)
        @ColumnWidth(20)
        private String exstationname;

        @ExcelProperty(value = "测试人员11",index = 11)
        private String firstName;
        @ExcelProperty(value = "测试人员12",index = 12)
        private String secName;
        @DateTimeFormat(pattern="yyyy-MM-dd HH:mm:ss")
        @JsonFormat(pattern="yyyy-MM-dd HH:mm:ss",locale = "zh",timezone="GMT+8")
        @ColumnWidth(20)
        @ExcelProperty(value = "测试时间13",index = 13)
        private Date checktime;
        @ExcelProperty(value = "测试状态14",index = 14)
        private Integer inspectionphase;

        @DateTimeFormat(pattern="yyyy-MM-dd HH:mm:ss")
        @JsonFormat(pattern="yyyy-MM-dd HH:mm:ss",locale = "zh",timezone="GMT+8")
        private Date transportdate;
    }

    //转换器
    public class CheckConverter implements Converter<Integer> {
        @Override
        public Class supportJavaTypeKey() {
            return Integer.class;
        }

        @Override
        public CellDataTypeEnum supportExcelTypeKey() {
            return CellDataTypeEnum.NUMBER;
        }

        @Override
        public Integer convertToJavaData(CellData cellData, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) throws Exception {
            return null;
        }

        @Override
        public CellData convertToExcelData(Integer value, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) throws Exception {
            if(value == 1){// 结果
                return new CellData<>("合格");
            }else if(value == 2){
                return new CellData<>("不合格");
            }else if(value == 3){
                return new CellData<>("处理中");
            }
            return new CellData<>(value);
        }
    }

}
@Data
class QueryCondition {
    String id;
    String name;
}

第二种思路：基于数据库的Cursor方式 服务端和数据库建立长连接 边读边写 用mybatis实现

 <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.3.2</version>
        </dependency>
 /**
     * 通过游标的方式获取
     * @param roBean
     * @param fileName
     * @param excludeColumnFiledNames
     * @param response
     */
    public void exportStreamDataZip(QueryCondition roBean, String fileName, Set<String> excludeColumnFiledNames, HttpServletResponse response){
        List<QueryCondition> beans = new ArrayList<>(1000);
        ExcelWriter excelWriter = null;
        try {
            excelWriter = EasyExcel.write(response.getOutputStream(), QueryCondition.class).build();
            response.setContentType("application/vnd.ms-excel");
            response.setCharacterEncoding("utf-8");
            response.setHeader("Content-disposition", "attachment;filename*=UTF-8''" + URLEncoder.encode(fileName+".xlsx", "UTF-8"));

            //查询数据 获取到游标    自己用个
            DefaultCursor<QueryCondition> allStream = (DefaultCursor<QueryCondition>) getAllStream(roBean);
            Iterator<QueryCondition> iterator = allStream.iterator();
            while (iterator.hasNext()){
                QueryCondition next = iterator.next();
                //满足多少条的时候就写一波
                if (beans.size()<10000&&!allStream.isConsumed()){
                    beans.add(next);
                    iterator.remove();
                }else {
                    //满足条件开始执行写  伪代码（自己实现写）
                    WriteSheet writeSheet = EasyExcel.writerSheet("列表数据导出").build();
                    writeSheet.setExcludeColumnFiledNames(excludeColumnFiledNames);
                    excelWriter.write(beans,writeSheet);
                    beans.clear();
                }
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            if (excelWriter != null) {
                excelWriter.finish();
            }
        }
    }

    /**
     * 数据库获取
     * @param bean
     * @return
     */
    Cursor<QueryCondition> getAllStream(QueryCondition bean){
        //basemap.getAllStream(bean)
        return null;
    }