<Button
          @click="open_import"
          type="primary"
          style="margin-right: 10px"
          >导入学生</Button
        >
        <Button
          type="primary"
          @click="downloadData()"
        >导出学生</Button
        >
    <!-- 批量导入 -->
    <Modal
      :mask-closable="false"
      v-model="import_flag"
      class="modal1"
      title="导入学生"
    >
      <div style="margin-left: 20px">
        <Form>
          <FormItem>
            <div>
              <p style="font-size: 16px; color: #333">
                导入前请先下载模板
                <Button type="primary" @click="download"
                  >下载学生信息模板</Button
                >
              </p>
            </div>
          </FormItem>
          <FormItem>
            <div class="flex" v-if="import_flag">
              <Upload action accept=".xls,.xlsx" :before-upload="handleUpload">
                <div v-if="!import_img" style="display: flex">
                  <Button
                    type="primary"
                    icon="ivu-icon ivu-icon- iconfont icon-shangchuan"
                    >上传excel</Button
                  >
                </div>
                <img
                  width="88px"
                  height="94px"
                  v-else
                  src="@/assets/img/my_lesson/excel.png"
                  alt
                />
              </Upload>
              <span
                class="theme_color"
                style="
                  margin-left: 10px;
                  font-size: 16px;
                  color: black !important;
                "
                >只能上传xlsx/xls文件</span
              >
            </div>
          </FormItem>
        </Form>
      </div>
      <div slot="footer">
        <Button @click="import_flag = false">取消</Button>
        <Button @click="ok_import" :loading="excel_flag" type="primary"
          >保存</Button
        >
      </div>
    </Modal>

<script>
import table2excel from "@/utils/table2excel.js";
export default {
  data() {
    return {
    import_flag: false, //导入学生弹窗
     form_data: undefined, //表格对象
      import_img: false, //excel 图片
      import_obj: {
        file: undefined,
      },
      excel_flag: false, //导入 确定动画
      exit_data: [], //导入 错误信息
    }
  },
methods: {
//导出
downloadData() {
      const file_name = "学生字典信息";
      const file_columns = [
        {
          title: "序号",
          type: "index",
          align: "center",
        },
        {
          title: "学生姓名",
          key: "username",
          align: "center",
        },
        {
          title: "登录账号",
          key: "loginname",
          align: "center",
        },
        {
          title: "性别",
          key: "usersex",
          align: "center",
        },
        {
          title: "学生手机号",
          key: "userphone",
          align: "center",
        },
        {
          title: "民族",
          key: "nation",
          align: "center",
        },
        {
          title: "学生身份证号",
          key: "cardid",
          align: "center",
        },
        {
          title: "户口所在地",
          key: "hkareaname",
          align: "center",
        },
        {
          title: "现住址",
          key: "xzareaname",
          align: "center",
        },
        {
          title: "学生证件照地址",
          key: "idphoto",
          align: "center",
        },
        {
          title: "父亲姓名",
          key: "fathername",
          align: "center",
        },
        {
          title: "父亲身份证号",
          key: "fathercardid",
          align: "center",
        },
        {
          title: "父亲手机号",
          key: "fatherphone",
          align: "center",
        },
        {
          title: "母亲姓名",
          key: "mothername",
          align: "center",
        },
        {
          title: "母亲身份证号",
          key: "mothercardid",
          align: "center",
        },
        {
          title: "母亲手机号",
          key: "motherphone",
          align: "center",
        },
        {
          title: "身份证正面地址",
          key: "cardzm",
          align: "center",
        },
        {
          title: "身份证反面地址",
          key: "cardfm",
          align: "center",
        },
        {
          title: "户口本首页地址",
          key: "householdzm",
          align: "center",
        },
        {
          title: "户口本本人页地址",
          key: "householdfm",
          align: "center",
        },
        {
          title: "审核状态",
          key: "checkstate",
          align: "center",
        },
      ];
      udict_list_dictstu({}).then((data) => {
        if (data.code === 0) {
          let file_data = data.obj.map((v) => {
            v.username = v.username || "";
            v.loginname = v.loginname || "";
            v.usersex =
              v.usersex === 1 ? "男" : v.usersex === 2 ? "女" : "未知";
            v.userphone = v.userphone || "";
            v.nation = v.nation || "";
            v.cardid = v.cardid || "";
            v.hkareaname = v.hkareaname || "";
            v.xzareaname = v.xzareaname || "";
            v.idphoto = v.idphoto || "";
            v.fathername = v.fathername || "";
            v.fathercardid = v.fathercardid || "";
            v.fatherphone = v.fatherphone || "";
            v.mothername = v.mothername || "";
            v.mothercardid = v.mothercardid || "";
            v.motherphone = v.motherphone || "";
            v.cardzm = v.cardzm || "";
            v.cardfm = v.cardfm || "";
            v.householdzm = v.householdzm || "";
            v.householdfm = v.householdfm || "";
            let checkstate = "";
            if (v.checkstate === 1) {
              checkstate = "待审核";
            } else if (v.checkstate === 3) {
              checkstate = "审核通过";
            } else if (v.checkstate === 4) {
              checkstate = "审核不通过";
            } else if (v.checkstate === -1) {
              checkstate = "已注册";
            } else if (v.checkstate === -2) {
              checkstate = "未注册";
            }
            v.checkstate = checkstate;
            return v;
          });
          this.dataToExcel(file_columns, file_data, file_name);
        } else {
          this.$Message.error(data.msg);
        }
      });
    },
    dataToExcel(columns, data, file_name) {
      table2excel(columns, data, file_name);
    }, 
   // 下载模板
    download() {
      let url = "@/../doc/student.xls";  //模版文件路径
      const a = document.createElement("a"); // 创建a标签
      a.setAttribute("download", "学生信息模版"); // download属性
      a.setAttribute("href", url); // href链接
      a.click(); // 自执行点击事件
    },
    // 上传文件
    handleUpload(file) {
      this.import_obj.file = file;
      let str = file.name.split(".");
      let suffix = str[str.length - 1];
      suffix = suffix.toLowerCase();
      if (suffix === "xls" || suffix === "xlsx") {
        this.import_img = true;
        this.form_data = new FormData();
        this.form_data.append("file", file);
        // this.form_data.append("schoolid", this.userInfo.content.schoolid);
        this.form_data.append("createid", this.userInfo.userid);
        // this.form_data.append("classid", this.form_list.classid);
        // this.form_data.append("groupid", 1);
      } else {
        this.$Message.error("请上传excel文件");
      }
      return false;
    },
    // 批量导入确定
    ok_import() {
      if (!this.form_data) {
        this.$Message.error("请选择上传文件");
        return;
      }
      this.excel_flag = true;
      udict_import(this.form_data).then((res) => {
        this.excel_flag = false;
        if (res.code == 0) {
          if (res.obj.code == 0) {
            this.$Message.success(res.msg);
            this.import_flag = false;
            this.init();
          } else {
            this.import_img = false;
            this.exit_flag = true;
            this.exit_data.usersExisted = res.obj.usersExisted.map((v) => {
              if (v.usersex == 1) {
                v.usersex = "男";
              } else if (v.usersex == 2) {
                v.usersex = "女";
              } else {
                v.usersex = "未知";
              }
              return v;
            });
            this.exit_data.usersExisted_length = res.obj.usersExisted.length;
            this.init();
          }
        } else {
          this.$Message.error(res.msg);
        }
      });
    },
    //导入学生按钮 每次参数都初始化
    open_import() {
      this.form_data = undefined;
      this.import_flag = true;
      this.excel_flag = false;
      this.import_img = false;
      this.import_obj.file = undefined;
    },
}
}
</script>

