前端使用xlsx导出导入文件

就vue来讲：

第一步首先要下载xlsx

yarn add xlsx 
yarn add xlsx-style

导入会用到的插件文件解析

yarn add worker-loader
yarn add uuid

下载好了过后，我们需要写个解析的导入文件：命名为xlsx.worker.js，然后需要引入到main.js中

xlsx.worker.js代码附上：

import { read, utils } from 'xlsx';

self.onmessage = e => {
  const { file, id, keyMaps } = e.data;
  const reader = new FileReader();
  reader.onload = e => {
    const workbook = read(e.target.result, {type: 'binary'});
    const Sheets = workbook.Sheets;
    const list = Object.keys(Sheets).map((key, index) => {
      const list = utils.sheet_to_json(Sheets[key]);
      // 某张表的label和key的map
      const keyMap = keyMaps[index];
      return list.map(item => {
        const row = {};
        for (let key in item) {
          const column = keyMap[key];
          const value = item[key];
          if (column) {
            if (typeof column === 'string') {
              row[column] = value + '';
            } else {
              const { type, key: prop } = column;
              if (type === 'number') {
                row[prop] = isNaN(value * 1) ? value : value * 1;
              } else {
                row[prop] = value;
              }
            }
          } else {
            // row[prop] = value;
          }
        }
        return row;
      });
    })
    self.postMessage({ id, list });
  }
  reader.readAsArrayBuffer(file);
}

把该文件引入到main.js中

import XLSXWorker from '@/workers/xlsx.worker';
Vue.prototype.$workers = {
  xlsx: new XLSXWorker()
};

好了，这样我们就完成引入部分了，就可以开始做导入功能了。

在.vue文件中写导入和导出功能附上代码：

<template>
  <a-modal
      title="商品导入"
      :width="1200"
      v-on="$listeners"
      :confirm-loading="loadingBtn"
      :visible="importVisible"
      @cancel="handleCancel"
      @ok="submitAdd">
    <a-form-model :label-col="{ span: 6 }" 
            :wrapper-col="{ span: 16 }"
            style="padding: 0 0 0 20px;">
      <BaseTitle title="设置商品" />
      <a-row>
        <a-col :span="9">
          <a-form-item :label-col="{ span: 4 }" :wrapper-col="{ span: 18 }">
            <a-button type="link" class="right-a" @click="downloadTemplete">下载商品导入模板</a-button>
            <a-upload-dragger name="file" 
                    :file-list="fileList"
                    :multiple="false"
                    :beforeUpload="onBeforeUpload"
                    accept=".xls,.xlsx,.xlsm"
                    action="">
              <div>
                <a-icon type="cloud-upload" style="font-size: 24px; color: @primary-color;" />
                <p>点击或拖拽商品文件导入</p>
              </div>
            </a-upload-dragger>
          </a-form-item>
        </a-col>
      </a-row>
      <BaseTitle title="导入结果" />
      <a-row>
        <a-table
          :columns="columns"
          :data-source="tableData"
          size="small" />
      </a-row>
    </a-form-model>
  </a-modal>
</template>

