项目：14-SpringBoot3+Vue3实现数据批量导入导出功能

一：部门相关的增删改查

1.DeptMapper.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.DeptMapper">

    <select id="selectAll"  resultType="com.example.entity.Dept">
        select * from dept
        <where>
            <if test="name != null">name like concat('%',#{name},'%')</if>
        </where>
        order by id desc
    </select>
    
    <!--更新-->
    <update id="updateById" parameterType="com.example.entity.Dept">
        update `dept` set name = #{name}
        where id = #{id}
    </update>
    
    <!--新增-->
    <insert id="insert" parameterType="com.example.entity.Dept">
        insert into `dept` (name)
        values(#{name})
    </insert>
</mapper>

2.dept.vue

<template>
  <div>
    <div class="card" style="margin-bottom: 5px">
      <el-input style="width: 240px;margin-right: 10px" v-model="data.name" placeholder="请输入名称查询"
        prefix-icon="Search"></el-input>
      <el-button type="primary" @click="load">查询</el-button>
      <el-button type="warning" @click="reset">重置</el-button>
    </div>
    <div class="card" style="margin-bottom: 5px">
      <el-button type="primary" @click="handleAdd">新增</el-button>
      <el-button type="danger" @click="delBatch">批量删除</el-button>
    </div>
    <div class="card" style="margin-bottom: 5px">
      <el-table :data="data.tableData" stripe @selection-change="handleSelectionChange">
        <el-table-column type="selection" width="55" />
        <el-table-column label="名称" prop="name" />
        <el-table-column label="操作" width="120">
          <template #default="scope">
            <el-button @click="handleUpdate(scope.row)" type="primary" :icon="Edit" circle></el-button>
            <el-button @click="del(scope.row.id)" type="danger" :icon="Delete" circle></el-button>
          </template>
        </el-table-column>
      </el-table>
      <div style="margin-top: 15px">
        <el-pagination @size-change="load" @current-change="load" v-model:current-page="data.pageNum"
          v-model:page-size="data.pageSize" :page-sizes="[5, 10, 15, 20]" background
          layout="total, sizes, prev, pager, next, jumper" :total="data.total" />
      </div>
    </div>
    <el-dialog title="部门信息" v-model="data.formVisible" width="500" destroy-on-close>
      <el-form ref="formRef" :rules="data.rules" :model="data.form" label-width="80px"
        style="padding-right: 40px;padding-top: 20px">
        <el-form-item label="名称" prop="name">
          <el-input v-model="data.form.name" autocomplete="off" placeholder="请输入名称" />
        </el-form-item>
      </el-form>
      <template #footer>
        <div class="dialog-footer">
          <el-button @click="data.formVisible = false">取消</el-button>
          <el-button type="primary" @click="save">保存</el-button>
        </div>
      </template>
    </el-dialog>
  </div>
</template>

<script setup>
import { reactive, ref } from 'vue';
import { Delete, Edit, Search } from '@element-plus/icons-vue';
import { ElMessage, ElMessageBox } from 'element-plus';
import request from '@/utils/request';

const data = reactive({
  name: null,
  tableData: [],
  pageNum: 1,
  pageSize: 10,
  total: 0,
  formVisible: false,
  form: {},
  ids: [],
  rules: {
    name: [
      { required: true, message: '请输入名称', trigger: 'blur' }
    ],
  }
})

const formRef = ref()

const load = () => {
  request.get('/dept/selectPage', {
    params: {
      pageNum: data.pageNum,
      pageSize: data.pageSize,
      name: data.name
    }
  }).then(res => {
    data.tableData = res.data.list
    data.total = res.data.total
  })
}
load()

const reset = () => {
  data.name = null
  load()
}

const handleAdd = () => {
  data.formVisible = true
  data.form = {}
}

const save = () => { // 在一个保存方法里面做两个操作，一个是新增，一个是编辑
  formRef.value.validate((valid) => {
    if (valid) {
      data.form.id ? update() : add()
    }
  })
}

const add = () => {
  request.post('/dept/add', data.form).then(res => { // 新增的对象里面没有Id
    if (res.code === '200') {
      data.formVisible = false
      ElMessage.success('操作成功')
      load() // 新增后一定要重新加载最新的数据
    } else {
      ElMessage.error(res.msg)
    }
  })
}

const update = () => {
  request.put('/dept/update', data.form).then(res => { // 编辑的对象里面包含Id
    if (res.code === '200') {
      data.formVisible = false
      ElMessage.success('操作成功')
      load() // 更新后一定要重新加载最新的数据
    } else {
      ElMessage.error(res.msg)
    }
  })
}

const handleUpdate = (row) => {
  data.form = JSON.parse(JSON.stringify(row))  // 深拷贝一个新的对象 用于编辑 这样就不会影响行对象的数据了
  data.formVisible = true
}

const del = (id) => {
  ElMessageBox.confirm('删除数据后无法恢复，确认要删除吗？', '删除确认', { type: 'warning' }).then(() => {
    request.delete('dept/deleteById/' + id).then(res => {
      if (res.code === '200') {
        ElMessage.success('操作成功')
        load() //删除后一定要重新加载最新的数据
      } else {
        ElMessage.error(res.msg)
      }
    })
  }).catch()
}

const handleSelectionChange = (rows) => { //返回所有选中的行对象数组 
  console.log(rows)
  // 从选中的行数组里面取出所有行的Id组成一个新的数组
  data.ids = rows.map(row => row.id)
  console.log(data.ids)
}
const delBatch = () => {
  if (data.ids.length === 0) {
    ElMessage.warning('请选择数据')
    return
  }
  ElMessageBox.confirm('删除数据后无法恢复，确认要删除吗？', '删除确认', { type: 'warning' }).then(() => {
    request.delete('/dept/deleteBatch', { data: data.ids }).then(res => {
      if (res.code === '200') {
        ElMessage.success('操作成功')
        load()
      } else {
        ElMessage.error(res.msg)
      }
    })
  }).catch()
}
</script>

3.添加路由和菜单

二：员工关联部门

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

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

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

三：导出数据到Excel

1.在页面加上导出的方法

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

2.引入poi-ooxml

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

3.后端接口

    // 导出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("deptName","部门");
        // 默认情况下，表头Alias的顺序会按照添加的顺序输出，如果想要自定义顺序，可以通过设置forceAlias字段来实现
        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.写出到输出流并关闭流
        ServletOutputStream os = response.getOutputStream();
        writer.flush(os);
        writer.close();
    }

四：从Excel导入数据

1.后端接口

    // 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("部门","deptName");
        List<Employee> employeelist = reader.readAll(Employee.class);
        // 3.写入list数据到数据库
        for (Employee employee : employeelist){
           employeeService.add(employee);
        }
        return Result.success();
    }

2.前端页面

<el-upload 
  style="display: inline-block;margin: 0 10px"
  action="http://localhost:8080/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)
  }
}