十二、SpringBoot+Vue实现数据批量导入和导出功能

<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="org.example.mapper.DepartmentMapper">
    <select id="selectAll" resultType="org.example.entity.Department">
        select * from department
        <where>
            <if test="name != null">
                name like concat('%',#{name},'%')
            </if>
        </where>
        order by id desc
    </select>

    <select id="selectById" resultType="org.example.entity.Department">
        select * from department where id = #{id}
    </select>
    <select id="selectByUsername" resultType="org.example.entity.Department">
        select * from department where username = #{username}
    </select>
    <select id="selectByNo" resultType="org.example.entity.Department">
        select * from department where no = #{no}
    </select>

    <insert id="add" parameterType="org.example.entity.Department">
            insert into department(name) values(#{name})
    </insert>

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

    <delete id="deleteById">
        delete from department where id = #{id}
    </delete>
</mapper>

<template>
  <div>
    <div class="card" style="margin-bottom: 5px">
      <el-input v-model="data.name" placeholder="请输入名称搜索" prefix-icon="Search" style="width: 240px"></el-input>
      <el-button type="primary" style="margin: 5px" @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="warning" @click="handleDeleteBatch">批量删除</el-button>
      <!--    <el-button type="info">导入</el-button>-->
      <!--    <el-button type="success">导出</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="120px">
          <template #default="scope">
            <el-button type="primary" @click="handleUpdate(scope.row)" :icon="Edit" circle></el-button>
            <el-button type="danger" @click="handleDelete(scope.row.id)" :icon="Delete" circle></el-button>
          </template>
        </el-table-column>
      </el-table>
      <div style="margin-top: 16px">
        <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]"
            layout="total, sizes, prev, pager, next, jumper"
            :total="data.total">
        </el-pagination>
      </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="80 px" style="padding-right: 20px; padding-left: 20px; padding-top: 50px">
        <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 router from "@/router/index.js";
import {reactive, ref} from "vue";
import request from "@/utils/request.js";
import {Delete, Edit, Search} from "@element-plus/icons-vue";
import {ElMessage, ElMessageBox} from "element-plus";
const data = reactive({
  name : null,
  pageNum: 1,
  pageSize : 10,
  total : 0,
  tableData : [],
  formVisible : false,
  form:{

  },
  ids : [],
  rules: {
    name : [
      {required : true, message : '请输入名称', trigger : 'blur'}
    ]
  }
});

const formRef = ref()

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

const reset = () => {
  data.name = null;
  load();
}
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('/department/add', data.form).then(res => {
    if (res.code === '200') {
      ElMessage.success('操作成功');
      load();
      data.formVisible = false;
    } else {
      ElMessage.error(res.msg);
    }
  })
}
const handleUpdate = (row) => {
  data.formVisible = true;
  data.form = JSON.parse(JSON.stringify(row));
}
const update = () => {
  request.put('/department/update', data.form).then(res => {
    if (res.code === '200') {
      ElMessage.success('操作成功');
      load();
      data.formVisible = false;
    } else {
      ElMessage.error(res.msg);
    }
  })
}

const handleDelete = (id) => {
  ElMessageBox.confirm('删除数据后无法恢复, 是否继续?', '提示', {type : 'warning'}).then(() => {
    request.delete('/department/delete/' + id).then(res => {
      if (res.code === '200') {
        ElMessage.success('操作成功');
        load();
        data.formVisible = false;
      } else {
        ElMessage.error(res.msg);
      }
    }).catch()
  })
}

const handleSelectionChange = (rows) => {
  console.log(rows)
  data.ids = rows.map(row => row.id)
  console.log(data.ids)
}

const handleDeleteBatch = () => {
  ElMessageBox.confirm('删除数据后无法恢复, 是否继续?', '提示', {type : 'warning'}).then(() => {
    request.delete('/department/deleteBatch',{data : data.ids}).then(res => {
      if (res.code === '200') {
        ElMessage.success('操作成功');
        load();
        data.formVisible = false;
      } else {
        ElMessage.error(res.msg);
      }
    }).catch()
  })
}
</script>

<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>

<el-table-column label="部门" prop="departmentName"/>

<select id="selectAll" resultType="org.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
     */
    @GetMapping("/export")
    public void export(HttpServletResponse response) throws IOException {
        // 1.拿到所有的员工数据
        List<Employee> employeeList = employeeService.seclectAll(null);
        // 2.构建ExcelWriter
        // 在内存操作，写出到浏览器
        ExcelWriter writer = ExcelUtil.getWriter(true);
        // 3.设置中文表头
        writer.addHeaderAlias("username", "账号");
        writer.addHeaderAlias("name", "名称");
        writer.addHeaderAlias("sex", "性别");
        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("员工信息", StandardCharsets.UTF_8);
        response.setHeader("Content-Disposition", "attachment;filename=" + fileName + ".xlsx");
        // 6.写出到输出流 并关闭writer
        ServletOutputStream os = response.getOutputStream();
        writer.flush(os);
        writer.close();
    }

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

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

    /**
     * excel导入
     */
    @PostMapping("/import")
    public Result importData(MultipartFile file) throws IOException {
        // 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)
  }
}