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

作者: 大鱼馆长 | 来源:发表于2022-08-03 10:48 被阅读0次

1. 引入EasyExcel的Maven依赖

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

2. 导入Excel文件到数据库

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

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

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

@ExcelProperty指定POJO的字段与Excel列的对应关系，列名由value指定。
@ExcelIgnore表示Excel导入导出的时候忽略该字段。
如果POJO中的字段和Excel中的列值之间存在差异，需要转换时，可以自定义转换器，并通过converter指定(具体实现参考下文)。

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
@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导入工具，本文设计了一个批量插入接口，用于批量插入数据到数据库，而非多次逐条插入。

批量插入接口

import java.util.List;

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

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> {
}

在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分批导入

分批入库，避免整个Excel文件加载到内存，影响性能。
invoke()用于处理Excel中一行解析形成的POJO对象，解析过程由EasyExcel根据POJO字段上的注解自动完成。
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实现文件导入

head()指定Excel行对应的POJO，本文是PersonPO。
registerReadListener()指定处理解析到的PersonPO的类，本文是我们2.3中实现的ExcelImportListener。
通过实现匿名内部类的方式，将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导出组件

泛型实现，通用性更好。
设置单元格长宽，字体，执行文件名。
设置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. 总结

本文利用EasyExcel实现了Excel文件的分批导入和导出功能，批量导入的设计原理也是EasyExcel官网推荐的用法。
多Sheet，多文件头的导入导出，以及其他更丰富的功能请参考EasyExcel官方文档。
本文的主要目的是，从Controller层到Dao层全流程演示Excel文件的导入导出，代码详尽，复制即可运行。
最后，本文测试的Excel文件内容和数据库中的数据如下：

Excel文件内容
导入数据库的结果

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

1. 引入EasyExcel的Maven依赖

2. 导入Excel文件到数据库

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

2.2 实现批量插入接口

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

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

2.5 使用EasyExcel实现文件导入

2.6 Web接口调用

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

3.1 实现Excel导出组件

3.2 Web调用

4. 总结

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读

Android