前两天遇到一个需求，就是前端做一个识别xls文件数据并将其转换成json格式,然后渲染成表格，并使其不符合规则的产生高亮
下载所需要的依赖包 npm install xlsx
导入包
import XLSX from "xlsx";
1.添加监听事件
2.负责读取Excel文件
mounted() {
this.$refs.upload.addEventListener("change", e => {
this.readExcel(e);
});
},
readExcel(e) {
//表格导入
var that = this;
const files = e.target.files;
console.log(files);
if (!that.verifyExcelFileOnUpload(files)) {//此处方法在下方2.1处
//驗證文件格式
return false;
}
const fileReader = new FileReader();
//当文件加载成功后执行
fileReader.onload = ev => {
try {
that.dsActors = [];
const data = ev.target.result;
const workbook = XLSX.read(data, {
type: "binary"
});
var sheet_name_list = workbook.SheetNames;
const ws = XLSX.utils.sheet_to_json(
workbook.Sheets[sheet_name_list[0]],
{ header: 1 }
);
//注：变量ws 就是获取到文件的数据
if (that.verifyDataAfterUpload(ws)) {//此方法在下方2.2处(为业务代码可忽略)
var mode = {
actorList: this.dsActors,
LoginUserId:this.$store.state.currentUser.Id
}; //此处为业务代码数据模型，
that.checkUploadData(mode);
} else {
this.$data.alertConfig.show(
this.$t("導入的數據有誤，請檢查高亮部分"),
"danger",
300
);
}
} catch (e) {
return false;
}
};
fileReader.readAsBinaryString(files[0]);
}, //讀取Excel文件
2.1判断文件格式是否正确
verifyExcelFileOnUploadverifyExcelFileOnUpload(files) {
console.log(files[0].size);
if (files[0].size > 2 * 1024 * 1024) {
this.$data.alertConfig.show(
this.$t("上傳文件太大，僅支持2MB以下的文件"),
"danger",
3
);
return false;
}
if (files.length <= 0) {
//如果没有文件名
return false;
} else if (!/\.(xls|xlsx)$/.test(files[0].name.toLowerCase())) {
//此处代码块内容为我的代码，做示范性作用，执行有可能报错，
/*
$("#i-file").val(""); //清空文件加載路徑
this.$data.alertConfig.show(
this.$t("上傳文件格式錯誤，目前只支援xls或xlsx格式"),
"danger",
3
);
*/
return false;
}
return true;
}, //驗證Excel文件格式
2.2判断加载的数据是否合格 （注这个是可以不用写的，只不过为了记住业务我写上去的，与前面关联不大，只用于做业务判断）
verifyDataAfterUpload(ws) {
var that = this;
that.dsActors = []; //清空接收数据
const actorTypes = [300010, 300020, 300030]; //參與者類型
var localValidatePassed = true;
var tempName = [];
var j = 0; //行號 ID
for (var i = 2; i < ws.length; i++) {
var sheetData = {
UserName: ws[i][0] != undefined ? ws[i][0] : "",
Name: ws[i][1] != undefined ? ws[i][1] : "",
TypeId: ws[i][2] != undefined ? ws[i][2] : "",
ErrorCellsIndex: [],
Id: i - 2
};
if (sheetData.Name == "") {
sheetData.ErrorCellsIndex.push(1);
localValidatePassed = false;
}
if (sheetData.UserName == "") {
sheetData.ErrorCellsIndex.push(0);
localValidatePassed = false;
}
if (actorTypes.indexOf(sheetData.TypeId) == -1) {
sheetData.ErrorCellsIndex.push(2);
localValidatePassed = false;
}
tempName.push(sheetData.UserName);
that.dsActors.push(sheetData);
}
var duplicateDataValidate = that.validateDuplicateData(tempName);
localValidatePassed = localValidatePassed && duplicateDataValidate;
return localValidatePassed;
}, //判斷加載數據是否合格
2.3判断重复用户名(业务代码)
validateDuplicateData(arr) {
var that = this;
var list = [];
for (var i = 0; i < arr.length; i++) {
var hasRead = false;
for (var k = 0; k < list.length; k++) {
if (i == list[k]) {
hasRead = true;
}
}
if (hasRead) {
break;
}
var _index = i,
haveSame = true;
for (var j = i + 1; j < arr.length; j++) {
if (arr[i] == arr[j]) {
list.push(j);
if (that.dsActors[i].ErrorCellsIndex.indexOf(0) == -1) {
that.dsActors[i].ErrorCellsIndex.push(0);
}
that.dsActors[j].ErrorCellsIndex.push(0);
haveSame = false;
}
}
}
return haveSame;
}, //驗證重複用戶名
