1、操作环境

后端：IDEA+SpringBoot+Mybatis
前端：WebStorm+Vue+ElementUI

2、后端代码

①添加依赖

<!--导入POI依赖，ms office文件生成-->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>4.1.0</version>
</dependency>

②自定义一个POI工具类，配置上传和下载的Excel文件信息：

package com.zzz.vuehr.utils;

import com.zzz.vuehr.bean.JObLevel;
import org.apache.poi.hpsf.DocumentSummaryInformation;
import org.apache.poi.hssf.usermodel.*;
import org.springframework.http.HttpHeaders;
import org.springframework.http.HttpStatus;
import org.springframework.http.MediaType;
import org.springframework.http.ResponseEntity;
import org.springframework.web.multipart.MultipartFile;

import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

public class PoiUtils {

    //这是把数据导出到本地保存为Excel文件的方法
    public static ResponseEntity<byte[]> exportJobLevelExcel(List<JObLevel> allJobLevels) throws IOException {
        HSSFWorkbook workbook = new HSSFWorkbook();//创建一个Excel文件

        //创建Excel文档属性，必不可少。少了的话，getDocumentSummaryInformation()方法就会返回null
        workbook.createInformationProperties();
        DocumentSummaryInformation info = workbook.getDocumentSummaryInformation();
        info.setCompany("KYO Ltd.");//设置公司信息
        info.setManager("kyo");//设置管理者
        info.setCategory("职称表");//设置文件名

        //设置文件中的日期格式
        HSSFCellStyle datecellStyle = workbook.createCellStyle();
        datecellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy"));//这个文件的日期格式和平时的不一样

        //创建表单
        HSSFSheet sheet = workbook.createSheet("百威美食尚职称表");
        HSSFRow r0 = sheet.createRow(0);//创建第一行
        HSSFCell c0 = r0.createCell(0);// 创建列
        HSSFCell c1 = r0.createCell(1);// 创建列
        HSSFCell c2 = r0.createCell(2);// 创建列
        HSSFCell c3 = r0.createCell(3);// 创建列
        HSSFCell c4 = r0.createCell(4);// 创建列

        c0.setCellValue("编号");
        c1.setCellValue("职称名");
        c2.setCellValue("职称级别");
        c3.setCellValue("创建时间");
        c4.setCellValue("是否可用");

        for (int i = 0; i < allJobLevels.size(); i++) {
            JObLevel jl=allJobLevels.get(i);
            HSSFRow row = sheet.createRow(i + 1);
            HSSFCell cell0 = row.createCell(0);
            cell0.setCellValue(jl.getId());
            HSSFCell cell1 = row.createCell(1);
            cell1.setCellValue(jl.getName());
            HSSFCell cell2 = row.createCell(2);
            cell2.setCellValue(jl.getTitlelevel());
            HSSFCell cell3 = row.createCell(3);
            cell3.setCellStyle(datecellStyle);//让日期格式数据正确显示
            cell3.setCellValue(jl.getCreatedate());
            HSSFCell cell4 = row.createCell(4);
            cell4.setCellValue(jl.getEnabled()?"是":"否");
        }
        HttpHeaders headers = new HttpHeaders();
        headers.setContentDispositionFormData("attachment",
                new String("职称表.xls".getBytes("UTF-8"),"iso-8859-1"));
        headers.setContentType(MediaType.APPLICATION_OCTET_STREAM);
        ByteArrayOutputStream baos=new ByteArrayOutputStream();
        workbook.write(baos);

        ResponseEntity<byte[]> entity = new ResponseEntity<>(baos.toByteArray(), headers, HttpStatus.CREATED);

        return entity;
    }

