vue项目前端生成EXCEL模板并解析上传JSON

一，安装依赖      并引入 

        "file-saver": "^2.0.5",

        "xlsx": "^0.18.5"

        import { saveAs } from "file-saver";

二，生成模板

function excelDownloadForArea(colIndex) {
  const ordA = 'A'.charCodeAt(0);
  const ordZ = 'Z'.charCodeAt(0);
  const len = ordZ - ordA + 1;
  let str = '';
  while (colIndex >= 0) {
    str = String.fromCharCode(colIndex % len + ordA) + str;
    colIndex = Math.floor(colIndex / len) - 1;
  }
  return str;
}

function tableToolTipsDisplay(type) {
  const toolTips = document.getElementsByClassName('el-tooltip__popper');
  toolTips.forEach((e) => {
    e.style.display = type;
  });
}

function tableShowSortTypePublic(key, self) {
  const orders = self[key + 'ForOrderType'];
  const columns = self.$refs[key + 'TableRef'].collectColumn;
  const orderTemp = [];
  let realCols = [];
  function getCols(columns) {
    columns.forEach((column) => {
      if (column.children && column.children.length > 0) {
        getCols(column.children);
      } else {
        realCols = realCols.concat(column);
      }
    });
  }
  getCols(columns);
  realCols.forEach((column) => {
    column.order = null;
    orders.forEach((order) => {
      if (column.property === order.property && orderTemp.indexOf(order.property) === -1) {
        orderTemp.push(order.property);
        column.order = order.order;
      }
      if (column.property === order.column && orderTemp.indexOf(order.column) === -1) {
        orderTemp.push(order.column);
        column.order = order.asc ? 'asc' : 'desc';
      }
    });
  });
  self[key + 'TableLoad']();
}

function tableScrollbarPublic(key, self) {
  self.$nextTick(() => {
    if (self.$refs && self.$refs[key + 'TableRef']?.layout) {
      self.$refs[key + 'TableRef'].layout.gutterWidth = 9;
      self.$refs[key + 'TableRef'].doLayout();
    }
  });
}

function treeTableColSumDealDataPublic(key, data) {
  let flag = [];
  data.forEach((item) => {
    flag.push(item);
    if (item.children.length > 0) {
      flag = flag.concat(treeTableColSumDealDataPublic(key, item.children));
    }
  });
  return flag;
}

function treeTableToggleSelectionPublic(key, self, row, select) {
  if (row) {
    self.$nextTick(() => {
      self.$refs[key + 'TableRef'] && self.$refs[key + 'TableRef'].toggleRowSelection(row, select);
    });
  }
}

function treeTableTraversePublic(key, self, data, id) {
  Object.keys(data).forEach((i) => {
    if (data[i].id === id) {
      self.$refs[key + 'TableRef'].setCurrentRow(data[i]);
    }
    if (data[i].children) {
      treeTableTraversePublic(key, self, data[i].children, id);
    }
  });
}

function treeTableChildrenDataLoadPublic(key, self, datas, rootDataArray) {
  for (let j = 0; j < rootDataArray.length; j++) {
    const dataArrayIndex = rootDataArray[j];
    const childrenArray = [];
    const id = dataArrayIndex.id;
    for (let i = 0; i < datas.length; i++) {
      const data = datas[i];
      const pid = data.pid;
      if (pid == id) {
        const objTem = {
          id: data.id,
          pid: pid,
          ...data
        };
        childrenArray.push(objTem);
      }
    }
    dataArrayIndex.children = childrenArray;
    if (childrenArray.length > 0) {
      treeTableChildrenDataLoadPublic(key, self, self[key + 'Data'], childrenArray);
    }
  }
  return rootDataArray;
}

export
{
  excelDownloadForArea,
  tableToolTipsDisplay,
  tableShowSortTypePublic,
  tableScrollbarPublic,
  treeTableColSumDealDataPublic,
  treeTableToggleSelectionPublic,
  treeTableTraversePublic,
  treeTableChildrenDataLoadPublic
};

