vue上传并读取Excel，格式化成数组传给后端，返回文件流导出Excel到本地

需求：
列表有上下架状态的一列，在数据过多的时候需要，统一修改。
1、点击批量上下架按钮
2、弹出批量导入弹框，如图1
3、点击下载模版，如图2，该模版是由前端定义的两个字段
4、点击请选择文件，可选择本机相关文件，如图3
5、点击导入，会把上传的excel读取校验每一行数据是否合格，然后转化成数组作为参数传给后台，如图4
6、后端会返回一个文件流，前端直接下载到本地excel

涉及知识：
1、使用js-xlsx库，前端读取Excel报表文件
2、读取本地文件理解FileReader对象的方法和事件以及上传按钮的美化。
3、前端接收文件流下载到本地

图片后为代码实现





代码实现：

list.vue

<template>
	<div>
      <el-button type="primary" @click="add" size="mini">批量上下架</el-button>
      <up-loader ref="addUploader" @downLoad="addExcelExport" @upLoad="addExcelImport"></up-loader>
    </div>
</template>

<script>
import UpLoader from "@/module/components/uploader/uploader.vue"
import eportsheet from '@/module/components/exportsheet/exportsheet'
export default {
	components: { UpLoader },
	data() {
    	return {
			importAddConfig: {
		        skuCode: '商品编码（SGU）',
		        priceStatusStr: '上下架状态',
		    },
		}
	},
	methods: {
		// 打开弹框
		add() {
	      this.$refs.addUploader.open()
	    },
	    // 下载模版
	    addExcelExport() {
	      eportsheet.exportFileByConfig([{}], `批量上下架模板.xlsx`, this.importAddConfig)
	    },
	    async addExcelImport(file) {
	      let parseConfig = {}
	      for (let key in this.importAddConfig) {
	        parseConfig[this.importAddConfig[key]] = key
	      }
	      await eportsheet.parseToJSON(file, parseConfig).then(result => {
	
	        if(!result){
	          this.$notify.error({ title: '提示', message:"上传文件数据解析为空" })
	          return false
	        }
	        for(let i=0; i<result.length; i++){
	          let row = result[i]
	          if(!row.skuCode){
	            this.$notify.error({ title: '提示', message:"商品编码（SGU）不能为空" })
	            return false
	          }
	          if(!row.priceStatusStr){
	            this.$notify.error({ title: '提示', message:"上下架状态不能为空" })
	            return false
	          }
	        }
	        // responseType:'blob' 下载文件流设置返回类型blob
	        this.$http.post(`${api.updateStatusByList}ForExcel`, result, {}, 'blob').then(res => {
	          if (res) {
	              // 关闭弹框
	              this.$refs.addUploader.close()
	              this.$notify.success({title: '操作提示',message: "上传成功"})
	              // 刷新list表格
	              this.search()
	              // 后端返回的文件流下载到本地
	              this.exportLoading = false
	              var blob = new Blob([res], {type: 'application/ms-excel'});
	              var elink = document.createElement('a');
	              elink.download = '批量上下架结果' + '.xlsx';
	              elink.href = URL.createObjectURL(blob);
	              document.body.appendChild(elink);
	              elink.click(); //点击下载
	              document.body.removeChild(elink); //下载完成移除元素
	              window.URL.revokeObjectURL(elink); //释放掉blob对象
	              this.loading = false
	              return
	          } else {
	            this.$notify.error({
	              title: '提示',
	              message: res.msg
	            })
	          }
	        })
	      })
	    },
	}
}
</script>

UpLoader.vue