    //这是解析上传的Excel文件为对象集合，从而批量添加数据的方法
    public static List<JObLevel> parseFile2List(MultipartFile file) throws IOException {
        List<JObLevel> jObLevels=new ArrayList<>();
        HSSFWorkbook workbook = new HSSFWorkbook(file.getInputStream());
        HSSFSheet sheet = workbook.getSheetAt(0);
        int physicalNumberOfRows = sheet.getPhysicalNumberOfRows();//获取表单所有的行
        for (int i = 1; i < physicalNumberOfRows; i++) {
            HSSFRow row = sheet.getRow(i);
            JObLevel j1=new JObLevel();

            HSSFCell c0 = row.getCell(0);
            j1.setId((int) c0.getNumericCellValue());

            HSSFCell c1 = row.getCell(1);
            j1.setName(c1.getStringCellValue());

            HSSFCell c2 = row.getCell(2);
            j1.setTitlelevel(c2.getStringCellValue());

            HSSFCell c3 = row.getCell(3);
            j1.setCreatedate(c3.getDateCellValue());

            HSSFCell c4 = row.getCell(4);
            j1.setEnabled(c4.getStringCellValue().equals("是"));

            jObLevels.add(j1);
        }

        return jObLevels;
    }
}

③Mapper文件配置的接口和SQL语句：

package com.zzz.vuehr.mapper;


import com.zzz.vuehr.bean.JObLevel;
import org.apache.ibatis.annotations.Param;

import java.util.List;

public interface JObLevelMapper {

     List<JObLevel> getAllJobLevels();
    
    Integer addBatchJobLevel(@Param("jls") List<JObLevel> jObLevels);
}

<resultMap id="BaseResultMap" type="com.zzz.vuehr.bean.JObLevel" >
  <id column="id" property="id" jdbcType="INTEGER" />
  <result column="name" property="name" jdbcType="VARCHAR" />
  <result column="titleLevel" property="titlelevel" jdbcType="CHAR" />
  <result column="createDate" property="createdate" jdbcType="TIMESTAMP" />
  <result column="enabled" property="enabled" jdbcType="BIT" />
</resultMap>

<insert id="addBatchJobLevel" parameterType="com.zzz.vuehr.bean.JObLevel">
  INSERT INTO joblevel(id, name, titleLevel, createDate, enabled)
  VALUES
  <foreach collection="jls" item="jl" separator=",">
    (#{jl.id},#{jl.name},#{jl.titlelevel},#{jl.createdate},#{jl.enabled})
  </foreach>
</insert>

<select id="getAllJobLevels" resultMap="BaseResultMap">
  select * from joblevel;
</select>

要操作的数据库里的表名为joblevel的信息：

④JObLevel对象：

package com.zzz.vuehr.bean;

import com.fasterxml.jackson.annotation.JsonFormat;

import java.util.Date;

public class JObLevel {
    private Integer id;

    private String name;

    private String titlelevel;

    private Date createdate;

    private Boolean enabled;

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name == null ? null : name.trim();
    }

    public String getTitlelevel() {
        return titlelevel;
    }

    public void setTitlelevel(String titlelevel) {
        this.titlelevel = titlelevel == null ? null : titlelevel.trim();
    }

    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss",timezone = "Asia/Shanghai")
    public Date getCreatedate() {
        return createdate;
    }

    public void setCreatedate(Date createdate) {
        this.createdate = createdate;
    }

    public Boolean getEnabled() {
        return enabled;
    }

    public void setEnabled(Boolean enabled) {
        this.enabled = enabled;
    }

    @Override
    public String toString() {
        return "JObLevel{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", titlelevel='" + titlelevel + '\'' +
                ", createdate=" + createdate +
                ", enabled=" + enabled +
                '}';
    }
}

⑤服务层的方法方法：

package com.zzz.vuehr.service;

import com.zzz.vuehr.bean.JObLevel;
import com.zzz.vuehr.mapper.JObLevelMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.util.Date;
import java.util.List;

@Service
public class JobLevelService {

    @Autowired
    JObLevelMapper jObLevelMapper;

    @Transactional
    public Integer addBatchJobLevel(List<JObLevel> jObLevels) {
        for (JObLevel jObLevel : jObLevels) {
            jObLevel.setCreatedate(new Date());
        }
        return jObLevelMapper.addBatchJobLevel(jObLevels);
    }

    @GetMapping("/export")
   //ResponseEntity里面装了所有响应的数据
    public ResponseEntity<byte[]> exportExcel() throws IOException {
    return PoiUtils.exportJobLevelExcel(jobLevelService.getAllJobLevels());
    }


