Excel的导入可分为2种

一.前端为主导,进行数据的处理

 

二.是后端为主导,进行数据的处理

前端步骤:

1.安装插件

npm i xlsx -S

2.引入(vue-admin-element中的已有的方案),通过路由路径定位到源码位置

3.在自己的项目中定义2个文件

• 使用组件：src/components/UploadExcel/index.vue(这是在vue-admin-element直接cv来的,可以直接复制修改)

<template>
  <div>
    <input ref="excel-upload-input" class="excel-upload-input" type="file"
 accept=".xlsx, .xls" @change="handleClick" />
    <div class="drop" @drop="handleDrop"
 @dragover="handleDragover" @dragenter="handleDragover">
      Drop excel file here or
      <el-button :loading="loading" style="margin-left:16px;"
 size="mini" type="primary" @click="handleUpload">
        Browse
      </el-button>
    </div>
  </div>
</template>

<script>
import XLSX from 'xlsx'

export default {
  props: {
    beforeUpload: Function, // eslint-disable-line
    onSuccess: Function // eslint-disable-line
  },
  data() {
    return {
      loading: false,
      excelData: {
        header: null,
        results: null
      }
    }
  },
  methods: {
    generateData({ header, results }) {
      this.excelData.header = header
      this.excelData.results = results
      this.onSuccess && this.onSuccess(this.excelData)
    },
    handleDrop(e) {
      e.stopPropagation()
      e.preventDefault()
      if (this.loading) return
      const files = e.dataTransfer.files
      if (files.length !== 1) {
        this.$message.error('Only support uploading one file!')
        return
      }
      const rawFile = files[0] // only use files[0]

      if (!this.isExcel(rawFile)) {
        this.$message.error('Only supports upload .xlsx, .xls, .csv suffix files')
        return false
      }
      this.upload(rawFile)
      e.stopPropagation()
      e.preventDefault()
    },
    handleDragover(e) {
      e.stopPropagation()
      e.preventDefault()
      e.dataTransfer.dropEffect = 'copy'
    },
    handleUpload() {
      this.$refs['excel-upload-input'].click()
    },
    handleClick(e) {
      const files = e.target.files
      const rawFile = files[0] // only use files[0]
      if (!rawFile) return
      this.upload(rawFile)
    },
    upload(rawFile) {
      this.$refs['excel-upload-input'].value = null 
// fix can't select the same excel

      if (!this.beforeUpload) {
        this.readerData(rawFile)
        return
      }
      const before = this.beforeUpload(rawFile)
      if (before) {
        this.readerData(rawFile)
      }
    },
    readerData(rawFile) {
      this.loading = true
      return new Promise((resolve, reject) => {
        const reader = new FileReader()
        reader.onload = e => {
          const data = e.target.result
          const workbook = XLSX.read(data, { type: 'array' })
          const firstSheetName = workbook.SheetNames[0]
          const worksheet = workbook.Sheets[firstSheetName]
          const header = this.getHeaderRow(worksheet)
          const results = XLSX.utils.sheet_to_json(worksheet)
          this.generateData({ header, results })
          this.loading = false
          resolve()
        }
        reader.readAsArrayBuffer(rawFile)
      })
    },
    getHeaderRow(sheet) {
      const headers = []
      const range = XLSX.utils.decode_range(sheet['!ref'])
      let C
      const R = range.s.r
      /* start in the first row */
      for (C = range.s.c; C <= range.e.c; ++C) {
        /* walk every column in the range */
        const cell = sheet[XLSX.utils.encode_cell({ c: C, r: R })]
        /* find the cell in the first row */
        let hdr = 'UNKNOWN ' + C // <-- replace with your desired default
        if (cell && cell.t) hdr = XLSX.utils.format_cell(cell)
        headers.push(hdr)
      }
      return headers
    },
    isExcel(file) {
      return /\.(xlsx|xls|csv)$/.test(file.name)
    }
  }
}
</script>

