Excel表格导入导出数据

Excel表格导入导出

将表格数据导出到Excel表格中

	<script>
  import axios from 'axios';
  export default {
    name: "me",
    methods:{
      submitDownload() {
        axios({
          headers: {
            'Content-Type': 'application/json'
          },
          transformRequest: [function(data) {
            data = JSON.stringify(data)
            return data
          }],
          url: 'http://192.168.3.113:8088/ysh/invoice/exportTable',
          method: 'post',
          data: { "companyId": "com12313213"},
          responseType: 'blob'    // 重点在于配置responseType: 'blob'
        }).then(res => {
          const link = document.createElement('a');  // 创建元素
          let blob = new Blob([res.data], { type: 'application/vnd.ms-excel' });
          link.style.display = 'none';
          link.href = URL.createObjectURL(blob);   // 创建下载的链接
          //num++
          link.setAttribute('download', '员工表.xlsx');  // 给下载后的文件命名
          document.body.appendChild(link);
          link.click();  // 点击下载
          document.body.removeChild(link);  //  下载完成移除元素
          window.URL.revokeObjectURL(link.href);  // 释放掉blob对象
        }).catch(_ => {
            });
      }
    }
  }
</script>

将Excel表格数据导入到table表格中

HTML

  <el-upload		
    class="uploads"
    action=""
    :multiple="false"
    :show-file-list="false"
    accept="csv, application/vnd.ms-excel, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
    :http-request="httpRequest"
  >
    <el-button size="small" type="primary" v-if="$btnAuthShow('员工信息导入', $route.meta.button)">员工信息导入</el-button>
  </el-upload>

接口

export function upload(uploadData){
  return request({
    url: '/ysh/lzYshAccountMemberInfo/upload',
    method: 'post',
    data: uploadData,
    headers: {
      'Content-Type': 'application/x-www-form-urlencoded'
    },
  })
}

JS

  httpRequest(e) {
  var formdata = new FormData(); //创建一个表单
  formdata.append("file", e.file);
  formdata.append(
    "companyId",
    JSON.parse(sessionStorage.getItem("companyId"))
  );
  upload(formdata).then((res) => {
    console.log(res);
    this.tableData = res.data.result_info;
    this.messageLeadResult = true;
    this.searchTable();
  });
 }

将Excel表格数据导入到table表格并且自定义数据更改信息

<script>
      //前端读取xlsx文件信息并展示在表格中
      if (!file) {
        // 没有文件
        return false;
      } else if (!/\.(xls|xlsx)$/.test(file.name.toLowerCase())) {
        // 判断是否excel格式
        that.$message.error("上传格式不正确，请上传xls或者xlsx格式");
        return false;
      }
      const fileReader = new FileReader();
      fileReader.onload = (ev) => {
        try {
          const data = ev.target.result;
          const workbook = XLSX.read(data, {
            type: "binary", // 以字符编码的方式解析
          });

          let startData = workbook.Sheets;
          let sheetList = workbook.SheetNames;
          //存放json数组格式的表格数据
          sheetList.forEach((item) => {
            let worksheet = workbook.Sheets[item];
            let json = XLSX.utils.sheet_to_json(workbook.Sheets[item]);
            this.resultJson = json;
          });
          console.log(this.resultJson);
          var keyMap = {
            "姓名": "memberName",
            "手机号": "userMobile",
            "身份证号": "memberCardNo",
            "失败原因": "errMsg",
          };
          var a = [];
          for (var item of this.resultJson) {
            var b = {};
            for (var key in item) {
              if (keyMap[key] === "姓名") {
                b[keyMap[key]] = item[key].split("*");
                b["width"] = b[keyMap[key]][0];
                b["height"] = b[keyMap[key]][1];
              }
              if (keyMap[key] === "手机号") {
                b[keyMap[key]] = item[key].split("-");
                b["begin_time"] = b[keyMap[key]][0];
                b["end_time"] = b[keyMap[key]][1];
              }
              b[keyMap[key]] = item[key];
            }
            a.push(b);
          }
          this.tableData = a;
          console.log(this.tableData)
          this.messageLeadResult = true
        } catch (e) {
          console.log("出错了");
          return false;
        }
      };
      fileReader.readAsBinaryString(file);
</script>