Excel

/* eslint-disable */
/* 
可以导出合并表头的表格为Excel
*/
let idTmr;
const getExplorer = () => {
    let explorer = window.navigator.userAgent;
    //ie
    if (explorer.indexOf("MSIE") >= 0) {
        return 'ie';
    }
    //firefox

    else if (explorer.indexOf("Firefox") >= 0) {
        return 'Firefox';
    }
    //Chrome
    else if (explorer.indexOf("Chrome") >= 0) {
        return 'Chrome';
    }
    //Opera
    else if (explorer.indexOf("Opera") >= 0) {
        return 'Opera';
    }
    //Safari
    else if (explorer.indexOf("Safari") >= 0) {
        return 'Safari';
    }
}
// 判断浏览器是否为IE
const exportToExcel = (data, name) => {

    // 判断是否为IE
    if (getExplorer() == 'ie') {
        tableToIE(data, name)
    } else {
        tableToNotIE(data, name)
    }
}

const Cleanup = () => {
    window.clearInterval(idTmr);
}

// ie浏览器下执行
const tableToIE = (data, name) => {
    let curTbl = data;
    let oXL = new ActiveXObject("Excel.Application");

    //创建AX对象excel
    let oWB = oXL.Workbooks.Add();
    //获取workbook对象
    let xlsheet = oWB.Worksheets(1);
    //激活当前sheet
    let sel = document.body.createTextRange();
    sel.moveToElementText(curTbl);
    //把表格中的内容移到TextRange中
    sel.select;
    //全选TextRange中内容
    sel.execCommand("Copy");
    //复制TextRange中内容
    xlsheet.Paste();
    //粘贴到活动的EXCEL中

    oXL.Visible = true;
    //设置excel可见属性

    try {
        let fname = oXL.Application.GetSaveAsFilename("Excel.xls", "Excel Spreadsheets (*.xls), *.xls");
    } catch (e) {
        print("Nested catch caught " + e);
    } finally {
        oWB.SaveAs(fname);

        oWB.Close(savechanges = false);
        //xls.visible = false;
        oXL.Quit();
        oXL = null;
        // 结束excel进程，退出完成
        window.setInterval("Cleanup();", 1);
        idTmr = window.setInterval("Cleanup();", 1);
    }
}