<style scoped>
.excel-upload-input {
  display: none;
  z-index: -9999;
}
.drop {
  border: 2px dashed #bbb;
  width: 600px;
  height: 160px;
  line-height: 160px;
  margin: 0 auto;
  font-size: 24px;
  border-radius: 5px;
  text-align: center;
  color: #bbb;
  position: relative;
}
</style>

定义组件：@/import/index.vue(

注意:1.使用以上的组件,要有on-success完成之后的回调函数才行,要具体操作

         2.如果是崭新的页面展示导入Excel,要注册import的路由)

 步骤:

       1.导入注册使用以上的组件

       2.处理数据

        本节是按接口要求，处理excel导入的数据

          处理内容包含：

• 字段中文转英文。excel中读入的是姓名,而后端需要的是username

• 日期处理。从excel中读入的时间是一个number值，而后端需要的是标准日期。

       3.符合后端接口要求,发送axios请求

<template>
  <div>
    <UploadExcel
      :on-success="handleSuccess"
    />
  </div>
</template>
<script>
import { formatExcelDate } from '@/utils/index.js'
import { importEmployee } from '@/api/employee'
import UploadExcel from '@/components/UploadExcel'
export default {
  name: 'Import',
  components: {
    UploadExcel
  },
  methods: {
    // 1. 字段中文->英文
    // 2. 还原excel中的日期
    formatData(header, results) {
      const mapInfo = {
        '入职日期': 'timeOfEntry',
        '手机号': 'mobile',
        '姓名': 'username',
        '转正日期': 'correctionTime',
        '工号': 'workNumber',
        '部门': 'departmentName',
        '聘用形式': 'formOfEmployment'
      }

      // results: [{姓名：xxx, 手机号:xxxx},{姓名：xxx, 手机号:xxxx}]
      // ....
      // 1. 数组-->数组 map
      // data: [{username：xxx, mobile:xxxx},{username：xxx, mobile:xxxx}]
      const data = results.map(obj => {
        // obj是{姓名：xxx, 手机号:xxxx}
        // 希望得到新对象：key是英文的，value不变
        const newObj = {}
        // 对于所有的obj的属性来说
        // Object.keys(obj) ==> ['姓名', '手机号']
        Object.keys(obj).forEach(zhkey => {
          const enKey = mapInfo[zhkey] // 从中文的key 转成 英文的key
          // if (key是日期) {
          if (enKey === 'timeOfEntry' || enKey === 'correctionTime') {
            // 对日期做特殊处理(有一个专门的函数来处理excel读出来的日期时间)
            // new Date('2020-12-12') 做这个转换，是因为后端需要标准日期格式
            newObj[enKey] = new Date(formatExcelDate(obj[zhkey]))
          } else {
            newObj[enKey] = obj[zhkey] // value不变
          }
        })
        return newObj
      })

      return data
    },
    async handleSuccess({ header, results }) {
      console.log('做格式转换之前', results)

      // 1. 做格式转换
      const data = this.formatData(header, results)
      console.log('做格式转换之后', data)
      // 2. 发请求
      const res = await importEmployee(data)
      console.log('批量添加', res)
      this.$router.back()
      // 后退回到员工管理页面
      // console.log('handleSuccess', obj)
    }
  }
}
</script>

Bug:员工管理-实现excel导入-数据处理-excel日期时间处理

 原因：excel内部进行特殊的编码

解决

需要借助公式来进行还原。在utils/index.js中定义如下

// 把excel文件中的日期格式的内容转回成标准时间

export function formatExcelDate(numb, format = '/') {

const time = new Date((numb - 25567) * 24 * 3600000 - 5 * 60 * 1000 - 43 * 1000 - 24 * 3600000 - 8 * 3600000)

time.setYear(time.getFullYear())

const year = time.getFullYear() + ''

const month = time.getMonth() + 1 + ''

const date = time.getDate() + ''

if (format && format.length === 1) {

return year + format + month + format + date

}

return year + (month < 10 ? '0' + month : month) + (date < 10 ? '0' + date : date)

}