Master EasyExcel: Fast Java Excel Export/Import with Custom Merges

Introduction

EasyExcel is a Java library that provides fast, memory‑efficient Excel read/write capabilities, especially for large files.

Quick Start

Add Dependency

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

Simple Export

Example of exporting user information using EasyExcel.

Define Entity

@Data
public class UserDO {
    @ExcelProperty("用户编号")
    @ColumnWidth(20)
    private Long id;

    @ExcelProperty("用户名")
    @ColumnWidth(20)
    private String username;

    @ExcelIgnore
    private String password;

    @ExcelProperty("昵称")
    @ColumnWidth(20)
    private String nickname;

    @ExcelProperty("生日")
    @ColumnWidth(20)
    @DateTimeFormat("yyyy-MM-dd")
    private Date birthday;

    @ExcelProperty("手机号")
    @ColumnWidth(20)
    private String phone;

    @ExcelProperty("身高（米）")
    @NumberFormat("#.##")
    @ColumnWidth(20)
    private Double height;

    @ExcelProperty(value = "性别", converter = GenderConverter.class)
    @ColumnWidth(10)
    private Integer gender;
}

Key annotations used in the entity:

@ExcelProperty : sets the column header and optionally a converter.

@ColumnWidth : defines the column width.

@DateTimeFormat : formats date values.

@NumberFormat : formats numeric values.

Custom Converter

Implement a Converter to map enum values to readable strings, e.g., gender.

public class GenderConverter implements Converter<Integer> {
    @Override
    public Class<?> supportJavaTypeKey() { return Integer.class; }
    @Override
    public CellDataTypeEnum supportExcelTypeKey() { return CellDataTypeEnum.STRING; }
    @Override
    public Integer convertToJavaData(ReadConverterContext<?> context) {
        return GenderEnum.convert(context.getReadCellData().getStringValue()).getValue();
    }
    @Override
    public WriteCellData<?> convertToExcelData(WriteConverterContext<Integer> context) {
        return new WriteCellData<>(GenderEnum.convert(context.getValue()).getDescription());
    }
}

public enum GenderEnum {
    UNKNOWN(0, "未知"),
    MALE(1, "男性"),
    FEMALE(2, "女性");
    private final Integer value;
    private final String description;
    // getters, constructors, and static convert methods omitted for brevity
}

Define API for Export

@RestController
@RequestMapping("/excel")
public class ExcelController {
    @GetMapping("/export/user")
    public void exportUserExcel(HttpServletResponse response) {
        try {
            setExcelResponseProp(response, "用户列表");
            List<UserDO> userList = getUserList();
            EasyExcel.write(response.getOutputStream())
                     .head(UserDO.class)
                     .excelType(ExcelTypeEnum.XLSX)
                     .sheet("用户列表")
                     .doWrite(userList);
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
    }
    // setExcelResponseProp and getUserList implementations omitted for brevity
}

Complex Export (One‑to‑Many)

EasyExcel does not natively support one‑to‑many export; a custom merge strategy is required.

Custom Merge Annotation

@Target({ElementType.FIELD})
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface ExcelMerge {
    boolean merge() default true;
    boolean isPrimaryKey() default false;
}

Merge Strategy Implementation

public class ExcelMergeStrategy implements RowWriteHandler {
    private Integer primaryKeyIndex;
    private final List<Integer> mergeColumnIndexList = new ArrayList<>();
    private final Class<?> elementType;
    public ExcelMergeStrategy(Class<?> elementType) { this.elementType = elementType; }
    @Override
    public void afterRowDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder,
                               Row row, Integer relativeRowIndex, Boolean isHead) {
        if (isHead) return;
        Sheet sheet = writeSheetHolder.getSheet();
        if (primaryKeyIndex == null) initPrimaryIndexAndMergeIndex(writeSheetHolder);
        if (row.getRowNum() <= 1) return;
        Row lastRow = sheet.getRow(row.getRowNum() - 1);
        if (lastRow.getCell(primaryKeyIndex).getStringCellValue()
                .equalsIgnoreCase(row.getCell(primaryKeyIndex).getStringCellValue())) {
            for (Integer mergeIndex : mergeColumnIndexList) {
                CellRangeAddress range = new CellRangeAddress(row.getRowNum() - 1, row.getRowNum(), mergeIndex, mergeIndex);
                sheet.addMergedRegionUnsafe(range);
            }
        }
    }
    private void initPrimaryIndexAndMergeIndex(WriteSheetHolder writeSheetHolder) {
        Sheet sheet = writeSheetHolder.getSheet();
        Row titleRow = sheet.getRow(0);
        Field[] fields = elementType.getDeclaredFields();
        for (int i = 0; i < fields.length; i++) {
            Field field = fields[i];
            ExcelProperty prop = field.getAnnotation(ExcelProperty.class);
            if (prop == null) continue;
            ExcelMerge merge = field.getAnnotation(ExcelMerge.class);
            if (merge == null) continue;
            Cell cell = titleRow.getCell(i);
            if (cell == null) continue;
            if (prop.value()[0].equalsIgnoreCase(cell.getStringCellValue())) {
                if (merge.isPrimaryKey()) primaryKeyIndex = i;
                if (merge.merge()) mergeColumnIndexList.add(i);
            }
        }
        if (primaryKeyIndex == null) {
            throw new IllegalStateException("@ExcelMerge must specify a primary key");
        }
    }
}

Export Order Example Using Merge Strategy

@GetMapping("/export/order")
public void exportOrderExcel(HttpServletResponse response) {
    try {
        setExcelResponseProp(response, "订单列表");
        List<OrderDO> orderList = getOrderList();
        List<OrderBO> exportData = convert(orderList);
        EasyExcel.write(response.getOutputStream())
                 .head(OrderBO.class)
                 .registerWriteHandler(new ExcelMergeStrategy(OrderBO.class))
                 .excelType(ExcelTypeEnum.XLSX)
                 .sheet("订单列表")
                 .doWrite(exportData);
    } catch (IOException e) {
        throw new RuntimeException(e);
    }
}

Simple Import

@PostMapping("/import/user")
public ResponseVO importUserExcel(@RequestPart("file") MultipartFile file) {
    try {
        List<UserDO> userList = EasyExcel.read(file.getInputStream())
                .head(UserDO.class)
                .sheet()
                .doReadSync();
        return ResponseVO.success(userList);
    } catch (IOException e) {
        return ResponseVO.error();
    }
}

Testing can be performed with tools such as Postman or Apifox; note that in Apifox the download icon must be clicked to retrieve the generated Excel file.

References

https://github.com/alibaba/easyexcel

https://www.yuque.com/easyexcel/doc/easyexcel

https://github.com/alibaba/easyexcel/issues/1780