Vue的Excel表格上传解析与导出

需要 安装  xlsx

HTML

  <div>
    <el-upload
      action="222"
      accept=".xlsx,.xls"
      :auto-upload="false"
      :show-file-list="false"
      :on-change="hanlde"
    >
      <el-button size="small" type="primary">点击上传</el-button>
    </el-upload>
    <el-button
      size="small"
      type="primary"
      @click="exportFile()"
      style="margin-top: 20px"
      >导出</el-button
    >

    <el-table
      :data="tableData"
      style=" 100%"
      border=""
      @selection-change="checkFun"
    >
      <el-table-column type="selection" width="55"> </el-table-column>
      <el-table-column prop="name" label="姓名" min-width="50" align="center">
      </el-table-column>
      <el-table-column prop="phone" label="电话" min-width="50" align="center">
      </el-table-column>
    </el-table>
  </div>

data

  data() {
    return {
      tableData: [],
      checkBoxData: [],
    };
  },

import 导入

import { readFile, character, delay } from "./util";
import { Loading } from "element-ui";
import xlsx from "xlsx";

解析excel文件

1、将excel文件解析成二进制文件

2、将二进制文件转换成json

3、将传递到后台的数据进行处理

  //解析excel到表格
    async hanlde(ev) {
      let file = ev.raw;
      if (!file) return;
      let loadingInsetance = Loading.service({
        text: "请稍等",
        background: "rgba(0,0,0,.5)",
      });
      await delay(1000);
      //读取file文件
      let data = await readFile(file);
      //解析二进制数据
      let workBook = xlsx.read(data, { type: "binary" });
      /*
      console.log(workBook):
            SheetNames:页卡
            Sheets: 基本信息
                !ref：从哪里开始到哪里结束
                !margins：信息
      */
      let worksheet = workBook.Sheets[workBook.SheetNames[0]];
      data = xlsx.utils.sheet_to_json(worksheet);
      //将data中电话和姓名 使用 name和phone代替
      let arr = [];
      data.forEach((item) => {
        let obj = {};
        for (let key in character) {
          if (!character.hasOwnProperty(key)) break;
          let v = character[key],
            text = v.text,
            type = v.type;
          v = item[text] || "";
          type === "string" ? (v = String(v)) : null;
          type === "number" ? (v = Number(v)) : null;
          obj[key] = v;
        }
        arr.push(obj);
        this.tableData = arr;
        loadingInsetance.close();
      });
    },
    //上传数据
    async submit() {
      //完成后干的事情
      let complate = () => {
        this.$message.success({
          message: "数据采集完毕",
          duration: 2000,
        });
      };
      //把数据一条条传给服务器
      let n = 0;
      let send = async () => {
        if (n > this.tableData.length - 1) {
          //传递完成了
          complate();
          return;
        }
        let body = this.tableData[n];
        let res = await UploadAPI(body);
        if (res.code === 200) {
          //成功
          n++;
        }
        send();
      };
      send();
    },

导出表格数据到excel 

1、选择需要导出的数据

2、将json数据转换成表格数据

    exportFile() {
      //指定格式
      let arr = this.checkBoxData.map((item) => {
        return {
          姓名: item.name,
          电话: item.phone,
        };
      });
      let sheet = xlsx.utils.json_to_sheet(arr),
        //新建表格
        book = xlsx.utils.book_new();
      //向表格插入sheet数据，sheet1 标签列，
      xlsx.utils.book_append_sheet(book, sheet, "sheet1");
      xlsx.writeFile(book, `导出${new Date().getTime()}.xls`);
    },
    checkFun(val) {
      this.checkBoxData = val;
      console.log(this.checkBoxData);
    },

utils：

//设置异步间隔延迟

export function delay(interval = 0){
    return new Promise(resolve=>{
        let timer = setTimeout(_=>{
            clearTimeout(timer);
            resolve()
        },interval)
    })
}


//把文件按照二进制读取

export function readFile(file){
    return new Promise(resolve=>{
        let reader = new FileReader();
        reader.readAsBinaryString(file);
        reader.onload = ev=>{
            resolve(ev.target.result)
        }
    })
}

//字段对招募

export let character = {
    name:{
        text :"姓名",
        type:"string"
    },
    phone:{
        text :"电话",
        type:"string"  
    }
}