Vue中使用el-upload+XLSX实现解析excel文件为json数据_vue xlsx解析excel_霸道流氓气质

网络投稿 02-07 1767

场景

业务要求为实现每天上报各部门计划人数，需要通过excel导入数据。

前端可以解析excel数据并进行初步的格式校验。

导入成功之后解析的数据

excel里的数据为

注：

博客：https://blog.csdn.net/badao_liumang_qizhi? 关注公众号霸道的程序猿获取编程相关电子书、教程推送与免费下载。

实现 npm安装以及注意事项 npm i xlsx@0.16.8

这里是指定版本安装的，一开始未指定版本，安装之后提示如下两个问题

Failed to execute 'readAsBinaryString' on 'FileReader': parameter 1 is not of type 'Blob'

?Cannot read property 'read' of undefined ??? at FileReader.reader

代码实现流程

首先在页面中引入

import XLSX from "xlsx";

然后页面添加el-upload

??? <el-upload ????? ref="upload" ????? :limit="1" ????? accept=".xlsx, .xls" ????? :headers="headers" ????? :action="upLoadUrl + '?planDateString=' + this.planDate" ????? :disabled="isUploading" ????? :on-progress="handleFileUploadProgress" ????? :on-success="handleFileSuccess" ????? :auto-upload="false" ????? :before-upload="beforeUpload" ????? drag ??? > ????? <i class="el-icon-upload"></i> ????? <div class="el-upload__text">将文件拖到此处，或<em>点击上传</em></div> ????? <div class="el-upload__tip text-center" slot="tip"> ??????? <span>仅允许导入xls、xlsx格式文件。</span> ????? </div> ??? </el-upload>

设置其before-upload属性，上传文件之前的钩子，参数为上传的文件，若返回false或者

返回Promise且被reject则停止上传。

实现其bofore-upload

??? //上传文件之前的钩子 ??? beforeUpload(file) { ????? //解析excel ????? this.analysisExcel(file).then((tableJson) => { ??????? if (tableJson && tableJson.length > 0) { ????????? //成功解析出数据 ????????? //只取第一个sheet的数据 ????????? let dataExcel = tableJson[0]; ????????? console.log("数据", dataExcel); ????????? console.log(JSON.stringify(dataExcel.sheet)); ??????? } ????? }); ??? }, ??? //解析excel ??? analysisExcel(file) { ????? return new Promise(function (resolve, reject) { ??????? const reader = new FileReader(); ??????? reader.onload = function (e) { ????????? const data = e.target.result; ????????? let datajson = XLSX.read(data, { ??????????? type: "binary", ????????? }); ????????? const result = []; ????????? datajson.SheetNames.forEach((sheetName) => { ??????????? result.push({ ????????????? sheetName: sheetName, ????????????? sheet: XLSX.utils.sheet_to_json(datajson.Sheets[sheetName]), ??????????? }); ????????? }); ????????? resolve(result); ??????? }; ??????? reader.readAsBinaryString(file); ????? }); ??? },

完整示例代码

<template> ?  ? <el-dialog :title="title" :visible.sync="open" width="400px" append-to-body> ??? <div class="block"> ????? <span class="demonstration">计划日期: </span> ????? <el-date-picker ??????? v-model="planDate" ??????? type="date" ??????? placeholder="选择计划日期" ??????? size="small" ??????? value-format="yyyy-MM-dd" ????? > ????? </el-date-picker> ??? </div> ??? <br /> ??? <el-upload ????? ref="upload" ????? :limit="1" ????? accept=".xlsx, .xls" ????? :headers="headers" ????? :action="upLoadUrl + '?planDateString=' + this.planDate" ????? :disabled="isUploading" ????? :on-progress="handleFileUploadProgress" ????? :on-success="handleFileSuccess" ????? :auto-upload="false" ????? :before-upload="beforeUpload" ????? drag ??? > ????? <i class="el-icon-upload"></i> ????? <div class="el-upload__text">将文件拖到此处，或<em>点击上传</em></div> ????? <div class="el-upload__tip text-center" slot="tip"> ??????? <span>仅允许导入xls、xlsx格式文件。</span> ????? </div> ??? </el-upload> ??? <div slot="footer" class="dialog-footer"> ????? <el-button type="primary" @click="submitFileForm">确定</el-button> ????? <el-button @click="open = false">取消</el-button> ??? </div> ? </el-dialog> </template> <script> import { getToken } from "@/utils/auth"; import XLSX from "xlsx"; import moment from "moment"; export default { ? data() { ??? return { ????? // 是否显示弹出层（用户导入） ????? open: false, ????? // 弹出层标题（用户导入） ????? title: "", ????? // 是否禁用上传 ????? isUploading: false, ????? //计划日期 ????? planDate: new Date(), ????? // 设置上传的请求头部 ????? headers: { Authorization: "Bearer " + getToken() }, ????? // 上传的地址 ????? upLoadUrl: "", ??? }; ? }, ? mounted() { ??? //默认计划日期为明天 ??? this.planDate = moment().subtract(-1, "days").format("YYYY-MM-DD"); ? }, ? methods: { ??? /** 导入按钮操作 */ ??? handleImport(data) { ????? this.title = data.title; ????? this.upLoadUrl = process.env.VUE_APP_BASE_API + data.upLoadUrl; ????? this.open = true; ??? }, ??? // 提交上传文件 ??? submitFileForm() { ????? this.$refs.upload.submit(); ??? }, ??? // 文件上传中处理 ??? handleFileUploadProgress() { ????? this.isUploading = true; ??? }, ??? // 文件上传成功处理 ??? handleFileSuccess(response) { ????? this.open = false; ????? this.isUploading = false; ????? this.$refs.upload.clearFiles(); ????? this.$alert( ??????? "<div style='overflow: auto;overflow-x: hidden;max-height: 70vh;padding: 10px 20px 0;'>" + ????????? response.msg + ????????? "</div>", ??????? "导入结果", ??????? { dangerouslyUseHTMLString: true } ????? ); ????? //上传数据成功后重新请求数据 ????? this.$emit("getList"); ??? }, ??? //上传文件之前的钩子 ??? beforeUpload(file) { ????? //解析excel ????? this.analysisExcel(file).then((tableJson) => { ??????? if (tableJson && tableJson.length > 0) { ????????? //成功解析出数据 ????????? //只取第一个sheet的数据 ????????? let dataExcel = tableJson[0]; ????????? console.log("数据", dataExcel); ????????? console.log(JSON.stringify(dataExcel.sheet)); ??????? } ????? }); ??? }, ??? //解析excel ??? analysisExcel(file) { ????? return new Promise(function (resolve, reject) { ??????? const reader = new FileReader(); ??????? reader.onload = function (e) { ????????? const data = e.target.result; ????????? let datajson = XLSX.read(data, { ??????????? type: "binary", ????????? }); ????????? const result = []; ????????? datajson.SheetNames.forEach((sheetName) => { ??????????? result.push({ ????????????? sheetName: sheetName, ????????????? sheet: XLSX.utils.sheet_to_json(datajson.Sheets[sheetName]), ??????????? }); ????????? }); ????????? resolve(result); ??????? }; ??????? reader.readAsBinaryString(file); ????? }); ??? }, ? }, }; </script> <style> </style>