SpringBoot+Vue+EasyExcel+MybatisPlus+Lombok前后端分离实现Excel文件导入导出（简单实用版）

2023-05-16

一、前言

文章参考自两位大佬的博客：

http://events.jianshu.io/p/4242556280fa

https://www.w3xue.com/exp/article/20228/80302.html

我在此基础上做了补充说明，以及把实践的注意事项说明清楚！

在此特别声明，若原作者认为我存在侵权行为，可联系我删除文章！！此文章仅用于学习，禁止商用转载！！！

二、准备

2.1、后端

（1）导入EasyExcel、MybatisPlus、Lombok依赖：

		<!-- easyexcel -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>3.1.1</version>
        </dependency>
        <!--MybatisPlus-->
		<dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.0.5</version>
        </dependency>
        <!--模板引擎，用于MybatisPlus代码自动生成-->
        <dependency>
            <groupId>org.apache.velocity</groupId>
            <artifactId>velocity-engine-core</artifactId>
            <version>2.0</version>
        </dependency>
        <!--Lombok-->
		<dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>

（2）在build中配置Maven文件过滤：

<!--maven静态资源文件过滤问题-->
        <resources>
            <resource>
                <directory>src/main/java</directory>
                <includes>
                    <include>**/*.properties</include>
                    <include>**/*.xml</include>
                    <include>**/*.yml</include>
                </includes>
                <filtering>false</filtering>
            </resource>
            <resource>
                <directory>src/main/resources</directory>
                <includes>
                    <include>**/*.properties</include>
                    <include>**/*.xml</include>
                    <include>**/*.yml</include>
                </includes>
                <filtering>false</filtering>
            </resource>
        </resources>

（3）在application.yml中配置上传文件的大小和MybatisPLus的Mapper文件地址：

spring:
# 上传文件大小设置
  servlet:
    multipart:
      #设置单个文件大小，单位MB和KB都可以
      max-file-size: 100MB
      #设置总上传的数据大小，单位MB和KB都可以
      max-request-size: 100MB

mybatis-plus:
  # 配置日志默认输出到控制台，因为Sql语句不可见，要查看日志才可见执行情况
  configuration:
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
  # 配置逻辑删除
  global-config:
    db-config:
      logic-delete-value: 1
      logic-not-delete-value: 0
    #xml文件位置
  mapper-locations: classpath:com/tang/mapper/xml/*.xml

（4）配置前后端跨域问题，一个Springboot的配置文件：

import org.springframework.context.annotation.Configuration;
import org.springframework.web.servlet.config.annotation.CorsRegistry;
import org.springframework.web.servlet.config.annotation.WebMvcConfigurer;

@Configuration
public class CorsConfig implements WebMvcConfigurer {

    @Override
    public void addCorsMappings(CorsRegistry registry) {  //解决springboot和ajax传数据时请求跨域的问题
        registry.addMapping("/**")
                .allowedHeaders("*")
                .allowedMethods("GET", "HEAD", "POST", "PUT", "DELETE","OPTIONS")
                .allowedOrigins("*")
                .allowCredentials(true)
                .maxAge(3600);
        WebMvcConfigurer.super.addCorsMappings(registry);
    }

}

2.2、前端

（1）安装element-ui，控制台执行：

cnpm install element-ui --save

在main.js中配置全局使用：

import ElementUI from 'element-ui';
import 'element-ui/lib/theme-chalk/index.css';

Vue.use(ElementUI);

new Vue({
  el: '#app',  //针对app元素使用element-ui
  render: h => h(App)  //element-ui
})

三、Excel文件导入数据库

1、对于大的Excel文件，需要将行数据分批解析成POJO对象，并写入数据库，避免全量加载占用过多内存。

2、插入数据库时，尽量用批量插入的方式，而不是多次调用单条插入的方式，减少网络开销，提高插入效率。

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

注意：本功能实现没有经过Service层。

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

1、@ExcelProperty指定POJO的字段与Excel列的对应关系，列名由value指定（value为表头名称，index为表头位置）。

2、@ExcelIgnore表示Excel导入导出的时候忽略该字段。

3、如果POJO中的字段和Excel中的列值之间存在差异，需要转换时，可以自定义转换器，并通过converter指定(具体实现参考下文)。

package com.tang.pojo;

import com.alibaba.excel.annotation.ExcelProperty;
import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.Version;
import com.baomidou.mybatisplus.annotation.TableId;
import java.io.Serializable;

import com.tang.excel.GenderConverter;
import lombok.Data;
import lombok.EqualsAndHashCode;
import lombok.experimental.Accessors;

/**
 * <p>
 * 
 * </p>
 *
 * @author 唐世华
 * @since 2023-03-30
 */
