Simplify Excel Import/Export in Spring Boot with an EasyExcel Wrapper

Environment Preparation

Development environment: Spring Boot + MyBatis‑Plus + database.

Database example:

CREATE TABLE `test` (
  `num` decimal(10,0) DEFAULT NULL COMMENT '数字',
  `sex` varchar(100) DEFAULT NULL COMMENT '性别',
  `name` varchar(100) DEFAULT NULL COMMENT '姓名',
  `born_date` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

Usage

Step 1: Inject the service

@Autowired
ExcelService excelService;

Step 2: Annotate fields Retain EasyExcel annotations on entity fields.

/**
 * Test entity
 */
@Data
@TableName("test")
public class TestEntity {
    @Schema(description = "数字")
    @ExcelProperty("数字")
    private BigDecimal num;

    @Schema(description = "性别")
    @ExcelProperty("性别")
    private String sex;

    @Schema(description = "姓名")
    @ExcelProperty("姓名")
    private String name;

    @Schema(description = "创建时间")
    @ExcelProperty(value = "创建时间", converter = DateConverter.class)
    private Date bornDate;
}

Step 3: Call import/export

@PostMapping("/importExcel")
public void importExcel(@RequestParam MultipartFile file){
    excelService.importExcel(file, TestEntity.class, 2, testService::saveBatch);
}

@PostMapping("/exportExcel")
public void exportExcel(HttpServletResponse response) throws IOException {
    excelService.exportExcel(testService.list(), TestEntity.class, response);
}

Full Controller Example

package com.dfec.server.controller;

import com.dfec.framework.excel.service.ExcelService;
import com.dfec.server.entity.TestEntity;
import com.dfec.server.service.TestService;
import lombok.RequiredArgsConstructor;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;

@RestController
@RequestMapping("test")
@RequiredArgsConstructor
public class TestController {
    private final ExcelService excelService;
    private final TestService testService;

    @PostMapping("/importExcel")
    public void importExcel(@RequestParam MultipartFile file){
        excelService.importExcel(file, TestEntity.class, 2, testService::saveBatch);
    }

    @PostMapping("/exportExcel")
    public void exportExcel(HttpServletResponse response) throws IOException {
        excelService.exportExcel(testService.list(), TestEntity.class, response);
    }
}

Implementation Details

The ExcelService interface defines overloaded methods for export and import, supporting plain export, type conversion, and template‑based export.

public interface ExcelService {
    <T> void exportExcel(List<T> list, Class<T> tClass, HttpServletResponse response) throws IOException;
    <T,R> void exportExcel(List<T> list, Function<T,R> map, Class<R> tClass, HttpServletResponse response) throws IOException;
    <T> void exportExcel(List<T> list, Class<T> tClass, String template, HttpServletResponse response) throws IOException;
    <T,R> void importExcel(MultipartFile file, Class<T> tClass, Integer headRowNumber, Function<T,R> map, Consumer<List<R>> consumer);
    <T> void importExcel(MultipartFile file, Class<T> tClass, Integer headRowNumber, Consumer<List<T>> consumer);
}

The default implementation uses Alibaba EasyExcel, registers a LocalDateTimeConverter for date handling, and sets proper response headers.

@Service
public class DefaultExcelServiceImpl implements ExcelService {
    @Override
    public <T> void exportExcel(List<T> list, Class<T> tClass, HttpServletResponse response) throws IOException {
        setResponse(response);
        EasyExcel.write(response.getOutputStream())
                 .head(tClass)
                 .excelType(ExcelTypeEnum.XLSX)
                 .registerConverter(new LocalDateTimeConverter())
                 .sheet("Sheet1")
                 .doWrite(list);
    }
    // other overloads omitted for brevity
    private void setResponse(HttpServletResponse response) throws UnsupportedEncodingException {
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        String fileName = URLEncoder.encode("data", "UTF-8").replaceAll("\\+", "%20");
        response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xls");
    }
}

Common Issues

Date format when using templates Solution: implement a custom DateConverter and specify it in the @ExcelProperty annotation.

public class DateConverter implements Converter<Date> {
    @Override
    public Date convertToJavaData(ReadConverterContext<?> context) throws Exception {
        // custom conversion logic
        return null;
    }
}

POI version compatibility Ensure the POI and ooxml versions match to avoid runtime errors.

LocalDateTime conversion Provide a LocalDateTimeConverter that formats and parses dates as "yyyy‑MM‑dd HH:mm:ss".

public class LocalDateTimeConverter implements Converter<LocalDateTime> {
    @Override
    public Class<LocalDateTime> supportJavaTypeKey() { return LocalDateTime.class; }
    @Override
    public CellDataTypeEnum supportExcelTypeKey() { return CellDataTypeEnum.STRING; }
    @Override
    public LocalDateTime convertToJavaData(ReadCellData cellData, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) {
        return LocalDateTime.parse(cellData.getStringValue(), DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss"));
    }
    @Override
    public WriteCellData<String> convertToExcelData(LocalDateTime value, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) {
        return new WriteCellData<>(value.format(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss")));
    }
}

Remaining Limitations

The current EasyExcel version (3.3.2) only supports template‑based export for XLS files; XLSX templates are not yet supported.

Reference: EasyExcel official documentation