vue excel导入

vue导入处理Excel表格详解
https://blog.csdn.net/m0_46309087/article/details/125022676

---

import goodImport from './goodsImport'
 onImport(){
      this.$refs.goodImport.init()
    },

<template>
  <div>
    <el-dialog
      :title="'导入'"
      :close-on-click-modal="false"
      append-to-body :before-close="handleClose"
      v-if="visible" :visible.sync="visible" top="5vh" width="400px">
      <div style="width:400px" v-loading="loading">
        <slot></slot>
        <el-upload ref="upload" :limit="1" accept=".xlsx, .xls, .csv" :headers="headers" :action="actionUrl"
                   :disabled="upload.isUploading" :on-progress="handleFileUploadProgress" :on-success="handleFileSuccess"
                   :on-error="handleFileError" :on-change="handleFileChange" :auto-upload="false"  drag>
          <i class="el-icon-upload"></i>
          <div class="el-upload__text">
            将文件拖到此处，或
            <em>点击上传</em>
          </div>
          <div class="el-upload__tip text-center" slot="tip">
            <span>仅允许导入xls、xlsx格式文件。</span>
            <el-link type="primary" :underline="false" style="font-size:12px;vertical-align: baseline;"
                     @click="downExcelTemp">下载模板
            </el-link>
          </div>
        </el-upload>
        <div slot="footer" class="dialog-footer flex justifycenter mt10">
          <el-button type="primary" @click="submitFileForm">确 定</el-button>
          <el-button @click="upload.open = false">取 消</el-button>
          <el-button @click="btnTest">校验结果</el-button>
        </div>
      </div>
    </el-dialog>
    <goods-import-see ref="goodsImportSee"></goods-import-see>
  </div>
</template>

<script>
import { validatenull } from "@/util/validate"
import {downBlobFile} from '@/util/util'
import store from "@/store"
import { env } from "@/config/env"
import * as XLSX from "xlsx"
import request from "@/router/axios"
import goodsImportSee from "./goodsImportSee"

