java写excel数量_java excel大数量导出（基于阿里easyexcel）

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

com.alibaba

easyexcel

2.2.6

org.apache.poi

poi

4.1.0

org.apache.poi

poi-ooxml-schemas

4.1.0

org.apache.poi

poi-ooxml

4.1.0

cn.hutool

hutool-all

5.5.1

compile

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

@RequestMapping(name = "数据导出接口", value = "getInsList.json", method = RequestMethod.POST)

public void getInsList(@ApiParam(value = "分页对象数据", required = true) @RequestBody InspectionInfoBean 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(), InspectionInfoExportDto.class).build();

for (int i = 1; i < 200; i++) {

// 分页去数据库查询数据 这里可以去数据库查询每一页的数据 这里你根据自身的分页去实现就行

PageBean pb = new PageBean<>();

pb.setBean(bean);

int pageSize = 100000;

pb.setStart((i - 1) * pageSize);

pb.setLength(pageSize);

List data = inspectionInfoService.getInsPageList(pb);

if(data == null){

if(i == 1){

errorHandle(response,"没有查询到结果");

}

return;

}

//这方法封装数据用 这个可以根据你自身业务处理

translationField(data);

WriteSheet writeSheet = EasyExcel.writerSheet(i,"列表数据导出"+i).build();

excelWriter.write(data,writeSheet);

//没数据退出

if(data.size() < pageSize){

return;

}

data.clear();

}

}catch (Exception e){

log.error("查验列表数据导出异常 ",e);

errorHandle(response,"导出异常"+e.getMessage());

}finally {

exportNumber.decrementAndGet();

if (excelWriter != null) {

excelWriter.finish();

}

}

}

public void errorHandle(HttpServletResponse response,String errMsg) {

InspectionInfoExportDto inspectionInfoExportDto = new InspectionInfoExportDto();

inspectionInfoExportDto.setInsid(errMsg);

try {

EasyExcel.write(response.getOutputStream(), InspectionInfoExportDto.class).sheet("查验列表数据导出").doWrite(Arrays.asList(inspectionInfoExportDto));

} 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 InspectionInfoExportDto 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 {

@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);

}

}

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

public void exportStreamDataZip(RecoveryOtherBean roBean,String fileName,Set excludeColumnFiledNames, HttpServletResponse response){

List beans = new ArrayList<>(1000);

ExcelWriter excelWriter = null;

try {

excelWriter = EasyExcel.write(response.getOutputStream(), ***.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 allStream = (DefaultCursor) necdMapper.getAllStream(roBean);

Iterator iterator = allStream.iterator();

while (iterator.hasNext()){

RecoveryOtherBean next = iterator.next();

//满足多少条的时候就写一波

if (beans.size()<10000&&!allStream.isConsumed()){

beans.add(next);

iterator.remove();

}else {

//满足条件开始执行写 伪代码(自己实现写)

WriteSheet writeSheet = EasyExcel.writerSheet("列表数据导出").build();

excelWriter.write(beans,writeSheet);

beans.clear();

}

}

}catch (Exception e){

e.printStackTrace();

}finally {

if (excelWriter != null) {

excelWriter.finish();

}

}

}

mapper 的语句 拿到游标Cursor

Cursor getAllStream(RecoveryOtherBean bean);