一、导入Excel文件

上传excel文件,并获取表格数据;以下有两种方式:①js方式,写在子组件中,引入本页。②结合element-ui中的el-upload,在本页中编写

方法一:js方法

编辑一个子组件,然后只需要在父组件中引入即可使用;

父组件
<template>
    <div class="upload-excels">
        <h4>①js方式,写在子组件中,引入本页。</h4>
        <excel1 v-on:getResult="updateExcel"></excel1>
    </div>
</template>

<script>
import excel1 from "@/view/components/upload-excel";
  export default {
    name:'',
    data () {
      return {};
    },
    components: {excel1},
    mounted() {},
    methods: {
        // 导入表格--start
        updateExcel(e) {
          console.log(e); // 打印出的为导入Excel表格的json数据
        },
        // 导入表格--end
    }
  }
</script>
子组件
<template>
  <div>
    <span>
      <input
        class="input-file"
        type="file"
        @change="exportData"
        accept=".csv, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet, application/vnd.ms-excel"
      />
      <!-- <button @click="btnClick">导入EXCEL</button> -->
    </span>
  </div>
</template>

<script>
export default {
  name: "upload-excel",
  data() {
    return {};
  },
  components: {},
  mounted() {},
  methods: {
    btnClick() {
      document.querySelector(".input-file").click();
      //
    },
    exportData(event) {
      if (!event.currentTarget.files.length) {
        return;
      }
      const that = this;
      // 拿取文件对象
      var f = event.currentTarget.files[0];
      // 用FileReader来读取
      var reader = new FileReader();
      // 重写FileReader上的readAsBinaryString方法
      FileReader.prototype.readAsBinaryString = function(f) {
        var binary = "";
        var wb; // 读取完成的数据
        var outdata; // 你需要的数据
        var reader = new FileReader();
        reader.onload = function(e) {
          // 读取成Uint8Array,再转换为Unicode编码(Unicode占两个字节)
          var bytes = new Uint8Array(reader.result);
          var length = bytes.byteLength;
          for (var i = 0; i < length; i++) {
            binary += String.fromCharCode(bytes[i]);
          }
          // 接下来就是xlsx了,具体可看api
          var XLSX = require("xlsx");
          wb = XLSX.read(binary, {
            type: "binary"
          });
          outdata = XLSX.utils.sheet_to_json(wb.Sheets[wb.SheetNames[0]]);
          // 自定义方法向父组件传递数据
          // console.log('outdata = ' + JSON.stringify(outdata))

          console.log(outdata);
          that.$emit("getResult", outdata);
        };
        reader.readAsArrayBuffer(f);
      };
      reader.readAsBinaryString(f);
    }
  },
  watch: {}
};
</script>
<style lang='' scoped>
</style>

方法二:与element-ui中的upload相结合

代码如下:

<template>
  <div class="upload-excels">
      <h3>上传excel文件,并获取表格数据;以下有两种方式:①js方式,写在子组件中,引入本页。②结合element-ui中的el-upload,在本页中编写</h3><br><br>
      <h4>②结合element-ui中的el-upload,在本页中编写</h4>
      <div class="excel2">
        <el-upload
          class="upload-demo"
          action
          :on-change="handleChange"
          :show-file-list="true"
          :on-remove="handleRemove"
          :file-list="fileListUpload"
          :limit="1"
          accept="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet, application/vnd.ms-excel"
          :auto-upload="false"
        >
          <el-button size="small" type="primary">选择文件</el-button>
          <div slot="tip" class="el-upload__tip">注:只 能 上 传 xlsx / xls 文 件</div>
        </el-upload>
      </div>
    </div>
</template>