export default {
  name: "goodsImport",
  components:{
    goodsImportSee
  },
  computed: {
    headers: function () {
      const tenantId = store.getters.userInfo.tenantId
      let headers = {
        'Authorization': "Bearer " + store.getters.access_token,
        'TENANT-ID': tenantId ? tenantId : env.VUE_APP_TENANT_ID
      }
      if (env.NODE_ENV == 'development') {
        if (env.VUE_APP_DEV_NAME) {
          headers['VERSION'] = env.VUE_APP_DEV_NAME;
        }
      }
      return headers;
    }
  },
  data(){
    return{
      visible: false,
      upload: {
        open: false,
        isUploading: false
      },
      loading: false,
      errorVisible: false,
      errorData: [],
      excelData: {
        header: null,
        results: null,
        handleJsonToExcelFn: null,
      },
      actionUrl: "/aims/overseagoods/imOverseaGoods"
    }
  },
  methods:{
    init(id) {
      this.visible = true

    },
    handleFileUploadProgress() {
      this.loading = true;
      this.upload.isUploading = true;
    },
    handleFileSuccess(response) {
      this.upload.isUploading = false;
      this.upload.open = false;
      this.$refs.upload.clearFiles();
      this.loading = false;
      this.visible = false
      // 校验失败
      if (response.code === 1) {
        this.$message.error("导入失败，以下数据不合法");
        // this.errorVisible = true;
        // this.errorData = response.data;
        this.$refs.upload.clearFiles();
      } else {
        this.$message.success(response.msg == null ? "导入成功" : response.msg);
        // 刷新表格
        this.$emit("refreshDataList", response.data);
      }
    },
    handleFileError(e) {
      console.log("err", e)
    },
    handleFileChange(file, fileList) {

      let files = { 0: file.raw }// 取到File
      if(!/\.(xls|xlsx|csv)$/.test(file.name.toLowerCase())){
        this.$message.error('上传格式不正确，请上传xls或者xlsx格式')
        return;
      }

      this.readExcel(files);
    },
    readExcel(files) { // 表格导入
      var that = this

      const fileReader = new FileReader()
      fileReader.onload = (ev) => {
        try {

          const data = ev.target.result
          const workbook = XLSX.read(data, {
            type: 'binary'
          })
          const firstSheetName = workbook.SheetNames[0]
          const worksheet = workbook.Sheets[firstSheetName]
          const header = that.getHeaderRow(worksheet)
          const results = XLSX.utils.sheet_to_json(worksheet)
          that.generateData({ header, results })

          // 重写数据
          // this.$refs.upload.value = ''
        } catch (e) {
          console.log("e", e)
          return false
        }
      }
      fileReader.readAsBinaryString(files[0])
    },


    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
    },
    // 目前支持一个sheet，之后再扩展
    jsonToExcel: ({
      json,
      opts = {},
      sheetName = 'default',
      fileName,
      retType
    }) => {
      return new Promise((resolve, reject) => {
        try {
          const s2abFn = (s) => {
            // 字符串转字符流
            let buf = new ArrayBuffer(s.length)
            let view = new Uint8Array(buf)
            for (let i = 0; i != s.length; ++i) view[i] = s.charCodeAt(i) & 0xff
            return buf
          }
          //===============================================
          const ws = XLSX.utils.json_to_sheet(json)
          const wb = XLSX.utils.book_new()
          // console.log({
          //   wb,ws
          // })
          XLSX.utils.book_append_sheet(wb, ws, sheetName)
          const defaultOpts = { bookType: 'xlsx', bookSST: false, type: 'binary' }
          const bs = XLSX.write(wb, Object.assign(defaultOpts, opts))
          // 使用file的时候，自动下载excel
          if (retType === 'file') {
            XLSX.writeFile(wb, fileName)
            resolve()
          }
          const blob = new Blob([s2abFn(bs)], { type: '' }) // 构造blob
          if (retType === 'blob') {
            resolve(blob)
          }
          const fileObj = new File([blob], fileName) // 默认构造file对象返回
          resolve(fileObj)
        } catch (err) {
          console.log("261", err)
          // Then.$message.error(err.message)
          reject(err)
        }
      })
    },
// excel的数据
    generateData({ header, results }) {
      this.excelData.header = header
      this.excelData.results = results
      this.excelData.handleJsonToExcelFn = this.handleJsonToExcelFn
    },
    // 调用jsonToExcel方法的默认设置
    // 这是异步操作，接收值的时候，需要使用如下方式
    // async handleSuccess({ results,handleJsonToExcelFn}) {
    //   let ss=await handleJsonToExcelFn(results);
    handleJsonToExcelFn(results) {
      if (!results) return;
      // 数据处理
      const defaultCellStyle = {
        font: { name: 'Verdana', sz: 13, color: 'FF00FF88' },
        fill: { fgColor: { rgb: 'FFFFAA00' } }
      }
      const opts = {
        cellStyles: true,
        defaultCellStyle: defaultCellStyle,
        showGridLines: false
      }
      return this.jsonToExcel({
        json: results,
        fileName: 'temp.xlsx',
        opts,
        retType: ''
      });;
    },

    excelDataError(){
      let bResult = false

      for(let item of this.excelData.results){
        if(validatenull(item["货物编码"])){
          //item["货物编码"]="校验：货物编码不能为空！"
          bResult = true
          break;
        }
        if(validatenull(item["货物名称"])){
          //item["货物名称"]="校验：货物名称不能为空！"
          bResult = true
          break;
        }
      }

      return bResult;
    },

    submitFileForm() {
      // console.log('this.excelData=')
      // console.log(this.excelData)

      if(this.excelDataError()){
        this.$message.error("数据校验失败，请查看校验结果！");
        return;
      }

      const _that = this;
      const submitFn = (obj) => {
        return request({
          url: _that.actionUrl,
          method: 'post',
          data: obj
        })
      }
      let config = {
        headers: {
          'Content-Type': 'multipart/form-data'
        }
      }
      if (!this.excelData.results) {
        this.$message.error("请上传文件");
        return;
      }

      const objExcel = {
        header: JSON.stringify(this.excelData.header),
        results: JSON.stringify(this.excelData.results)
      }
      // console.log(objExcel)
      this.loading = true;
      // console.log("submitFn")
      submitFn(objExcel, config).then(res => {
        //submitFn(this.excelData, config).then(res => {
        this.handleFileSuccess(res.data)
        // console.log("返回值了")
        // console.log(res)


      }).finally(() => {
        this.loading = false;
      })

    },

    handleClose(){
      this.visible = false
      this.$nextTick(()=>{
        // this.$refs.dataForm.resetFields();
      })
    },
    btnTest(){
      if(validatenull(this.excelData.header)){
        this.$message.error("请上传文件");
        return
      }
      // console.log(this.excelData)
      this.$refs.goodsImportSee.init(this.excelData)
    },
    downExcelTemp() {
      const tempUrl = "/aims/overseagoods/exTemplateGoods"
      downBlobFile(tempUrl, {}, "海外仓货物管理模板");
    },
  }
}
</script>

<style scoped>

</style>