SuperAPI AI Directory
Partner with us中文AI newsFavorites
Skillseasyexcel-read

easyexcel-read

Office & productivity

View repository

Updated 10-16 08:47

About this skill

EasyExcel Data Reading (Read)

Official documentation:https://easyexcel.opensource.alibaba.com/docs/current/quickstart/read Official examples:https://github.com/alibaba/easyexcel/blob/master/easyexcel-test/src/test/java/com/alibaba/easyexcel/test/demo/read/ReadTest.java Current version:com.alibaba:easyexcel:4.0.3(Apache-2.0, repository archived, bug fixes only)

This skill focuses on the "reading Excel data" scenario. When the user needs to parse .xlsx/.xls files (data import, batch database insertion, reconciliation file parsing, user uploads), this skill should be used.

Capability Boundaries

✅ Strengths

  1. Listener mode (streaming, no OOM for large files)
  2. Synchronous reading (returns a List all at once for small files)
  3. Multi-Sheet reading (all / partial)
  4. Multi-row header support (headRowNumber）
  5. Custom converters (date/number/enum)
  6. Exception handling (row-level fault tolerance)
  7. Reading extra information (comments/hyperlinks/merged cells)
  8. Formulas and raw cell types
  9. Reading without creating objects (directly get a Map)
  10. Reading files uploaded via Web

⚠️ Limitations

  1. Listeners cannot be managed by Spring (must be new'ed, and fields need to be passed Spring Beans via constructor)
  2. A sheet cannot be read repeatedly (multiple reads require reconstructing the reader)
  3. For 03 version multi-sheet, pass them in all at once to avoid repeated parsing
  4. headRowNumber When not specified, it is inferred from the @ExcelProperty#value() number of headers
  5. doReadSync() Not recommended for large data volumes

❌ Out of Scope (scenarios where this skill should not be used; please use another skill instead)

  1. Fill according to a pre-made template → Not applicablethis skill, use easyexcel-fill
  2. Programmatically generate Excel → Not applicablethis skill, use easyexcel-write
  3. CSV file → Not applicablethis skill, use commons-csv / OpenCSV on your own (EasyExcel also supports CSV, requires excelType(CSV)）
  4. Word/PPT → Not applicablethis skill, use Apache POI

Data Privacy

This skill does not collect, store, or transmit any user data. All code examples are for local development reference only.

When to use this skill

  • The user says "read Excel", "parse Excel", "import Excel", "upload Excel"
  • The user uploads a .xlsx file and requests that the data be read as Java objects or a Map
  • There are business requirements such as "batch database insertion", "reconciliation file parsing", "dynamic column reading", etc.
  • Need to read extra information such as comments/hyperlinks/merged cells

Quick Start

Typical invocation methods (4 ways)：

// 写法 1：JDK8+ PageReadListener（since 3.0.0-beta1，最简洁）
EasyExcel.read(fileName, DemoData.class, new PageReadListener<DemoData>(dataList -> {
    for (DemoData data : dataList) {
        log.info("读取到数据: {}", JSON.toJSONString(data));
    }
})).sheet().doRead();

// 写法 2：匿名 ReadListener
EasyExcel.read(fileName, DemoData.class, new ReadListener<DemoData>() {
    @Override public void invoke(DemoData data, AnalysisContext context) { /* 每行回调 */ }
    @Override public void doAfterAllAnalysed(AnalysisContext context) { /* 全部完成 */ }
}).sheet().doRead();

// 写法 3：自定义 ReadListener
EasyExcel.read(fileName, DemoData.class, new DemoDataListener()).sheet().doRead();

// 写法 4：ExcelReader（一个文件一个 reader）
try (ExcelReader excelReader = EasyExcel.read(fileName, DemoData.class,
        new DemoDataListener()).build()) {
    ReadSheet readSheet = EasyExcel.readSheet(0).build();
    excelReader.read(readSheet);
}

Original official text: "By default, PageReadListener reads 100 rows of data each time and then returns them; just call it to use the data directly"

Workflow

Step 1. Confirm the scenario — data scale, whether extra information is included, whether there is a DTO Step 2. Define the data model — write the DTO/VO, annotate with @ExcelProperty and other annotations Step 3. Choose the reading method — listener (streaming) / synchronous (small files) / without creating objects Step 4. Implement ReadListener — inject DAO (constructor); handle invoke / doAfterAllAnalysed / onException / invokeHead / extra Step 5. Execute reading — doRead() or excelReader.read(readSheet) Step 6. Validate output — check row count, conversion exceptions, comment/hyperlink capture

Critical: Mode selection decisions

数据规模？
├── < 1000 行 + 一次性返回 → 同步模式：doReadSync()
├── < 1 万行 + 简单处理 → PageReadListener
├── > 1 万行 + 入库 → ReadListener + 批量入库（每 2000 行）
└── 不需要对象 + 动态列 → ReadListener<Map<Integer, String>>

需要额外信息？
├── 批注 → extraRead(CellExtraTypeEnum.COMMENT)
├── 超链接 → extraRead(CellExtraTypeEnum.HYPERLINK)
└── 合并单元格 → extraRead(CellExtraTypeEnum.MERGE)

需要多 Sheet？
├── 全部 → doReadAll()
└── 部分 → ExcelReader.read(readSheet1, readSheet2, ...)

Critical: Standard listener template

@Slf4j
public class DemoDataListener implements ReadListener<DemoData> {
    /**
     * 官方原文："DemoDataListener 不能被spring管理，要每次读取excel都要new"
     */
    private static final int BATCH_COUNT = 100;
    private List<DemoData> cachedDataList =
        ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
    private DemoDAO demoDAO;

    public DemoDataListener() {
        demoDAO = new DemoDAO();
    }

    /**
     * 官方原文："如果使用了spring,请使用这个构造方法。
     *            每次创建Listener的时候需要把spring管理的类传进来"
     */
    public DemoDataListener(DemoDAO demoDAO) {
        this.demoDAO = demoDAO;
    }

    @Override
    public void invoke(DemoData data, AnalysisContext context) {
        log.info("解析到一条数据:{}", JSON.toJSONString(data));
        cachedDataList.add(data);
        if (cachedDataList.size() >= BATCH_COUNT) {
            saveData();
            cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
        }
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        saveData();  // ⚠️ 处理最后一批
        log.info("所有数据解析完成！");
    }

    private void saveData() {
        log.info("{}条数据，开始存储数据库！", cachedDataList.size());
        demoDAO.save(cachedDataList);
        log.info("存储数据库成功！");
    }
}

// 持久层（官方原文："如果是mybatis,尽量别直接调用多次insert,
//            自己写一个mapper里面新增一个方法batchInsert"）
public class DemoDAO {
    public void save(List<DemoData> list) {
        // mybatis batchInsert
    }
}

Reference Library (load on demand)

Detailed code and comments have been split into references/. Load by scenario:

API & Basic Modes

Scenario-based (multiple header rows / synchronous / header / extra information / formulas / exceptions / no object creation / Web upload)

Troubleshooting

References