通過設(shè)置sheet數(shù)量，完成分批導(dǎo)出，每個(gè)sheet存100萬數(shù)據(jù)，每次查詢插入20萬數(shù)據(jù)，避免超時(shí)，內(nèi)存溢出等問題，可以根據(jù)服務(wù)器配置調(diào)整參數(shù)設(shè)置。
1.引入依賴

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

2.創(chuàng)建對應(yīng)的實(shí)體類
@ExcelProperty設(shè)置的就是導(dǎo)出的列名，還可以設(shè)置排序等等

@Data
@NoArgsConstructor
@AllArgsConstructor
public class AltitudeMonExportExcelVO implements Serializable {

    @ExcelProperty(value = "站名")
    private String cname;

    @ExcelProperty(value = "站號")
    private String v01301;

    @ExcelProperty(value = "月平均氣溫(氣溫)")
    private String v12001_701="0";

    @ExcelProperty(value = "月平均氣壓")
    private String v10004_701="0";

    @ExcelProperty(value = "月平均相對濕度(相對濕度)")
    private String v13003_701="0";

    @ExcelProperty(value = "月平均溫度露點(diǎn)差(露點(diǎn)溫度)")
    private String v12301_701="0";
}

3.核心導(dǎo)出代碼

public void exportSurfList2(StatisticsRequestVo requestVo,HttpServletResponse response) throws IOException {
        log.info("*********地面統(tǒng)計(jì)查詢列表導(dǎo)出開始！**************");
        //文件名
        String fileName =String.valueOf(System.currentTimeMillis());

        OutputStream outputStream =null;
        try {
            //記錄總數(shù):實(shí)際中需要根據(jù)查詢條件進(jìn)行統(tǒng)計(jì)即可:一共多少條
            int totalCount=mapper.seleCount();
            //每一個(gè)Sheet存放100w條數(shù)據(jù)
            Integer sheetDataRows = ExcelConstants.PER_SHEET_ROW_COUNT;
            //每次寫入的數(shù)據(jù)量20w,每頁查詢20W
            Integer writeDataRows = ExcelConstants.PER_WRITE_ROW_COUNT;
            //計(jì)算需要的Sheet數(shù)量
            Integer sheetNum = totalCount % sheetDataRows == 0 ? (totalCount / sheetDataRows) : (totalCount / sheetDataRows + 1);
            //計(jì)算一般情況下每一個(gè)Sheet需要寫入的次數(shù)(一般情況不包含最后一個(gè)sheet,因?yàn)樽詈笠粋€(gè)sheet不確定會寫入多少條數(shù)據(jù))
            Integer oneSheetWriteCount = sheetDataRows / writeDataRows;
            //計(jì)算最后一個(gè)sheet需要寫入的次數(shù)
            Integer lastSheetWriteCount = totalCount % sheetDataRows == 0 ? oneSheetWriteCount : (totalCount % sheetDataRows % writeDataRows == 0 ? (totalCount / sheetDataRows / writeDataRows) : ((totalCount % sheetDataRows)+ / writeDataRows + 1));
            outputStream = response.getOutputStream();
            //必須放到循環(huán)外，否則會刷新流
            ExcelWriter excelWriter = EasyExcel.write(outputStream).build();
            //開始分批查詢分次寫入
            for (int i = 0; i < sheetNum; i++) {
                //創(chuàng)建Sheet
                WriteSheet sheet = new WriteSheet();
                sheet.setSheetName("Sheet"+i);
                sheet.setSheetNo(i);
                //循環(huán)寫入次數(shù): j的自增條件是當(dāng)不是最后一個(gè)Sheet的時(shí)候?qū)懭氪螖?shù)為正常的每個(gè)Sheet寫入的次數(shù),如果是最后一個(gè)就需要使用計(jì)算的次數(shù)lastSheetWriteCount
                for (int j = 0; j < (i != sheetNum - 1 ? oneSheetWriteCount : lastSheetWriteCount); j++) {
                    //分頁查詢一次20w
                    Page page1 = new Page(j + 1 + oneSheetWriteCount * i, writeDataRows);
                    //查詢分頁列表---按照自己的業(yè)務(wù)查列表，分頁這個(gè)一定要使用這個(gè)：page1.getPageNum(),page1.getPageSize()?。?！
                    List<Map<String, Object>> list = mapper.selectList(page1.getPageNum(),page1.getPageSize());
                    List<AltitudeMonExportExcelVO > SurfDayList = new ArrayList<>();
                   //寫入到excel:
                    /**************z只需要選擇一種方式即可*****************/
                    //這里可以通過設(shè)置includeColumnFiledNames、excludeColumnFiledNames導(dǎo)出什么字段，可以動態(tài)配置，前端傳過來那些列，就導(dǎo)出那些列
                    //方式1、只導(dǎo)出v01301這一列
                    Set<String> includeColumnFiledNames = new HashSet<String>();
                    includeColumnFiledNames.add("v01301");
                    WriteSheet writeSheet = EasyExcel.writerSheet(i, "Sheet" + (i + 1)).head(AltitudeMonExportExcelVO.class).includeColumnFiledNames(includeColumnFiledNames)
                            .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()).build();
                    //方式2、排除v01301這一列，其他的全部導(dǎo)出
                    Set<String> excludeColumnFiledNames = new HashSet<String>();
                    excludeColumnFiledNames.add("v01301");
                    WriteSheet writeSheet = EasyExcel.writerSheet(i, "Sheet" + (i + 1)).head(AltitudeMonExportExcelVO.class).excludeColumnFiledNames(includeColumnFiledNames)
                            .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()).build();
                    excelWriter.write(list, writeSheet);
                    //方式3、不做設(shè)置，全部導(dǎo)出
                    WriteSheet writeSheet = EasyExcel.writerSheet(i, "Sheet" + (i + 1)).head(AltitudeMonExportExcelVO.class)
                            .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()).build();
                    excelWriter.write(list, writeSheet);
                }
            }
            // 下載EXCEL，返回給前段stream流
            response.setContentType("application/octet-stream");
            response.setCharacterEncoding("utf-8");
            response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
            excelWriter.finish();
            outputStream.flush();
            outputStream.close();
            log.info("*********地面統(tǒng)計(jì)查詢列表導(dǎo)出結(jié)束！**************");
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            if (outputStream != null) {
                outputStream.close();
            }
        }

    }

4.配置類

public class ExcelConstants {
    //一個(gè)sheet裝100w數(shù)據(jù)
    public static final Integer PER_SHEET_ROW_COUNT = 1000000;
    //每次查詢20w數(shù)據(jù)，每次寫入20w數(shù)據(jù)
    public static final Integer PER_WRITE_ROW_COUNT = 200000;
}

至此就完成導(dǎo)出。文章來源地址http://www.zghlxwxcb.cn/news/detail-504373.html

到了這里，關(guān)于使用EasyExcel實(shí)現(xiàn)excel導(dǎo)出，支持百萬大數(shù)據(jù)量導(dǎo)出-----超簡單的文章就介紹完了。如果您還想了解更多內(nèi)容，請?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！