    public List<JObLevel> getAllJobLevels() {
       return jObLevelMapper.getAllJobLevels();
    }


}

⑥控制层代码：

package com.zzz.vuehr.controller.system.basic;

import com.zzz.vuehr.bean.JObLevel;
import com.zzz.vuehr.bean.RespBean;
import com.zzz.vuehr.service.JobLevelService;
import com.zzz.vuehr.utils.PoiUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletRequest;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.List;

@RestController
@RequestMapping("/system/basic/jl")
public class JobLevelController {

    @Autowired
    JobLevelService jobLevelService;

    SimpleDateFormat sdf=new SimpleDateFormat("/yyyy/MM/dd/");

    @PostMapping("/import")
    public RespBean importData(MultipartFile file, HttpServletRequest req) throws IOException {
        List<JObLevel> jObLevels=PoiUtils.parseFile2List(file);

        for (JObLevel jObLevel : jObLevels) {
            System.out.println(jObLevel);
        }

        //根据上传的职称级别信息文件，批量添加数据
        if (jobLevelService.addBatchJobLevel(jObLevels)==1){
            return RespBean.ok("批量导入成功！");
        }else {
            return RespBean.error("批量导入失败！");
        }
    }
    

    @GetMapping("/export")
    //ResponseEntity里面装了所有响应的数据
    public ResponseEntity<byte[]> exportExcel() throws IOException {
        return PoiUtils.exportJobLevelExcel(jobLevelService.getAllJobLevels());
    }

    
}

⑦自定义的RespBean，用来存放响应的数据

package com.zzz.vuehr.bean;

public class RespBean {
    private Integer status;
    private String msg;
    private Object obj;

    public static RespBean ok(String msg, Object obj) {
        return new RespBean(200, msg, obj);
    }

    public static RespBean ok(String msg) {
        return new RespBean(200, msg, null);
    }

    public static RespBean error(String msg, Object obj) {
        return new RespBean(500, msg, obj);
    }

    public static RespBean error(String msg) {
        return new RespBean(500, msg, null);
    }

    private RespBean() {
    }

    private RespBean(Integer status, String msg, Object obj) {
        this.status = status;
        this.msg = msg;
        this.obj = obj;
    }

    public Integer getStatus() {
        return status;
    }

    public void setStatus(Integer status) {
        this.status = status;
    }

    public String getMsg() {
        return msg;
    }

    public void setMsg(String msg) {
        this.msg = msg;
    }

    public Object getObj() {
        return obj;
    }

    public void setObj(Object obj) {
        this.obj = obj;
    }
}

3、前端代码

①导入数据和下载表格的页面展示：

导入和导出的Excel文件格式示例：

②导出数据按钮

<el-button @click="exportData" type="success" size="mini" icon="el-icon-download">导出数据</el-button>

导出数据的方法：

exportData(){
  /*表示在当前页面打开新地址*/
  window.open("/system/basic/jl/export","_parent");
}

③导入数据的按钮：

<el-upload
  style="display: inline"
  :show-file-list="false"
  :on-success="onSuccess"
  :on-error="onError"
  :before-upload="beforeUpload"
  action="/system/basic/jl/import">
  <el-button size="mini" type="success" :disabled="!enabledUploadBtn" :icon="uploadBtnIcon">{{btnText}}</el-button>
</el-upload>

导入数据的关键方法：

importData(){
  //1.查找到存放文件的元素
  let myfile=this.$refs.myfile;
  //2.钙元素内部有一个file数组，里面存放了所有选择的file。
  // 由于上传文件时，文件可以多选，因此这里拿到的files对象是一个数组
  let files=myfile.files;
  //3.从files对象中，获取自己要上传的文件（即数组中的第一项）
  let file=files[0];
  //4.构造一个FormData，用来存放上传的数据，注意FormData不可以使用链式配置
  var formData = new FormData();
  formData.append("file",file);
  formData.append("username","Kyokkk");
  //5.构造好FormData后，就可以直接上传数据了，FormData就是要上传的数据
  //6.文件上传注意两点，①请求方法为POST，②设置Content-Type为multipart/form-data
  this.uploadFileRequest("/system/basic/jl/import",formData).then(resp=>{
    if (resp) {
      alert(resp);
    }
  })
}

