上传Excel文件并将Excel数据导入到mysql数据库中

---

前言

在网站开发中，经常会使用到Excel导入数据的场景。具体的流程就是首先上传Excel文件，然后前端将Excel文件的数据传输到后端，后端对其进行解析，然后再执行入库操作等。这篇文章介绍使用阿里巴巴的EasyExcel工具进行Excel的导入和导出。

---

一、Apache POI和EasyExcel

说起Excel导入导出就不得不提Apache POI，它是一个比较经典的Excel处理工具，使用非常广泛，但是Apache POI存在以下缺点：

• Apache POI高性能模式（SAX）学习成本高，它相较于EasyExcel一个很大的缺点就是难度较大，代码繁琐。
• Apache POI低性能模式（Dom解析模式，一次性加载整个文档）的内存消耗大，

EasyExcel本质上还是来源于POI，EasyExcel有如下几个特点：

• EasyExcel重写了POI对07版Excel的解析，将内存消耗大大降低了，不会出现内存溢出。
• 在上层进行了模型封装，使用者上手更加方便。

二、后端使用EasyExcel解析前端上传的Excel数据

1、引入库

在pom.xml加入依赖项即可，我使用的是3.0.5版本的。

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

2、domain层为实体属性加上@ExcelProperty注解

为了精简篇幅，这里就省略get和set方法了。
这里的@ExcelProperty就是实体类中的属性和Excel文件中的列相对应的名称，另外还可以使用@ExcelIgnore注解来指定某些属性不进行导出。

public class CourseInfo extends BaseEntity {

    @ExcelProperty("id")
    private Long id;

    @ExcelProperty("课程编号")
    private String courseno;

    @ExcelProperty("课程名称")
    private String coursename;

    @ExcelProperty("课程属性")
    private String courseattr;

    @ExcelProperty("学分")
    private Integer credit;

    @ExcelProperty("学时")
    private Integer totalhour;

    @ExcelProperty("状态")
    private String status;

    @ExcelProperty("描述")
    private String description;

3、然后需要定义一个通用的监听器，在拿到前端上传的Excel之后，使用这个监听器进行处理，通用监听器代码如下：

package com.wbz.system.utils;

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import org.apache.commons.compress.utils.Lists;

import java.util.List;

public class GeneralListener<T> extends AnalysisEventListener<T> {

    private List<T> list = Lists.newArrayList();

    @Override
    public void invoke(T t, AnalysisContext analysisContext) {
        list.add(t);
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        System.out.println("read data complete!");
    }
    public List<T> getList(){
        return list;
    }
}

3、Controller层接收前端上传的Excel文件，调用监听器进行解析

这里有三种方式，除了上面提到的使用通用监听器之外，还可以使用“实现ReadListener”、“继承AnalysisEventListener”的方式，个人觉得通用监听方式最好用，其他两种方式就不再展开。使用通用监听器的代码已经进行了详细注释。

    @PostMapping("/importCourse")
    public AjaxResult importCourse(MultipartFile file) throws Exception{
//        1、使用实现ReadListener的方式
//        EasyExcel.read(file.getInputStream(),CourseInfo.class, new UploadDataListener(courseInfoMapper)).sheet().doRead();
//        2、使用继承AnalysisEventListener的方式
//        EasyExcel.read(file.getInputStream(), CourseInfo.class,new CourseInfoListener(courseService)).sheet().doRead();
//        3、使用继承AnalysisEventListener且使用泛型的方式：
//        3.1、建立一个通用的读取监听器，在该监听器读取到数据，并使用List保存，读取完成之后，使用getList()方法获得
        GeneralListener<CourseInfo> generalListener = new GeneralListener<>();
//        3.2、执行Excel的读取，.read()方法需要传入三个参数：1、文件。2、实体类。3、监听器。
        EasyExcel.read(file.getInputStream(),CourseInfo.class,generalListener).sheet().doRead();
//        3.3、获得监听器读取的数据列表
        List<CourseInfo> courseInfoList = generalListener.getList();
//        3.4、执行后续入库操作
        courseService.insertCourseByBatch(courseInfoList);
        return AjaxResult.success();
    }

最后是批量插入mysql数据库的操作，大家如果感兴趣可以看我这篇介绍批量插入mysql数据库的文章：
https://blog.youkuaiyun.com/qq_43403676/article/details/124406698
导入的Excel截图：

4、导出为Excel文件

导出同样需要在实体类上加上@ExcelProperty注解，如果不想导出某些字段，则可以使用@ExcelIgnore标注。
导出Excel文件的代码如下：

