Vue + Element 實現匯入匯出Excel

一隻程式猿小白發表於2020-04-13
VueExcel

1、首先搭建Vue 專案（具體可參考以前文章，不再詳述：https://blog.csdn.net/qq_42540989/article/details/89853923）

2、引入Element（你可以引入整個 Element，或是根據需要僅引入部分元件。我們先介紹如何引入完整的 Element。）

//在main.js中引用

import ElementUi from 'element-ui'

import 'element-ui/lib/theme-chalk/index.css';

Vue.use(ElementUi)

3、 在components 資料夾中新建一個Vue檔案

// excal.vue

<template>
  <div class="hello">
    <h1>{{ msg }}</h1>
   <el-row>
  <el-button>預設按鈕</el-button>
  <el-button type="primary">主要按鈕</el-button>
  <el-button type="success">成功按鈕</el-button>
</el-row>
  </div>
</template>

<script>
  export default {
    data() {
      return {
        imageUrl: '',
        msg:'hello,Element'
      };
    },
    methods: {
    }
  }
</script>
<style scoped>

</style>

// index.js

import Vue from 'vue'
import Router from 'vue-router'
import HelloWorld from '@/components/HelloWorld'
import excal from '@/components/excal'
Vue.use(Router)

export default new Router({
  routes: [
    {
      path: '/',
      name: 'HelloWorld',
      component: HelloWorld
    },
    {
      path: '/enter/',
      name: 'excal',
      component: excal
    }
  ]
})

3、 執行專案

// 執行

npm run dev

4、訪問  http://localhost:8080/#/enter  檢視  element-ui 是否成功引入

 

5、匯入匯出 - -  開始引入工具庫

//  file-saver      xlsx       script-loader

cnpm install -S file-saver xlsx

cnpm install -D script-loader

 6、匯入程式碼: https://github.com/MrBaiLiJie/importExcal

// https://github.com/MrBaiLiJie/importExcal

<template>
  <div class="hello">
    <h1>{{ msg }}</h1>
    <!-- <el-row>
  <el-button>預設按鈕</el-button>
  <el-button type="primary">主要按鈕</el-button>
  <el-button type="success">成功按鈕</el-button>
    </el-row>-->
    <el-upload
      class="upload-demo"
      action
      :on-change="handleChange"
      :on-exceed="handleExceed"
      :on-remove="handleRemove"
      :before-remove="beforeRemove"
      :file-list="fileListUpload"
      :limit="limitUpload"
      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>
</template>

<script>
export default {
  data() {
    return {
      imageUrl: "",
      msg: "hello,Element",
      limitUpload: 1,
      fileTemp: "",
      file:"",
      fileListUpload: []
    };
  },
  methods: {
    handleChange(file,fileList){
      // console.log(file)
      this.fileTemp = file.raw;
      if(this.fileTemp){
        // console.log(this.fileTemp.type)
        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:"附件格式錯誤，請刪除後重新上傳!"
          });
        }
      }
      
    },
    importfxx(obj) {
      console.log(obj)
      let _this = this;
      // 通過DOM取檔案資料
      this.file = obj;
      var rABS = false; //是否將檔案讀取為二進位制字串
      var f = this.file;
      var reader = new FileReader();
      //if (!FileReader.prototype.readAsBinaryString) {
      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就是你想要的東西
          this.da = [...outdata];
          let arr = [];
          this.da.map(v => {
            let obj = {};
            obj.code = v["裝置ID"];
            obj.type = v["裝置型號"];
            arr.push(obj);
          });
          return arr;
        };
        reader.readAsArrayBuffer(f);
      };

      if (rABS) {
        reader.readAsArrayBuffer(f);
      } else {
        reader.readAsBinaryString(f);
      }
    },
    beforeRemove(file, fileList) {
      return this.$confirm(`確定移除 ${file.name}？`);
    },
    handleRemove(file, fileList) {
      // console.log(file)
      this.fileTemp = null;
    },
    handleExceed(files, fileList) {
      this.$message.warning(
        `當前限制選擇1個檔案，本次選擇了 ${
          files.length
        } 個檔案，共選擇了 ${files.length + fileList.length} 個檔案`
      );
    },
    
  }
};
</script>
<style scoped>
</style>

6、 補充

xls 是一個特有的二進位制格式，其核心結構是複合文件型別的結構，而 xlsx 的核心結構是 XML 型別的結構，採用的是基於 XML 的壓縮方式，使其佔用的空間更小。xlsx 中最後一個 x 的意義就在於此。

 

7、匯出步驟

// 步驟

1、引入js檔案

在src資料夾下新建excal資料夾，引入兩個js檔案     Blob.js    Export2Excel.js

// js檔案 已上傳至github

地址：https://github.com/MrBaiLiJie/importExcal/tree/master/src/excal

2、在main.js引入

import Blob from './excal/Blob.js'
import Export2Excel from './excal/Export2Excel.js'

3、開啟Export2Excel.js

require('script-loader!file-saver');
require('script-loader!./Blob');
require('script-loader!xlsx/dist/xlsx.core.min');

這幾個檔案不支援import引入，所以需要script-loader來將他們掛載到全域性環境下。

8、匯出程式碼：https://github.com/MrBaiLiJie/importExcal

// https://github.com/MrBaiLiJie/importExcal

<template>
  <div class="hello">
    <h1>{{ msg }}</h1>
    <!-- <el-row>
  <el-button>預設按鈕</el-button>
  <el-button type="primary">主要按鈕</el-button>
  <el-button type="success">成功按鈕</el-button>
    </el-row>-->
    <!-- 匯出 -->
    <el-button @click="outExe">匯出</el-button>
  </div>
</template>

<script>
export default {
  data() {
    return {
      imageUrl: "",
      msg: "hello,Element",
      limitUpload: 1,
      fileTemp: "",
      file: "",
      fileListUpload: [],
      excelData:[],
      dataList:[{userId:1,name:'小白',age:'18',status:"上學"},{userId:2,name:'小黑',age:'22',status:"待業"},{userId:3,name:'小紅',age:'28',status:"就業"}]
    };
  },
  methods: {
    // 匯出
    outExe() {
      this.$confirm("此操作將匯出excel檔案, 是否繼續?", "提示", {
        confirmButtonText: "確定",
        cancelButtonText: "取消",
        type: "warning"
      })
        .then(() => {
          this.excelData = this.dataList; //你要匯出的資料list。
          this.export2Excel();
        })
        .catch(() => {});
    },
    export2Excel() {
      var that = this;
      require.ensure([], () => {
        const { export_json_to_excel } = require("../excal/Export2Excel"); //這裡必須使用絕對路徑，根據自己的命名和路徑
        const tHeader = [
          "userId",
          "name",
          "age",
          "status",
        ]; // 匯出的表頭名
        const filterVal = [
          "userId",
          "name",
          "age",
          "status",
        ]; // 匯出的表頭欄位名
        const list = that.excelData;
        // that.excelData為傳入的資料
        const data = that.formatJson(filterVal, list);
        export_json_to_excel(tHeader, data, `測試匯出excel`); // 匯出的表格名稱，根據需要自己命名
        // tHeader為匯出Excel表頭名稱，`xxxxxx`即為匯出Excel名稱
      });
    },
    formatJson(filterVal, jsonData) {
      return jsonData.map(v => filterVal.map(j => v[j]));
    }
  }
};
</script>
<style scoped>
</style>

9、參考文獻

element-ui官方文件：https://element.eleme.io/#/zh-CN/component/upload

https://segmentfault.com/a/1190000018993619#item-3-5

相關文章