vue 使用js XLSX读取 excel 转换日期格式

最近要实现一个功能，大概就是前端读取excel 文件，excel内容展示在页面上，用户确认无误后再上传至后端，使用的XLSX来解析excel,读取日期的时候是数字，解决这个问题花了蛮久时间，记录一下

xlsx 官方github

1.首先要 安装 XLSX

npm install xlsx
npm install moment

2.再导入

import XLSX from 'xlsx'
import moment from 'moment'

3.用的是element-ui 的 el-upload组件上传，绑定upload

upload (file, fileList) {
      let files = {0: file.raw}
      this.readExcel1(files)
    }

4.读取excel，日期格式需要加上 cellDates: true  参数， el-table-column  需要显示的时候也要格式化

 readExcel1 (files) {
      // console.log(files)
      if (files.length <= 0) {
        return false
      } else if (!/\.(xls|xlsx)$/.test(files[0].name.toLowerCase())) {
        this.$Message.error('上传格式不正确，请上传xls或者xlsx格式')
        return false
      }

      const fileReader = new FileReader()
      fileReader.onload = (ev) => {
        try {
          const data = ev.target.result
          const workbook = XLSX.read(data, {
            type: 'binary',
            cellDates: true
          })
          const wsname = workbook.SheetNames[0]// 取第一张表
          const ws = XLSX.utils.sheet_to_json(workbook.Sheets[wsname])// 生成json表格内容
          this.list = ws
        } catch (e) {
          return false
        }
      }
      fileReader.readAsBinaryString(files[0])
    }

<el-table-column :formatter="dateFormat" align="center" label="日期" prop="日期" />

dateFormat: function (row, column) {
      var date = row[column.property]
      if (date === undefined) {
        return ''
      }
      return moment(date).format('YYYY-MM-DD')
    }

最后附在所有代码

<template>
  <div class="app-container">
    <!-- 查询和其他操作 -->
    <el-upload
      class="upload-demo"
      ref="upload"
      :action=uploadUrl()
      :on-preview="handlePreview"
      :on-remove="handleRemove"
      :on-success="handleAvatarSuccess"
      :on-change="upload"
      accept=".xlsx"
      :file-list="fileList"
      :auto-upload="false">
      <el-button slot="trigger" size="small" type="primary">选取文件</el-button>
      <el-button style="margin-left: 10px;" size="small" type="success" @click="submitUpload">上传到服务器</el-button>
      <div slot="tip" class="el-upload__tip">只能上传xlsx文件,选取文件确认无误后点击上传服务器</div>
    </el-upload>

    <!-- 查询结果 -->
    <el-table v-loading="listLoading" :data="list" element-loading-text="正在查询中。。。" border size="mini" highlight-current-row>
      <el-table-column :formatter="dateFormat" align="center" label="日期" prop="日期" />
      <el-table-column align="center" label="应用名称" prop="应用名称" />
      <el-table-column align="center" label="广告id" prop="广告id" />
      <el-table-column align="center" label="广告源" prop="广告源" />
      <el-table-column align="center" label="收入金额" prop="收入金额"/>
      <el-table-column align="center" label="广告触发类型" prop="广告触发类型"/>
    </el-table>
  </div>
</template>

<script>
import XLSX from 'xlsx'
import moment from 'moment'
export default {
  name: 'importAdIncome',
  data () {
    return {
      fileList: [],
      downloadLoading: false,
      listLoading: false,
      list: []
    }
  },
  created () {
  },
  methods: {
    submitUpload () {
      this.$refs.upload.submit()
    },
    handleRemove (file, fileList) {
      // console.log(file, fileList)
    },
    handlePreview (file) {
      // console.log(file)
    },
    handleAvatarSuccess (response, file, fileList) {
      // console.log(response)
      if (response && response.code === '200') {
        this.$notify.success({
          title: '成功',
          message: '导入成功'})
      }
    },
    upload (file, fileList) {
      let files = {0: file.raw}
      this.readExcel1(files)
    },
    readExcel1 (files) {
      // console.log(files)
      if (files.length <= 0) {
        return false
      } else if (!/\.(xls|xlsx)$/.test(files[0].name.toLowerCase())) {
        this.$Message.error('上传格式不正确，请上传xls或者xlsx格式')
        return false
      }

      const fileReader = new FileReader()
      fileReader.onload = (ev) => {
        try {
          const data = ev.target.result
          const workbook = XLSX.read(data, {
            type: 'binary',
            cellDates: true
          })
          const wsname = workbook.SheetNames[0]// 取第一张表
          const ws = XLSX.utils.sheet_to_json(workbook.Sheets[wsname])// 生成json表格内容
          this.list = ws
        } catch (e) {
          return false
        }
      }
      fileReader.readAsBinaryString(files[0])
    },
    uploadUrl () {
      return process.env.BASE_API + '/upload/importIncomeExcel'
    },
    dateFormat: function (row, column) {
      var date = row[column.property]
      if (date === undefined) {
        return ''
      }
      return moment(date).format('YYYY-MM-DD')
    }
  }
}
</script>

<style scoped>
</style>