📢📢📢📣📣📣
哈喽!大家好,我是一位上进心十足,拥有极强学习力的在校大学生😜😜😜
我所写的博客的领域是面向后端技术的学习,未来会持续更新更多的【后端技术】以及【学习心得】。 偶尔会分享些前端基础知识,会更新实战项目,以及开发应用!
❤️❤️❤️ 感谢各位大可爱小可爱! ❤️❤️❤️
在做MVC的项目中,常常会有涉及对很多数据的操作,而经常做的就是导入和导出了,我们先来了解一下导入导出。
导出:就是将你页面查询的数据导出到Excel表格,当然还有导出其他文件。
导入:就是将你需要添加的数据导入到数据库。
一、功能展示
二、项目搭建
1. 新建一个Springboot项目
File -> New Project 根据下图指引做:
2. 点击next,选择Springboot版本和组件,版本可以跟我不同。
3. 点击 Finish完成项目创建,然后开始导入Pom依赖:
如果依赖下载的太慢的话,可以使用aliyun,方法是在你的 pom.xml里加入下面的代码:
<repositories>
<repository>
<id>nexus-aliyun</id>
<name>nexus-aliyun</name>
<url>http://maven.aliyun.com/nexus/content/groups/public/</url>
<releases>
<enabled>true</enabled>
</releases>
<snapshots>
<enabled>false</enabled>
</snapshots>
</repository>
</repositories>
<pluginRepositories>
<pluginRepository>
<id>public</id>
<name>aliyun nexus</name>
<url>http://maven.aliyun.com/nexus/content/groups/public/</url>
<releases>
<enabled>true</enabled>
</releases>
<snapshots>
<enabled>false</enabled>
</snapshots>
</pluginRepository>
</pluginRepositories>
4. 导入依赖之后会出现Springboot的启动按钮:
直接点击绿色的三角启动运行项目。
运行效果:
看到这个 8080 表示你的项目启动好了。
二、Hutool工具的引入
在pom.xml里面导入我们的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>
在官网里面对于文件上传也给了一定的讲解
从文件中读取Excel为ExcelReader
ExcelReader reader = ExcelUtil.getReader(FileUtil.file("test.xlsx"));
从流中读取Excel为ExcelReader(比如从ClassPath中读取Excel文件)
ExcelReader reader = ExcelUtil.getReader(ResourceUtil.getStream("aaa.xlsx"));
读取指定的sheet
ExcelReader reader;
//通过sheet编号获取
reader = ExcelUtil.getReader(FileUtil.file("test.xlsx"), 0);
//通过sheet名获取
reader = ExcelUtil.getReader(FileUtil.file("test.xlsx"), "sheet1");
读取大数据量的Excel
private RowHandler createRowHandler() {
return new RowHandler() {
@Override
public void handle(int sheetIndex, int rowIndex, List<Object> rowlist) {
Console.log("[{}] [{}] {}", sheetIndex, rowIndex, rowlist);
}
};
}
ExcelUtil.readBySax("aaa.xlsx", 0, createRowHandler());
三、前端页面
在前端,用的是vue2.0,其实对于上传文件在element-ui里面也有对应的组件,但总感觉差点意思,于是乎对其进行一定的修改
<el-upload action="http://localhost:9090/user/import" :show-file-list="false" accept=".xlsx" :on-success="handleExcelImportSuccess" style="display: inline-block">
<el-button type="primary" class="ml-5"><i class="el-icon-upload"></i>导入</el-button>
</el-upload>
//存入methods里面
handleExcelImportSuccess() {
this.$message.success("文件导入成功")
this.load()
}
基本功能跟组件一样,在点击我们的导入按钮时会让你选择需要上传的文件。导出会进行接口调用,直接跳转到后端页面进行下载请求。
四、后端控制层
在后端,我们在controller里面定义好我们该方法,并对该方法进行命令执行
/**
* 导出接口
*/
@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);
//自定义标题别名
writer.addHeaderAlias("username", "用户名");
writer.addHeaderAlias("password", "密码");
writer.addHeaderAlias("nickname", "昵称");
writer.addHeaderAlias("email", "邮箱");
writer.addHeaderAlias("phone", "联系方式");
writer.addHeaderAlias("address", "地址");
writer.addHeaderAlias("createTime", "创建时间");
writer.addHeaderAlias("avatarUrl", "头像");
//一次性写出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();
}
/**
* excel导入
* @param
* @throws Exception
*/
@PostMapping("/import")
public Result imp(MultipartFile file) throws Exception {
InputStream inputStream = file.getInputStream();
ExcelReader reader = ExcelUtil.getReader(inputStream);
//通过JavaBean的方式读取Excel内的对象,但要求表头必须是英文,跟JavaBean的属性要对应
List<User> list = reader.readAll(User.class);
userService.saveBatch(list);
return Result.success(true);
}
五、核心代码
<template>
<div>
<div style="margin: 10px 0">
<el-input style="width: 200px; margin-left: 10px" placeholder="请输入用户名" clearable suffix-icon="el-icon-user" v-model="username" ></el-input>
<el-button class="ml-5" type="primary" @click="load"><i class="el-icon-search" />搜索</el-button>
<el-button type="warning" @click="reset"><i class="el-icon-refresh" />刷新</el-button>
</div>
<div style="margin: 10px 0">
<el-button type="primary" @click="handleAdd" class="ml-10"><i class="el-icon-circle-plus-outline" />新增</el-button>
<el-popconfirm
class="ml-5"
confirm-button-text='确认'
cancel-button-text='取消'
icon="el-icon-info"
icon-color="red"
title="确定批量删除这些信息吗?"
@confirm="delBatch">
<el-button type="danger" slot="reference" ><i class="el-icon-remove-outline" />删除</el-button>
</el-popconfirm>
<el-upload action="http://localhost:9090/user/import" :show-file-list="false" accept=".xlsx"
:on-success="handleExcelImportSuccess" style="display: inline-block">
<el-button type="primary" class="ml-5"><i class="el-icon-upload"></i>导入</el-button>
</el-upload>
<el-button type="primary" class="ml-5" @click="exp"><i class="el-icon-download" />导出</el-button>
</div>
<el-table :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="username" label="用户名" ></el-table-column>
<el-table-column prop="nickname" label="昵称" ></el-table-column>
<el-table-column prop="email" label="邮箱" ></el-table-column>
<el-table-column prop="phone" label="联系方式" ></el-table-column>
<el-table-column prop="address" label="地址"></el-table-column>
<el-table-column prop="role" label="身份"></el-table-column>
<el-table-column label="操作">
<template slot-scope="scope">
<el-button type="success" @click="handleEdit(scope.row)"><i class="el-icon-edit-outline" />编辑</el-button>
</template>
</el-table-column>
</el-table>
<div style="padding: 10px 0">
<el-pagination
@size-change="handleSizeChange"
@current-change="handleCurrentChange"
:current-page="pageNum"
:page-sizes="[ 5, 10, 15]"
: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 :model="form" label-width="100px" 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>
</div>
</template>
<script>
export default {
name: "User",
data() {
return {
tableData: [],
total: 0,
pageNum: 1,
pageSize: 5,
username: "",
form: {},
dialogFormVisible: false,
multipleSelection: [],
headerBg: "headerBg",
roles: []
}
},
created() {
this.load()
},
methods: {
load: function () {
this.request.get("/user/page", {
params: {
pageNum: this.pageNum,
pageSize: this.pageSize,
username: this.username,
}
}).then(res => {
this.tableData = res.data.records
this.total = res.data.total
})
this.request.get("/role").then(res => {
this.roles = res.data
})
},
home() {
this.$router.push("/")
},
save() {
this.request.post("/user", this.form).then(res => {
if (res.code === '200') {
this.$message.success("保存成功")
this.dialogFormVisible = false
this.load()
} else {
this.$message.error("保存失败")
}
})
},
handleAdd() {
this.dialogFormVisible = true
this.form = {}
},
handleEdit(row) {
this.form = row
this.dialogFormVisible = true
},
handleSelectionChange(val) {
console.log(val)
this.multipleSelection = val;
},
exp() {
window.open("http://localhost:9090/user/export")
},
handleExcelImportSuccess() {
this.$message.success("文件导入成功")
this.load()
},
delBatch() {
let ids = this.multipleSelection.map(v => v.id) //[{}, {}, {}] => [1,2,3]
this.request.post("/user/del/batch", ids).then(res => {
if (res.code === '200') {
this.$message.success("删除用户成功")
this.load()
} else {
this.$message.error("删除用户失败")
}
})
},
reset() {
this.username = ""
this.load()
},
handleSizeChange(pageSize) {
console.log(pageSize)
this.pageSize = pageSize
this.load()
},
handleCurrentChange(pageNum) {
console.log(pageNum)
this.pageNum = pageNum
this.load()
},
}
}
</script>
<style>
.headerBg {
background: #eee!important;
}
</style>
文件上传存储sql
CREATE TABLE `file` (
`id` int NOT NULL AUTO_INCREMENT COMMENT 'ID',
`name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '文件名称',
`type` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '文件类型',
`size` bigint DEFAULT NULL COMMENT '文件大小(kb)',
`url` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '下载链接',
`md5` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '文件md5',
`creat_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT '时间',
`is_delete` tinyint(1) DEFAULT '0' COMMENT '是否删除',
PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=115 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
文件上传核心接口
import cn.hutool.core.io.FileUtil;
import cn.hutool.core.util.IdUtil;
import cn.hutool.core.util.StrUtil;
import cn.hutool.crypto.SecureUtil;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.example.demo.common.Constants;
import com.example.demo.common.Result;
import com.example.demo.entity.Files;
import com.example.demo.mapper.FileMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.data.redis.core.StringRedisTemplate;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;
import javax.annotation.Resource;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.IOException;
import java.net.URLEncoder;
import java.util.List;
@RestController
@RequestMapping("/file")
public class FileController {
@Value("${files.upload.path}")
private String fileUploadPath;
@Value("${server.ip}")
private String serverIp;
@Resource
private FileMapper fileMapper;
@Autowired
private StringRedisTemplate stringRedisTemplate;
@PostMapping("/upload")
public String upload(@RequestParam MultipartFile file) throws IOException {
String originalFilename = file.getOriginalFilename();
String type = FileUtil.extName(originalFilename);
long size = file.getSize();
// 定义一个文件唯一的标识码
String fileUUID = IdUtil.fastSimpleUUID() + StrUtil.DOT + type;
File uploadFile = new File(fileUploadPath + fileUUID);
// 判断配置的文件目录是否存在,若不存在则创建一个新的文件目录
File parentFile = uploadFile.getParentFile();
//判断目录是否存在,不存在就新建
if (!parentFile.exists()) {
parentFile.mkdirs();
}
String url;
// 获取文件的md5
String md5 = SecureUtil.md5(file.getInputStream());
// 从数据库查询是否存在相同的记录
Files dbFiles = getFileByMd5(md5);
if (dbFiles != null) {
url = dbFiles.getUrl();
} else {
// 上传文件到磁盘
file.transferTo(uploadFile);
// 数据库若不存在重复文件,则不删除刚才上传的文件
url = "http://" + serverIp + ":9090/file/" + fileUUID;
}
//存储到数据库
Files saveFile = new Files();
saveFile.setName(originalFilename);
saveFile.setType(type);
saveFile.setSize(size/1024);
saveFile.setUrl(url);
saveFile.setMd5(md5);
fileMapper.insert(saveFile);
return url;
}
@GetMapping("/{fileUUID}")
public void download(@PathVariable String fileUUID, HttpServletResponse response) throws IOException {
// 根据文件的唯一标识码获取文件
File uploadFile = new File(fileUploadPath + fileUUID);
// 设置输出流的格式
ServletOutputStream os = response.getOutputStream();
response.addHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileUUID, "UTF-8"));
response.setContentType("application/octet-stream");
// 读取文件的字节流
os.write(FileUtil.readBytes(uploadFile));
os.flush();
os.close();
}
/**
* 通过文件的md5查询文件
* @param md5
* @return
*/
private Files getFileByMd5(String md5) {
// 查询文件的md5是否存在
QueryWrapper<Files> queryWrapper = new QueryWrapper<>();
queryWrapper.eq("md5", md5);
List<Files> filesList = fileMapper.selectList(queryWrapper);
return filesList.size() == 0 ? null : filesList.get(0);
}
// @CachePut(value = "files", key = "'frontAll'")
@PostMapping("/update")
public Result update(@RequestBody Files files) {
fileMapper.updateById(files);
flushRedis(Constants.FILES_KEY);
return Result.success();
}
@GetMapping("/detail/{id}")
public Result getById(@PathVariable Integer id) {
return Result.success(fileMapper.selectById(id));
}
//清除一条缓存,key为要清空的数据
// @CacheEvict(value="files",key="'frontAll'")
@DeleteMapping("/{id}")
public Result delete(@PathVariable Integer id) {
Files files = fileMapper.selectById(id);
files.setIsDelete(true);
fileMapper.updateById(files);
flushRedis(Constants.FILES_KEY);
return Result.success();
}
@PostMapping("/del/batch")
public Result deleteBatch(@RequestBody List<Integer> ids) {
// select * from sys_file where id in (id,id,id...)
QueryWrapper<Files> queryWrapper = new QueryWrapper<>();
queryWrapper.in("id", ids);
List<Files> files = fileMapper.selectList(queryWrapper);
for (Files file : files) {
file.setIsDelete(true);
fileMapper.updateById(file);
}
return Result.success();
}
/**
* 分页查询接口
* @param pageNum
* @param pageSize
* @param name
* @return
*/
@GetMapping("/page")
public Result findPage(@RequestParam Integer pageNum,
@RequestParam Integer pageSize,
@RequestParam(defaultValue = "") String name) {
QueryWrapper<Files> queryWrapper = new QueryWrapper<>();
// 查询未删除的记录
queryWrapper.eq("is_delete", false);
queryWrapper.orderByDesc("id");
if (!"".equals(name)) {
queryWrapper.like("name", name);
}
return Result.success(fileMapper.selectPage(new Page<>(pageNum, pageSize), queryWrapper));
}
// 删除缓存
private void flushRedis(String key) {
stringRedisTemplate.delete(key);
}
}
⛵小结
以上就是对SpringBoot整合Mybatis实现文件导入导出简单的概述,不仅使得我们的项目更加的趋于完美,增加可用性,也提升了我们对于编程的能力和思维!
如果这篇文章有帮助到你,希望可以给作者点个赞👍,创作不易,如果有对后端技术、前端领域感兴趣的,也欢迎关注 ,我将会给你带来巨大的收获与惊喜💝💝💝!