vue手动拖入和导入excel模版

1.列表按钮
<el-button @click=“importExcel(scope.row.id)” size=“small” type=“text”>导入excel模版

2.按钮弹框

3.data定义数据
data () {
return {
projectId: ‘’,
importDialogVisible: false,
fileList: [], //手动上传
upload_file: ‘’, //导入excel模版名称
verifyFile: ‘’, //校验文件
file: {}, //excel文件对象
}
}

4.获取上传校验文件数据(这个数据要和上传excel模版使用md5加密做对比，如果和上传md5数据一致说明用户没有修改excel数据)
//获取校验文件md5
verifyFileExcel(e){
// 错误情况判断
const files = e.target.files
if (files.length <= 0) {
return false
} else if (!/.(txt)KaTeX parse error: Expected '}', got 'EOF' at end of input: … this.message({
message: “上传格式不正确，请上传txt格式”,
type: “warning”
})
return false
}
const file = event.target.files[0]
const reader = new FileReader()
reader.onload = (event) => {
this.getVerifyFile(event.target.result)
}
reader.readAsText(file)
},
//不能在读取方法中使用data定义的属性赋值，要使用外部方法传值
getVerifyFile(data){
this.verifyFile = data
},
4.导入前清理数据
importExcel (id) {
this.importDialogVisible = true
this.projectId = id
//清空上传表单
this.upload_file = ‘’
this.fileList = []
if(this.KaTeX parse error: Expected '}', got 'EOF' at end of input: … this.refs.fileInput1.value = null
}
if(this.KaTeX parse error: Expected '}', got 'EOF' at end of input: … this.refs.fileInput2.value = null
}
//初始化校验文件位空值
this.verifyFile = null
},
5.手动上传Excel模版
Excel(e) {
let that = this
// 错误情况判断
const files = e.target.files
if (files.length <= 0) {
return false
} else if (!/.(xls|xlsx)KaTeX parse error: Expected '}', got 'EOF' at end of input: … this.message({
message: “上传格式不正确，请上传xls或者xlsx格式”,
type: “warning”
})
return false
} else {
that.upload_file = files[0].name
}
//将上传excel文件转字节流
const reader = new FileReader()
const file = event.target.files[0]
//将Excel模版生成流对象
const formData = new FormData()
formData.append(‘file’,file)
this.file = formData
const XLSX = require(‘xlsx’)
reader.onload = event => {
const data = new Uint8Array(event.target.result)
const workbook = XLSX.read(data, { type: ‘array’ })
const sheetName = workbook.SheetNames[0]
const worksheet = workbook.Sheets[sheetName]
const json = XLSX.utils.sheet_to_json(worksheet, { header: 1 })
//将获取的数据传入整理数据方法中，这个方法可以传入后端接口
this.getFileList(json)
}
reader.readAsArrayBuffer(file)
}
6.拖拽上传文件
handleDrop(e) {
// 阻止浏览器的默认行为
e.preventDefault()
const file = e.dataTransfer.files[0]
if(file.size <= 0){
return false
}else if (!/.(xls|xlsx)KaTeX parse error: Expected '}', got 'EOF' at end of input: … this.message({
message: “上传格式不正确，请上传xls或者xlsx格式”,
type: “warning”
})
} else {
this.upload_file = file.name
}
const reader = new FileReader()
this.fileList = []
const XLSX = require(‘xlsx’)
reader.onload = event => {
const data = new Uint8Array(event.target.result)
console.log(data)
const workbook = XLSX.read(data, { type: ‘array’ })
const sheetName = workbook.SheetNames[0]
const worksheet = workbook.Sheets[sheetName]
const json = XLSX.utils.sheet_to_json(worksheet, { header: 1 })
this.getFileList(json)
}
reader.readAsArrayBuffer(file)
}
7.提交Excel模版数据(主要是校验文件作对比，查询接口是否已经上传同样的Excel模版数据，整理Excel模版数据,传给后端接口)

        async submitForm() {//1.判断校验文件是否上传if(this.verifyFile === '' || this.verifyFile === null){this.$message({message: "请上传校验文件！",type: "warning"})return}//2.返回生成校验文件var fileData = await api.getFileType(this.file)//查询是否上传excel报表(保存使用上传校验文件数据做唯一字段)var type = await api.getVerifyFile(this.verifyFile)if(type.length > 0){this.$message({message: this.upload_file + "已上传,请勿重复上传！",type: "warning"})return}//对比校验文件(对比成功提交Excel数据)if(this.verifyFile === fileData){//获取excel模版数据const files = []const dataList = []if(this.fileList.length > 0){for (let i = 0; i < this.fileList.length; i++) {if(i > 1){files.push(this.fileList[i])}}//将数据转换成对象files.forEach(item => {var param = {projectId: this.projectId,scannedName: null,problemNumber: null,scanPageNumber: null,errorRate: null,problemStatisticsName: this.upload_file.replace(".xlsx","").replace(".xls",""),verifyFile: this.verifyFile,problemStatisticsFileUrl: this.problemStatisticsFileUrl}//将excel模版数据保存到对象中//定义excel对象for (const key in item) {if(key === '0'){param.scannedName = item[key]} else if(key === '1'){param.problemNumber = item[key]} else if(key === '2'){param.scanPageNumber = item[key]} else if(key === '3'){param.errorRate = item[key]}}dataList.push(param)})}//向后端接口Excel模版数据api.importPersonnelProblem(dataList).then((data) => {this.$message({type: 'success',message: '数据导入成功!'})this.getDataList()this.importDialogVisible = false}).catch((err) => {util.$message.showInfo2(err)})} else {this.$message({type: 'warning',message: 'excel数据改动,校验文件失败!'})//刷新列表方法this.getDataList()this.importDialogVisible = false}} 

dataList数据返给接口:

8.上传excel返回加密的校验文件和校验文件数据对比
接口使用MultipartFile对象接收
/**
* 生成md5校验文件
* @return
*/
@Override
public String getMd5File(MultipartFile file) {
InputStream is = null;
try {
is = file.getInputStream();
} catch (IOException e) {
e.printStackTrace();
}
int iAvail = 0;
try {
iAvail = is.available();
} catch (IOException e) {
e.printStackTrace();
}
//2.转为字节流
byte[] bytes = new byte[iAvail];
try {
is.read(bytes);
} catch (IOException e) {
e.printStackTrace();
}
//3.将文件名转成utf-8字节数组
String str = file.getOriginalFilename().replace(“.xlsx”,“”).replace(“.xls”,“”);
byte[] byteArray = str.getBytes(StandardCharsets.UTF_8);
//4.合并文件名utf-8和excel文件字节数组
byte[] type = addBytes(byteArray ,bytes);
//5.md5加密生成校验文件
String md5 = DigestUtils.md5Hex(type).toUpperCase();
System.out.println(“md5大写：” + md5);
return md5;
}