import { excelDownloadForArea } from "../../utils/tableRelevantUtil";
button_23Click() {
      const self = this;
      // 导入模板下载
      // const tableData = ['菜单id', '菜单编码', '菜单别名', '功能模块ID', '功能ID', '父菜单', '显示顺序', '是否是叶子节点', '创建时间'];
      // const tableField = ['menuId', 'menuText', 'menuAlias', 'functionModuleId', 'functionId', 'parentId', 'seq', 'isLeaf', 'createTime'];
      const tableData = [
        "姓名",
        "用户账户",
        "性别",
        "最大会话数",
        "手机号",
        "微信账号",
      ];
      const tableField = [
        "staffName",
        "userAccount",
        "sex",
        "maxSessions",
        "mobile",
        "weChat",
      ];
      const columns = [];
      for (let i = 0; i < tableData.length; i++) {
        const excelCol = {};
        // excelCol.header = tableData[0][i];
        excelCol.key = tableField[i];
        excelCol.width = 30;
        columns.push(excelCol);
      }
      const Excel = require("exceljs");
      const workbook = new Excel.Workbook();
      const worksheet = workbook.addWorksheet("sheet1");
      const fileName = "data.xlsx";
      workbook.created = new Date();
      workbook.modified = new Date();
      worksheet.columns = columns;
      const colArray = [];
      const tableHead = [];
      let level = 1;
      let maxLevel = 1;
      
      tableHead.push({
        title: "姓名",
        horizontal: "center",
        colspan: 1,
        rowspan: 1,
        level: level,
      });
      const staffNameIndex = tableField.indexOf("staffName");
      const staffNameIdLetter = excelDownloadForArea(staffNameIndex);
      colArray.push({
        letter: staffNameIdLetter,
        horizontal: "center",
        dataType: "string",
      });

      tableHead.push({
        title: "用户账户",
        horizontal: "center",
        colspan: 1,
        rowspan: 1,
        level: level,
      });
      const userAccountIdIndex = tableField.indexOf("userAccount");
      const userAccountIdIdLetter = excelDownloadForArea(userAccountIdIndex);
      colArray.push({
        letter: userAccountIdIdLetter,
        horizontal: "center",
        dataType: "string",
      });

      tableHead.push({
        title: "性别",
        horizontal: "center",
        colspan: 1,
        rowspan: 1,
        level: level,
      });
      const sexIndex = tableField.indexOf("sex");
      const sexIdLetter = excelDownloadForArea(sexIndex);
      colArray.push({
        letter: sexIdLetter,
        horizontal: "center",
        dataType: "string",
      });

      tableHead.push({
        title: "最大会话数",
        horizontal: "center",
        colspan: 1,
        rowspan: 1,
        level: level,
      });
      const maxSessionsIdIndex = tableField.indexOf("maxSessions");
      const maxSessionsIdIdLetter = excelDownloadForArea(maxSessionsIdIndex);
      colArray.push({
        letter: maxSessionsIdIdLetter,
        horizontal: "center",
        dataType: "string",
      });

      tableHead.push({
        title: "手机号",
        horizontal: "center",
        colspan: 1,
        rowspan: 1,
        level: level,
      });
      const telephoneIdIndex = tableField.indexOf("mobile");
      const telephoneIdIdLetter = excelDownloadForArea(telephoneIdIndex);
      colArray.push({
        letter: telephoneIdIdLetter,
        horizontal: "center",
        dataType: "string",
      });

      tableHead.push({
        title: "微信账号",
        horizontal: "center",
        colspan: 1,
        rowspan: 1,
        level: level,
      });
      const weChatIdIndex = tableField.indexOf("weChat");
      const weChatIdIdLetter = excelDownloadForArea(weChatIdIndex);
      colArray.push({
        letter: weChatIdIdLetter,
        horizontal: "center",
        dataType: "string",
      });

      --level;
      for (let i = 0; i < tableHead.length; i++) {
        if (tableHead[i].level > maxLevel) {
          maxLevel = tableHead[i].level;
        }
      }
      for (let i = 0; i < tableHead.length; i++) {
        if (tableHead[i].rowspan + tableHead[i].level - 1 > maxLevel) {
          tableHead[i].rowspan = maxLevel - tableHead[i].level + 1;
        }
      }
      const excelCell = [];
      const rows = [];
      for (let i = 0; i < tableField.length; i++) {
        for (let j = 0; j < maxLevel; j++) {
          const cell = { xAxis: i, yAxis: j, isEdit: false };
          excelCell.push(cell);
        }
      }
      for (let i = 0; i < maxLevel; i++) {
        let x = 0;
        const row = [];
        for (let j = 0; j < tableHead.length; j++) {
          const index = excelCell.findIndex(function (value, index, arr) {
            return value.isEdit === false && value.yAxis === i;
          });
          if (index !== -1) {
            x = excelCell.find(function (value, index, arr) {
              return value.isEdit === false && value.yAxis === i;
            }).xAxis;
          }
          if (tableHead[j].level - 1 === i) {
            const startArea = excelDownloadForArea(x) + (1 + i);
            const endArea =
              excelDownloadForArea(x + tableHead[j].colspan - 1) +
              (tableHead[j].rowspan + i);
            for (let k = x; k < x + tableHead[j].colspan; k++) {
              for (let m = i; m < tableHead[j].rowspan + i; m++) {
                excelCell.find(function (value, index, arr) {
                  return value.xAxis === k && value.yAxis === m;
                }).isEdit = true;
              }
            }
            while (row.length < x) {
              row.push("");
            }
            row[x] = tableHead[j].title;
            tableHead[j].merge = startArea + ":" + endArea;
          }
        }
        rows.push(row);
      }
      worksheet.insertRows(1, rows);
      for (let j = 0; j < tableHead.length; j++) {
        worksheet.mergeCells(tableHead[j].merge);
        worksheet.getCell(tableHead[j].merge.split(":")[0]).alignment = {
          vertical: "middle",
          horizontal: tableHead[j].horizontal,
        };
      }
      for (let i = 0; i < colArray.length; i++) {
        for (let j = maxLevel + 1; j < 1000; j++) {
          worksheet.getCell(colArray[i].letter + j).alignment = {
            horizontal: colArray[i].horizontal,
          };
          if (colArray[i].dataType === "date") {
            worksheet.getCell(colArray[i].letter + j).dataValidation = {
              type: "date",
              operator: "between",
              showErrorMessage: true,
              allowBlank: true,
              formulae: [new Date(1900, 1, 1), new Date(2100, 1, 1)],
              showInputMessage: true,
              promptTitle: "日期",
              prompt: "请输入日期格式数据",
            };
          } else if (colArray[i].dataType === "number") {
            worksheet.getCell(colArray[i].letter + j).dataValidation = {
              type: "decimal",
              allowBlank: true,
              showErrorMessage: true,
              showInputMessage: true,
              promptTitle: "数字",
              prompt: "请输入数字格式数据",
            };
          }
        }
      }
      workbook.xlsx.writeBuffer().then(function (buffer) {
        saveAs(
          new Blob([buffer], {
            type: "application/octet-stream",
          }),
          fileName
        );
      });
    },

