导入Excel并预览导入的结果及其导入失败的信息功能组件封装

最新推荐文章于 2025-11-21 09:05:51 发布
原创最新推荐文章于 2025-11-21 09:05:51 发布 · 357 阅读
4 ·
CC 4.0 BY-SA版权
文章标签：
#excel #vue.js #前端 #elementui
项目框架及依赖

此项目是基于vue2 + elementUI下开发的，此次使用到的插件有xlsx插件库
xlsx插件安装命令npm install xlsx
此模块实现的功能

前端导入Excel并预览导入的结果及其导入失败的信息功能组件
封装组件的代码

代码如下
<template>
  <el-dialog
    title="导入人员补贴"
    width="70%"
    :visible.sync="visible"
    @close="closeHander"
    :close-on-click-modal="false"
  >
    <div class="button-box">
      <el-button
        type="primary"
        icon="el-icon-folder-add"
        size="mini"
        @click="uploadingtHander"
        style="margin-right: 15px"
      >
        打开文件
      </el-button>
      <input
        type="file"
        id="uploadExcel"
        ref="uploadExcel"
        v-show="false"
        accept=".xls, .xlsx, .excel"
        @change="readExcel"
      />

      <el-button
        type="warning"
        icon="el-icon-edit"
        size="mini"
        @click="importHander"
        :loading="importLoading"
      >
        开始导入
      </el-button>

      <el-button
        type="info"
        icon="el-icon-download"
        size="mini"
        @click="exportHander"
      >
        导出失败信息
      </el-button>
    </div>

    <el-table
      :data="showData"
      height="380px"
      border
      :stripe="true"
      :header-cell-style="{ background: '#eef1f6', color: '#606266' }"
    >
      <template v-for="(item, index) in column">
        <el-table-column
          :prop="item.property"
          :label="item.label"
          :key="index"
          v-if="item.property == 'excelReturnResult'"
        >
          <template slot-scope="scope">
            <span
              :style="{
                color: scope.row.excelReturnResult != 'success' ? 'red' : '',
              }"
              >{{ scope.row.excelReturnResult }}</span
            >
          </template>
        </el-table-column>
        <el-table-column
          :prop="item.property"
          :label="item.label"
          :key="index"
          v-else
        >
        </el-table-column>
      </template>
    </el-table>

    <div class="botton-box">
      <!-- 导入统计结果 -->
      <div style="color: red">
        共{{ allData.length }}
        <span v-if="allData.every((item) => item.excelReturnResult)">
          ,成功{{
            allData.filter((item) => item.excelReturnResult == "success").length
          }}, 失败{{
            allData.filter((item) => item.excelReturnResult !== "success").length
          }}
        </span>
        &nbsp;
      </div>

      <!-- 分页器 -->
      <div class="block">
        <!-- :page-sizes="[20]" -->
        <el-pagination
          background
          :current-page.sync="currentPage"
          :page-sizes="[20]"
          :page-size="currentSize"
          layout=" jumper, prev, pager,next, total,sizes"
          :total="dataCount"
          @size-change="handleSizeChange"
          @current-change="handleCurrentChange"
        />
      </div>
    </div>
    <div slot="footer" class="dialog-footer">
      <el-button type="default" @click="closeHander">关闭</el-button>
    </div>
  </el-dialog>
</template>

<script>
import { uploadExcel } from "@/api/attendanceManagement";
//excel文件数据解析
import * as XLSX from "xlsx";

