导出上万条数据excel，避免内存溢出解决方案

步骤1.项目中的pom.xml文件引入hutool的jar <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>4.1.2</version> </dependency> <dependency> <groupId>cn.hutool</groupId> <artifactId>hutool-all</artifactId> <version>5.8.0.M3</version> </dependency>

步骤2.创建测试对象【你的项目需要引入Lombok插件，否则注解报错，具体怎么引入可以百度】，如下：

import lombok.AllArgsConstructor; import lombok.Builder; import lombok.Data; import lombok.NoArgsConstructor;

import java.io.Serializable; import java.util.Date;

/** * <p> * 设备基本信息表 * </p> * * @author wcl * @since 2021-12-25 */ @Data @Builder @NoArgsConstructor @AllArgsConstructor public class BasTDevice implements Serializable {

private static final long serialVersionUID = -349318113259997487L;

private String id;

private String planId;

private String devNo;

private String devModel;

private String productNo;

private Date buyDate;

private String qrCodeUrl;

private String remark;

}

步骤3.直接上代码，【请在你的代码逻辑中直接调用该方法即可】

/** * desc * @author wcl * @date 2022/4/21 16:03 * @Param mulSheet true代表将数据写入到多个sheet中，否则只写入到一个sheet中 * @return **/ public void importDataToExcel(HttpServletResponse response,boolean mulSheet) { ExcelWriter writer=null; ServletOutputStream out=null; try{ List<BasDevice> rows=new ArrayList<>(); for(int i=0;i<81000;i++){ rows.add(BasDevice.builder() .id((i+1)+"") .qrCodeUrl(RandomUtil.randomString(30)) .buyDate(new Date()) .devNo(RandomUtil.randomString(30)) .planId("12345") .devModel(RandomUtil.randomNumbers(10)+"") .productNo("ABC_"+RandomUtil.randomNumbers(8)) .remark("导出OK") .build() ); } writer= ExcelUtil.getBigWriter(); writer.renameSheet("数据导出万条"); LinkedMap<String, String> headerAlias=new LinkedMap<>(); headerAlias.put("id","编号"); headerAlias.put("qrCodeUrl","地址"); headerAlias.put("buyDate","购买日期"); headerAlias.put("devNo","设备编码"); headerAlias.put("planId","计划id"); headerAlias.put("devModel","设备型号"); headerAlias.put("productNo","产品编码"); headerAlias.put("remark","备注"); writer.setHeaderAlias(headerAlias); for(int i=0;i<headerAlias.size();i++){ writer.setColumnWidth(i,25); } // 设置只导出有别名的字段 writer.setOnlyAlias(true); // 设置冻结行 writer.setFreezePane(1); if(mulSheet){ int page=((int)rows.size()/20000)+(rows.size()%20000>0?1:0); writer.renameSheet("导出万条数据1"); List<BasDevice> list=new ArrayList<>(); for(int i=1;i<=page;i++){ //每个sheet20000条数据 writer.setSheet("导出万条数据"+i); for(int j=0;j<headerAlias.size();j++){ writer.setColumnWidth(j,25); } // 设置只导出有别名的字段 writer.setOnlyAlias(true); // 设置冻结行 writer.setFreezePane(1); int finalI = i; list=rows.parallelStream().filter(x->Integer.parseInt(x.getId())>(finalI -1)*20000 && Integer.parseInt(x.getId())<20000*finalI+1).collect(Collectors.toList()); writer.write(list,true); } }else{ writer.write(rows,true); } response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8"); response.setHeader("Content-Disposition","attachment;filename=test.xlsx"); out=response.getOutputStream(); writer.flush(out, true); }catch (Exception e){ e.printStackTrace(); }finally { if(writer!=null){ writer.close(); } if(out!=null){ IoUtil.close(out); } } }

4.导出结果示例

导出到一个sheet

导出到多个sheet,我的是每个sheet设置导入2W条