Wrapping EasyExcel for Simplified Import and Export in Spring Boot Applications

Environment Preparation

Development environment: Spring Boot, MyBatis‑Plus, and a MySQL database.

Database definition (DDL):

-- `dfec-tcht-platform-dev`.test definition
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 Excel service in the controller

@Autowired
ExcelService excelService;

Step 2: Annotate entity fields with EasyExcel annotations

/**
 * 【Please fill feature name】 object test
 * @author trg
 * @date Fri Jan 19 14:14:08 CST 2024
 */
@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 = "创建时间")
    private Date bornDate;
}

Step 3: Implement import and export endpoints

@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);
}

Complete Code

Controller:

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;

/**
 * @author trg
 * @title: TestController
 */
@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);
    }
}

ExcelService Interface (simplified):

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

DefaultExcelServiceImpl (implementation highlights):

@Service
public class DefaultExcelServiceImpl implements ExcelService {
    @Override
    public
void exportExcel(List
list, Class
tClass, HttpServletResponse response) throws IOException {
        setResponse(response);
        EasyExcel.write(response.getOutputStream())
                 .head(tClass)
                 .excelType(ExcelTypeEnum.XLSX)
                 .registerConverter(new LocalDateTimeConverter())
                 .sheet("工作簿1")
                 .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");
    }
}

ExcelUtils (utility class) and ExcelListener (listener) are also provided to read/write Excel files, handle templates, exclude columns, and batch processing.

Encountered Issues

Date format mismatch when using template export/import : Implement a custom DateConverter and annotate date fields with converter = DateConverter.class .

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

LocalDateTime conversion : Use LocalDateTimeConverter to map between LocalDateTime and string representation.

Legacy Problem

The current EasyExcel version (3.3.2) only supports .xls when exporting with a template; .xlsx export is not yet functional.

References

EasyExcel official documentation: https://easyexcel.opensource.alibaba.com/docs/current/

Additional promotional and community links are omitted as they do not contribute to the technical content.