间接需要调用的方法和自定义常量：

data(){
  return {
    uploadBtnIcon:'el-icon-upload2',
    enabledUploadBtn:true,
    dialogVisible:false,
    btnText:'数据导入',
    jl:{
      name:'',
      titlelevel:'中级'
    },
    jls:[],
    tls: [{
      value: '员级',
      label: '员级'
    }, {
      value: '初级',
      label: '初级'
    }, {
      value: '中级',
      label: '中级'
    }, {
      value: '副高级',
      label: '副高级'
    }, {
      value: '正高级',
      label: '正高级'
    }],
    value: ''
  }
},
mounted(){
    this.initJls();
},
methods:{

  onSuccess(response,file,fileList){
    this.enabledUploadBtn=true;
    this.uploadBtnIcon="el-icon-upload2";
    this.btnText='数据导入';
    alert("数据导入成功！");
    this.initJls();
  },

  onError(err,file,fileList){
    this.enabledUploadBtn=true;
    this.uploadBtnIcon="el-icon-upload2";
    this.btnText='数据导入';
    alert("数据导入失败！请检查是否有重复数据,和网络连接状况！");
  },
  beforeUpload(file){
    this.enabledUploadBtn=false;
    this.uploadBtnIcon="el-icon-loading";
    this.btnText='正在导入';
  },
initJls() {      
  this.getRequest("/system/basic/jl/").then(resp => {
    if (resp) {
      this.jls = resp;
    }
  })
}

把请求自定义成Restful风格的api.js文件；

import axios from 'axios'
import {Message} from 'element-ui'
axios.interceptors.request.use(config => {
  return config;
}, err => {
  Message.error({message: '请求超时!'});
  // return Promise.resolve(err);
})

axios.interceptors.response.use(data => {//{data:{status:200,msg"",obj:{}},status:200}
  if (data.status && data.status == 200 && data.data.status == 500) {
    //业务逻辑错误
    Message.error({message: data.data.msg});
    return;
  }
  if (data.data.msg) {
    Message.success({message: data.data.msg});
  }
  return data.data;
}, err => {
  if (err.response.status == 504 || err.response.status == 404) {
    Message.error({message: '服务器被吃了⊙﹏⊙∥'});
  } else if (err.response.status == 403) {
    Message.error({message: '权限不足,请联系管理员!'});
  } else if (err.response.status == 401) {
    Message.error({message: err.response.data.msg});
  } else {
    if (err.response.data.msg) {
      Message.error({message: err.response.data.msg});
    }else{
      Message.error({message: '未知错误!'});
    }
  }
  // return Promise.resolve(err);
})
let base = '';
export const postKeyValueRequest = (url, params) => {
  return axios({
    method: 'post',
    url: `${base}${url}`,
    data: params,
    transformRequest: [function (data) {
      let ret = ''
      for (let it in data) {
        ret += encodeURIComponent(it) + '=' + encodeURIComponent(data[it]) + '&'
      }
      return ret
    }],
    headers: {
      'Content-Type': 'application/x-www-form-urlencoded'
    }
  });
}
export const postRequest = (url, params) => {
  return axios({
    method: 'post',
    url: `${base}${url}`,
    data: params,
    headers: {
      'Content-Type': 'application/json'
    }
  });
}
export const uploadFileRequest = (url, params) => {
  return axios({
    method: 'post',
    url: `${base}${url}`,
    data: params,
    headers: {
      'Content-Type': 'multipart/form-data'
    }
  });
}
export const putRequest = (url, params) => {
  return axios({
    method: 'put',
    url: `${base}${url}`,
    data: params,
    headers: {
      'Content-Type': 'application/json'
    }
  });
}
export const deleteRequest = (url) => {
  return axios({
    method: 'delete',
    url: `${base}${url}`
  });
}
export const getRequest = (url) => {
  return axios({
    method: 'get',
    url: `${base}${url}`
  });
}

4、测试

在WebStorm和IDEA启动前端和后端，测试上传和下载即可