EasyExcel实现文件导入导出(简单实用)

EasyExcel官方文档

1. 引入EasyExcel的Maven依赖

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

2. 导入Excel文件到数据库

  1. 对于大的Excel文件，需要将行数据分批解析成POJO对象，并写入数据库，避免全量加载占用过多内存。
  2. 插入数据库时，尽量用批量插入的方式，而不是多次调用单条插入的方式，减少网络开销，提高插入效率。

基于上述两个原则，代码实现如下，示例中的POJO是PersonPO:

2.1 定义POJO并给字段添加必要的注解

  1. @ExcelProperty指定POJO的字段与Excel列的对应关系，列名由value指定。
  2. @ExcelIgnore表示Excel导入导出的时候忽略该字段。
  3. 如果POJO中的字段和Excel中的列值之间存在差异，需要转换时，可以自定义转换器，并通过converter指定(具体实现参考下文)。
    注意：类上不能使用@Accessors(chain = true)注解，要么改成false，要不根本不用这个注解。
import com.alibaba.excel.annotation.ExcelIgnore;
import com.alibaba.excel.annotation.ExcelProperty;
import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;

import java.io.Serializable;

import com.spring.accumulator.io.excel.GenderConverter;
import lombok.NoArgsConstructor;
import lombok.Data;

/**
 * (Person)表实体类
 *
 * @author wangrubin
 * @since 2022-07-15 18:22:45
 */
@Data
// 因为和EasyExcel导入冲突，不能使用@Accessors(chain = true)
@NoArgsConstructor
@TableName("person")
public class PersonPO implements Serializable {

    @TableId(value = "id", type = IdType.AUTO)
    @ExcelIgnore
    private Long id;

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

    @ExcelProperty(value = "性别", converter = GenderConverter.class)
    private Integer male;

    @ExcelProperty(value = "年龄")
    private Integer age;
}

2.2 实现批量插入接口

为了实现通用的Excel导入工具，本文设计了一个批量插入接口，用于批量插入数据到数据库，而非多次逐条插入。

  1. 批量插入接口
import java.util.List;

/**
 * 批量插入的Mapper, 用xml配置文件自定义批量插入，
 * 避免MyBatis的逐条插入降低性能
 *
 * @param <T>
 * @author wangrubin
 * @date 2022-08-02
 */
public interface BatchInsertMapper<T> {
    void batchInsert(List<T> list);
}
  1. PersonMapper继承BatchInsertMapper
import java.util.List;

/**
 * (Person)表数据库访问层
 *
 * @author wangrubin
 * @since 2022-07-15 18:22:45
 */
@Mapper
public interface PersonMapper extends BaseMapper<PersonPO>, BatchInsertMapper<PersonPO> {
}
  1. 在PersonMapper.xml写批量插入语句
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.spring.accumulator.dao.PersonMapper">
    <insert id="batchInsert" parameterType="list">
        insert into wangrubin_db.person
        (name, age, male)
        values
        <foreach collection="list" item="item" index="index" separator=",">
            (
            #{item.name},
            #{item.age},
            #{item.male}
            )
        </foreach>
    </insert>
</mapper>

2.3 自定义Excel的类型转换器，实现性别转换

在PersonPO中，我们用1，0表示男，女；但是在Excel文件中，用汉字"男"和"女"替代1和0，所以需要进行转换。

/**
 * Excel性别列对应的转换器
 *
 * @author wangrubin
 * @date 2022-08-02
 */
public class GenderConverter implements Converter<Integer> {
    @Override
    public Class<?> supportJavaTypeKey() {
        return Integer.class;
    }

    @Override
    public CellDataTypeEnum supportExcelTypeKey() {
        return CellDataTypeEnum.STRING;
    }

    /**
     * 这里读的时候会调用，将Excel中的字段汉字转换成Java的Integer对象
     *
     * @param context context
     * @return Java中的Integer对象
     */
    @Override
    public Integer convertToJavaData(ReadConverterContext<?> context) {
        return context.getReadCellData().getStringValue().equals("男") ? 1 : 0;
    }

    /**
     * 这里是写的时候会调用，将Java的Integer对象转换成Excel中的字符串
     *
     * @return Excel中要存储的字符串
     */
    @Override
    public WriteCellData<?> convertToExcelData(WriteConverterContext<Integer> context) {
        String gender = context.getValue() == 1 ? "男" : "女";
        return new WriteCellData<String>(gender);
    }
}