三，导入并解析

 importExcel() {
      const self = this;
      // 数据表格导入
      const importFile = document.createElement("input");
      importFile.style.display = "none";
      importFile.type = "file";
      importFile.value = "";
      importFile.accept =
        ".csv,.xlc,.xlm,.xls,.xlt, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet, application/vnd.ms-excel";
      importFile.onchange = function (event) {
        self.button_24importF(event, () => {
          // 此处生成了空方法体
        });
      };
      importFile.click();
    },
    button_24importF(event, callback) {
      const self = this;
      const file = event.currentTarget.files[0];
      let success = false;
      const startIndex = file.name.lastIndexOf(".");
      if (startIndex < 0) {
        self.$message("文件格式错误，请重新选择！");
        return;
      }
      const types = file.name.slice(startIndex + 1);
      const fileType = ["xlsx", "xlc", "xlm", "xls", "xlt"].some(
        (item) => item === types
      );
      if (!fileType) {
        self.$message("文件格式错误，请重新选择！");

        return;
      }
      const Excel = require("exceljs");
      const fileReader = new FileReader();
      const dicts = [];
      const relates = [];
      fileReader.onload = function (ev) {
        const data = ev.target.result;
        const workbook = new Excel.Workbook();
        workbook.xlsx.load(data).then(function () {
          // const values = ['菜单id', '菜单编码', '菜单别名', '功能模块ID', '功能ID', '父菜单', '显示顺序', '是否是叶子节点', '创建时间'];
          // const prop = ['menuId', 'menuText', 'menuAlias', 'functionModuleId', 'functionId', 'parentId', 'seq', 'isLeaf', 'createTime'];
          const values = [
            "姓名",
            "用户账户",
            "性别",
            "最大会话数",
            "手机号",
            "微信账号",
          ];
          const prop = [
            "staffName",
            "userAccount",
            "sex",
            "maxSessions",
            "mobile",
            "weChat",
          ];
          function cellValueToDict(keys, row) {
            const data = {};
            row.eachCell(function (cell, colNumber) {
              let value = cell.value;
              if (cell.type === Excel.ValueType.Date) {
                value = new Date(value.valueOf() - 8 * 60 * 60 * 1000);
              }
              if (dicts.length !== 0) {
                const findIndex = dicts.findIndex(function (dictItem) {
                  return dictItem.field === prop[colNumber - 1];
                });
                if (findIndex !== -1) {
                  if (value.toString().includes(",")) {
                    const valueArr = [];
                    value.split(",").forEach(function (val) {
                      const dictItem = dicts[findIndex].dict.find(function (
                        dictItem
                      ) {
                        return (
                          val === dictItem.label &&
                          dictItem.field === prop[colNumber - 1]
                        );
                      });
                      if (dictItem) {
                        valueArr.push(dictItem.value);
                      } else {
                        valueArr.push(value);
                      }
                    });
                    value = valueArr.join(",");
                  } else {
                    const dictItem = dicts[findIndex].dict.find(function (
                      dictItem
                    ) {
                      return (
                        value === dictItem.label &&
                        dictItem.field === prop[colNumber - 1]
                      );
                    });
                    if (dictItem) {
                      value = dictItem.value;
                    }
                  }
                }
              }
              data[prop[colNumber - 1]] = value;
            });
            return data;
          }
          const dataArray = [];
          let keys = [];
          const worksheet = workbook.worksheets[0]; // 获取第一个worksheet
          worksheet.eachRow(function (row, rowNumber) {
            if (rowNumber === 1) {
              keys = row.values;
            } else if (rowNumber > 1) {
              const rowDict = cellValueToDict(keys, row);
              dataArray.push(rowDict);
            }
          });
          const valuesCopy = values.slice(0);
          const keysCopy = keys
            .filter(function (el) {
              return el;
            })
            .slice(0);
          if (
            JSON.stringify(keysCopy.sort()) !==
            JSON.stringify(valuesCopy.sort())
          ) {
            HussarRouter.showMsg(self, "导入模板错误，请重新选择！", "error");
            return;
          }
          //处理时间格式问题开始
          dataArray.forEach(function (item) {
            for (let val in item) {
              if (item[val] instanceof Date) {
                item[val] = dateFormatPublic("datetime", item[val]);
              }
            }
          });

          /**
           * parentId 机构编号
           * parentName 机构名称
           * departmentId 所在机构
           */
          dataArray.forEach((item) => {
            item.departmentId = self.currentNodeData.departmentId;
            item.parentId = self.currentNodeData.id;
            item.parentName = self.currentNodeData.label;
          });

        });
      };
      fileReader.readAsBinaryString(file);
    },