Guide to Using EasyExcel in Java: Quick Start, Export/Import, and Custom Merge Strategies

Introduction

EasyExcel is a Java‑based, fast, and concise tool for handling large Excel files without running into memory overflow issues. It simplifies reading and writing Excel data while abstracting performance and memory concerns.

Quick Start

Import Dependency

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

Simple Export

Exporting user information is demonstrated step‑by‑step.

Define Entity Class

The UserDO class uses EasyExcel annotations to map fields to Excel columns.

/**
 * User information
 *
 * @author william@StarImmortal
 */
@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;
}

The core annotations used are:

@ExcelProperty : sets the header name and optional converter.

@ColumnWidth : defines column width.

@DateTimeFormat : formats date values.

@NumberFormat : formats numeric values.

Custom Converter

To convert enum values (e.g., gender) to strings, implement the Converter interface.

/**
 * Excel gender converter
 *
 * @author william@StarImmortal
 */
public class GenderConverter implements Converter
{
    @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
context) {
        return new WriteCellData<>(GenderEnum.convert(context.getValue()).getDescription());
    }
}

/**
 * Gender enum
 *
 * @author william@StarImmortal
 */
@Getter
@AllArgsConstructor
public enum GenderEnum {
    UNKNOWN(0, "未知"),
    MALE(1, "男性"),
    FEMALE(2, "女性");

    private final Integer value;
    @JsonFormat
    private final String description;

    public static GenderEnum convert(Integer value) {
        return Stream.of(values()).filter(e -> e.value.equals(value)).findAny().orElse(UNKNOWN);
    }

    public static GenderEnum convert(String description) {
        return Stream.of(values()).filter(e -> e.description.equals(description)).findAny().orElse(UNKNOWN);
    }
}

Define Export Interface

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

    private void setExcelResponseProp(HttpServletResponse response, String rawFileName) throws UnsupportedEncodingException {
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding("utf-8");
        String fileName = URLEncoder.encode(rawFileName, "UTF-8").replaceAll("\\+", "%20");
        response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
    }

    private List
getUserList() throws IOException {
        ObjectMapper mapper = new ObjectMapper();
        ClassPathResource resource = new ClassPathResource("mock/users.json");
        InputStream is = resource.getInputStream();
        return mapper.readValue(is, new TypeReference
>() {});
    }
}

Test the endpoint with Postman or Apifox; note that in Apifox you must click the download icon in the response to save the file.

Complex Export (One‑to‑Many)

EasyExcel does not support one‑to‑many export out of the box. A custom merge strategy can be used to achieve this.

Custom Annotation

/**
 * Annotation to indicate merge requirements and primary key.
 *
 * @author william@StarImmortal
 */
@Target({ElementType.FIELD})
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface ExcelMerge {
    boolean merge() default true;
    boolean isPrimaryKey() default false;
}

Entity with Merge Annotations

@Data
public class OrderBO {
    @ExcelProperty("订单主键")
    @ColumnWidth(16)
    @ExcelMerge(merge = true, isPrimaryKey = true)
    private String id;

    @ExcelProperty("订单编号")
    @ColumnWidth(20)
    @ExcelMerge(merge = true)
    private String orderId;

    @ExcelProperty("收货地址")
    @ColumnWidth(20)
    @ExcelMerge(merge = true)
    private String address;

    @ExcelProperty("创建时间")
    @ColumnWidth(20)
    @DateTimeFormat("yyyy-MM-dd HH:mm:ss")
    @ExcelMerge(merge = true)
    private Date createTime;

    @ExcelProperty({"商品信息", "商品编号"})
    @ColumnWidth(20)
    private String productId;

    @ExcelProperty({"商品信息", "商品名称"})
    @ColumnWidth(20)
    private String name;

    @ExcelProperty({"商品信息", "商品标题"})
    @ColumnWidth(30)
    private String subtitle;

    @ExcelProperty({"商品信息", "品牌名称"})
    @ColumnWidth(20)
    private String brandName;

    @ExcelProperty({"商品信息", "商品价格"})
    @ColumnWidth(20)
    private BigDecimal price;

    @ExcelProperty({"商品信息", "商品数量"})
    @ColumnWidth(20)
    private Integer count;
}

Custom Merge Strategy

/**
 * Custom cell merge strategy for EasyExcel.
 *
 * @author william@StarImmortal
 */
public class ExcelMergeStrategy implements RowWriteHandler {
    private Integer primaryKeyIndex;
    private final List
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++) {
            ExcelProperty prop = fields[i].getAnnotation(ExcelProperty.class);
            if (prop == null) continue;
            ExcelMerge merge = fields[i].getAnnotation(ExcelMerge.class);
            if (merge == null) continue;
            for (int col = 0; col < titleRow.getLastCellNum(); col++) {
                Cell cell = titleRow.getCell(col);
                if (cell == null) continue;
                if (prop.value()[0].equalsIgnoreCase(cell.getStringCellValue())) {
                    if (merge.isPrimaryKey()) primaryKeyIndex = col;
                    if (merge.merge()) mergeColumnIndexList.add(col);
                }
            }
        }
        if (primaryKeyIndex == null) throw new IllegalStateException("使用@ExcelMerge注解必须指定主键");
    }
}

Register the strategy in the controller:

@GetMapping("/export/order")
public void exportOrderExcel(HttpServletResponse response) {
    try {
        setExcelResponseProp(response, "订单列表");
        List
orderList = getOrderList();
        List
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);
    }
}

Test the order export endpoint similarly.

Simple Import

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

Reference links:

Project repository: https://github.com/alibaba/easyexcel

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

One‑to‑many export discussion: https://github.com/alibaba/easyexcel/issues/1780