需要用到xlsx 插件,指定0.16.9版本
npm install xlsx@0.16.9 --save
还需要一个日期的转换方法,已经封装好了Date.js 文件
// 把excel文件中的日期格式的内容转回成标准时间
function formatExcelDate(numb, format = '/') {
const time = new Date((numb - 25567) * 24 * 3600000 - 5 * 60 * 1000 - 43 * 1000 - 24 * 3600000 - 8 * 3600000)
time.setYear(time.getFullYear())
const year = time.getFullYear() + ''
const month = time.getMonth() + 1 + ''
const date = time.getDate() + ''
if (format && format.length === 1) {
return year + format + month + format + date
}
return year + (month < 10 ? '0' + month : month) + (date < 10 ? '0' + date : date)
};
const MyDate={
formatExcelDate,
}
export default MyDate;
vue 页面
<el-button type="primary" @click="dialogvisible1=true">上传文件</el-button>
<!--上传文件弹出框-->
<el-dialog title="上传文件" :visible.sync="dialogvisible1" style="width:100%;" @close="OnClose1">
<el-upload
ref="upload"
:limit="1"
accept=".xls,.xlsx"
name="files"
multiple
:action="baseURL"
:on-change="handleFileChange1"
:before-remove="handleFileRemove"
:auto-upload="false"
:file-list="upload.fileList"
>
<el-button slot="trigger" size="small" type="primary">选取文件</el-button>
<el-button style="margin-left: 10px;" size="small" type="success" @click="submitFileForm">上传到服务器</el-button>
</el-upload>
<el-table style="width: auto" border :data="tableData" id="tableData">
<template v-for="(item,index) in tableHead">
<el-table-column :prop="item.column_name" :label="item.column_comment" :key="index"
v-if="item.column_name !== 'id'"></el-table-column>
</template>
</el-table>
</el-dialog>
js 实现,需要先引用封装的日期转换文件,xlsx
import aDate from '../static/js/Dates'
import XLSX from 'xlsx'
export default {
aDate,
},
data(){}
}
js实现的代码
handleFileChange1(file, fileList) {
const types = file.name.split(".")[1];
const fileType = ["xlsx", "xlc", "xlm", "xls", "xlt"].some(
item => item === types
);
if (!fileType) {
alert("格式错误!请重新选择");
return;
}
this.file2Xce(file).then(tabJson => {
if (tabJson && tabJson.length > 0) {
const data = {};
const this_ = this;
const dataSheet=tabJson[0].sheet;
for(var i=0;i<dataSheet.length;i++){
dataSheet[i].出生日期=aDate.formatExcelDate(parseInt(dataSheet[i].出生日期,'/'));
dataSheet[i].入学时间=aDate.formatExcelDate(parseInt(dataSheet[i].入学时间,'/'));
}
this_.card = JSON.stringify(dataSheet);
data.card = this_.card; //中英文转化
this_.excelToTable(data);//调用生成table方法
}
});
this.upload.fileList = fileList;
},
file2Xce(file) {
return new Promise(function (resolve) {
const reader = new FileReader();
let rABS = typeof FileReader !== "undefined" && (FileReader.prototype||{}).readAsBinaryString;
reader.onload = function (e) {
const data = e.target.result;
if (!rABS) {
data = new Uint8Array(data)
}
this.wb = XLSX.read(data, {type: rABS ? 'binary' : 'array'});
const result = [];
this.wb.SheetNames.forEach(sheetName => {
result.push({
sheetName: sheetName,
sheet: XLSX.utils.sheet_to_json(this.wb.Sheets[sheetName])
});
});
resolve(result);
};
// reader.readAsBinaryString(file.raw);
if (rABS) {
reader.readAsBinaryString(file.raw)
} else {
reader.readAsArrayBuffer(file.raw)
}
});
},
// 把excel用转化为table
excelToTable(data) {
const outData = JSON.parse(data.card);
// console.log("excel数据: "+JSON.stringify(outData));
const tableHeadList = [];
let num = 1;
for (const tableHead2 in outData[0]) {
const tableHead1 = {};
tableHead1['column_name'] = "column_name" + num;
tableHead1['column_comment'] = tableHead2;
num = num + 1;
tableHeadList.push(tableHead1);
}
// console.log("表头数据:"+JSON.stringify(tableHeadList));
this.tableHead = tableHeadList;//定制表头
const tableDataList = [];
for (let j = 0; j < outData.length; j++) {
const tableData1 = {};
for (let k = 0; k < tableHeadList.length; k++) {
// console.log("表头字段:"+tableHeadList[k]['column_name']+",表头数据:" + tableHeadList[k]['column_comment']);
for (const outDataKey in outData[j]) {
if (outData[j].hasOwnProperty(outDataKey)) {//对于(可能迭代异常(自定义/继承)成员,可能缺少 hasOwnProperty 检查)的错误的解决,用if语句判断
if (tableHeadList[k]['column_comment'] === outDataKey) {
tableData1[tableHeadList[k]['column_name']] = outData[j][outDataKey];
}
}
}
}
tableDataList.push(tableData1);
}
this.tableData = tableDataList;//给表格内容赋值
},
// 删除之前钩子
handleFileRemove(file, fileList) {
this.upload.fileList = fileList;
},
// 提交上传文件
submitFileForm() {
// 创建新的数据对象
let formData = new FormData();
// 将上传的文件放到数据对象中
this.upload.fileList.forEach(file => {
formData.append('file', file.raw);
this.upload.fileName.push(file.name);
});
// 文件名
formData.append('fileName', this.upload.fileName);
// 自定义上传
this.uploadFile(formData).then(res => {
console.log(res);
if(res.data>0){
this.$refs.upload.clearFiles();
this.$message.success('上传成功!');
this.tableData=[];
this.tableHead=[];
this.upload.fileList=[];
this.dialogvisible1=false;
}else{
this.$message.error(response.msg);
}
})
// .catch(error => {
// this.$message.error('上传失败!');
// });
},
// 封装的上传请求
uploadFile(params) {
this.baseURL='XXXX';//请求的接口
this.myToken=window.localStorage.getItem('token');
/*设置请求头*/
return this.$axios.post('XXXX', params,
{ headers: { 'Content-Type': 'multipart/form-data', token: this.myToken}})
},
不用谢我,可以直接拿来使用,别忘记点个赞。