js实现特定excel导入文件拼装数据

js实现特定excel导入文件拼装数据

<template>
  <div class="content">
      <aui-file-upload :action="action" :before-upload="beforeAvatarUpload" :file-list="fileList" @change="handleChange" style="margin-left:10px;">
        <aui-button type="primary">点击上传</aui-button>
      </aui-file-upload>
  </div>
</template>

<script>
import { Button, Layout, Row, Col, Select, Option, FileUpload } from "@aurora/vue";
import XLSX from 'xlsx'
export default {
  name: "CommonFilters",
  components: {
    AuiButton: Button,
    AuiLayout: Layout,
    AuiRow: Row,
    AuiCol: Col,
    AuiSelect: Select,
    AuiOption: Option,
    AuiFileUpload: FileUpload,
  },
  data() {
    return {
      action: '',
      imageUrl: '',
      fileList: [],
    };
  },
  methods: {
    // 读excel
    handleChange(res) {
      let abc = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'
      var files = res.raw
      var reader = new FileReader();
      let start,end
      reader.onloadend = async ()=> {
        start = new Date()
        let submitData = []
        let workbook = XLSX.read(reader.result, {bookType: 'xlsx', bookSST: false, type: 'binary'})
        let x = 10,y = 6
        // for(y<workbook)
        let orm = {last_update_by:this.userAccount}

        let Sheets = workbook.SheetNames
        // sheets名字
        let keyFirst = Sheets[0]
        // 拿到的值
        let excelData = workbook.Sheets[keyFirst]
        let lenY = (excelData['!ref'].split(':')[1]).replace(/[^0-9]/ig,"")
        let x_top = (excelData['!ref'].split(':')[1]).replace(/[^A-Z]/ig,"")
        let lenX = 0
        if(x_top.length==1){
          lenX = abc.indexOf(x_top)+1
        }else{
          for(let j=0;j<x_top.length;j++){
            if(j<x_top.length-1){
              lenX += (abc.indexOf(x_top[j])+1)*26
            }else{
              lenX+=abc.indexOf(x_top[j])+1
            }
            // console.log(abc.indexOf(x_top[j])+1)
          }
        }
        // console.log(excelData,lenY,lenX)
        // Y轴
        do{
          // console.log(y)
          // X轴
          let list_first = null
          for(;x<lenX;x++){
            let key = null,qian = null
            if(x<26){
              key = abc[x]+y
              qian = abc[x]
            }else{
              let ho = x%26
              let qi = parseInt(x/26)
              key = abc[qi-1]+abc[ho]+y
              qian = abc[qi-1]+abc[ho]
            }
            if(excelData[qian+'1']!=undefined){
              list_first = excelData[qian+'1']
            }
            let obj = {}
            obj['region_cn'] = excelData['B'+y]?.v || ''
            obj['region_en'] = excelData['B'+y]?.v || ''
            obj['region_code'] = 1

            obj['repoffice_cn'] = excelData['D'+y]?.v || ''
            obj['repoffice_en'] = excelData['D'+y]?.v || ''
            obj['repoffice_code'] = excelData['C'+y]?.v || ''

            obj['country_cn'] = excelData['F'+y]?.v || ''
            obj['country_en'] = excelData['F'+y]?.v || ''
            obj['country_code'] = excelData['E'+y]?.v || ''

            obj['dept_cn'] = excelData['H'+y]?.v || ''
            obj['dept_en'] = excelData['H'+y]?.v || ''
            obj['dept_code'] = excelData['G'+y]?.v || ''

            obj['account_cn'] = excelData['J'+y]?.v || ''
            obj['account_en'] = excelData['J'+y]?.v || ''
            obj['account_code'] = excelData['I'+y]?.v || ''

            obj['column_value'] = excelData[key]?.v || undefined
            obj['last_update_by'] = this.$service.base.getUserInfoSync().employeeNumber;
            await this.submitExcel(obj)
            console.log(`上传完成坐标：x:${x},y:${y}`)
            submitData.push(obj)
          }


          x=10
          y++
        }while(y<parseInt(lenY))

        console.log(submitData)
        end = new Date()
        let second = ((end-start) % (1000 * 60)) / 1000;
        let minutes = parseInt(((end-start) % (1000 * 60 * 60)) / (1000 * 60));
        console.log(`用时${minutes}分${second}秒`)
        this.sendSearch()
      }
      // reader.readAsText(files);
      reader.readAsBinaryString(files)
    },
    async submitExcel(param){
        // await new Promise(resolve => setTimeout(resolve, 1000));
        let params = Object.assign({},param)
        let value = Object.values(params)
        let key = Object.keys(params)
        key.map((obj,idx)=>{
          params[obj] = unescape(encodeURIComponent(value[idx]));
        })
        params = JSON.stringify(params)
        params = escape(params).replace(/\:/g, '%3A')
        let result = await httpAjax('xxx', params)
        if(result=='ok'){
          // console.log(result,'添加成功')
        }else{
          console.log(param)
          Modal.message({ message:`类型：${this.gl[param.column_id]}添加失败`, status: 'error' })
        }
    },
    //  禁止提交
    beforeAvatarUpload(file) {
      return false
    },
  },
};
</script>
[添加链接描述](https://download.csdn.net/download/qq_26934957/12461673)