引入插件
import * as XLSX from "xlsx/xlsx.mjs";import { read, utils } from 'xlsx/xlsx.mjs';
上传文件方法
// 上传文件状态改变时的钩子,添加文件、上传成功和上传失败时都会被调用 async handle(ev) { //改变表格key值 this.$refs.cpkTable.loading = true; this.$refs.cpkLine.myChart.showLoading({ text: "加载中...", color: "#1890ff", textColor: "#1890ff", showSpinner: false, maskColor: "rgba(255, 255, 255, 0.7)",}); this.tableKey = true; this.file = ev.raw; this.excelName = this.file.name; //截取表格文件名 this.excelName = this.excelName.substring( 0, this.excelName.lastIndexOf(".") ); console.log("上传的未解析源文件", this.file); if (!this.file) { console.log("文件打开失败"); return; } else { //<!-- 用来解析表头的代码--> // let data = await this.readFile(file); // let workbook = XLSX.read(data, { type: "binary" }); //解析二进制格式数据 // console.log("二进制数据的解析:", workbook); // let worksheet = workbook.Sheets[workbook.SheetNames[0]]; //获取第一个Sheet // // 调用解析表头方法 // this.getHeader(worksheet); // let result = XLSX.utils.sheet_to_json(worksheet); //转换为json数据格式 // console.log("最终解析的 json 格式数据:", result); // this.tableData = result; // // 调用处理表头函数 // this.setTable(); //<!-- 用来解析表头的代码--> // 读取 Excel 文件 //<!-- 不需要解析表头的代码--> const file = this.file; const reader = new FileReader(); const that = this reader.onload = function(e) { const data = new Uint8Array(e.target.result); const workbook = read(data, { type: 'array' }); const sheetName = workbook.SheetNames[0]; const worksheet = workbook.Sheets[sheetName]; const jsonData = utils.sheet_to_json(worksheet, { header: 1, raw: false, defval: '' }); // const worksheet = workbook.Sheets[workbook.SheetNames[0]]; // const jsonData = XLSX.utils.sheet_to_json(worksheet, { header: 1 }); that.tableData = jsonData that.lineData = jsonData // 在这里处理读取到的数据 }; reader.readAsArrayBuffer(file); that.fileOpen = false that.$refs.cpkLine.lineData = this.tableData; that.LSL = this.form.lsl that.MBZ = this.form.targetValue that.USL = this.form.usl setTimeout(()=>{ that.$message.success(this.excelName+'导入成功') that.$refs.upload.clearFiles(); that.$refs.cpkTable.loading = false; that.$refs.cpkLine.myChart.hideLoading(); },800) // that.tableLength = that.tableData.length // this.setTable() //<!-- 不需要解析表头的代码--> } },
解析获取到表头的数据
getHeader(sheet) { // const XLSX = XLSX; const headers = []; const range = XLSX.utils.decode_range(sheet["!ref"]); // worksheet['!ref'] 是工作表的有效范围 let C; /* 获取单元格值 start in the first row */ const R = range.s.r; // 行 // C 列 let i = 0; for (C = range.s.c; C <= range.e.c; ++C) { var cell = sheet[ XLSX.utils.encode_cell({ c: C, r: R }) ]; /* 根据地址得到单元格的值find the cell in the first row */ var hdr = "UNKNOWN" + C; // 如果有空表头,会替换为您想要的默认值replace with your desired default // XLSX.utils.format_cell 生成单元格文本值 if (cell && cell.t) hdr = XLSX.utils.format_cell(cell); if (hdr.indexOf("UNKNOWN") > -1) { if (!i) { hdr = "__EMPTY"; } else { hdr = "__EMPTY_" + i; } i++; } headers.push(hdr); } // 保存至data中 this.excelHeader = headers; return headers; },
设置中英文映射 就是字段对应
// 设置表格中英文映射 setTable() { const headers = this.excelHeader; const excellist = this.tableData; const tableTitleData = []; // 存储表格表头数据 const tableMapTitle = {}; // 设置表格内容中英文对照用 headers.forEach((_, i) => { tableMapTitle[_] = "cord" + i; tableTitleData.push({ prop: "cord" + i, label: _, width: 100, }); }); console.log("表格头标题:", tableTitleData); // 映射表格内容属性名为英文 const newTableData = []; excellist.forEach((_) => { const newObj = {}; Object.keys(_).forEach((key) => { newObj[tableMapTitle[key]] = _[key]; }); newTableData.push(newObj); }); this.tableColumn = tableTitleData; this.tableData = newTableData; },