// 按照二进制读取文件 excel文件的处理
const readFile = (file) => {
  return new Promise((resolve) => {
    const reader = new FileReader();
    reader.readAsBinaryString(file);
    reader.onload = (e) => {
      resolve(e.target.result);
    };
  });
};
export default {
  data() {
    // currentSize: 20,
    return {
      visible: false,
      currentPage: 1,
      currentSize: 20,
      dataCount: 0,
      // 当前展示数据
      showData: [],
      // 原始数据
      allData: [],
      column: [
        {
          property: "In_FPersonNo",
          label: "人员编号",
        },
        {
          property: "In_FPersonName",
          label: "姓名",
        },
        {
          property: "In_FSubsidyImportTypeNo",
          label: "导入方式",
        },
        {
          property: "In_FSubsidyMonth",
          label: "补贴月份",
        },
        {
          property: "In_FSubsidyMoney",
          label: "补贴金额",
        },
        // {
        //   property: "In_FSubsidyUsable",
        //   label: "发放方式",
        // },
        {
          property: "excelReturnResult",
          label: "导入结果",
        },
      ],
      tableConfig: {
        In_FPersonNo: "人员编号",
        In_FPersonName: "姓名",
        In_FSubsidyImportTypeNo: "导入方式",
        In_FSubsidyMonth: "补贴月份",
        In_FSubsidyMoney: "补贴金额",
        // In_FSubsidyUsable: "发放方式"
      },
      // 开始导入加载
      importLoading: false,
      // 导入的文件名
      excelName: '',
      // 上传接收的文件流
      excelFile: null,
    };
  },
  methods: {
    init() {
      this.currentPage = 1;
      this.currentSize = 20;
      this.dataCount = 0;
      this.visible = true;
    },
    closeHander() {
      this.showData = [];
      this.allData = [];
      // 清空上传文件事件的值
      this.$refs.uploadExcel.value = null;
      this.excelName = ''
      this.excelFile = null
      this.visible = false;
    },
    handleSizeChange(val) {
      this.currentSize = val;
      this.showData = this.allData
        ? this.allData.slice(
            (this.currentPage - 1) * this.currentSize,
            (this.currentPage - 1) * this.currentSize + this.currentSize
          )
        : [];
    },
    handleCurrentChange(val) {
      // 当前页
      this.currentPage = val;
      this.showData = this.allData
        ? this.allData.slice(
            (this.currentPage - 1) * this.currentSize,
            (this.currentPage - 1) * this.currentSize + this.currentSize
          )
        : [];
    },
    // 上传文件
    uploadingtHander() {
      this.$refs.uploadExcel.click(); // 浏览文件
    },
    readExcel(event) {
      if (event.target.files.length != 0) {
        let fileList = event.target.files;
        this.excelName = fileList[0].name;
        this.excelFile = this.$refs.uploadExcel.files[0];
        console.log("this.excelFile =>", this.excelFile);
        this.gmDrSure();
      }
    },
    //读取Excel中的数据
    // 导入时excel中必须填写的有：人员编号、姓名、导入方式、补贴月份、补贴金额
    async gmDrSure() {
      //读取file中的数据
      //把文件解析成二进制数据，把二级制数据变成excel表格式的数据
      let data = await readFile(this.excelFile);
      let workbook = XLSX.read(data, { type: "binary" });
      //拿到第一个sheet表的数据，把第一个表格的数据转换成JSON数据
      const worksheet = workbook.Sheets[workbook.SheetNames[0]];
      data = XLSX.utils.sheet_to_json(worksheet);
      console.log("data222", data);
      // return
      const arr = [];
      data.forEach((item) => {
        let obj = {};
        for (let key in this.tableConfig) {
          // 此处keyof是为了解决直接使用key索引会报错问题
          let v = this.tableConfig[key];
          v = item[v] || "";
          obj[key] = v;
        }
        arr.push(obj);
      });
      console.log("表格数据", arr);

      this.allData = arr;

      this.dataCount = this.allData ? this.allData.length : 0;
      // 分页计算
      this.showData = this.allData.length
        ? this.allData.slice(
            (this.currentPage - 1) * this.currentSize,
            (this.currentPage - 1) * this.currentSize + this.currentSize
          )
        : [];
    },
    // 开始导入
    importHander() {
      let formData = new FormData();
      formData.append("file", this.excelFile);
      // console.log("formData", formData);
      if (this.excelName === "") {
        this.$message({
          type: "warning",
          message: "请选择excel文件！",
          duration: 2 * 1000,
        });
        return;
      }

      // 组装请求数据
      let jsonStr = `{"apiName":"/ykt/COST_Subsidy_UpdateImport",
                "excelColums":"In_FPersonNo,In_FPersonName,In_FSubsidyImportTypeNo,In_FSubsidyMonth,In_FSubsidyMoney",
                "excelColumsName":"人员编号,姓名,导入方式,补贴月份,补贴金额"}`;
      formData.append("jsonStr", jsonStr);

      // 此导入如果工号已经在人员编号中存在，则会进行更新人员的信息
      this.importLoading = true;
      uploadExcel(formData)
        .then((res) => {
          if (res.code == 200) {
            this.allData = res.data;
            this.dataCount = this.allData ? this.allData.length : 0;
            this.showData = this.allData
              ? this.allData.slice(
                  (this.currentPage - 1) * this.currentSize,
                  (this.currentPage - 1) * this.currentSize + this.currentSize
                )
              : [];

            this.$message.success("导入成功！");
          } else {
            this.$message.warning("导入失败：" + res.msg);
          }
        })
        .finally(() => {
          this.importLoading = false;
          // 清空上传文件事件的值
          this.$refs.uploadExcel.value = null;
        });
    },
    // 导出执行开始导入时未成功的信息
    exportHander() {
      if(!this.allData.every(item => item.excelReturnResult )) {
        this.$message.warning('数据未操作开始导入，请先操作开始导入！')
        return
      }

      if(this.allData.every(item => item.excelReturnResult == 'success' )) {
        this.$message.warning('导入时无失败的数据！')
        return
      }

      const tableData = this.transData(this.column, this.allData.filter(item => item.excelReturnResult != 'success'));
      // 转换成excel时，使用原始的格式，这样导出的时候数字过长不会变成科学计数法
      var xlsxParam = { raw: true };
      // 将一组JS数据数组转换为工作表
      const ws = XLSX.utils.aoa_to_sheet(tableData, xlsxParam); // 使用二维数组创建一个工作表对象
      // 创建workbook 工作簿对象
      const wb = XLSX.utils.book_new();
      // XLSX.utils.book_append_sheet(wb, ws, 'Sheet1')
      XLSX.utils.book_append_sheet(wb, ws, '补贴导入失败结果数据');
      // 将workbook写入文件
      XLSX.writeFile(wb, '补贴导入失败结果数据' + ".xlsx");
    },
    // 转换表头组装数据
    transData(columns, tableList) {
      const obj = columns.reduce((acc, cur) => {
        if (!acc.titles && !acc.keys) {
          acc.titles = [];
          acc.keys = [];
        }
        acc.titles.push(cur.label);
        acc.keys.push(cur.property);
        return acc;
      }, {});
      const tableBody = tableList.map((item) => {
        return obj.keys.map((key) => item[key]);
      });
      return [obj.titles, ...tableBody];
    },
  },
};
</script>

<style lang="scss" scoped>
// 分页器
.block {
  margin-top: 20px;
  // margin-bottom: 20px;
  overflow: hidden;
  .el-pagination {
    float: right;
    margin-right: 20px;
    // margin-bottom: 20px;
  }
}
.dialog-footer {
  display: flex;
  justify-content: center;
}

.button-box {
  margin-bottom: 20px;
}

.botton-box {
  display: flex;
  justify-content: space-between;
  align-items: center;
}
</style>