    @PostMapping("/exportCourse")
    public void exportCourse(HttpServletResponse response, CourseInfo courseInfo) throws IOException{
        List<CourseInfo> list = courseService.selectCourseInfoList(courseInfo);
        List<CourseInfo> exportList = JSON.parseArray(JSON.toJSONString(list),CourseInfo.class);
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        String filePrefix="测试文件";
        try {
            // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
            String fileName = URLEncoder.encode(filePrefix, "UTF-8").replaceAll("\\+", "%20");
            //Content-disposition 的 attachment参数将文件作为附件下载
            response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
            EasyExcel.write(response.getOutputStream(), CourseInfo.class).sheet("模板").doWrite(exportList);
        } catch (Exception e) {
            System.out.println("下载文件失败!"+e);
        }
        System.out.println("下载数据大小为:{}");
        System.out.println(exportList.size());
    }

导出Excel截图：

三、前端使用elemen-ui的el-upload控件上传Excel文件

el-upload控件：

    <el-dialog :title="upload.title" :visible.sync="upload.open" width="400px" append-to-body>
      <el-upload
        ref="upload"
        :limit="1"
        accept=".xlsx, .xls"
        :headers="upload.headers"
        :action="upload.url"
        :disabled="upload.isUploading"
        :on-progress="handleFileUploadProgress"
        :on-success="handleFileSuccess"
        :auto-upload="false"
        drag
      >
        <i class="el-icon-upload"></i>
        <div class="el-upload__text">
          将文件拖到此处，或
          <em>点击上传</em>
        </div>
        <div class="el-upload__tip" style="color:red" slot="tip">提示：仅允许导入“xls”或“xlsx”格式文件！</div>
      </el-upload>
      <div slot="footer" class="dialog-footer">
        <el-button type="primary" @click="submitImportCourse">确 定</el-button>
        <el-button @click="upload.open = false">取 消</el-button>
      </div>
    </el-dialog>

绑定的upload数据，这里可以设置标题、请求头、url等（url是上传的地址，也就是在确定上传之后，会将数据发送到后端哪个方法进行处理）：

upload: {
        // 是否显示弹出层（用户导入）
        open: false,
        // 弹出层标题（用户导入）
        title: "",
        // 是否禁用上传
        isUploading: false,
        // 是否更新已经存在的用户数据
        updateSupport: 0,
        // 设置上传的请求头部
        headers: { Authorization: "Bearer " + getToken() },
        // 上传的地址
        url: process.env.VUE_APP_BASE_API + "/course/importCourse"
      },

定义启动上传、上传中、上传成功等函数：

 // 文件上传中处理
    handleFileUploadProgress(event, file, fileList) {
      this.upload.isUploading = true;
    },
    // 文件上传成功处理
    handleFileSuccess(response, file, fileList) {
      this.upload.open = false;
      this.upload.isUploading = false;
      this.$refs.upload.clearFiles();
      this.$alert(response.msg, "导入结果", { dangerouslyUseHTMLString: true });
      this.getList();
    },
    // 提交上传文件
    submitImportCourse() {
      this.$refs.upload.submit();
    },

---

总结

以上就是使用EasyExcel进行Excel文件的导入和导出的方法，以及使用element-ui进行文件上传。EasyExcel还有很多其他好用的方法，就不再进行详细介绍，需要使用的时候，查阅官方文档就可以了。这里给出几个参考网址：

• https://www.yuque.com/easyexcel/doc/read
• https://blog.youkuaiyun.com/qq_34457443/article/details/114283713
• https://blog.youkuaiyun.com/kuishao1314aa/article/details/115678654?spm=1001.2101.3001.6650.1&utm_medium=distribute.pc_relevant.none-task-blog-2%7Edefault%7ECTRLIST%7ERate-1.pc_relevant_default&depth_1-utm_source=distribute.pc_relevant.none-task-blog-2%7Edefault%7ECTRLIST%7ERate-1.pc_relevant_default&utm_relevant_index=2