通过设置sheet数量，完成分批导出，每个sheet存100万数据，每次查询插入20万数据，避免超时，内存溢出等问题，可以根据服务器配置调整参数设置。
1.引入依赖

com.alibabaeasyexcel3.0.5

2.创建对应的实体类
@ExcelProperty设置的就是导出的列名，还可以设置排序等等

@Data@NoArgsConstructor@AllArgsConstructorpublic 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 = "月平均温度露点差(露点温度)")private String v12301_701="0";}

3.核心导出代码

public void exportSurfList2(StatisticsRequestVo requestVo,HttpServletResponse response) throws IOException {log.info("*********地面统计查询列表导出开始！**************");//文件名String fileName =String.valueOf(System.currentTimeMillis());OutputStream outputStream =null;try {//记录总数:实际中需要根据查询条件进行统计即可:一共多少条int totalCount=mapper.seleCount();//每一个Sheet存放100w条数据Integer sheetDataRows = ExcelConstants.PER_SHEET_ROW_COUNT;//每次写入的数据量20w,每页查询20WInteger writeDataRows = ExcelConstants.PER_WRITE_ROW_COUNT;//计算需要的Sheet数量Integer sheetNum = totalCount % sheetDataRows == 0 ? (totalCount / sheetDataRows) : (totalCount / sheetDataRows + 1);//计算一般情况下每一个Sheet需要写入的次数(一般情况不包含最后一个sheet,因为最后一个sheet不确定会写入多少条数据)Integer oneSheetWriteCount = sheetDataRows / writeDataRows;//计算最后一个sheet需要写入的次数Integer lastSheetWriteCount = totalCount % sheetDataRows == 0 ? oneSheetWriteCount : (totalCount % sheetDataRows % writeDataRows == 0 ? (totalCount / sheetDataRows / writeDataRows) : (totalCount / sheetDataRows / writeDataRows + 1));outputStream = response.getOutputStream();//必须放到循环外，否则会刷新流ExcelWriter excelWriter = EasyExcel.write(outputStream).build();//开始分批查询分次写入for (int i = 0; i < sheetNum; i++) {//创建SheetWriteSheet sheet = new WriteSheet();sheet.setSheetName("Sheet"+i);sheet.setSheetNo(i);//循环写入次数: j的自增条件是当不是最后一个Sheet的时候写入次数为正常的每个Sheet写入的次数,如果是最后一个就需要使用计算的次数lastSheetWriteCountfor (int j = 0; j < (i != sheetNum - 1 ? oneSheetWriteCount : lastSheetWriteCount); j++) {//分页查询一次20wPage page1 = new Page(j + 1 + oneSheetWriteCount * i, writeDataRows);//查询分页列表---按照自己的业务查列表，分页这个一定要使用这个：page1.getPageNum(),page1.getPageSize()！！！List<Map> list = mapper.selectList(page1.getPageNum(),page1.getPageSize());List SurfDayList = new ArrayList(); //写入到excel:/**************z只需要选择一种方式即可*****************///这里可以通过设置includeColumnFiledNames、excludeColumnFiledNames导出什么字段，可以动态配置，前端传过来那些列，就导出那些列//方式1、只导出v01301这一列Set includeColumnFiledNames = new HashSet();includeColumnFiledNames.add("v01301");WriteSheet writeSheet = EasyExcel.writerSheet(i, "Sheet" + (i + 1)).head(AltitudeMonExportExcelVO.class).includeColumnFiledNames(includeColumnFiledNames).registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()).build();//方式2、排除v01301这一列，其他的全部导出Set excludeColumnFiledNames = new HashSet();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、不做设置，全部导出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("*********地面统计查询列表导出结束！**************");} catch (Exception e) {e.printStackTrace();}finally {if (outputStream != null) {outputStream.close();}}}

4.配置类

public class ExcelConstants {//一个sheet装100w数据public static final Integer PER_SHEET_ROW_COUNT = 1000000;//每次查询20w数据，每次写入20w数据public static final Integer PER_WRITE_ROW_COUNT = 200000;}

至此就完成导出。