<template>
  <!--批量导入弹窗-->
  <el-dialog :visible.sync="visible" size="large" title="批量导入" :loading="loadingInfo">
    <el-row class="mv10">
      <el-col :span="24">
        <label :for="`excelFile${tmp}`" class="excelFileLabel">
          <span class="file-label-text">请选择文件</span>
          <input accept=".xlm,.xls,.xlt,.xlw,.xlsx" type="file" :id="`excelFile${tmp}`" hidden @change="handleFileSelect" :value="fileValue" />
          <el-input class="search-input" v-model="file.name" disabled></el-input>
        </label>
        <el-button style="margin-left: 10px" size="small" type="text" @click="exportTemp">下载模版</el-button>
      </el-col>
    </el-row>
    <el-row>
      <el-col :span="24">
        <el-button style="margin-top: 20px" :loading="loadingInfo" size="small" type="primary" @click="submitUpload">导入
        </el-button>
        <el-button @click="exportErrorFile" v-if="upStatus.code">下载错误列表</el-button>
      </el-col>
    </el-row>
    <el-row class="mt20" v-if="upStatus.code">
      <el-col :span="24" style="color:red">
        <p>{{file.name}} 导入失败，请修改后重新上传</p>
        <p>失败原因：{{upStatus.msg}}</p>
      </el-col>
    </el-row>
  </el-dialog>
</template>
<script>