// 非ie浏览器下执行
const tableToNotIE = (function () {
    // 编码要用utf-8不然默认gbk会出现中文乱码
    const uri = 'data:application/vnd.ms-excel;base64,',
        template = '<html xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns="http://www.w3.org/TR/REC-html40"><head><meta charset="UTF-8"><!--[if gte mso 9]><xml><x:ExcelWorkbook><x:ExcelWorksheets><x:ExcelWorksheet><x:Name>{worksheet}</x:Name><x:WorksheetOptions><x:DisplayGridlines/></x:WorksheetOptions></x:ExcelWorksheet></x:ExcelWorksheets></x:ExcelWorkbook></xml><![endif]--></head><body><table>{table}</table></body></html>';

    const base64 = function (s) {
        return window.btoa(unescape(encodeURIComponent(s)));
    }

    const format = (s, c) => {
        return s.replace(/{(\w+)}/g,
            (m, p) => {
                return c[p];
            })
    }

    return (table, name) => {
        const ctx = {
            worksheet: name,
            table
        }

        const url = uri + base64(format(template, ctx));

        // if (navigator.userAgent.indexOf("Firefox") > -1){
        //  window.location.href = url
        // } else {
            const aLink = document.createElement('a');
            aLink.href = url;
            aLink.download = `${name || ''}.xls`;
            let event;
            if (window.MouseEvent) {
                event = new MouseEvent('click');
            } else {
                event = document.createEvent('MouseEvents');
                event.initMouseEvent('click', true, false, window, 0, 0, 0, 0, 0, false, false, false, false, 0, null);
            }
            aLink.dispatchEvent(event);
        // }
    }
})()

// 导出函数
const table2excel = (column, data, excelName) => {
    const typeMap = {
        image: getImageHtml,
        text: getTextHtml
    }
    function getMergeData(item) {
        let mergeStr = "";
        if (item.rowspan) {
            mergeStr += ` rowspan=${item.rowspan} `;
        }
        if (item.colspan) {
            mergeStr += ` colspan=${item.colspan} `;
        }
        return mergeStr;
    }
    function getTheadRow(column, theadArr = [], floor = 0) {
        if (theadArr.length == floor) {
            theadArr.push("");  
        }
        let trItem = column.reduce((result, item) => {
            let mergeData = getMergeData(item);
            result += `<th${mergeData}>${item.title}</th$>`;
            if (item.children) {
                getTheadRow(item.children, theadArr, floor + 1);
            }
            return result;
        }, '');
        if (theadArr[floor]) {
            theadArr.splice(floor, 1, theadArr[floor] + trItem);
        } else {
            theadArr.splice(floor, 1, trItem);
        }
        return theadArr;
    }
    let theadArr = getTheadRow(column);
    theadArr = theadArr.map(v => {
        return `<tr>${v}</tr>`;
    });
    let thead = `<thead>${theadArr.join("")}</thead>`;
    // thead = `<thead>
    //                      <tr>
    //                          <th rowspan="2">姓名</th>
    //                          <th colspan="2">电话</th>
    //                      </tr>
    //                      <tr>
    //                          <th>phone</th>
    //                          <th>tel</th>
    //                      </tr>
    //                  </thead>`
    let tbody = data.reduce((result, row, rowIndex) => {
        function getTbodyRow(column, colKeys = []) {
            column.forEach(item => {
                if (item.children && item.children.length > 0) {
                    getTbodyRow(item.children, colKeys);
                } else {
                    colKeys.push({
                        type: item.type,
                        key: item.key
                    });
                }
            });
            return colKeys;
        }
        let colKeys = getTbodyRow(column);
        const temp = colKeys.reduce((tds, col) => {
            let itemVal = "";
            if (col.type == "index") {
                itemVal = rowIndex + 1;
            } else {
                itemVal = row[col.key];
            }
            let newtd = typeMap[col.type == "image" ? "image" : "text"](itemVal);
            tds += newtd;
            return tds;
        }, '');
        result += `<tr>${temp}</tr>`
        return result
    }, '')

    tbody = `<tbody>${tbody}</tbody>`
    // tbody = `<tr>
    //                  <td>Bill Gates</td>
    //                  <td>555 77 854</td>
    //                  <td>555 77 855</td>
    //              </tr>`

    const table = thead + tbody;
    // 导出表格
    exportToExcel(table, excelName);

    function getTextHtml(val) {
        return `<td style="text-align: center;mso-number-format:'\@';" ng-bind="data.paySerialNo">${val}</td>`;
    }

    function getImageHtml(val, options) {
        options = Object.assign({width: 40, height: 60}, options)
        return `<td style="width: ${options.width}px; height: ${options.height}px; text-align: center; vertical-align: middle"><img src="${val}" width=${options.width} height=${options.height}></td>`
    }
}

export default table2excel