Android

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

2022-08-03  本文已影响0人  大鱼馆长

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指定(具体实现参考下文)。
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导入工具，本文设计了一个批量插入接口，用于批量插入数据到数据库，而非多次逐条插入。

  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文件内容
    导入数据库的结果
上一篇下一篇

猜你喜欢

热点阅读