easyexcel官網(wǎng)
https://www.yuque.com/easyexcel/doc/easyexcel

測試用poi版本 3.15 3.17存在 如果用戶不想等待中斷會導(dǎo)致內(nèi)存泄漏 4.1.0沒有該問題-

        <!--引用相關(guān)依賴-->
        <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>

第一個(gè)思路：分頁導(dǎo)出 查詢數(shù)據(jù)庫一批寫入一批

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

    private static AtomicInteger exportNumber = new AtomicInteger();
    @RequestMapping(name = "數(shù)據(jù)導(dǎo)出接口", 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''" + "查驗(yàn)列表數(shù)據(jù)導(dǎo)出" + ".xlsx");
        //限制導(dǎo)出的次數(shù)  分布式的話 可以選用中間件去操作
        if(exportNumber.incrementAndGet() > 5){
            errorHandle(response,"超出同時(shí)導(dǎo)出的數(shù)量 請稍后導(dǎo)出");
            return;
        }
        ExcelWriter excelWriter = null;
        try {
            excelWriter = EasyExcel.write(response.getOutputStream(), TestDto.class).build();
            int i = 0;
            while (true){
                // 分頁去數(shù)據(jù)庫查詢數(shù)據(jù) 這里可以去數(shù)據(jù)庫查詢每一頁的數(shù)據(jù) 這里你根據(jù)自身的分頁去實(shí)現(xiàn)就行
                List<TestDto> data =  getPageList(bean);
                if(data == null){
                    return;
                }
                //這方法封裝數(shù)據(jù)用 這個(gè)可以根據(jù)你自身業(yè)務(wù)處理
                WriteSheet writeSheet = EasyExcel.writerSheet(i,"列表數(shù)據(jù)導(dǎo)出"+i).build();
                excelWriter.write(data,writeSheet);
                i++;
                data.clear();
            }
        }catch (Exception e){
            log.error("查驗(yàn)列表數(shù)據(jù)導(dǎo)出異常 ",e);
            errorHandle(response,"導(dǎo)出異常"+e.getMessage());
        }finally {
            exportNumber.decrementAndGet();
            if (excelWriter != null) {
                excelWriter.finish();
            }
        }
    }

    /**  分頁去數(shù)據(jù)庫查詢數(shù)據(jù) 這里可以去數(shù)據(jù)庫查詢每一頁的數(shù)據(jù) 這里你根據(jù)自身的分頁去實(shí)現(xiàn)就行*/
    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("查驗(yàn)列表數(shù)據(jù)導(dǎo)出").doWrite(Arrays.asList(testDto));
        } catch (IOException e) {
            log.error("errorHandle 異常{}",e.getMessage());
        }
    }

    // 頭背景設(shè)置成紅色 IndexedColors.RED.getIndex()  導(dǎo)出的對象
    @HeadStyle(fillPatternType = FillPatternType.SOLID_FOREGROUND, fillForegroundColor = 17)
    // 頭字體設(shè)置成20
    @HeadFontStyle(fontHeightInPoints = 13)
    @ColumnWidth(10)
    @HeadRowHeight(30)
    // 內(nèi)容字體設(shè)置成20
    @ContentFontStyle(fontHeightInPoints = 13)
    @ExcelIgnoreUnannotated()
    @Data
    public class TestDto implements Serializable {
        @ExcelProperty(value = "查驗(yàn)編號",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;

        /** 查驗(yàn)結(jié)果  1合格  2 不合格*/
        @ExcelProperty(value = "測試結(jié)果",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 = "測試時(shí)間13",index = 13)
        private Date checktime;
        @ExcelProperty(value = "測試狀態(tài)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;
    }

    //轉(zhuǎn)換器
    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){// 結(jié)果
                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;
}

第二種思路：基于數(shù)據(jù)庫的Cursor方式 服務(wù)端和數(shù)據(jù)庫建立長連接 邊讀邊寫 用mybatis實(shí)現(xiàn)

 <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.3.2</version>
        </dependency>
 /**
     * 通過游標(biāo)的方式獲取
     * @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"));

            //查詢數(shù)據(jù) 獲取到游標(biāo)    自己用個(gè)
            DefaultCursor<QueryCondition> allStream = (DefaultCursor<QueryCondition>) getAllStream(roBean);
            Iterator<QueryCondition> iterator = allStream.iterator();
            while (iterator.hasNext()){
                QueryCondition next = iterator.next();
                //滿足多少條的時(shí)候就寫一波
                if (beans.size()<10000&&!allStream.isConsumed()){
                    beans.add(next);
                    iterator.remove();
                }else {
                    //滿足條件開始執(zhí)行寫  偽代碼（自己實(shí)現(xiàn)寫）
                    WriteSheet writeSheet = EasyExcel.writerSheet("列表數(shù)據(jù)導(dǎo)出").build();
                    writeSheet.setExcludeColumnFiledNames(excludeColumnFiledNames);
                    excelWriter.write(beans,writeSheet);
                    beans.clear();
                }
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            if (excelWriter != null) {
                excelWriter.finish();
            }
        }
    }

    /**
     * 數(shù)據(jù)庫獲取
     * @param bean
     * @return
     */
    Cursor<QueryCondition> getAllStream(QueryCondition bean){
        //basemap.getAllStream(bean)
        return null;
    }