项目实战第八记
1.写在前面
- 本篇博客用Springboot和Vue实现数据导入、导出
2. 后端接口编写
2.1 Hutool实现导入、导出
导入依赖
<!-- hutool -->
<dependency>
<groupId>cn.hutool</groupId>
<artifactId>hutool-all</artifactId>
<version>5.7.20</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.1.2</version>
</dependency>
2.2 导出
代码中给出了足够的注释，相信大家应该能看懂
/*
导出
*/
@GetMapping("/export")
public void export(HttpServletResponse response) throws Exception {
// 从数据库查询出所有的数据
List<User> list = userService.list();
// 通过工具类创建writer 写出到磁盘路径
// ExcelWriter writer = ExcelUtil.getWriter(filesUploadPath + "/用户信息.xlsx");
// 在内存操作，写出到浏览器
ExcelWriter writer = ExcelUtil.getWriter(true);
// 参数true时只导出有别名的
writer.setOnlyAlias(true);
//自定义标题别名
writer.addHeaderAlias("username", "用户名");
writer.addHeaderAlias("password", "密码");
writer.addHeaderAlias("nickname", "昵称");
writer.addHeaderAlias("email", "邮箱");
writer.addHeaderAlias("phone", "电话");
writer.addHeaderAlias("address", "地址");
writer.addHeaderAlias("createTime", "创建时间");
// 一次性写出list内的对象到excel，使用默认样式，强制输出标题
writer.write(list, true);
// 设置浏览器响应的格式
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");
ServletOutputStream out = response.getOutputStream();
writer.flush(out, true);
out.close();
writer.close();
}
2.3 导入
/*
导入
*/
@PostMapping("/import")
public Boolean imp(MultipartFile file) throws Exception {
InputStream inputStream = file.getInputStream();
ExcelReader reader = ExcelUtil.getReader(inputStream);
//自定义标题别名 反转
reader.addHeaderAlias("用户名", "username");
reader.addHeaderAlias("密码", "password");
reader.addHeaderAlias("昵称", "nickname");
reader.addHeaderAlias("邮箱", "email");
reader.addHeaderAlias("电话", "phone");
reader.addHeaderAlias("地址", "address");
// reader.addHeaderAlias("创建时间", "createTime");
// 方式1：(推荐) 通过 javabean的方式读取Excel内的对象，但是要求表头必须是英文，跟javabean的属性要对应起来
List<User> list = reader.readAll(User.class);
userService.saveBatch(list);
return true;
}
注：在这里将英文名的标题和中文名转换，便于数据的导入
2.4 模板下载
在导入的时候，给用户提供模版的下载，方便交互
/**
* 指想要下载的文件的路径
* @param response
* @功能描述 下载文件:将输入流中的数据循环写入到响应输出流中，而不是一次性读取到内存
*/
@GetMapping("/download")
public void downloadLocal(HttpServletResponse response) throws IOException {
String path = "D:\\实战项目\\前后端分离\\后台管理系统演示\\FB\\src\\main\\resources\\templates\\用户信息.xlsx";
// 读到流中
InputStream inputStream = new FileInputStream(path);// 文件的存放路径
response.reset();
response.setContentType("application/octet-stream");
//String filename = new File(path).getName();
String filename = "用户信息模板.xlsx";
response.addHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode(filename, "UTF-8"));
ServletOutputStream outputStream = response.getOutputStream();
byte[] b = new byte[1024];
int len;
//从输入流中读取一定数量的字节，并将其存储在缓冲区字节数组中，读到末尾返回-1
while ((len = inputStream.read(b)) > 0) {
outputStream.write(b, 0, len);
}
inputStream.close();
}
模板存放的项目路径，如下图所示：
2.5 完整的UserController
package com.ppj.controller;
import cn.hutool.core.collection.CollUtil;
import cn.hutool.poi.excel.ExcelReader;
import cn.hutool.poi.excel.ExcelUtil;
import cn.hutool.poi.excel.ExcelWriter;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import org.springframework.web.bind.annotation.*;
import javax.annotation.Resource;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.net.URLEncoder;
import java.util.Arrays;
import java.util.List;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.ppj.common.Result;
import com.ppj.service.IUserService;
import com.ppj.entity.User;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;
/**
* <p>
* 前端控制器
* </p>
*
* @author ppj
* @since 2024-04-20
*/
@RestController
@RequestMapping("/user")
public class UserController {
@Resource
private IUserService userService;
// 新增或者更新
@PostMapping
public Result save(@RequestBody User user) {
userService.saveOrUpdate(user);
return Result.success();
}
@DeleteMapping("/{userIds}")
public Result delete(@PathVariable Integer[] userIds) {
userService.removeByIds(Arrays.asList(userIds));
return Result.success();
}
@GetMapping
public Result findAll() {
return Result.success(userService.list());
}
@GetMapping("/{id}")
public Result findOne(@PathVariable Integer id) {
return Result.success(userService.getById(id));
}
@GetMapping("/page")
public Result findPage(@RequestParam Integer pageNum,
@RequestParam Integer pageSize,
@RequestParam(required = false) String username,
@RequestParam(required = false) String address) {
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
if(username != null){
queryWrapper.like("username",username);
}
if(address != null){
queryWrapper.like("address",address);
}
return Result.success(userService.page(new Page<>(pageNum, pageSize), queryWrapper));
}
/*
导出
*/
@GetMapping("/export")
public void export(HttpServletResponse response) throws Exception {
// 从数据库查询出所有的数据
List<User> list = userService.list();
// 通过工具类创建writer 写出到磁盘路径
// ExcelWriter writer = ExcelUtil.getWriter(filesUploadPath + "/用户信息模板.xlsx");
// 在内存操作，写出到浏览器
ExcelWriter writer = ExcelUtil.getWriter(true);
// 参数true时只导出有别名的
writer.setOnlyAlias(true);
//自定义标题别名
writer.addHeaderAlias("username", "用户名");
writer.addHeaderAlias("password", "密码");
writer.addHeaderAlias("nickname", "昵称");
writer.addHeaderAlias("email", "邮箱");
writer.addHeaderAlias("phone", "电话");
writer.addHeaderAlias("address", "地址");
writer.addHeaderAlias("createTime", "创建时间");
// 一次性写出list内的对象到excel，使用默认样式，强制输出标题
writer.write(list, true);
// 设置浏览器响应的格式
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");
ServletOutputStream out = response.getOutputStream();
writer.flush(out, true);
out.close();
writer.close();
}
/*
导入
*/
@PostMapping("/import")
public Boolean imp(MultipartFile file) throws Exception {
InputStream inputStream = file.getInputStream();
ExcelReader reader = ExcelUtil.getReader(inputStream);
//自定义标题别名 反转
reader.addHeaderAlias("用户名", "username");
reader.addHeaderAlias("密码", "password");
reader.addHeaderAlias("昵称", "nickname");
reader.addHeaderAlias("邮箱", "email");
reader.addHeaderAlias("电话", "phone");
reader.addHeaderAlias("地址", "address");
// reader.addHeaderAlias("创建时间", "createTime");
// 方式1：(推荐) 通过 javabean的方式读取Excel内的对象，但是要求表头必须是英文，跟javabean的属性要对应起来
List<User> list = reader.readAll(User.class);
userService.saveBatch(list);
return true;
}
/**
* 指想要下载的文件的路径
* @param response
* @功能描述 下载文件:将输入流中的数据循环写入到响应输出流中，而不是一次性读取到内存
*/
@GetMapping("/download")
public void downloadLocal(HttpServletResponse response) throws IOException {
String path = "D:\\实战项目\\前后端分离\\后台管理系统演示\\FB\\src\\main\\resources\\templates\\用户信息模板.xlsx";
// 读到流中
InputStream inputStream = new FileInputStream(path);// 文件的存放路径
response.reset();
response.setContentType("application/octet-stream");
//String filename = new File(path).getName();
String filename = "用户信息模板.xlsx";
response.addHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode(filename, "UTF-8"));
ServletOutputStream outputStream = response.getOutputStream();
byte[] b = new byte[1024];
int len;
//从输入流中读取一定数量的字节，并将其存储在缓冲区字节数组中，读到末尾返回-1
while ((len = inputStream.read(b)) > 0) {
outputStream.write(b, 0, len);
}
inputStream.close();
}
}
启动测试结果如下效果所示：
导出结果
导入测试
下载模板测试
3.前端编写
3.1 导出
<el-button type="warning" @click="handleExport" class="ml-5">导出 <i class="el-icon-top"></i></el-button>
// 导出
handleExport(){
window.open('http://localhost:9000/user/export');
this.$message.success("导出成功");
},
3.2 导入
// =======第一步
<el-button type="success" @click="handleImport" class="ml-5">导入 <i class="el-icon-bottom"></i></el-button>
// =======第二步
handleImport(){
this.upload.title = '用户导入'
this.upload.open = true
},
// ======第三步
<!-- 用户导入对话框 -->
<el-dialog :title="upload.title" :visible.sync="upload.open" width="400px">
<el-upload
ref="upload"
:limit="1"
accept=".xlsx, .xls"
:action="upload.url"
:disabled="upload.isUploading"
:on-progress="handleFileUploadProgress"
:on-success="handleFileSuccess"
:auto-upload="false"
drag
>
<i class="el-icon-upload"></i>
<div class="el-upload__text">
将文件拖到此处，或
<em>点击上传</em>
</div>
<div class="el-upload__tip" slot="tip">
<el-link type="info" style="font-size: 16px;color:green" @click="importTemplate">下载模板</el-link>
</div>
<div class="el-upload__tip" style="color: red" slot="tip">
提示：仅允许导入“xls”或“xlsx”格式文件！
</div>
</el-upload>
<div slot="footer" class="dialog-footer">
<el-button type="primary" @click="submitFileForm">确 定</el-button>
<el-button @click="upload.open = false">取 消</el-button>
</div>
</el-dialog>
// data定义
//用户导入参数
upload: {
//是否显示弹出层（用户导入）
open: false,
//弹出层标题（用户导入）
title: "",
//上传的地址
url: "http://localhost:9000/user/import",
},
// ======第四步
handleImport(){
this.upload.title = '用户导入'
this.upload.open = true
},
importTemplate(){
this.$message.success("正在下载模版");
window.open('http://localhost:9000/user/download')
},
//文件上传处理
handleFileUploadProgress(event,file,fileList){
//this.upload.isUploading = true;
this.loading = true;
},
//文件上传成功处理
handleFileSuccess(response,file,fileList){
this.loading = false;
this.upload.open = false;
// this.upload.isUploading = false;
this.$refs.upload.clearFiles();
this.$message.success("导入成功");
this.getList()
},
//提交上传文件
submitFileForm(){
this.$refs.upload.submit();
}
3.3 完整的User.vue
<template>
<div>
<div style="margin: 10px 0">
<el-input style="width: 200px" placeholder="请输入名称" suffix-icon="el-icon-search" v-model="username"></el-input>
<el-input style="width: 200px" placeholder="请输入地址" suffix-icon="el-icon-position" class="ml-5" v-model="address"></el-input>
<el-button class="ml-5" type="primary" @click="getList">搜索</el-button>
<el-button icon="el-icon-refresh" size="mini" @click="resetQuery">重置</el-button>
</div>
<div style="margin: 10px 0">
<el-button type="primary" @click="handleAdd">新增 <i class="el-icon-circle-plus-outline"></i></el-button>
<el-button type="warning" plain icon="el-icon-edit" size="mini" :disabled="single" @click="handleUpdate">修改</el-button>
<el-button type="danger" :disabled="multiple" @click="handleDelete">删除 <i class="el-icon-remove-outline"></i></el-button>
<!-- <el-upload action="http://localhost:9000/user/import" :show-file-list="false" accept="xlsx" :on-success="handleImport" style="display: inline-block">-->
<!-- <el-button type="primary" class="ml-5">导入 <i class="el-icon-bottom"></i></el-button>-->
<!-- </el-upload>-->
<el-button type="success" @click="handleImport" class="ml-5">导入 <i class="el-icon-bottom"></i></el-button>
<el-button type="warning" @click="handleExport" class="ml-5">导出 <i class="el-icon-top"></i></el-button>
</div>
<el-table v-loading="loading" :data="tableData" border stripe :header-cell-class-name="headerBg" @selection-change="handleSelectionChange">
<el-table-column type="selection" width="55"></el-table-column>
<el-table-column prop="id" label="序号" width="140"></el-table-column>
<el-table-column prop="username" label="用户名" width="140"></el-table-column>
<el-table-column prop="nickname" label="昵称" width="140"></el-table-column>
<el-table-column prop="email" label="邮箱" width="200"></el-table-column>
<el-table-column prop="address" label="地址" width="140"></el-table-column>
<el-table-column prop="createTime" label="创建时间" width="140"></el-table-column>
<el-table-column label="操作" align="center">
<template v-slot="scope">
<el-button type="success" @click="handleUpdate(scope.row)">编辑 <i class="el-icon-edit"></i></el-button>
<el-button type="danger" @click="handleDelete(scope.row)">删除 <i class="el-icon-remove-outline"></i></el-button>
</template>
</el-table-column>
</el-table>
<div style="padding: 10px 0">
<el-pagination
class="page"
@size-change="handleSizeChange"
@current-change="handleCurrentChange"
:page-sizes="[5, 10]"
:page-size="pageSize"
layout="total, sizes, prev, pager, next, jumper"
:total="total">
</el-pagination>
</div>
<!-- 用户信息 -->
<el-dialog title="用户信息" :visible.sync="dialogFormVisible" width="30%" >
<el-form label-width="80px" size="small">
<el-form-item label="用户名">
<el-input v-model="form.username" autocomplete="off"></el-input>
</el-form-item>
<el-form-item label="昵称">
<el-input v-model="form.nickname" autocomplete="off"></el-input>
</el-form-item>
<el-form-item label="邮箱">
<el-input v-model="form.email" autocomplete="off"></el-input>
</el-form-item>
<el-form-item label="电话">
<el-input v-model="form.phone" autocomplete="off"></el-input>
</el-form-item>
<el-form-item label="地址">
<el-input v-model="form.address" autocomplete="off"></el-input>
</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>
<!-- 用户导入对话框 -->
<el-dialog :title="upload.title" :visible.sync="upload.open" width="400px">
<el-upload
ref="upload"
:limit="1"
accept=".xlsx, .xls"
:action="upload.url"
:disabled="upload.isUploading"
:on-progress="handleFileUploadProgress"
:on-success="handleFileSuccess"
:auto-upload="false"
drag
>
<i class="el-icon-upload"></i>
<div class="el-upload__text">
将文件拖到此处，或
<em>点击上传</em>
</div>
<div class="el-upload__tip" slot="tip">
<el-link type="info" style="font-size: 16px;color:green" @click="importTemplate">下载模板</el-link>
</div>
<div class="el-upload__tip" style="color: red" slot="tip">
提示：仅允许导入“xls”或“xlsx”格式文件！
</div>
</el-upload>
<div slot="footer" class="dialog-footer">
<el-button type="primary" @click="submitFileForm">确 定</el-button>
<el-button @click="upload.open = false">取 消</el-button>
</div>
</el-dialog>
</div>
</template>
<script>
export default {
name: "User",
data(){
return {
tableData: [],
pageSize: 5,
total: 0,
pageNum: 1,
username: '',
address: '',
collapseBtnClass: 'el-icon-s-fold',
isCollapse: false,
sideWidth: 200,
logoTextShow: true,
headerBg: 'headerBg',
dialogFormVisible: false,
form: {},
// 遮罩层
loading: true,
// 选中数组
ids: [],
// 非单个禁用
single: true,
// 非多个禁用
multiple: true,
//用户导入参数
upload: {
//是否显示弹出层（用户导入）
open: false,
//弹出层标题（用户导入）
title: "",
//是否禁用上传
// isUploading: false,
//是否更新已经存在的用户数据
//updateSupport: 0,
//设置上传的请求头部
//headers: "",
//上传的地址
url: "http://localhost:9000/user/import",
},
}
},
created() {
this.getList();
},
methods: {
getList(){
this.loading = true;
this.request.get('/user/page',
{
params: {
pageNum: this.pageNum,
pageSize: this.pageSize,
username: this.username,
address: this.address
}
}
).then(res => {
this.tableData = res.data.records;
this.total = res.data.total;
this.loading = false;
})
},
handleSizeChange(val) {
this.pageSize = val;
},
handleCurrentChange(val) {
this.pageNum = val;
this.getList();
},
// 新增
handleAdd(){
this.dialogFormVisible = true;
this.form = {};
},
save(){
this.request.post("/user",this.form).then(res => {
if(res.code === "200" || res.code === 200){
this.$message.success("操作成功")
}else {
this.$message.error("操作失败")
}
this.dialogFormVisible = false;
this.getList();
})
},
// 修改
handleUpdate(row){
this.form = row;
this.dialogFormVisible = true;
},
// 删除
handleDelete(row){
let _this = this;
const userIds = row.id || this.ids;
this.$confirm('是否确认删除用户编号为"' + userIds + '"的数据项？', '删除用户', {
confirmButtonText: '确定',
cancelButtonText: '取消',
type: 'warning'
}).then(() => {
_this.request.delete("/user/"+userIds).then(res=>{
if(res.code === "200" || res.code === 200){
_this.$message.success("删除成功")
}else {
_this.$message.error("删除失败")
}
this.getList();
})
}).catch(() => {
});
},
// 多选框选中数据
handleSelectionChange(selection) {
this.ids = selection.map(item => item.id);
this.single = selection.length != 1;
this.multiple = !selection.length;
},
// 重置按钮
resetQuery(){
this.username = undefined;
this.address = undefined;
this.getList();
},
// 导出
handleExport(){
window.open('http://localhost:9000/user/export');
this.$message.success("导出成功");
},
// handleImport(){
// this.$message.success('导入成功')
// this.getList()
// },
handleImport(){
this.upload.title = '用户导入'
this.upload.open = true
},
importTemplate(){
this.$message.success("正在下载模版");
window.open('http://localhost:9000/user/download')
},
//文件上传处理
handleFileUploadProgress(event,file,fileList){
//this.upload.isUploading = true;
this.loading = true;
},
//文件上传成功处理
handleFileSuccess(response,file,fileList){
this.loading = false;
this.upload.open = false;
// this.upload.isUploading = false;
this.$refs.upload.clearFiles();
this.$message.success("导入成功");
this.getList()
},
//提交上传文件
submitFileForm(){
this.$refs.upload.submit();
}
}
}
</script>
<style scoped>
</style>
3.4 最终效果
总结
- 本篇博客主要实现数据导入、导出和模板的下载功能，然后进行页面的设计，这些功能都是很常用的
- 难点主要是导入部分，接口注意以下标题转换，前端使用element的手动上传文件
声明
项目源于此地址：程序员青戈
写在最后
如果此文对您有所帮助，请帅戈靓女们务必不要吝啬你们的Zan，感谢！！不懂的可以在评论区评论，有空会及时回复。
文章会一直更新
扫一扫
289
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
