vue 前端读取Excel文件并解析

前端读取Excel文件并解析

- 前端如何解释Excel呢

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

前端如何解释Excel呢

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

安装插件

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)">
          <el-button v-permission="'are you ok'" type="primary" size="small" plain class="flex" >you know the name of the custom button</el-button>
        </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)
    }
}