excel导入导出，实现批量新增功能

实现方式一（xlsx插件）

基于 vue-admin-element 中的插件实现

安装插件

npm install xlsx file-saver -S
npm install script-loader -S -D

在根目录下创建/vendors/Export2Excel文件

/* eslint-disable */
import { saveAs } from 'file-saver'
import * as XLSX from 'xlsx'

function generateArray(table) {
  var out = [];
  var rows = table.querySelectorAll('tr');
  var ranges = [];
  for (var R = 0; R < rows.length; ++R) {
    var outRow = [];
    var row = rows[R];
    var columns = row.querySelectorAll('td');
    for (var C = 0; C < columns.length; ++C) {
      var cell = columns[C];
      var colspan = cell.getAttribute('colspan');
      var rowspan = cell.getAttribute('rowspan');
      var cellValue = cell.innerText;
      if (cellValue !== "" && cellValue == +cellValue) cellValue = +cellValue;

      //Skip ranges
      ranges.forEach(function (range) {
        if (R >= range.s.r && R <= range.e.r && outRow.length >= range.s.c && outRow.length <= range.e.c) {
          for (var i = 0; i <= range.e.c - range.s.c; ++i) outRow.push(null);
        }
      });

      //Handle Row Span
      if (rowspan || colspan) {
        rowspan = rowspan || 1;
        colspan = colspan || 1;
        ranges.push({
          s: {
            r: R,
            c: outRow.length
          },
          e: {
            r: R + rowspan - 1,
            c: outRow.length + colspan - 1
          }
        });
      };

      //Handle Value
      outRow.push(cellValue !== "" ? cellValue : null);

      //Handle Colspan
      if (colspan)
        for (var k = 0; k < colspan - 1; ++k) outRow.push(null);
    }
    out.push(outRow);
  }
  return [out, ranges];
};

function datenum(v, date1904) {
  if (date1904) v += 1462;
  var epoch = Date.parse(v);
  return (epoch - new Date(Date.UTC(1899, 11, 30))) / (24 * 60 * 60 * 1000);
}

function sheet_from_array_of_arrays(data, opts) {
  var ws = {};
  var range = {
    s: {
      c: 10000000,
      r: 10000000
    },
    e: {
      c: 0,
      r: 0
    }
  };
  for (var R = 0; R != data.length; ++R) {
    for (var C = 0; C != data[R].length; ++C) {
      if (range.s.r > R) range.s.r = R;
      if (range.s.c > C) range.s.c = C;
      if (range.e.r < R) range.e.r = R;
      if (range.e.c < C) range.e.c = C;
      var cell = {
        v: data[R][C]
      };
      if (cell.v == null) continue;
      var cell_ref = XLSX.utils.encode_cell({
        c: C,
        r: R
      });

      if (typeof cell.v === 'number') cell.t = 'n';
      else if (typeof cell.v === 'boolean') cell.t = 'b';
      else if (cell.v instanceof Date) {
        cell.t = 'n';
        cell.z = XLSX.SSF._table[14];
        cell.v = datenum(cell.v);
      } else cell.t = 's';

      ws[cell_ref] = cell;
    }
  }
  if (range.s.c < 10000000) ws['!ref'] = XLSX.utils.encode_range(range);
  return ws;
}

function Workbook() {
  if (!(this instanceof Workbook)) return new Workbook();
  this.SheetNames = [];
  this.Sheets = {};
}

function s2ab(s) {
  var buf = new ArrayBuffer(s.length);
  var view = new Uint8Array(buf);
  for (var i = 0; i != s.length; ++i) view[i] = s.charCodeAt(i) & 0xFF;
  return buf;
}