<script>
  export default {
    name:'',
    data () {
      return {
        fileListUpload:[],
        tableData2: []
      };
    },
    components: {},
    mounted() {},
    methods: {
        // 导入表格-el-upload--start
        // excel表上传
        handleChange(file, fileList) {
          this.fileTemp = file.raw;
          // 判断上传文件格式
          if (this.fileTemp) {
            if (
              this.fileTemp.type ==
                "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet" ||
              this.fileTemp.type == "application/vnd.ms-excel"
            ) {
              this.importfxx(this.fileTemp);
            } else {
              this.$message({
                type: "warning",
                message: "附件格式错误,请删除后重新上传!"
              });
            }
          } else {
            this.$message({
              type: "warning",
              message: "请上传附件!"
            });
          }
        },
        // 移除excel表
        handleRemove(file, fileList) {
          this.fileTemp = null;
        },
        importfxx(obj) {
          let _this = this;
          // 通过DOM取文件数据
          this.file = obj;
          var rABS = false; //是否将文件读取为二进制字符串
          var f = this.file;
          var reader = new FileReader();
          FileReader.prototype.readAsBinaryString = function(f) {
            var binary = "";
            var rABS = false; //是否将文件读取为二进制字符串
            var pt = this;
            var wb; //读取完成的数据
            var outdata;
            var reader = new FileReader();
            reader.onload = function(e) {
              var bytes = new Uint8Array(reader.result);
              var length = bytes.byteLength;
              for (var i = 0; i < length; i++) {
                binary += String.fromCharCode(bytes[i]);
              }
              var XLSX = require("xlsx");
              if (rABS) {
                wb = XLSX.read(btoa(fixdata(binary)), {
                  //手动转化
                  type: "base64"
                });
              } else {
                wb = XLSX.read(binary, {
                  type: "binary"
                });
              }
              outdata = XLSX.utils.sheet_to_json(wb.Sheets[wb.SheetNames[0]]); //outdata就是读取excel内容之后输出的东西
              console.log(outdata);
              this.da = [...outdata];
              let arr = [];
              this.da.map(v => {
                let obj = {};
                obj.ip = v["ip"]; //ip是表的标题
                arr.push(obj);
              });
    
              //return arr
              // 把读取的excel表格中的内容放进tableData2中(这里要改成自己的表的名字)
              _this.tableData2 = _this.tableData2.concat(arr);
            };
            reader.readAsArrayBuffer(f);
          };
          if (rABS) {
            reader.readAsArrayBuffer(f);
          } else {
            reader.readAsBinaryString(f);
          }
        },
    // 导入表格-el-upload--end
    },
    watch: {}
  }

</script>
<style lang='scss' scoped>
.upload-excels{
    width:100%;
    height:300px;
    .excel2{
      width:500px;
      height:100px;
      border:1px solid red;
    }
  }
</style>

二、导出Excel表格

1.安装依赖
cnpm install -S file-saver xlsx

cnpm install -D script-loader
2.依赖文件

需要安装依赖文件 Blob.jsExport2Excel.js ,在src中创建文件夹 vendor 把这两个文件放入其中,如下:(Blob.js与Export2Excel.js两个文件在文章最后附有)

3.代码如下
<template>
    <button @click="getExcel(userList)">导出表格</button>
    <button @click="outExe">导出模板</button>
</template>
<script>
    export default {
        name: "",
        data() {
            return {
                userList: [{
                    userNum: 100001, //客户编号
                    userName: '张琳梵', // 客户名字
                    phone: 158xxxxxxxxx, // 电话
                    job: '餐饮', // 行业
                    address: '河南省郑州市中原区xxx号', // 地址
                    from: '手动录入', //来源
                    salesman: '李明', // 销售人员
                    createTime: '2018-09-25' // 入库时间
                },
                {
                    userNum: 100005,
                    userName: '张琳梵',
                    phone: 158xxxxxxxxxx,
                    job: '百货',
                    address: '河南省郑州市高新区xxx号',
                    from: '手动录入',
                    salesman: '李明',
                    createTime: '2018-09-25'
                }]
            };
        },
        components: {},
        mounted() {},
        methods:{
            // 导出模板
            outExe() {
              require.ensure([], () => {
                const { export_json_to_excel } = require("@/vendor/Export2Excel"); //引入文件
                const tHeader = ["客户编号","客户名字","电话","行业","地址","来源","销售人员","入库时间"]; //将对应的属性名转换成中文
                const data = [];
                export_json_to_excel(tHeader, data, "模板");
              });
            },
            // 导出表格
            getExcel(res) {
              require.ensure([], () => {
                const { export_json_to_excel } = require("@/vendor/Export2Excel.js");
                const tHeader = ["客户编号","客户名字","电话","行业","地址","来源","销售人员","入库时间"];
                const filterVal = ["userNum","userName","phone","job","address","from","salesman","createTime"];
                const list = res;
                const data = this.formatJson(filterVal, list);
                export_json_to_excel(tHeader, data, "导出表格名称");
              });
            },
        
            formatJson(filterVal, jsonData) {
              return jsonData.map(v => filterVal.map(j => v[j]));
            },
        }
    }
    
</script>

Blob.js与Export2Excel.js在下方:

见本人笔记:《vue-导出Excel,导入excel文件并获取Excel内容》

Logo

前往低代码交流专区

更多推荐