@Data
@EqualsAndHashCode(callSuper = false)
public class Consumer implements Serializable {

    private static final long serialVersionUID = 1L;

    @TableId(value = "id", type = IdType.AUTO)
    @ExcelProperty(value = "ID", index = 0)
    private Integer id;

    @ExcelProperty(value = "用户名", index = 1)
    private String aaaa;

    @ExcelProperty(value = "密码", index = 2)
    private String password;

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

    @ExcelProperty(value = "手机号码", index = 4)
    private String phone;


}

补充1：用lombok的同志注意，不要在实体类上使用链式编程的注解：@Accessors(chain = true)，easyexcel和lombok会有冲突，导致获取的数据为null。

补充2：不要使用Mysql的关键字作为数据表的字段名，否则会报错

3.2、实现批量插入接口

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

（1）批量插入接口：

import java.util.List;

/**
 * 批量插入的Mapper, 用xml配置文件自定义批量插入，
 * 避免MyBatis的逐条插入降低性能
 *
 * @param <T>
 * @author 唐世华
 * @date 2023-03-31
 */
//<T>是Java中的泛型
public interface BatchInsertMapper<T> {
    void batchInsert(List<T> list);
}

（2）Mapper层接口ConsumerMapper继承BatchInsertMapper：

import com.tang.excel.BatchInsertMapper;
import com.tang.pojo.Consumer;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Mapper;

/**
 * <p>
 *  Mapper 接口
 * </p>
 *
 * @author 唐世华
 * @since 2023-03-30
 */
@Mapper
public interface ConsumerMapper extends BaseMapper<Consumer>, BatchInsertMapper<Consumer> {

}

（3）在ConsumerMapper.xml中编写批量插入Sql语句：

<?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.tang.mapper.ConsumerMapper">
    <insert id="batchInsert" parameterType="list">
        insert into excel.consumer
        (aaaa, password, sex, phone)
        values
        <foreach collection="list" item="item" index="index" separator=",">
            (
             #{item.aaaa},
             #{item.password},
             #{item.sex},
             #{item.phone}
            )
        </foreach>
    </insert>
</mapper>

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

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

import com.alibaba.excel.converters.Converter;
import com.alibaba.excel.converters.ReadConverterContext;
import com.alibaba.excel.converters.WriteConverterContext;
import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.data.WriteCellData;

/**
 * Excel性别列对应的转换器
 *
 * @author 唐世华
 * @date 2023-03-31
 */
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);
    }
}

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

1、分批入库，避免整个Excel文件加载到内存，影响性能。

2、invoke()用于处理Excel中一行解析形成的POJO对象，解析过程由EasyExcel根据POJO字段上的注解自动完成。

3、doAfterAllAnalysed()在invoke（）方法处理完整个Sheet中的所有数据之后调用，本文中用于将最后一批缓存的数据入库。

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.read.listener.ReadListener;
import lombok.extern.slf4j.Slf4j;

import java.util.ArrayList;
import java.util.List;

/**
 * 从Excel文件流中分批导入数据到库中
 * EasyExcel参考文档：https://easyexcel.opensource.alibaba.com/docs/current/api/
 *
 * @param <T>
 * @author 唐世华
 * @date 2023-03-31
 */

@Slf4j
public abstract class ExcelImportListener<T> implements ReadListener<T> {

    /**
     * 缓存大小,100条数据写入一次数据库
     */
    private static final int BATCH_SIZE = 100;

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

