vue实现excel文件的导入和读取

1.效果展示

上传数据前

 上传数据后

或者

 

2.下载

npm install xlsx@0.17.0

如果一直报关于xlsx的read的错误，这里是因为xlsx的0.18.0版本已经没有read属性了，所以最好是使用0.18.0版本以下的xlsx。

3.js文件

excel.js

import { stringify } from "json5";

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

// 字段对应表
export let character = {
  staffName: {
    text: "员工姓名",
    type: "string",
  },
  sex: {
    text: "性别",
    type: "string",
  },
  idNumber: {
    text: "身份证号",
    type: "string",
  },
  staffEmail: {
    text: "员工邮箱",
    type: "string",
  },
  phone: {
    text: "电话号码",
    type: "string",
  },
  jobPreference: {
    text: "工作偏好",
    type: "string",
  },
  timePreference: {
    text: "时间偏好",
    type: "string",
  },
  datePreference: {
    text: "时间段偏好",
    type: "string",
  }
};

// 时间字符串格式化
export function formatTime(str, tpl) {
  let arr = str.match(/\d+/g).map((item) => {
    return item.length < 2 ? "0" + item : item;
  });
  tpl = tpl || "{0}年{1}月{2}日 {3}时{4}分{5}秒";
  return tpl.replace(/\{(\d+)\}/g, (_, group) => {
    return arr[group] || "00";
  });
}

utils.js 实现加载

// 设置异步延迟间隔
export function delay(interval = 0) {
    return new Promise((resolve) => {
      let timer = setTimeout((_) => {
        clearTimeout(timer);                                                                                                          
        resolve();
      }, interval);
    });
  }

4 .界面处理

<template>
  <el-upload
    ref="upload"
    class="upload-demo"
    action
    accept=".xlsx,.xls"
    :show-file-list="false"
    :on-exceed="handleExceed"
    :auto-upload="false"
    :on-change="handle"
  >
    <template #trigger>
      <el-button type="primary">选择文件</el-button>
    </template>
  </el-upload>
  <el-table :data="employeeData" border style="width: 100%">
    <el-table-column prop="staffName" label="姓名" width="100px" />
    <el-table-column prop="sex" label="性别" width="80px" />
    <el-table-column prop="idNumber" label="身份证号" width="200px" />
    <el-table-column prop="phone" label="电话号码" width="150px" />
    <el-table-column prop="staffEmail" label="邮箱" width="200px" />
    <el-table-column prop="jobPreference" label="工作偏好" />
    <el-table-column prop="timePreference" label="时间偏好" />
    <el-table-column prop="datePreference" label="时间段偏好" />
    <el-table-column label="操作">
        <el-button>编辑</el-button>
        <el-button type="danger">删除</el-button>
    </el-table-column>
  </el-table>
</template>
<script>
//excel文件的处理
import { character, readFile } from "@/assets/js/excel";
//异步消息处理
import { delay } from "@/assets/js/utils";
//excel文件数据解析
import XLSX from "xlsx";
import { ElLoading } from 'element-plus'

export default {
  data() {
    return {
      employeeData: [],
    };
  },
  component: {},
  created() {
  },
  methods: {
    //采集excel数据
    async handle(ev) {
      //这个是上传的文件
      let file = ev.raw;
      // console.log(file, "file");
      //没有文件
      if (!file) return;

      //解析和上传到后端的时候进行loading加载显示
      let loadingInstance =  ElLoading.service({
        text: "请稍等一下，数据正在处理中"
      });

      await delay(100);//延迟

      //读取file中的数据
      //把文件解析成二进制数据，把二级制数据变成excel表格式的数据
      let data = await readFile(file);
      let workbook = XLSX.read(data, { type: "binary" });

      //拿到第一个sheet表的数据，把第一个表格的数据转换成JSON数据
      const worksheet = workbook.Sheets[workbook.SheetNames[0]];
      data = XLSX.utils.sheet_to_json(worksheet);

      //把读取出来的数据变成最后可以传递给服务器的数据
      let employees = [];
      data.forEach((item) => {
        let obj = {};
        for (let key in character) {
          if (!character.hasOwnProperty(key)) break;
          let v = character[key];
          const text = v.text;
          const type = v.type;
          v = item[text] || "";

          //对数据类型的处理
          type === "string" ? (v = String(v)) : null;
          type === "number" ? (v = Number(v)) : null;
          obj[key] = v;
        }
        employees.push(obj);
      });
      console.log(employees, "最后传入后端的excel数据");

      this.employeeData = employees;
      loadingInstance.close();
    },
  },
};
</script>