export function export_table_to_excel(id) {
  var theTable = document.getElementById(id);
  var oo = generateArray(theTable);
  var ranges = oo[1];

  /* original data */
  var data = oo[0];
  var ws_name = "SheetJS";

  var wb = new Workbook(),
    ws = sheet_from_array_of_arrays(data);

  /* add ranges to worksheet */
  // ws['!cols'] = ['apple', 'banan'];
  ws['!merges'] = ranges;

  /* add worksheet to workbook */
  wb.SheetNames.push(ws_name);
  wb.Sheets[ws_name] = ws;

  var wbout = XLSX.write(wb, {
    bookType: 'xlsx',
    bookSST: false,
    type: 'binary'
  });

  saveAs(new Blob([s2ab(wbout)], {
    type: "application/octet-stream"
  }), "test.xlsx")
}

export function export_json_to_excel({
  multiHeader = [],
  header,
  data,
  filename,
  merges = [],
  autoWidth = true,
  bookType = 'xlsx'
} = {}) {
  /* original data */
  filename = filename || 'excel-list'
  data = [...data]
  data.unshift(header);

  for (let i = multiHeader.length - 1; i > -1; i--) {
    data.unshift(multiHeader[i])
  }

  var ws_name = "SheetJS";
  var wb = new Workbook(),
    ws = sheet_from_array_of_arrays(data);

  if (merges.length > 0) {
    if (!ws['!merges']) ws['!merges'] = [];
    merges.forEach(item => {
      ws['!merges'].push(XLSX.utils.decode_range(item))
    })
  }

  if (autoWidth) {
    /*设置worksheet每列的最大宽度*/
    const colWidth = data.map(row => row.map(val => {
      /*先判断是否为null/undefined*/
      if (val == null) {
        return {
          'wch': 10
        };
      }
      /*再判断是否为中文*/
      else if (val.toString().charCodeAt(0) > 255) {
        return {
          'wch': val.toString().length * 2
        };
      } else {
        return {
          'wch': val.toString().length
        };
      }
    }))
    /*以第一行为初始值*/
    let result = colWidth[0];
    for (let i = 1; i < colWidth.length; i++) {
      for (let j = 0; j < colWidth[i].length; j++) {
        if (result[j]['wch'] < colWidth[i][j]['wch']) {
          result[j]['wch'] = colWidth[i][j]['wch'];
        }
      }
    }
    ws['!cols'] = result;
  }

  /* add worksheet to workbook */
  wb.SheetNames.push(ws_name);
  wb.Sheets[ws_name] = ws;

  var wbout = XLSX.write(wb, {
    bookType: bookType,
    bookSST: false,
    type: 'binary'
  });
  saveAs(new Blob([s2ab(wbout)], {
    type: "application/octet-stream"
  }), `${filename}.${bookType}`);
}

表格导出(下载表格模板)

toExcel() {
    const headers = {
        活动名称: "activity_name",
        用户名: "user_name",
        联系方式: "phone",
        报名时间: "created_at",
    };
    import("@/vendors/Export2Excel").then(async (excel) => {
        // 1、获取需要导出的数据
        // this.dataSource
        // 2、进行数据的转化
        const data = this.fromJson(headers, this.dataSource);
        const multiHeader = [["报名活动统计", "", "", ""]];
        const merges = ["A1:D1"];
        excel.export_json_to_excel({
            header: Object.keys(headers), // 表头数组 -> ['活动名称', '用户名', '联系方式', '报名时间', ....]
            data, // [['活动名称', '张三', '联系方式', '报名时间'],[],[],[],[],[],[]]
            filename: "报名活动统计",
            autoWidth: true,
            bookType: "xlsx",
            multiHeader, // 复杂表头
            merges, // 合并选项
        });
    });
},
// 该方法负责将数组转化成二维数组
fromJson(headers, rows) {
    const result = rows.map((item) => {
        return Object.keys(headers).map((key) => {
            return item[headers[key]];
        });
    });
    return result;
},

表格导入

组件封装

