基于Vue实现Excel解析与导出功能详解

目录

• 前言
• 基本介绍
• 代码实现

• 基本结构
• 上传解析

• 基本结构
• 导出Excel

   <!-- 上传文件按钮 -->
   <div class="buttonBox">
     <el-upload
       action
       accept=".xlsx, .xls"
       :auto-upload="false"
       :show-file-list="false"
       :on-change="handle"
     >
       <el-button type="primary" slot="trigger">选取EXCEL文件</el-button>
     </el-upload>
     <el-button type="success" @click="submit" :disabled="disable">采集数据提交</el-button>
   </div>
   <!-- 解析出来的数据 -->
   <div class="tableBox" v-show="show">
     <h3>
       <i class="el-icon-info"></i>
       小主，以下是采集完成的数据，请您检查无误后，点击“采集数据提交”按钮上传至服务器哦！
     </h3>
     <el-table :data="tempData" style="width: 100%" :height="height" border>
       <el-table-column prop="name" label="姓名" min-width="50%"></el-table-column>
       <el-table-column prop="phone" label="电话" min-width="50%"></el-table-column>
     </el-table>
   </div>

// 把文件按照二进制进行读取
export function readFile(file) {
    return new Promise(resolve => {
        let reader = new FileReader();
        reader.readAsBinaryString(file);
        reader.onload = ev => {
             resolve(ev.target.result);
        };
    });
 }

  //读取FILE中的数据（变为JSON格式）
  let data = await readFile(file);
  let workbook = xlsx.read(data, { type: "binary" }),
    worksheet = workbook.Sheets[workbook.SheetNames[0]];
  data = xlsx.utils.sheet_to_json(worksheet);
  // 打印结果加下图
  console.log(workbook);

// 字段对应表
    export let character = {
        name: {
            text: "姓名",
            type: 'string'
        },
        phone: {
            text: "电话",
            type: 'string'
        }
    };

   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);
    });

    // 提交数据给服务器
    async submit() {
      if (this.tempData.length <= 0) {
        this.$message({
          message: "小主，请您先选择EXCEL文件！",
          type: "warning",
          showClose: true
        });
        return;
      }
      this.disable = true;
      let loadingInstance = Loading.service({
        text: "小主，请您稍等片刻，奴家正在玩命处理中！",
        background: "rgba(0,0,0,.5)"
      });
      // 完成后处理的事情
      let complate = () => {
        this.$message({
          message: "小主，奴家已经帮您把数据上传了！",
          type: "success",
          showClose: true
        });
        this.show = false;
        this.disable = false;
        loadingInstance.close();
      };
      // 需要把数据一条条传递给服务器
      let n = 0;
      let send = async () => {
        if (n > this.tempData.length - 1) {
          // 都传递完了
          complate();
          return;
        }
        let body = this.tempData[n];
        // 走接口
        let result = await createAPI(body);
        if (parseInt(result.code) === 0) {
          // 成功
          n++;
        }
        send();
      };
      send();
    }

  <div class="container">
    <!-- 上传按钮 -->
    <div class="buttonBox">
      <router-link to="/upload">
        <el-tooltip content="EXCEL数据采集" placement="top">
          <el-button type="primary" icon="el-icon-edit" circle></el-button>
        </el-tooltip>
      </router-link>
    </div>
    <!-- 搜索区域 -->
    <div class="searchBox">
      <el-input v-model="search" placeholder="基于姓名、手机模糊搜索" @change="searchHandle"></el-input>
      <el-button type="success" @click="submit" :disabled="disabled">导出选中的数据</el-button>
    </div>
    <!-- 列表区域 -->
    <div class="tableBox">
      <el-table
        :data="tableData"
        :height="height"
        style="width: 100%"
        v-loading="loading"
        element-loading-text="小主，奴家正在努力加载中..."
        @selection-change="selectionChange"
      >
        <el-table-column type="selection" width="50" align="center"></el-table-column>
        <el-table-column prop="id" label="编号" min-width="10%"></el-table-column>
        <el-table-column prop="name" label="姓名" min-width="20%"></el-table-column>
        <el-table-column prop="phone" label="电话" min-width="20%"></el-table-column>
        <el-table-column prop="time" label="创建时间" min-width="25%" :formatter="formatter"></el-table-column>
      </el-table>
    </div>
    <!-- 分页区域 -->
    <div class="pageBox">
      <el-pagination
        background
        hide-on-single-page
        layout="total, sizes, prev, pager, next"
        :page-size="pageSize"
        :current-page="page"
        :total="total"
        @size-change="sizeChange"
        @current-change="prevNext"
        @prev-click="prevNext"
        @next-click="prevNext"
      ></el-pagination>
    </div>
  </div>

    // 导出数据
    submit() {
      if (this.selectionList.length <= 0) {
        this.$message({
          message: "小主，请您先选择要导出的数据哦！",
          type: "warning",
          showClose: true
        });
        return;
      }
      this.disabled = true;
      let loadingInstance = Loading.service({
        text: "小主，请您稍等片刻，奴家正在玩命处理中...",
        background: "rgba(0,0,0,.5)"
      });
      let arr = this.selectionList.map(item => {
        return {
          编号: item.id,
          姓名: item.name,
          电话: item.phone
        };
      });
      // 将json数据变为sheet数据
      let sheet = xslx.utils.json_to_sheet(arr),
      // 新建表格
        book = xslx.utils.book_new();
      // 在表格中插入一个sheet
      xslx.utils.book_append_sheet(book, sheet, "sheet1");
      // 通过xlsx的writeFile方法将文件写入
      xslx.writeFile(book, `user${new Date().getTime()}.xls`);
      loadingInstance.close();
      this.disabled = false;
    }