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

excel不经过后台实现解析和预览（vue）

news 来源：原创 2024/10/1 5:43:50

数据流读取和数据解析方面通过xlsx组件

安装命令 npm install xlsx -S

它先将上传的excel变成流，然后再根据流进行下一步处理。这个流可以交给其他组件处理比如我用的预览组件是用了其他组件（@vue-office/excel）就是把这个流交给其它组件就实现预览了，

@vue-office/exce 预览组件地址如下

#docx文档预览组件
npm install @vue-office/docx vue-demi@0.14.6#excel文档预览组件
npm install @vue-office/excel vue-demi@0.14.6#pdf文档预览组件
npm install @vue-office/pdf vue-demi@0.14.6

如果是vue2.6版本或以下还需要额外安装 @vue/composition-api

npm install @vue/composition-api

<el-upload                class="upload-demo"                ref="upload"                action=""                :auto-upload="false"                :file-list="fileList"                :on-change="handleChange"                multiple                :show-file-list="false"                
>              <el-button type="text">点击上传{{ bankDialogIsShow }}</el-button>                         
</el-upload>

// 上一个html 上传后调用这个，这个方法处理后会调用弹窗，把必要的参数传到弹窗页面去处理显示操作等内容		
handleChange(file,fileList){        this.fileList = [fileList[fileList.length - 1]]; // 只能上传一个Excel，重复上传会覆盖之前的        this.file = file.raw;        let reader = new FileReader()        let _this = this        reader.readAsArrayBuffer(this.file)        const that = thisreader.onload = function () {      that.bankDialogIsShow = true     let buffer = reader.result            let bytes = new Uint8Array(buffer)            let length = bytes.byteLength            let binary = ''            for (let i = 0; i < length; i++) {                binary += String.fromCharCode(bytes[i])            }            let XLSX = require('xlsx')            let wb = XLSX.read(binary, {                type: 'binary'            })that.$refs.bankDialog.open(XLSX,wb,buffer)//这个里面的this，不是当前dom，上面有函数}},

<template><div><el-dialog title="提示" :visible.sync="dialogVisible" :fullscreen="true" :before-close="handleClose"><span slot="footer" class="dialog-footer"><el-button @click="dialogVisible = false">取 消</el-button><el-button type="primary" @click="submist">确 定</el-button></span>excel预览部分<div style="width: 100vw; height: 600px;"><vue-office-pdf:key="componentKey"v-if="officeV":options="options":src="pdfUrl"@rendered="renderedHandler"@error="errorHandler"/></div>操作部分<el-tabs type="border-card" v-model="activeName" v-if="isTableShow" @tab-click="handleClick"> <el-tab-pane :label="name" v-for=" (name, key, index) in SheetNames" :key="key"><el-table style="width: 92vw;height: 200px; " :data="excelDataAllObj[name]" stripe><el-table-column label="序号" width="50" align="center"><template slot-scope="scope"><template v-if="scope.$index == 0"></template><template v-else>{{ scope.row['id'] }}</template></template></el-table-column><el-table-column label="帐号" width="150" align="center"><template slot-scope="scope"><template v-if="scope.$index == 0"><el-select v-model="selColumn[name]['帐号']" placeholder="请选择"><el-option v-for="item in optionsObj[name]" :key="item.value" :label="optionsObj[name][item]":value="optionsObj[name][item]"></el-option></el-select></template><template v-else>{{ scope.row[selColumn[name]['帐号']] }}</template></template></el-table-column>。。。。其他字段</el-table></el-tab-pane></el-tabs></el-dialog></div>
</template><script>
import { excelBankData } from "@/api/tab-bank";
import matchMap from "@/assets/matchMap.json"
import VueOfficePdf from "@vue-office/excel";
import '@vue-office/excel/lib/index.css'
import {Message
} from "element-ui";
export default {props: {// dialogVisible: {// 	type: Boolean,// 	default: false// }, //是否显示id: {type: String,default: ''}, //传递的id},data() {return {componentKey:0,// 预览组件的配置对象，设置xls: true才能预览展示xls类型，否则显示空白options:{xls: true,
//过滤器1beforeTransformData: (workbookData) => {console.log('beforeTransformData ',workbookData)return workbookData}, //底层通过exceljs获取excel文件内容，通过该钩子函数，可以对获取的excel文件内容进行修改，比如某个单元格的数据显示不正确，可以在此自行修改每个单元格的value值。
//过滤器2，我使用了这个 作用是只展示下面修改部分tab选择那个sheet,正常预览是导入有几个sheet就展示几个，这里有个问题是 arr.push(workbookData[this.activeName]) 改变赋值后 预览页面是不刷新的 需要handleClick(tab, event) {this.componentKey += 1;  }变换预览组件:ref，让它以为是新东西强制更新ui                
transformData: (workbookData) => {console.log('transformData ',workbookData)const arr = []arr.push(workbookData[this.activeName])return arr},},officeV:true,pdfUrl:'',matchMap:matchMap,testA: '',activeName: 0,isTableShow: false,dialogVisible: false,outTableData: [{}],outRelExcelMap: {},selColumn: {},outCnEnRel: {'帐号': 'account'},excelDataAll: [],excelDataAllObj: {},optionsObj: {},SheetNames: [],excelDatas: [],htmlExcel:''}},computed: {excelData() {}},methods: {handleClick(tab, event) {console.log(tab, event);// this.officeV = false// this.$nextTick(function(){//   this.officeV = true// })this.componentKey += 1;},findKeyByValue(obj, value) {const result = Object.entries(obj).find(([key, val]) => val === value);return result ? result[0] : null;},
//用于联想提示自动选择下拉框的findMatchKeyInJSON(excelColName){for(let subKey in matchMap){//   console.log(subKey,' matchMap ')if (matchMap.hasOwnProperty(subKey)) {const oneJSON = matchMap[subKey]const matchKey = oneJSON.find(e=> e==excelColName )if(matchKey){return subKey}}}return undefined},async submist() {//SheetNames//excelDataAllObjconst tdata = this.excelDataAllObjconst outCnEnRelTemp = this.outCnEnRellet outData = []for (let j = 0; j < this.SheetNames.length; j++) {const name = this.SheetNames[j]for (let i = 0; i < tdata[name].length; i++) {const excelOne = tdata[name][i]let outOne = {}for (let key in excelOne) {if (excelOne.hasOwnProperty(key)) {const outKeyCN = this.findKeyByValue(this.selColumn[name], key)if (outKeyCN) {const enKey = outCnEnRelTemp[outKeyCN]outOne[enKey] = excelOne[key]}}}outData.push(outOne)}}const respose = await excelBankData(outData)const { code, msg, data } = resposeMessage({message: data,type: msg,})},close() {this.optionsObj = {}this.excelDataAll = {}},getTableVal(keyName) {const t = scope.row[selColumn[keyName]]if (t) {return '笑话'}return "？"},handleClose(done) {this.$confirm('确认关闭？').then(_ => {done();}).catch(_ => { });},open(XLSX, wb,buffer) {this.pdfUrl =  buffer//  console.log('弹窗参数 ' + JSON.stringify(wb))this.dialogVisible = truethis.SheetNames = wb.SheetNamesthis.excelDatas = wb.Sheetsfor (let i = 0; i < wb.SheetNames.length; i++) {const sheetName = wb.SheetNames[i]this.$set(this.selColumn, sheetName, {})this.$set(this.optionsObj, sheetName, {})let excelData = {}excelData = XLSX.utils.sheet_to_json(wb.Sheets[sheetName])this.htmlExcel = XLSX.utils.sheet_to_json(wb.Sheets[sheetName])//  console.log('数据 '+i,wb.SheetNames[i],JSON.stringify(excelData))let outData = []let selOptions = []for (let i = 0; i < excelData.length; i++) {const obj = excelData[i]obj['id'] = i + ''let recordOne = {}outData.push(recordOne)let selOption = {}for (let key in obj) {if (obj.hasOwnProperty(key)) {//  console.log(key, obj[key]);if (key) {const wantColName = this.findMatchKeyInJSON(key)if(wantColName){this.$set(this.selColumn[sheetName], wantColName, key)}//  console.log('wantColName ',wantColName,' key ',key)this.optionsObj[sheetName][key] = key}}}//    // this.excelDataAll = [...excelData]// 固化i变量，防止地址内容变动影响对象键值对对应关系 huangjingnan 240825this.excelDataAllObj[sheetName] = excelData//    console.log(sheetName,' 弹窗中展示 excel上传数据 ', JSON.stringify(this.excelDataAllObj))}}this.isTableShow = true},renderedHandler() {console.log("渲染完成");},errorHandler() {console.log("渲染失败");}},components: {VueOfficePdf,}
};
</script>
<style scoped lang="scss">
/* 自定义滚动条样式 */
.el-table--scrollable-x .el-table__body-wrapper::-webkit-scrollbar {width: 10px;/* 设置滚动条的宽度 */height: 100px;/* 设置滚动条的高度 */
}/* 自定义滚动条滑块样式 */
.el-table--scrollable-x .el-table__body-wrapper::-webkit-scrollbar-thumb {border-radius: 5px;/* 设置滑块的圆角 */background-color: rgba(144, 147, 153, 0.3);/* 设置滑块的背景颜色 */
}/* 自定义滚动条轨道样式 */
.el-table--scrollable-x .el-table__body-wrapper::-webkit-scrollbar-track {background-color: #f0f2f5;/* 设置轨道的背景颜色 */
}:deep .x-spreadsheet-bottombar{display: none;
}
</style>