2.4 继承ReadListener接口，实现Excel分批导入

  1. 分批入库，避免整个Excel文件加载到内存，影响性能。
  2. invoke()用于处理Excel中一行解析形成的POJO对象，解析过程由EasyExcel根据POJO字段上的注解自动完成。
  3. doAfterAllAnalysed()在invoke方法处理完整个Sheet中的所有数据之后调用，本文中用于将最后一批缓存的数据入库。
/**
 * 从Excel文件流中分批导入数据到库中
 * EasyExcel参考文档：https://easyexcel.opensource.alibaba.com/docs/current/quickstart/read
 *
 * @param <T>
 * @author wangrubin
 * @date 2022-08-02
 */
@Slf4j
public abstract class ExcelImportListener<T> implements ReadListener<T> {
    /**
     * 缓存大小
     */
    private static final int BATCH_SIZE = 100;

    /**
     * 缓存数据
     */
    private List<T> cacheList = new ArrayList<>(BATCH_SIZE);

    @Override
    public void invoke(T po, AnalysisContext analysisContext) {
        cacheList.add(po);
        if (cacheList.size() >= BATCH_SIZE) {
            log.info("完成一批Excel记录的导入，条数为：{}", cacheList.size());
            getMapper().batchInsert(cacheList);
            cacheList = new ArrayList<>(BATCH_SIZE);
        }
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        getMapper().batchInsert(cacheList);
        log.info("完成最后一批Excel记录的导入，条数为：{}", cacheList.size());
    }

    /**
     * 获取批量插入的Mapper
     * @return 批量插入的Mapper
     */
    protected abstract BatchInsertMapper<T> getMapper();
}

2.5 使用EasyExcel实现文件导入

  1. head()指定Excel行对应的POJO，本文是PersonPO。
  2. registerReadListener()指定处理解析到的PersonPO的类，本文是我们2.3中实现的ExcelImportListener。
  3. 通过实现匿名内部类的方式，将personMapper传递给ExcelImportListener，用于批量插入。
import com.alibaba.excel.EasyExcel;
import com.spring.accumulator.dao.BatchInsertMapper;
import com.spring.accumulator.dao.PersonMapper;
import com.spring.accumulator.entity.PersonPO;
import lombok.extern.slf4j.Slf4j;
import org.springframework.stereotype.Component;
import org.springframework.web.multipart.MultipartFile;

import javax.annotation.Resource;
import java.io.IOException;

/**
 * Excel导入组件
 *
 * @author wangrubin
 * @date 2022-08-02
 */
@Slf4j
@Component
public class ExcelComponent {

    @Resource
    private PersonMapper personMapper;

    /**
     * Excel文件分批导入数据库
     *
     * @param file 上传的文件
     * @throws IOException 读取文件异常
     */
    public void importPersonFile(MultipartFile file) throws IOException {
        EasyExcel.read(file.getInputStream())
                .head(PersonPO.class)
                .registerReadListener(new ExcelImportListener<PersonPO>() {
                    @Override
                    protected BatchInsertMapper<PersonPO> getMapper() {
                        return personMapper;
                    }
                }).sheet().doRead();
    }
}

2.6 Web接口调用

import javax.annotation.Resource;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;

@RestController
@RequestMapping("/excel")
public class ImportController {

    @Resource
    private ExcelComponent excelComponent;

    @PostMapping("/import-person")
    public Boolean importPersonFile(@RequestParam("file") MultipartFile file) throws IOException {
        excelComponent.importPersonFile(file);
        return true;
    }

3. 从数据库导出成Excel文件(下载功能)

导出也会用到导入阶段定义的POJO和Converter，此处不再赘述。

3.1 实现Excel导出组件

