Encapsulating EasyExcel for Simplified Import/Export in Spring Boot Applications

In order to avoid repetitive integration of the same dependencies across multiple project groups and to ensure a simple, uniform API for developers, the author encapsulates EasyExcel in a Spring Boot + MyBatis‑Plus environment.

Environment Preparation

Development environment: SpringBoot + MyBatis‑Plus + database.

Database table definition (SQL):

-- `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 Steps

Step 1 – Inject the service in the controller @Autowired ExcelService excelService;

Step 2 – Annotate entity fields The original EasyExcel annotations are retained. /** * 【请填写功能名称】对象 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); }

Full Controller Code package com.dfec.server.controller; import com.baomidou.mybatisplus.core.toolkit.IdWorker; import com.dfec.framework.excel.service.ExcelService; import com.dfec.server.entity.TestEntity; import com.dfec.server.entity.TestVo; 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; import java.util.function.Function; /** * @author trg * @title: TestController * @projectName df-platform * @description TODO * @date 2023/6/19 15:22 */ @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); } }

Encapsulation Process

The core idea is to provide import/export interfaces while keeping dependencies minimal.

Starting from the ExcelService interface, several overloads are defined for different scenarios (simple export, export with conversion, template export, import with conversion, etc.).

package com.dfec.framework.excel.service;

import org.springframework.web.multipart.MultipartFile;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.util.List;
import java.util.function.Consumer;
import java.util.function.Function;

/**
 * ExcelService
 * @author LiuBin
 */
public interface ExcelService {
void exportExcel(List
list, Class
tClass, HttpServletResponse response) throws IOException;
void exportExcel(List
list, Function
map, Class
tClass, 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);
}

The implementation DefaultExcelServiceImpl uses EasyExcel to perform the actual read/write operations and sets proper HTTP response headers.

package com.dfec.framework.excel.service.impl;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.dfec.framework.excel.convert.LocalDateTimeConverter;
import com.dfec.framework.excel.service.ExcelService;
import com.dfec.framework.excel.util.ExcelUtils;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;
import java.util.List;
import java.util.function.Consumer;
import java.util.function.Function;
import java.util.stream.Collectors;

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

Utility class ExcelUtils provides helper methods for reading and writing Excel files, handling response headers, and supporting template‑based export.

package com.dfec.framework.excel.util;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelReader;
import com.alibaba.excel.read.builder.ExcelReaderBuilder;
import com.alibaba.excel.read.metadata.ReadSheet;
import com.alibaba.fastjson.JSON;
import com.dfec.common.exception.ServiceException;
import com.dfec.framework.excel.listener.ExcelListener;
import com.dfec.framework.excel.service.ExcelBaseService;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.util.*;

public class ExcelUtils {
    public static
void excelExport(HttpServletResponse response, String fileName, String sheetName,
                                      Class
head, List
data) throws IOException {
        write(response, fileName);
        EasyExcel.write(response.getOutputStream(), head).autoCloseStream(Boolean.FALSE)
                .sheet(sheetName).doWrite(data);
    }
    // other overloads omitted for brevity
    private static void write(HttpServletResponse response, String fileName) {
        try {
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            response.setCharacterEncoding("utf-8");
            response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
        } catch (Exception e) {
            response.reset();
            response.setContentType("application/json");
            response.setCharacterEncoding("utf-8");
            Map
map = new HashMap<>();
            map.put("status", "failure");
            map.put("message", "下载文件失败" + e.getMessage());
            try { response.getWriter().println(JSON.toJSONString(map)); } catch (IOException ex) { throw new RuntimeException(ex); }
        }
    }
    // read methods omitted for brevity
}

Encountered Issues

1. Date format mismatch when exporting via template

Solution: implement a custom DateConverter and specify it on the field annotation.

package com.dfec.server;

import com.alibaba.excel.converters.Converter;
import com.alibaba.excel.converters.ReadConverterContext;
import com.alibaba.excel.enums.CellDataTypeEnum;
import com.dfec.common.utils.str.StringUtils;
import java.util.Date;

public class DateConverter implements Converter
{
    @Override
    public Date convertToJavaData(ReadConverterContext
context) throws Exception {
        // conversion logic, return null for blank strings
        return Converter.super.convertToJavaData(context);
    }
}

Annotate the entity field:

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

2. POI version compatibility

Ensure that the versions of POI and ooxml match to avoid runtime errors.

3. LocalDateTime conversion

A custom LocalDateTimeConverter is provided to handle string ↔︎ LocalDateTime conversion.

package com.dfec.framework.excel.convert;

import com.alibaba.excel.converters.Converter;
import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.GlobalConfiguration;
import com.alibaba.excel.metadata.data.ReadCellData;
import com.alibaba.excel.metadata.data.WriteCellData;
import com.alibaba.excel.metadata.property.ExcelContentProperty;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;

public class LocalDateTimeConverter implements Converter
{
    @Override public Class
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
convertToExcelData(LocalDateTime value, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) {
        return new WriteCellData<>(value.format(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss")));
    }
}

Open Issues

The current EasyExcel version (3.3.2) only supports template export for .xls files; .xlsx template export is not yet functional.

References

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

Original article source: juejin.cn/post/7328242736027762739