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

①后端配置端口：修改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 {@AutowiredUserService 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-uploadclass="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.recordsthis.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;
}