Demand:

Excel files need to be parsed, uploaded to a table in the database and downloaded.

Technology:

ElementUI for front-end vue, spring boot for back-end, mybatis

Achieving results:

 

Upload implementation:

1) Field type selection

The field type used to store files in the database is BLOB; the corresponding type of the field in the entity is byte []; and the corresponding field in Mybatis is Mapper is#{fileContent, jdbcType=BLOB}.

2) Front-end code

<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">Select file</el-button>
  <el-button
    style="margin-left: 10px; background-image: linear-gradient(-90deg, #1565C0 0%, #1E88E5 97%);"
    size="small"
    type="primary"
    @click="submitUpload"
  >upload</el-button>
</el-upload>

submitUpload() {
  if(this.formSearch.type== ""){
    this.$message.error('Please select the type of file you want to upload!');
  }else{
    this.$refs.upload.submit();
  }
}

Explanation: Here: data is not only for me to transfer files to the background, but also for type parameters, which can be unnecessary. The on-success method is an operation after the server successfully returns, such as reloading the current page. May refer to Element Official Online Upload Component

3) back end code

Controller layer: MutipartFile array receives files

@RequestMapping(value="/uploadExcel",method = RequestMethod.POST)
public String uploadExcel(@RequestParam("file") MultipartFile[] file, Integer type){

}

Service Layer: Excel parses first and then drops out of the library. Key places where multipartFile can be converted to byte [] type through multipartFile.getBytes()

for (MultipartFile multipartFile : file) {
	
	// Document MD5 weighting
	String fileMD5 = FileUtils.getFileMD5String(multipartFile);
	List<String> recordList = xxx.getAllUploadMd5s();
	if (recordList.contains(fileMD5)){
		throw new Exception("The file has been uploaded");
	}

	// EasyExcel parsing
	InputStream inputStream = multipartFile.getInputStream();
	ExcelListener excelListener = new ExcelListener();
	Sheet sheet = null;
	int sheetNo = 1;
	int headLineMun = 1;
	EasyExcelFactory.readBySax(inputStream, sheet, excelListener);
	inputStream.close();
	
	// File upload to database
	this.addExcelFile(multipartFile.getBytes(), recordId);
	
}

Dao level

<insert id="saveExcelFile" parameterType="com.xxx.xxx.ExcelUploadFileDto">
	insert into EXCEL_UPLOAD_FILE (EXCEL_CONTENT) values (#{excelContent,jdbcType=BLOB})
</insert>

Download implementation

1) Front-end code

<el-table-column prop="fileName" label="File name">
  <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) Backend code

Controller layer

@RequestMapping(value="/downloadExcel",method = RequestMethod.GET)
public String downloadExcel(HttpServletResponse response, Integer id){

}

Service level

byte[] fileByte = xxx.getExcelContent();

// Download files as streams
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();