vue+springboot实现excel批量数据的导入导出

别问我，我什么都不知道

于 2024-05-09 20:55:58 发布

阅读量417

点赞数 2

文章标签： vue.js 前端 javascript

本文链接：https://blog.csdn.net/m0_74924621/article/details/138596603

版权

①后端配置端口：修改UserController

UserController：

package com.example.springboot.controller;

import cn.hutool.core.util.StrUtil;
import cn.hutool.poi.excel.ExcelReader;
import cn.hutool.poi.excel.ExcelUtil;
import cn.hutool.poi.excel.ExcelWriter;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.example.springboot.common.Result;
import com.example.springboot.entity.User;
import com.example.springboot.exception.ServiceException;
import com.example.springboot.service.UserService;
import com.example.springboot.utils.TokenUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DuplicateKeyException;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.stream.Collectors;

@CrossOrigin
@RestController
@RequestMapping("/user")
public class UserController {
    @Autowired
    UserService userservice;

    @PostMapping("/add")
     public Result add(@RequestBody User user){
        try{
            userservice.save(user);
        }catch(Exception e){
            if(e instanceof DuplicateKeyException){
                return Result.error("插入数据错误");
            }else{
                return Result.error("系统错误");
            }
        }
         return Result.success();
     }

    @PutMapping("/update")
    public Result update(@RequestBody User user){
            userservice.updateById(user);
        return Result.success();
    }

    @DeleteMapping("/delete/{id}")
    public Result delete(@PathVariable Integer id){
        User currentUser= TokenUtils.getCurrentUser();
        if(id.equals(currentUser.getId())){
            throw new ServiceException("不能删除当前用户");
        }
        userservice.removeById(id);
        return Result.success();
    }

    @DeleteMapping("/delete/batch")
    public Result batchDelete(@RequestBody List<Integer> ids){
        User currentUser=TokenUtils.getCurrentUser();
        if(currentUser != null&&currentUser.getId()!=null &&ids.contains(currentUser.getId())){
            throw new ServiceException("不能删除当前用户");
        }
        userservice.removeBatchByIds(ids);
        return Result.success();
    }

    @GetMapping("/selectall")
    public Result selectall(){
        List<User>userlist=userservice.list(new QueryWrapper<User>().orderByDesc("id"));
        return Result.success(userlist);
    }

    @GetMapping("/selectbyid/{id}")
    public Result selectbyid(@PathVariable Integer id){
        User user=userservice.getById(id);
        return Result.success(user);
    }

    @GetMapping("/selectByPage")
    public Result selectByPage(@RequestParam Integer pageNum,
                               @RequestParam Integer pageSize,
                               @RequestParam String username,
                               @RequestParam String name){
        QueryWrapper<User>queryWrapper=new QueryWrapper<User>().orderByDesc("id");
        queryWrapper.like(StrUtil.isNotBlank(username),"username",username);
        queryWrapper.like(StrUtil.isNotBlank(name),"name",name);
        Page<User>page=userservice.page(new Page<>(pageNum,pageSize),queryWrapper);
        return Result.success(page);
    }
    @GetMapping("/export")
    public void exportData(@RequestParam(required = false) String username,
                           @RequestParam(required = false) String name,
                           @RequestParam(required = false) String ids,
                           HttpServletResponse response) throws IOException {
        ExcelWriter writer= ExcelUtil.getWriter(true);
        List<User> list;
        QueryWrapper<User> queryWrapper=new QueryWrapper<>();
        if(StrUtil.isNotBlank(ids)){
            List<Integer> idsArr1=Arrays.stream(ids.split(",")).map(Integer::valueOf).collect(Collectors.toList());
            queryWrapper.in("id",idsArr1);
        }else{
            queryWrapper.like(StrUtil.isNotBlank(username),"username",username);
            queryWrapper.like(StrUtil.isNotBlank(name),"name",name);
        }
        list=userservice.list(queryWrapper);
        writer.write(list,true);
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");
        response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode("用户信息表", "UTF-8") + ".xlsx");
        ServletOutputStream outputStream= response.getOutputStream();
        writer.flush(outputStream,true);
        writer.close();
        outputStream.flush();
        outputStream.close();
    }

    @PostMapping("/import")
    public Result importData(MultipartFile file) throws IOException{
        ExcelReader reader=ExcelUtil.getReader(file.getInputStream());
        List<User> userList=reader.readAll(User.class);
        try{
            userservice.saveBatch(userList);
        }catch (Exception e){
            e.printStackTrace();
            return Result.error("批量导入数据出错");
        }
        return Result.success();
    }
}

②前端修改页面

User：

