前端读取Excel文件并解析

前端如何解释Excel呢

平时项目中对于Excel的导入解析是很常见的功能，一般都是放在后端执行；但是也有特殊的情况，偶尔也有要求说前端执行解析，判空，校验等，最后组装成后端接口想要的数据结构。

前端如何解释Excel呢

因为我使用插件执行的 you know

安装插件

<code>npm install xlsx@0.14.1 // 0.14.1 是我使用的版本

还有个nanoid

npm i nanoid

此处我没有使用安装的这个 而是使用 自定的代码如下：

// index.js

const createId = () => {

return (

Number(Math.random().toString().substr(2, 7) + Date.now()).toString(36) +

Date.now()

)

}

export {

createId as default,

createId

}

安装已经完成了 当然 依然 you know

import XLSX from 'xlsx'

import nanoid from 'xxxx/xxx/index'

报一丝 差点忘记了 template

<el-upload class="upload" action="" :auto-upload="false" :show-file-list="false" :multiple="false" :on-change="(file, fileList) => importTemp(file, fileList)">code>

<el-button v-permission="'are you ok'" type="primary" size="small" plain class="flex" >you know the name of the custom button</el-button>code>

</el-upload>

finally

methods: {

importTemp(file, fileList) {

const fileReader = new FileReader()

fileReader.onload = ev => {

try {

const data = ev.target.result

const workbook = XLSX.read(data, {

type: 'binary'

})

const sheet = Object.keys(workbook.Sheets)[1] // 我是用的是第二个

const json = XLSX.utils.sheet_to_json(workbook.Sheets[sheet]) //

const worksheet = workbook.Sheets[sheet]

const jsonData = XLSX.utils.sheet_to_json(worksheet, { header: 1 })

if (!jsonData.length) return

const headers = jsonData[0]

if (!headers.length) return

const colorParam = headers.find(i => /xxx/.test(i))

const transtypeParam = headers.find(i => /xxx/.test(i))

const operationTypeParam = headers.find(i => /xxx/.test(i))

const systemCodeParam = headers.find(i => /xxx/.test(i))

const platCodeParam = headers.find(i => /xxx/.test(i))

const truckingBillNoParam = headers.find(i => /xxx/.test(i))

const delivyPlanTypeParam = headers.find(i => /xxx/.test(i))

const targetObj = {}

const nameMap = { // 定义必填的字段

systemCode: systemCodeParam,

platCode: platCodeParam,

operationType: operationTypeParam,

truckingBillNo: truckingBillNoParam,

transType: transtypeParam,

vehicleNumber: '车牌号',

carColor: colorParam,

driverName: '司机姓名',

idcard: '司机身份证号',

driverPhone: '司机手机号',

clientCompanyCode: 'xxx',

clientCompanyName: 'xxx名称',

// messageBatchNo: 'xxx批次号',

// detailsCounts: '明细条数',

billId: 'xxx',

billDependId: 'xxx',

factoryBillId: 'xxx',

delivyPlanType: delivyPlanTypeParam

}

for (let [idx, el] of json.entries()) {

let tempObj = {

}

for (const key of Object.keys(nameMap)) {

const value = el[nameMap[key]]

if (!value && value !== 0) {

this.$message.warning(`第${idx + 2}行，字段: ${nameMap[key]} 为必填值`)

return

}

tempObj[key] = value

}

tempObj = {

...tempObj,

queueId: el['车辆排队号']

}

if (targetObj[tempObj.truckingBillNo]) {

targetObj[tempObj.truckingBillNo].push(tempObj)

} else {

targetObj[tempObj.truckingBillNo] = [tempObj]

}

}

const targetList = Object.keys(targetObj).map((key, i) => {

const mainitem = targetObj[key][0]

const target = {}

Object.keys(mainitem).map(key => {

if (!['qqq', 'xxxx', 'ssss', 'wwww'].includes(key)) {

const val = (mainitem[key] + '').replace(/\s/gi, '')

target[key] = ['null', 'undefined'].includes(val) ? '' : val // 判空下

}

})

target.details = targetObj[key].map(item => {

return {

billId: item.qqq,

billDependId: item.xxxx,

factoryBillId: item.ssss,

delivyPlanType: item.wwww

}

})

target.detailsCounts = target.details.length

target.messageBatchNo = nanoid() + i

return target

})

// 后台请求接口

import({ // 你自己的哦

importDataList: targetList

}).then(res => {

const { msg, status } = res

if (status) {

this.$message.success('发送成功')

} else {

this.$message.error(msg ?? '操作失败')

}

})

} catch (e) {

console.log(e, 'error')

}

}

fileReader.readAsBinaryString(file.raw)

}

}

catch

双人行也有我师焉：哎呦不错哦