vue手动拖入和导入excel模版

1.列表按钮
导入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
            }
        } 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86

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;
}