  1. 泛型实现，通用性更好。
  2. 设置单元格长宽，字体，执行文件名。
  3. 设置Response响应头，以实现Excel文件的下载和中文文件名的支持。
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import com.alibaba.excel.write.style.column.LongestMatchColumnWidthStyleStrategy;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.springframework.http.HttpHeaders;
import org.springframework.stereotype.Component;

import javax.servlet.http.HttpServletResponse;
import java.net.URLEncoder;
import java.nio.charset.StandardCharsets;
import java.util.List;

/**
 * 将数据以Excel的格式写入输出流
 * EasyExcel参考文档：https://easyexcel.opensource.alibaba.com/docs/current/quickstart/write
 *
 * @author wangrubin
 * @date 2022-08-02
 */
@Slf4j
@Component
public class ExcelExportHandler {
    /**
     * 下载Excel格式的数据
     *
     * @param response response
     * @param fileName 文件名（支持中文）
     * @param data     待下载的数据
     * @param clazz    封装数据的POJO
     * @param <T>      数据泛型
     */
    public <T> void export(HttpServletResponse response, String fileName,
                           List<T> data, Class<T> clazz) {
        try {
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            response.setCharacterEncoding("utf-8");
            // 这里URLEncoder.encode可以防止中文乱码
            String encodedFileName = URLEncoder.encode(fileName, StandardCharsets.UTF_8.name()).replaceAll("\\+", "%20");
            response.setHeader(HttpHeaders.CONTENT_DISPOSITION, "attachment;filename*=utf-8''" + encodedFileName + ".xlsx");
            // 这里需要设置不关闭流
            EasyExcel.write(response.getOutputStream(), clazz)
                    .sheet("Sheet1")
                    // 设置单元格宽度自适应
                    .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
                    // 设置单元格高度和字体
                    .registerWriteHandler(getHeightAndFontStrategy())
                    .doWrite(data);
            log.info("下载{}条记录到文件{}", data.size(), fileName);
        } catch (Exception e) {
            // 重置response
            log.error("文件下载失败" + e.getMessage());
            throw new RuntimeException("下载文件失败", e);
        }
    }

    /**
     * 自定义Excel导出策略，设置表头和数据行的字体和高度
     *
     * @return Excel导出策略
     */
    private HorizontalCellStyleStrategy getHeightAndFontStrategy() {
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        WriteFont headWriteFont = new WriteFont();
        headWriteFont.setFontHeightInPoints((short) 11);
        headWriteFont.setBold(true);
        headWriteCellStyle.setWriteFont(headWriteFont);
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        WriteFont contentWriteFont = new WriteFont();
        contentWriteFont.setFontHeightInPoints((short) 11);
        contentWriteCellStyle.setWriteFont(contentWriteFont);
        contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        return new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
    }
}

3.2 Web调用

注意：用postman测试的时候，不要在选择文件存储路径时修改文件名，要不然下载到本地的文件格式会出错。

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.spring.accumulator.dao.PersonMapper;
import com.spring.accumulator.entity.PersonPO;
import com.spring.accumulator.io.excel.ExcelExportHandler;
import org.springframework.web.bind.annotation.*;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletResponse;
import java.util.List;

@RestController
@RequestMapping("/excel")
public class ImportController {

    @Resource
    private PersonMapper personMapper;
    
    @Resource
    private ExcelExportHandler excelExportHandler;

    @GetMapping("/export-person")
    public void exportPersonFile(HttpServletResponse response) {
        List<PersonPO> data = personMapper.selectList(new QueryWrapper<>());
        excelExportHandler.export(response, "人员表", data, PersonPO.class);
    }
}

4. 总结

  1. 本文利用EasyExcel实现了Excel文件的分批导入和导出功能，批量导入的设计原理也是EasyExcel官网推荐的用法。
  2. 多Sheet，多文件头的导入导出，以及其他更丰富的功能请参考EasyExcel官方文档。

  3. 本文的主要目的是，从Controller层到Dao层全流程演示Excel文件的导入导出，代码详尽，复制即可运行。
    最后，本文测试的Excel文件内容和数据库中的数据如下：


    Excel文件内容

    导入数据库的结果
最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 203,324评论 5赞 476
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 85,303评论 2赞 381
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 150,192评论 0赞 337
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 54,555评论 1赞 273
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 63,569评论 5赞 365
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 48,566评论 1赞 281
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 37,927评论 3赞 395
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 36,583评论 0赞 257
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 40,827评论 1赞 297
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 35,590评论 2赞 320
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 37,669评论 1赞 329
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 33,365评论 4赞 318
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 38,941评论 3赞 307
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 29,928评论 0赞 19
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 31,159评论 1赞 259
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 42,880评论 2赞 349
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 42,399评论 2赞 342

推荐阅读更多精彩内容