Master Efficient Excel Import/Export in Java with EasyExcel and Thread Pools

Why EasyExcel for Java Web Projects

Java web development often requires importing and exporting Excel files. Traditional tools like EasyPOI or Hutool can cause high memory consumption when processing large datasets. EasyExcel, combined with Java 8 functional programming, offers a lightweight solution that mitigates OOM risks.

Optimization Strategies

Use Java 8 functional programming to reduce boilerplate code for data import.

Apply reflection to create a generic interface that can import any Excel‑mapped object.

Employ a thread‑pool to process massive Excel files without blocking the main thread.

Leverage generics to implement flexible data export.

Maven Dependency

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

Importing a Single Sheet with Generics

Define a POJO that represents the Excel row and annotate it with EasyExcel annotations.

@Data
@NoArgsConstructor
@AllArgsConstructor
@TableName("stu_info")
@ApiModel("学生信息")
public class StuInfo implements Serializable {
    private static final long serialVersionUID = 1L;

    @ColumnWidth(10)
    @ApiModelProperty("姓名")
    @ExcelProperty(value = "姓名", order = 0)
    @ExportHeader(value = "姓名", index = 1)
    private String name;

    @ExcelProperty(value = "年龄", order = 1)
    @ApiModelProperty("年龄")
    @ExportHeader(value = "年龄", index = 2)
    private Integer age;

    @ExcelProperty(value = "身高", order = 2)
    @ApiModelProperty("身高")
    @ExportHeader(value = "身高", index = 4)
    private Double tall;

    @ExcelProperty(value = "自我介绍", order = 3)
    @ApiModelProperty("自我介绍")
    @ExportHeader(value = "自我介绍", index = 3, ignore = true)
    private String selfIntroduce;

    @ExcelProperty(value = "图片信息", order = 4)
    @ApiModelProperty("图片信息")
    @ExportHeader(value = "图片信息", ignore = true)
    private Blob picture;

    @ExcelProperty(value = "性别", order = 5)
    @ApiModelProperty("性别")
    private Integer gender;

    @DateTimeFormat("yyyy-MM-dd HH:mm:ss")
    @ExcelProperty(value = "入学时间", order = 6)
    @ApiModelProperty("入学时间")
    private String intake;

    @ExcelProperty(value = "出生日期", order = 7)
    @ApiModelProperty("出生日期")
    private String birthday;
}

Custom ReadListener for Batch Processing

The listener stores rows in batches to avoid memory overflow.

@Slf4j
public class UploadDataListener<T> implements ReadListener<T> {
    private static final int BATCH_COUNT = 100;
    private List<T> cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
    private Predicate<T> predicate;
    private Consumer<Collection<T>> consumer;

    public UploadDataListener(Predicate<T> predicate, Consumer<Collection<T>> consumer) {
        this.predicate = predicate;
        this.consumer = consumer;
    }

    public UploadDataListener(Consumer<Collection<T>> consumer) {
        this.consumer = consumer;
    }

    @Override
    public void invoke(T data, AnalysisContext context) {
        if (predicate != null && !predicate.test(data)) {
            return;
        }
        cachedDataList.add(data);
        if (cachedDataList.size() >= BATCH_COUNT) {
            try {
                consumer.accept(cachedDataList);
            } catch (Exception e) {
                log.error("Failed to upload data! data={}", cachedDataList);
                throw new BizException("导入失败");
            }
            cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
        }
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        if (CollUtil.isNotEmpty(cachedDataList)) {
            try {
                consumer.accept(cachedDataList);
                log.info("所有数据解析完成！");
            } catch (Exception e) {
                log.error("Failed to upload data! data={}", cachedDataList);
                if (e instanceof BizException) {
                    throw e;
                }
                throw new BizException("导入失败");
            }
        }
    }
}

Controller Example for Object‑Based Import

@ApiOperation("只需要一个readListener，解决全部的问题")
@PostMapping("/update")
@ResponseBody
public R<String> aListener4AllExcel(MultipartFile file) throws IOException {
    try {
        EasyExcel.read(file.getInputStream(), StuInfo.class,
                new UploadDataListener<>(list -> {
                    ValidationUtils.validate(list);
                    service.saveBatch(list);
                    log.info("从Excel导入数据一共 {} 行", list.size());
                }))
                .sheet()
                .doRead();
    } catch (IOException e) {
        log.error("导入失败", e);
        throw new BizException("导入失败");
    }
    return R.success("SUCCESS");
}

Importing Arbitrary Data via Map

When the target entity is unknown, read each row as a Map<Integer, String> and process it generically.

@Slf4j
public class NonClazzOrientedListener implements ReadListener<Map<Integer, String>> {
    private static final int BATCH_COUNT = 100;
    private List<List<Object>> rowsList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
    private List<Object> rowList = new ArrayList<>();
    private Predicate<Map<Integer, String>> predicate;
    private Consumer<List> consumer;

    public NonClazzOrientedListener(Predicate<Map<Integer, String>> predicate, Consumer<List> consumer) {
        this.predicate = predicate;
        this.consumer = consumer;
    }

    public NonClazzOrientedListener(Consumer<List> consumer) {
        this.consumer = consumer;
    }

    @Override
    public void invoke(Map<Integer, String> row, AnalysisContext context) {
        row.forEach((k, v) -> rowList.add(v == null ? "" : v));
        rowsList.add(new ArrayList<>(rowList));
        rowList.clear();
        if (rowsList.size() > BATCH_COUNT) {
            consumer.accept(rowsList);
            rowsList.clear();
        }
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        consumer.accept(rowsList);
        rowsList.clear();
    }
}

Header Mapping for Unordered Templates

Read the header row, compare it with the database column list, and keep only matching columns to ensure correct ordering.

@Override
public void invokeHead(Map<Integer, ReadCellData<?>> headMap, AnalysisContext context) {
    Map<Integer, String> columnMap = ConverterUtils.convertToStringMap(headMap, context);
    Map<String, String> columnList = new HashMap<>(); // fetch from DB
    List<Integer> filterList = new ArrayList<>();
    columnMap.forEach((idx, name) -> {
        if (columnList.containsKey(name)) {
            filterList.add(idx);
        }
    });
    log.info("解析到一条头数据:{}", JSON.toJSONString(columnMap));
}

Thread‑Pool Assisted Large‑Scale Import

Because the ReadListener cannot be managed by Spring IoC, instantiate it outside the container and submit the processing tasks to a thread pool to fully utilize CPU resources.

Generic Export Methods

public <T> void commonExport(String fileName, List<T> data, Class<T> clazz, HttpServletResponse response) throws IOException {
    if (CollectionUtil.isEmpty(data)) {
        data = new ArrayList<>();
    }
    fileName = URLEncoder.encode(fileName, "UTF-8");
    response.setContentType("application/vnd.ms-excel");
    response.setCharacterEncoding("utf-8");
    response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
    EasyExcel.write(response.getOutputStream()).head(clazz).sheet("sheet1").doWrite(data);
}

public void exportFreely(String fileName, List<List<Object>> data, List<List<String>> head, HttpServletResponse response) throws IOException {
    if (CollectionUtil.isEmpty(data)) {
        data = new ArrayList<>();
    }
    fileName = URLEncoder.encode(fileName, "UTF-8");
    response.setContentType("application/vnd.ms-excel");
    response.setCharacterEncoding("utf-8");
    response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
    EasyExcel.write(response.getOutputStream()).head(head).sheet("sheet1").doWrite(data);
}

These utilities provide a unified way to export both typed objects and arbitrary tabular data, with optional filtering and dynamic column selection.