需求:
需要将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();