当前位置：首页 > news >正文

Vue+elementui 纯前端实现Excel导入导出功能（区分表头标题）

news 来源：原创 2024/5/9 7:44:56

引入插件

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 = thisreader.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 = jsonDatathat.lineData = jsonData// 在这里处理读取到的数据};reader.readAsArrayBuffer(file);that.fileOpen = falsethat.$refs.cpkLine.lineData = this.tableData;that.LSL = this.form.lslthat.MBZ = this.form.targetValuethat.USL = this.form.uslsetTimeout(()=>{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;},