<template>
  <div>
    <div>
      <el-input style="width: 200px" placeholder="查询用户名" v-model="username"></el-input>
      <el-input style="width: 200px;margin: 0 10px" placeholder="查询姓名" v-model="name"></el-input>
      <el-button type="primary" @click="load(1)">查询</el-button>
      <el-button type="info" @click="reset">重置</el-button>
    </div>
    <div style="margin: 10px 0">
      <el-button type="primary" @click="handleAdd">新增</el-button>
      <el-button type="danger" @click="delbatch">批量删除</el-button>
      <el-button type="info" @click="exportData" plain>批量导出</el-button>
      <el-upload action="http://localhost:9090/user/import" :headers="{token:user.token}" style="display: inline-block;margin-left: 10px" :show-file-list="false" :on-success="handleImport">
        <el-button type="primary" plain>批量导入</el-button>
      </el-upload>
    </div>
    <el-table  @selection-change="handleSelectionChange" :data="tableData" stripe :header-cell-style="{backgroundColor:'aliceblue',color:'#666'} ">
      <el-table-column type="selection" width="55" align="center">
      </el-table-column>
      <el-table-column prop="id" label="ID" width="70"></el-table-column>
      <el-table-column prop="username" label="用户名"></el-table-column>
      <el-table-column prop="name" label="姓名"></el-table-column>
      <el-table-column prop="phone" label="手机号"></el-table-column>
      <el-table-column prop="email" label="邮箱"></el-table-column>
      <el-table-column prop="address" label="地址"></el-table-column>
      <el-table-column label="头像">
        <template v-slot="scope">
          <div style="display: flex;align-items: center">
            <el-image style="width: 50px;height: 50px;border-radius: 50%" v-if="scope.row.avatar" :src="scope.row.avatar" :preview-src-list="[scope.row.avatar]"></el-image>
          </div>
        </template>
      </el-table-column>
      <el-table-column prop="role" label="角色"></el-table-column>
      <el-table-column label="操作" align="center" width="180">
        <template v-slot="scope">
          <div style="display: flex">
            <el-button type="primary" plain size="mini" @click="handleEdit(scope.row)">编辑</el-button>
            <el-button type="danger" plain size="mini" @click="del(scope.row.id)">删除</el-button>
          </div>
        </template>
      </el-table-column>
    </el-table>
    <div class="block" style="margin: 10px 0">
      <el-pagination
          @current-change="handleCurrentChange"
          :current-page="pageNum"
          :page-sizes="[100, 200, 300, 400]"
          :page-size="pageSize"
          layout="total, prev, pager, next"
          :total="total">
      </el-pagination>
    </div>
    <el-dialog title="收货地址" :visible.sync="formVisible" width="30%">
      <el-form :model="form" label-width="80px" style="padding-right: 20px" :rules="rules" ref="formRef">
        <el-form-item label="用户名" prop="username">
          <el-input v-model="form.username" ></el-input>
        </el-form-item>
        <el-form-item label="姓名" prop="name">
          <el-input v-model="form.name"></el-input>
        </el-form-item>
        <el-form-item label="电话" prop="phone">
          <el-input v-model="form.phone"></el-input>
        </el-form-item>
        <el-form-item label="邮箱" prop="email">
          <el-input v-model="form.email"></el-input>
        </el-form-item>
        <el-form-item label="地址" prop="address">
          <el-input type="textarea" v-model="form.address"></el-input>
        </el-form-item>
        <el-form-item label="角色" prop="role">
          <el-radio-group v-model="form.role">
            <el-radio label="管理员"></el-radio>
            <el-radio label="用户"></el-radio>
          </el-radio-group>
        </el-form-item>
        <el-form-item label="头像">
            <el-upload
            class="avatar-uploader"
            action="http://localhost:9090/file/upload"
            :headers="{ token: user.token }"
            :file-list="form.avatar?[form.avatar]:[]"
            list-type="picture"
            :on-success="handleAvatarSuccess">
          <el-button type="primary">上传头像</el-button>
        </el-upload>
        </el-form-item>
      </el-form>
      <div slot="footer" class="dialog-footer">
        <el-button @click="dialogFormVisible = false">取 消</el-button>
        <el-button type="primary" @click="save">确 定</el-button>
      </div>
    </el-dialog>
  </div>
</template>