export default {
  name: 'upLoader',
  props: {
    loadingInfo: { type: Boolean, default: false }
  },
  data () {
    return {
      tmp: Date.now(),
      visible: false,
      fileValue: '',
      loading: false,
      file: { name: '' },
      upStatus: {
        code: '',
        msg: '',
        data: []
      },
      tempConfig: {}
    }
  },
  methods: {
    handleFileSelect (e) {
      this.file = e.target.files[0] || { name: '' }
      // 如果文件改变需要初始化上传状态
      this.upStatus = { code: '', msg: '', data: [] }
    },
    exportTemp () {
      try {
        this.$emit('downLoad')
      } catch (e) {
        this.$notify.error({
          title: '提示',
          message: '模板下载遇到错误'
        })
      }
    },
    submitUpload () {
      if (!this.file.name || this.file.name.indexOf(".xl") === -1) {
        this.$notify.warning({
          title: '提示',
          message: '请选择excel文件'
        })
        return
      }
      this.$emit('upLoad', this.file)
    open () {
      this.visible = true
      this.fileValue = ''
      this.file = { name: '' }
    },
    close () {
      this.visible = false
    }
  }
}
</script>
<style scoped>
.file-label-text {
  cursor: pointer;
  color: #409eff;
}
</style>

eportsheet.js

import XLSX from 'xlsx'
import * as util from '@/utils/utils'

// 下载模版
function exportFileByConfig (json, fileName, exportConfig, extraData = []) {
  if (exportConfig) {
    json = json.map((item, index) => {
      let newItem = {}
      for (let k of Object.keys(exportConfig)) {
        let value = item[k]
        if (value === true) {
          value = '是'
        }
        if (value === false) {
          value = '否'
        }
        if (value === null || value === undefined) {
          value = ''
        }
        newItem[exportConfig[k]] = value
      }
      return newItem
    })
  }
  createFile(json, fileName, extraData)
}
// 创建文件并下载
function createFile (json, fileName, extraList) {
  let _tmpdata = json[0]
  json.unshift({})
  var keyMap = [] // 获取keys
  for (var k in _tmpdata) {
    keyMap.push(k)
    json[0][k] = k
  }
  let tmpdata = [] // 用来保存转换好的json
  let blankNum = extraList.length ? 2 : 1
  console.log('extraList', extraList)
  json.map((v, i) => keyMap.map((k, j) => Object.assign({}, {
    v: v[k],
    // position: (j > 25 ? getCharCol(j) : String.fromCharCode(65 + j)) + (i + 1 + extraList.length) // 开头加个空行
    position: (j > 25 ? getCharCol(j) : String.fromCharCode(65 + j)) + (i + blankNum)
  }))).reduce((prev, next) => prev.concat(next)).forEach((v, i) => {
    tmpdata[v.position] = {
      v: v.v
    }
  })
  let temObj = {}
  for (let i = 0; i < extraList.length; i++) {
    let item = extraList[i]
    for (let j = 0; j < item.length; j++) {
      let key = String.fromCharCode(65 + i) + (j + 1)
      temObj[key] = {v: item[j].label + item[j].value}
    }
  }
  tmpdata = Object.assign(temObj, tmpdata)
  var outputPos = Object.keys(tmpdata) // 设置区域,比如表格从A1到D10
  var tmpWB = {
    SheetNames: ['mySheet'], // 保存的表标题
    Sheets: {
      'mySheet': Object.assign({},
        tmpdata, // 内容
        {
          '!ref': 'A1' + ':' + outputPos[outputPos.length - 1] // 设置填充区域
        })
    }
  }
  let wbout = XLSX.write(tmpWB, {bookType: 'xlsx', type: 'binary'})
  /* force a download */
  let tmpDown = new Blob([s2ab(wbout)], {type: 'application/octet-stream'})
  var href = URL.createObjectURL(tmpDown) // 创建对象超链接
  var a = document.createElement('a')
  a.href = href // 绑定a标签
  a.download = fileName || `导出数据${util.formatDate(new Date())}.xlsx`
  document.body.appendChild(a)
  a.click() // 模拟点击实现下载
  document.body.removeChild(a)
  setTimeout(function () { // 延时释放
    URL.revokeObjectURL(tmpDown) // 用URL.revokeObjectURL()来释放这个object URL
  }, 100)
}

/*  读取导入的Excel
 *  @excelFile File
 *  @config Object 解析字段
 *  @setter Object XLSX.utils.sheet_to_json的配置
*/
function parseToJSON (excelFile, config, setter = {}) {
  return new Promise((resolve, reject) => {
    if (window.FileReader) { // 判断浏览器是否支持FileReader对象
      let result = []
      let fr = new FileReader() // 新建文件读取
      fr.readAsBinaryString(excelFile) // 将文件读取二进制码
      fr.onload = ev => { // 数据读取成功完成时触发
        try {
          let data = ev.target.result
          // 以二进制流方式读取得到整份excel表格对象
          let workbook = XLSX.read(data, {
            type: 'binary'
          })
          // 只遍历第一个表
          let name = workbook.SheetNames[0]
          let sheet = workbook.Sheets[name]
          if (sheet) {
            result = XLSX.utils.sheet_to_json(sheet, {})
            if (config) {
              for (let item of result) {
                for (let excelTitleKey in config) {
                  item[config[excelTitleKey]] = ''
                }
              }
              result.forEach(item => {
                for (let key in item) {
                  if (config[key]) {
                    item[config[key]] = item[key].toString().trim()
                    delete item[key]
                  }
                }
              })
              // 去空行
              if (result && result.length) {
                let keyList = Object.keys(result[0])
                for (let i = result.length - 1; i > 0; i--) {
                  let value = ''
                  for (let key of keyList) {
                    value += result[i][key]
                  }
                  if (value.trim() === '') {
                    result.splice(i, 1)
                  }
                }
              }
            }
          }
          resolve(result)
        } catch (e) {
          reject(new Error('文件类型不正确'))
        }
      }
      return
    }
    reject(new Error('该浏览器不支持该功能，请更换或升级浏览器'))
  })
}


export default {
  exportFile,
  exportFileByConfig,
}

request.js

/**
 * @description: RequestBody请求
 * @param {_url} 请求地址
 * @param {_params} 请求参数
 * @param {_oParam} 其他参数，用于控制一些非正常现象  urlType： 1：登录接口   其它：正常接口
 * @return:
 */
const post = (_url, _params = {}, _oParam, resType, boomdebug) => {
  return axios({
    method: "post",
    url: formatUrl(_url, boomdebug),
    data: formatParams(_params),
    responseType: resType ? resType : 'json',
    headers: setHeaders('body', _oParam)
  });
};

export default {
  post,
}