<script>
  import { columns } from '@/tables/goods/goodsImport';
  import BaseTitle from '@/components/BaseTitle/index.vue'
  import {goodsService} from '@/service/index'
  import * as xlsx from 'xlsx';
  import { v4 as uuid } from 'uuid';

  const keyMaps = [
    {
      '商品名称(必填)': 'cnName',
      '仓库id(必填)': 'warehouseId',
      '品牌id(必填)': 'brandId',
      '国家编码(必填)': 'originCountryCode',
      '商品规格(必填)': 'goodsModel',
      '毛重（克）(必填)': { key: 'netWeight', type: 'number' },
      '净重（克）(必填)': { key: 'grossWeight', type: 'number' },
      '币种(必填)': 'currency',
      '条形码(必填)': 'barCode',
      '到期时间(必填)': 'expirationDate',
      'hsCode': 'hsCode',
      '销售价格': { key: 'priceSales', type: 'number' },
      '成本价格': { key: 'priceCost', type: 'number' },
      '法定计量单位': 'unit',
      '商品配料': 'goodsComponent',
      '包装类型': 'packageType'
    },
    { '仓库名称': 'warehouseTitle', '仓库编码': 'id' },
    { '品牌名称': 'brandName', '仓库编码': 'brandId' },
    { '国家': 'cnname', '国家编码': 'isoNcode' },
    { '币种名称': 'shortCnname', '币种编码': 'customsCode' },
    { '计量单位': 'shortCnname', '计量单位编码': 'customsCode' }
  ];

  export default {
    components: { BaseTitle },
    model: {
      prop: 'importVisible',
      event: 'change'
    },
    props: {
      importVisible: {type: Boolean, default: false},
      importData: {type:Object, default: ()=> ({})}
    },
    data() {
      return {
        fileList: [],
        tableData: [],
        columns,
        rowSelection: {},
        worderId: uuid(),//商品ID
        loadingBtn: false
      }
    },
    mounted() {
      //导入数据解析结果监听
      this.$workers.xlsx.onmessage = res => {
        const { id, list } = res.data;
        if (id !== this.worderId) {
          return;
        }
        this.tableData = list[0];
      }
    },
    methods: {
      //下载商品导入模板
      downloadTemplete() {
        //商品列表-sheet1
        let newList = [{
          '商品名称(必填)': '',
          '仓库id(必填)': '',
          '品牌id(必填)': '',
          '国家编码(必填)': '',
          '商品规格(必填)': '',
          '毛重（克）(必填)': 0,
          '净重（克）(必填)': 0,
          '币种(必填)': '',
          '条形码(必填)': '',
          '到期时间(必填)': '',
          'hsCode': '',
          '销售价格': 0,
          '成本价格': 0,
          '法定计量单位': '',
          '商品配料': '',
          '包装类型': ''
        }]
        //仓库对应编码
        const warehouseList = []
        const warehouseName = {
          warehouseTitle: '仓库名称',
          id: '仓库编码'
        }
        // 转换
        if(this.importData.warehouseList.length) {
          this.importData.warehouseList.forEach(item=>{
            let obj = {};
            for(let key in item){
              if(warehouseName[key]){
                obj[warehouseName[key]] = item[key];
              }
            }
            warehouseList.push(obj);
          })
        } else {
          warehouseList.push({'仓库名称': '', '仓库编码': ''});
        }
        
        //品牌对应编码
        const bransList = []
        const brandName = {
          brandName: '品牌名称',
          brandId: '品牌编码'
        }
        // 转换
        if(this.importData.brandList.length) {
          this.importData.brandList.forEach(item=>{
            let obj = {};
            for(let key in item){
              if(brandName[key]){
                obj[brandName[key]] = item[key];
              }
            }
            bransList.push(obj);
          })
        } else {
          bransList.push({'品牌名称': '', '品牌编码': ''});
        }

        //国家对应编码
        const countryList = []
        const countryName = {
          cnname: '国家',
          isoNcode: '国家编码'
        }
        // 转换
        if(this.importData.addressList.length) {
          this.importData.addressList.forEach(item=>{
            let obj = {};
            for(let key in item){
              if(countryName[key]){
                obj[countryName[key]] = item[key];
              }
            }
            countryList.push(obj);
          })
        } else {
          countryList.push({'国家': '', '国家编码': ''});
        }

        //币种对应编码
        const currencyList = []
        const currencyName = {
          shortCnname: '币种名称',
          customsCode: '币种编码'
        }
        // 转换
        if(this.importData.currencyList.length) {
          this.importData.currencyList.forEach(item=>{
            let obj = {};
            for(let key in item){
              if(currencyName[key]){
                obj[currencyName[key]] = item[key];
              }
            }
            currencyList.push(obj);
          })
        } else {
          currencyList.push({'币种名称': '', '币种编码': ''});
        }

        //法定计量单位对应编码
        const unitList = []
        const unitName = {
          shortCnname: '计量单位',
          customsCode: '计量单位编码'
        }
        // 转换
        if(this.importData.unitList.length) {
          this.importData.unitList.forEach(item=>{
            let obj = {};
            for(let key in item){
              if(unitName[key]){
                obj[unitName[key]] = item[key];
              }
            }
            unitList.push(obj);
          })
        } else {
          unitList.push({'计量单位': '', '计量单位编码': ''});
        }

        let sheet1 = xlsx.utils.json_to_sheet(newList);//商品列表
        let wscols = [    // 每列不同宽度px
          { wch: 15 },{ wch: 15 },{ wch: 15 },
          { wch: 15 },{ wch: 15 },{ wch: 15 },
          { wch: 15 },{ wch: 15 },{ wch: 15 },
          { wch: 15 },{ wch: 15 },{ wch: 15 },
          { wch: 15 },{ wch: 15 },{ wch: 15 },
          { wch: 15 },{ wch: 15 }
        ];
        // workbook.SheetNames[0]获取到到是文件里的到第一个表格
        sheet1["!cols"] = wscols;
        let wsrows = [{ hpx: 20 }];  // 每行固定高度px
        for (let i = 0; i <= this.total; i++) {   // total  列表条数
          wsrows.push({ hpx: 20 });
        }
        sheet1["!rows"] = wsrows;
        let sheet2 = xlsx.utils.json_to_sheet(warehouseList);//仓库列表
        let sheet3 = xlsx.utils.json_to_sheet(bransList);//品牌列表
        let sheet4 = xlsx.utils.json_to_sheet(countryList);//国家列表
        let sheet5 = xlsx.utils.json_to_sheet(currencyList);//币种列表
        let sheet6 = xlsx.utils.json_to_sheet(unitList);//法定计量单位列表
        let book = xlsx.utils.book_new();
        xlsx.utils.book_append_sheet(book, sheet1, "商品列表");
        xlsx.utils.book_append_sheet(book, sheet2, "仓库对应编码");
        xlsx.utils.book_append_sheet(book, sheet3, "品牌对应编码");
        xlsx.utils.book_append_sheet(book, sheet4, "国家对应编码");
        xlsx.utils.book_append_sheet(book, sheet5, "币种对应编码");
        xlsx.utils.book_append_sheet(book, sheet6, "法定计量单位对应编码");
        xlsx.writeFile(book, `iwms_goods_import_template.xlsx`);
      },
      //上传处理
      onBeforeUpload(file, fileList) {
        this.fileList = [fileList.pop()];
        this.$workers.xlsx.postMessage({
          id: this.worderId,
          keyMaps,
          file,
        });
        return false;
      },
      //保存
      submitAdd() {
        if(!this.fileList.length){
          this.$message.error('请上传文件')
        }
        this.loadingBtn = true
        goodsService.goodsAddto({goodsData: this.tableData}).then(res => {
          this.loadingBtn = false
          if(res.code !== 200) return
          this.$message.success('导入成功')
          this.handleCancel()
          this.$emit('confirm', confirm);
        })
      },
      handleCancel() {
        this.$emit('cancel');
        this.fileList = [];
        this.tableData = []
      }
    }
  }
</script>
<style scoped lang="less">
.right-a { margin-right: 10px;}
/deep/.ant-btn-link{padding:0;}
</style>

下载的模板图片：

 

 

最后图片：