    /*
    * po:从excel中解析一行得到的实体类(pojo)
    * */
    @Override
    public void invoke(T po, AnalysisContext analysisContext) {
        System.out.println(po);
        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();

}

3.5、使用EasyExcel实现文件导入

1、head()指定Excel行对应的POJO，本文是Consumer。

2、registerReadListener()指定处理解析到的Consumer的类，本文是我们2.3中实现的ExcelImportListener。

3、通过实现匿名内部类的方式，将consumerMapper传递给ExcelImportListener，用于批量插入。

import com.alibaba.excel.EasyExcel;
import com.tang.mapper.ConsumerMapper;
import com.tang.pojo.Consumer;
import lombok.extern.slf4j.Slf4j;
import org.springframework.stereotype.Component;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.multipart.MultipartFile;

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

/**
 * Excel导入组件
 *
 * @author 唐世华
 * @date 2023-03-31
 */
@Slf4j
@Component
public class ExcelComponent {

    @Resource
    private ConsumerMapper consumerMapper;

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

3.6、后端Controller层调用

import com.alibaba.excel.EasyExcel;
import com.tang.excel.ExcelComponent;
import com.tang.excel.ExcelExportHandle;
import com.tang.pojo.Consumer;
import com.tang.service.ConsumerService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.net.URLEncoder;
import java.util.List;

/**
 * <p>
 *  前端控制器
 * </p>
 *
 * @author 唐世华
 * @since 2023-03-30
 */
@RestController
@RequestMapping("/consumer")
public class ConsumerController {

    @Resource
    private ExcelComponent excelComponent;

    @PostMapping("/updown")
    public Boolean updown(@RequestParam("files") MultipartFile file) throws IOException{
        excelComponent.importConsumerFile(file);
        return true;
    }

}

3.7、前端代码实现

（1）accept：接受的文件类型，name：和后端RequestParam中的名字对应。

		<el-upload
            class="upload-demo"
            method="post"
            action="http://localhost:8888/consumer/updown"
            accept=".xlsx,.xls"
            :show-file-list="false"
            name="file"
        >
            <el-button type="primary">导入</el-button>
        </el-upload>

四、数据库数据导出为Excel（下载功能）

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

4.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/api/write
 *
 * @author 唐世华
 * @date 2023-03-31
 */
@Slf4j
@Component
public class ExcelExportHandle {

    /**
     * 下载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);
    }

}

4.2、后端Controller层调用

import com.alibaba.excel.EasyExcel;
import com.tang.excel.ExcelComponent;
import com.tang.excel.ExcelExportHandle;
import com.tang.pojo.Consumer;
import com.tang.service.ConsumerService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.net.URLEncoder;
import java.util.List;

/**
 * <p>
 *  前端控制器
 * </p>
 *
 * @author 唐世华
 * @since 2023-03-30
 */
@RestController
@RequestMapping("/consumer")
public class ConsumerController {

    @Autowired
    private ConsumerService consumerService;

    @Resource
    private ExcelExportHandle excelExportHandle;

    @GetMapping("/down2")
    public void down2(HttpServletResponse response){
    	//从数据库中取出导出的数据
        List<Consumer> list = consumerService.list(null);
        System.out.println(list);
        excelExportHandle.export(response, "用户表", list, Consumer.class);
    }

}

4.3、前端代码实现

<template>
    <div>
        <el-button type="primary" style="margin-top: 20px" @click="downLoad">导出</el-button>
    </div>
</template>

<script>
export default {
    name: "ExcelPage",
    methods: {
        downLoad() {  //导出excel文件
            window.location.href='http://localhost:8888/consumer/down2';
            this.$message.success("导出成功");
        }
    }
}
</script>

五、总结

可能有些同学觉得我写的文章大多数的地方都和参考的博文一样，你这不是抄袭吗？

我这里来回答一下这个问题。我觉得我自己总结的没有参考的文章总结的好，与其用自己写的不好的话，我宁愿用其他博主总结的话，我想我这也是在帮助他们传播文章了。

我在原有内容的基础上，把自己所踩的坑都给写上来了，新加入的内容都和我踩的坑有关，所以你们直接使用一般来说是没有Bug的。我还加入了前端如何调用后端的内容，算是改善且发扬光大吧。

本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系:hwhale#tublm.com(使用前将#替换为@)