<script>
export default {
  name:'User',
  data(){
    return{
      tableData:[],
      pageNum:1,
      pageSize:5,
      username:'',
      name:'',
      total:0,
      formVisible:false,
      form:{},
      user:JSON.parse(localStorage.getItem('honey-user'||'{}')),
      rules:{
        username:[{
          required:true,
          message:'请输入用户名',
          trigger:'blur'
        }]
      },
      ids:[]
    }
  },
  created() {
    this.load()
  },
  methods:{
    handleImport(res,file,fileList){
      if(res.code==='200'){
        this.$message.success("操作成功")
        this.load(1)
      }else{
        this.$message.error(res.msg)
      }
    },
    exportData(){
      if(!this.ids.length){
        window.open("http://localhost:9090/user/export?token="+this.user.token+"&username="+this.username+"&name="+this.name)
      }else{
        let idStr=this.ids.join(',')
        window.open("http://localhost:9090/user/export?token="+this.user.token+"&ids="+idStr)
      }
    },
    delbatch(){
      if(!this.ids.length){
        this.$message.warning("请选择数据")
        return
      }
      this.$confirm('您确认删除吗','确认删除',{type:'warning'}).then(response=>{
        this.$request.delete('/user/delete/batch',{data:this.ids}).then(res=>{
          if(res.code === '200'){
            this.$message.success('操作成功')
            this.load(1)
          }else{
            this.$message.error(res.msg)
          }
        })
      }).catch(()=>{})
    },
    handleSelectionChange(rows){
      this.ids=rows.map(v=>v.id)
    },
    del(id){
      this.$confirm('您确认删除吗','确认删除',{type:'warning'}).then(response=>{
        this.$request.delete('/user/delete/'+id).then(res=>{
          if(res.code === '200'){
            this.$message.success('操作成功')
            this.load(1)
          }else{
            this.$message.error(res.msg)
          }
        })
      }).catch(()=>{})
    },
    handleEdit(row){
      this.form=JSON.parse(JSON.stringify(row))
      this.formVisible=true
    },
    handleAdd(){
      this.form={role:'用户'}
      this.formVisible=true
    },
    save(){
      this.$refs.formRef.validate((valid)=>{
        if(valid){
          this.$request({
            url:this.form.id? '/user/update' : '/user/add',
            method:this.form.id? 'PUT' : 'POST',
            data:this.form
          }).then(res=>{
            if(res.code === '200'){
              this.$message.success('保存成功')
              this.load(1)
              this.formVisible=false
            }else{
              this.$message.error(res.msg)
            }
          })
        }
      })
    },
    handleAvatarSuccess(response,file,fileList){
      console.log(response)
      this.form.avatar=response.data
    },
    reset(){
      this.name=''
      this.username=''
      this.load()
    },
    load(pageNum){
      if(pageNum){
        this.pageNum=pageNum
      }
      this.$request.get('/user/selectByPage',{
        params:{
          pageNum:this.pageNum,
          pageSize:this.pageSize,
          username:this.username,
          name:this.name
        }
      }).then(res=>{
        this.tableData=res.data.records
        this.total=res.data.total
      })
    },
    handleCurrentChange(pageNum){
      this.load(pageNum)
    },
  }
}
</script>

<style scoped>

</style>

③后端引入依赖：修改pom.xml

pom.xml:

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <groupId>com.example</groupId>
    <artifactId>springboot</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>springboot</name>
    <description>springboot</description>
    <properties>
        <java.version>1.8</java.version>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
        <spring-boot.version>2.7.6</spring-boot.version>
    </properties>
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.3.0</version>
        </dependency>
        <!-- mybatis-plus -->
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.5.3.2</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>5.2.3</version>
        </dependency>
        <dependency>
            <groupId>com.mysql</groupId>
            <artifactId>mysql-connector-j</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>cn.hutool</groupId>
            <artifactId>hutool-all</artifactId>
            <version>5.8.18</version>
        </dependency>
        <!-- JWT -->
        <dependency>
            <groupId>com.auth0</groupId>
            <artifactId>java-jwt</artifactId>
            <version>4.3.0</version>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
    </dependencies>
    <dependencyManagement>
        <dependencies>
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-dependencies</artifactId>
                <version>${spring-boot.version}</version>
                <type>pom</type>
                <scope>import</scope>
            </dependency>
        </dependencies>
    </dependencyManagement>

    <build>
        <plugins>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-compiler-plugin</artifactId>
                <version>3.8.1</version>
                <configuration>
                    <source>1.8</source>
                    <target>1.8</target>
                    <encoding>UTF-8</encoding>
                </configuration>
            </plugin>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
                <version>${spring-boot.version}</version>
                <configuration>
                    <mainClass>com.example.springboot.SpringbootApplication</mainClass>
                    <skip>true</skip>
                </configuration>
                <executions>
                    <execution>
                        <id>repackage</id>
                        <goals>
                            <goal>repackage</goal>
                        </goals>
                    </execution>
                </executions>
            </plugin>
        </plugins>
    </build>

</project>

④修改后端实体类：User

User：

package com.example.springboot.entity;

import cn.hutool.core.annotation.Alias;
import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import lombok.AllArgsConstructor;
import lombok.Data;

@Data
@TableName("user")
public class User {
    @TableId(type= IdType.AUTO)
    @Alias("序号")
    private Integer id;
    @Alias("用户名")
    private String username;
    @Alias("密码")
    private String password;
    @Alias("姓名")
    private String name;
    @Alias("电话")
    private String phone;
    @Alias("邮箱")
    private String email;
    @Alias("地址")
    private String address;
    @Alias("头像")
    private String avatar;
    @Alias("角色")
    private String role;

    @TableField(exist = false)
    private String token;
}