<template>
  <div class="upload-excel">
    <div class="btn-upload">
      <a-button @click="handleUpload"> 点击上传 </a-button>
    </div>

    <input
      ref="excel-upload-input"
      class="excel-upload-input"
      type="file"
      accept=".xlsx, .xls"
      @change="handleClick"
    />
    <!-- <div
      class="drop"
      @drop="handleDrop"
      @dragover="handleDragover"
      @dragenter="handleDragover"
    >
      <i class="el-icon-upload" />
      <span>将文件拖到此处</span>
    </div> -->
  </div>
</template>

<script>
import * as XLSX from "xlsx";
export default {
  props: {
    beforeUpload: Function, // eslint-disable-line
    onSuccess: Function, // eslint-disable-line
  },
  data() {
    return {
      loading: false,
      excelData: {
        header: null,
        results: null,
      },
    };
  },
  methods: {
    generateData({ header, results }) {
      this.excelData.header = header;
      this.excelData.results = results;
      this.onSuccess && this.onSuccess(this.excelData);  // 触发父组件中success函数
    },
    handleDrop(e) {
      e.stopPropagation();
      e.preventDefault();
      if (this.loading) return;
      const files = e.dataTransfer.files;
      if (files.length !== 1) {
        // this.$message.error("Only support uploading one file!");
        alert("Only support uploading one file!");
        return;
      }
      const rawFile = files[0]; // only use files[0]
      if (!this.isExcel(rawFile)) {
        alert("Only supports upload .xlsx, .xls, .csv suffix files");
        // this.$message.error(
        //   "Only supports upload .xlsx, .xls, .csv suffix files"
        // );
        return false;
      }
      this.upload(rawFile);
      e.stopPropagation();
      e.preventDefault();
    },
    handleDragover(e) {
      e.stopPropagation();
      e.preventDefault();
      e.dataTransfer.dropEffect = "copy";
    },
    handleUpload() {
      this.$refs["excel-upload-input"].click();
    },
    handleClick(e) {
      const files = e.target.files;
      const rawFile = files[0]; // only use files[0]
      if (!rawFile) return;
      this.upload(rawFile);
    },
    upload(rawFile) {
      this.$refs["excel-upload-input"].value = null; // fix can't select the same excel
      if (!this.beforeUpload) {
        this.readerData(rawFile);
        return;
      }
      const before = this.beforeUpload(rawFile);
      if (before) {
        this.readerData(rawFile);
      }
    },
    readerData(rawFile) {
      //   this.loading = true;
      return new Promise((resolve, reject) => {
        const reader = new FileReader();
        reader.onload = (e) => {
          const data = e.target.result;
          const workbook = XLSX.read(data, { type: "array" });
          const firstSheetName = workbook.SheetNames[0];
          const worksheet = workbook.Sheets[firstSheetName];
          const header = this.getHeaderRow(worksheet);
          const results = XLSX.utils.sheet_to_json(worksheet);
          this.generateData({ header, results });
          //   this.loading = false;
          resolve();
        };
        reader.readAsArrayBuffer(rawFile);
      });
    },
    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;
    },
    isExcel(file) {
      return /\.(xlsx|xls|csv)$/.test(file.name);
    },
    // beforeUpload(file) {
    //   //   const isLt1M = file.size / 1024 < 8;
    //   //   if (isLt1M) {
    //   //     return true;
    //   //   }
    //   //   alert("Please do not upload files larger than 1m in size.");
    //   //   this.$message({
    //   //     message: "Please do not upload files larger than 1m in size.",
    //   //     type: "warning",
    //   //   });
    //   return false;
    // },
    handleSuccess(data) {
      console.log("data", data);
    },
  },
};
</script>

<style lang="less" scoped>
.upload-excel {
  display: flex;
  justify-content: center;
  //   margin-top: 100px;
  .excel-upload-input {
    display: none;
    z-index: -9999;
  }
  .btn-upload,
  .drop {
    // border: 1px dashed #bbb;
    // width: 350px;
    // height: 160px;
    text-align: center;
    // line-height: 160px;
  }
  .drop {
    line-height: 80px;
    color: #bbb;
    i {
      font-size: 60px;
      display: block;
    }
  }
}
</style>

