需求:
需要将Excel文件进行解析,然后上传到数据库某个表中,并能够进行下载
技术:
前端vue之ElementUI,后端springboot,mybatis
实现效果:
上传实现:
1)字段类型选择
数据库中用于存储文件的字段类型为BLOB;实体中该字段对应的类型为 byte[] ;Mybatis的Mapper对应字段为 #{fileContent, jdbcType=BLOB}
2)前端代码
<el-upload
class="upload-demo"
ref="upload"
action="http://localhost:xxx/xxx/uploadExcel"
:data="{'type': formSearch.type}"
:on-success="handleSuccess"
:auto-upload="false">
<el-button slot="trigger" size="small" type="primary" width="80px">选取文件</el-button>
<el-button
style="margin-left: 10px; background-image: linear-gradient(-90deg, #1565C0 0%, #1E88E5 97%);"
size="small"
type="primary"
@click="submitUpload"
>上传</el-button>
</el-upload>
submitUpload() {
if(this.formSearch.type== ""){
this.$message.error('请选择需要上传的文件类型!');
}else{
this.$refs.upload.submit();
}
}
说明:这边:data是我除了把文件传到后台,还要额外传type参数,可以不需要。 :on-success方法是服务端成功返回后的操作,如重新加载当前页面。 可参考Element官网上传组件
3)后端代码
Controller层:MutipartFile数组接收文件
@RequestMapping(value="/uploadExcel",method = RequestMethod.POST)
public String uploadExcel(@RequestParam("file") MultipartFile[] file, Integer type){
}
Service层:Excel先解析后落库,关键地方multipartFile可以通过 multipartFile.getBytes() 转成 byte[] 类型
for (MultipartFile multipartFile : file) {
// 文件MD5判重
String fileMD5 = FileUtils.getFileMD5String(multipartFile);
List<String> recordList = xxx.getAllUploadMd5s();
if (recordList.contains(fileMD5)){
throw new Exception("该文件已经上传");
}
// EasyExcel解析
InputStream inputStream = multipartFile.getInputStream();
ExcelListener excelListener = new ExcelListener();
Sheet sheet = null;
int sheetNo = 1;
int headLineMun = 1;
EasyExcelFactory.readBySax(inputStream, sheet, excelListener);
inputStream.close();
// 文件上传到数据库
this.addExcelFile(multipartFile.getBytes(), recordId);
}
Dao层
<insert id="saveExcelFile" parameterType="com.xxx.xxx.ExcelUploadFileDto">
insert into EXCEL_UPLOAD_FILE (EXCEL_CONTENT) values (#{excelContent,jdbcType=BLOB})
</insert>
下载实现
1)前端代码
<el-table-column prop="fileName" label="文件名称">
<template slot-scope="scope">
<a :href='"http://localhost/xxx/xxx/downloadExcel?id=" + scope.row.id'
class="buttonText">{{scope.row.fileName}}</a>
</template>
</el-table-column>
2)后端代码
Controller层
@RequestMapping(value="/downloadExcel",method = RequestMethod.GET)
public String downloadExcel(HttpServletResponse response, Integer id){
}
Service层
byte[] fileByte = xxx.getExcelContent();
// 以流的形式下载文件
res.setContentType("application/x-msdownload");
res.setCharacterEncoding("UTF-8");
res.setHeader("Content-Disposition", "attachment; filename=" +
new String(xxx.getFileName().getBytes("UTF-8"), "iso-8859-1"));
OutputStream outputStream = new BufferedOutputStream(res.getOutputStream());
outputStream.write(fileByte);
outputStream.flush();
outputStream.close();