15. SpringBoot3+Vue3实现数据批量导入导出功能

516 字约 2 分钟读完3577 次阅读更新于 2026/5/3

部门相关的增删改查

Department.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.example.mapper.DepartmentMapper">

    <select id="selectAll" resultType="com.example.entity.Department">
        select * from department
        <where>
            <if test="name != null">name like concat('%', #{name}, '%')</if>
        </where>
        order by id desc
    </select>
    
    <insert id="insert" parameterType="com.example.entity.Department">
        insert into `department` (name)
        values (#{name})
    </insert>

    <update id="updateById" parameterType="com.example.entity.Department">
        update `department` set name = #{name}
        where id = #{id}
    </update>

</mapper>

Department.vue

员工关联部门

<el-form-item label="部门">
  <el-select style="width: 100%" v-model="data.form.departmentId">
    <el-option v-for="item in data.departmentList" :key="item.id" :label="item.name" :value="item.id"></el-option>
  </el-select>
</el-form-item>

insert 写错了

关联查询员工表数据和 部门的名称

<select id="selectAll" resultType="com.example.entity.Employee">
  select employee.*, department.name as departmentName from employee
  left join department on employee.department_id = department.id
  <where>
    <if test="name != null">employee.name like concat('%', #{name}, '%')</if>
  </where>
  order by employee.id desc
</select>

导出数据到 Excel

/**
 * 导出excel
 */
@GetMapping("/export")
public void export(HttpServletResponse response) throws Exception {
    // 1. 拿到所有的员工数据
    List<Employee> employeeList = employeeService.selectAll(null);
    // 2. 构建 ExcelWriter
    // 在内存操作，写出到浏览器
    ExcelWriter writer = ExcelUtil.getWriter(true);
    // 3. 设置中文表头
    writer.addHeaderAlias("username", "账号");
    writer.addHeaderAlias("name", "名称");
    writer.addHeaderAlias("sex", "性别");
    writer.addHeaderAlias("no", "工号");
    writer.addHeaderAlias("age", "年龄");
    writer.addHeaderAlias("description", "个人介绍");
    writer.addHeaderAlias("departmentName", "部门");
    // 默认的，未添加alias的属性也会写出，如果想只写出加了别名的字段，可以调用此方法排除之
    writer.setOnlyAlias(true);
    // 4. 写出数据到writer
    writer.write(employeeList, true);
    // 5. 设置输出的文件的名称  以及输出流的头信息
    // 设置浏览器响应的格式
    response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");
    String fileName = URLEncoder.encode("员工信息", "UTF-8");
    response.setHeader("Content-Disposition", "attachment;filename=" + fileName + ".xlsx");
    // 6. 写出到输出流 并关闭 writer
    ServletOutputStream os = response.getOutputStream();
    writer.flush(os);
    writer.close();
}

导出的完整的 excel

在 页面加上这个导出的方法

const exportData = () => {
  // 导出数据 是通过流的形式下载 excel   打开流的链接，浏览器会自动帮我们下载文件
  window.open('http://localhost:9090/employee/export')
}

报错了：

引入 poi-ooxml

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>5.3.0</version>
</dependency>

从 Excel 导入数据

/**
 * excel 导入
 */
@PostMapping("/import")
public Result importData(MultipartFile file) throws Exception {
    // 1. 拿到输入流 构建 reader
    InputStream inputStream = file.getInputStream();
    ExcelReader reader = ExcelUtil.getReader(inputStream);
    // 2. 读取 excel里面的数据
    reader.addHeaderAlias("账号", "username");
    reader.addHeaderAlias("名称", "name");
    reader.addHeaderAlias("性别", "sex");
    reader.addHeaderAlias("工号", "no");
    reader.addHeaderAlias("年龄", "age");
    reader.addHeaderAlias("个人介绍", "description");
    reader.addHeaderAlias( "部门", "departmentName");
    List<Employee> employeeList = reader.readAll(Employee.class);
    // 3. 写入list数据到数据库
    for (Employee employee : employeeList) {
        employeeService.add(employee);
    }
    return Result.success();
}

前端页面导入按钮

<el-upload
    style="display: inline-block; margin: 0 10px"
    action="http://localhost:9090/employee/import"
    :show-file-list="false"
    :on-success="importSuccess"
>
  <el-button type="info">导入</el-button>
</el-upload>

              
const importSuccess = (res) => {
  if (res.code === '200') {
    ElMessage.success('批量导入数据成功')
    load()
  } else {
    ElMessage.error(res.msg)
  }
}