组件使用

<templete>
    <upload-excel :onSuccess="success" :beforeUpload="beforeUpload" />
</templete>
import UploadExcel from '@/components/uploadExcel'

async success({ header, results }) {
    const headers = {
        车位名称: "car_name",
        所属区域: "district_name",
        所属楼宇: "building_name",
        所属楼层: "building_detail_name",
        车位类型: "type",
        租赁价格: "price",
        标题: "phone",
        车位详情: "remark",
    };
    // 遍历所有的数组
    const arr = results.map((item) => {
        // 需要将每一个条数据里面的中文都换成英文
        const userInfo = {};
        Object.keys(item).forEach((key) => {
            // key是当前的中文名 找到对应的英文名
            if (headers[key] === "timeOfEntry" || headers[key] === "correctionTime") {
                userInfo[headers[key]] = this.formatDate(item[key], "/"); // 只有这样, 才能入库
                return;
            }
            userInfo[headers[key]] = item[key];
        });
        // 最终userInfo变成了全是英文
        return userInfo;
    });
    console.log(arr);
    this.data = arr;
    // await importEmployee(arr);
    // this.$message.success("导入成功");
    // this.$router.back(); // 回到上一页
},
beforeUpload(file) {
    // const isLt1M = file.size / 1024 < 8;
    // if (isLt1M) {
    //   return true;
    // }
    // this.$message({
    //   message: "Please do not upload files larger than 1m in size.",
    //   type: "warning",
    // });
    // return false;
    return true;
},

实现方式二（表格都由后端处理）

<templete>
    <a-button class="bottom" type="primary" @click="toExcel">点击下载模板</a-button>
    <div class="div">
        导入:
        <a-button style="margin-left: 85px" @click="handleUpload">选择文件</a-button>
        <input
            ref="excel-upload-input"
            class="excel-upload-input"
            type="file"
            accept=".xlsx, .xls"
            @change="handleClick"
        />
    </div>
</templete>

// 点击下载模板
async toExcel() {
  axios
    .post('/api/ics/room/template', '', {
      responseType: 'blob', // 表明返回服务器返回的数据类型
      headers: {
        'content-type': 'multipart/from-data'
      }
    })
    .then((res) => {
      const link = document.createElement('a') //利用a标签自定义下载文件名
      let blob = new Blob([res], { type: 'application/vnd.ms-excel' }) //创建Blob对象，设置文件类型
      link.style.display = 'none'
      //设置连接
      link.href = URL.createObjectURL(blob)
      link.download = '批量导入房间模板.xls' //自定义文件名字
      document.body.appendChild(link)
      //模拟点击事件
      link.click()
    })
},
// 点击上传文件，实际实现input点击事件
handleUpload() {
    this.$refs['excel-upload-input'].click()
},
// 上传文件（本来在这里就可以把文件传给后端，但需求是需要点击提交按钮，上传文件，所以又加一个函数）
handleClick(e) {
    const files = e.target.files
    const rawFile = files[0] // only use files[0]
    if (!rawFile) return
    // this.rawFile = rawFile
    this.rawFile.push(rawFile)
    console.log(this.rawFile, 'this.rawFile') // 得到上传文件列表
    this.$message.success('文件上传成功')
},
// 提交
onSubmit() {
    // const formData = new FormData()
    // formData.append('files[]', this.rawFile)
    axios({
        url: '/api/ics/room/import',
        method: 'post',
        data: { 'files[]': this.rawFile },
        headers: { 'Content-Type': 'multipart/form-data' }
    }).then((res) => {
        // this.$emit("upload", res.data);
        if (res.status_code == 201) {
          this.$message.success('批量新增房间正在处理中...')
          this.$router.back()
        }
    })
},