Vue：xlsx實作Excel檔案的導入導出

依賴

npm i file-saver xlsx -S      

引入element-ui

import Vue from 'vue'
import App from './App.vue'
import ElementUI from 'element-ui';
import 'element-ui/lib/theme-chalk/index.css';

Vue.use(ElementUI);

new Vue({
  el: '#app',
  render: h => h(App)
})      

App.

vue

<template>
  <div class="container">
    <div style="display:flex;">
      <el-upload
        ref="upload"
        action="action"
        accept="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
        :show-file-list="false"
        :on-change="onUploadChange"
      >
        <el-button
          size="small"
          type="primary"
        >點選上傳</el-button>
      </el-upload>

      <el-button
        size="small"
        @click="handleDownload"
      >點選下載下傳</el-button>

    </div>

    <el-table
      :data="tableData"
      border
      style="width: 100%"
    >
      <el-table-column
        v-for="key in Object.keys(tableData[0]|| {})"
        :prop="key"
        :label="key"
      >
      </el-table-column>
    </el-table>
  </div>
</template>

<script>
import { readExcelToJson, saveJsonToExcel } from './utils.js';

export default {
  data() {
    return {
      file: null,
      tableData: [],
    };
  },

  methods: {
    // 讀取檔案為json資料
    onUploadChange(file) {
      console.log(file);
      this.file = file;
      readExcelToJson(file).then((res) => {
        this.tableData = res;
      });
    },

    handleDownload() {
      saveJsonToExcel(this.tableData, this.file.name);
    },
  },
};
</script>

<style lang="scss">
body {
  background: #f4f4f4;
  padding: 0;
  margin: 0;
}
.container {
  width: 1024px;
  min-height: 100vh;
  margin: 0 auto;
  padding: 20px;
  background: #fff;
}
</style>
      

工具類檔案 utils.js

import XLSX from "xlsx";
import FileSaver from 'file-saver';

/**
 * 異步讀取Excel檔案的sheet表為json資料
 * 不支援合并單元格
 * @param {File對象} file
 */
export function readExcelToJson(file) {
  return new Promise((resolve, reject) => {
    const reader = new FileReader();

    reader.onload = (e) => {
      let data = new Uint8Array(e.target.result);
      let workbook = XLSX.read(data, { type: "array" });
      //  console.log("workbook: ", workbook);

      //将Excel 第一個sheet内容轉為json格式
      let worksheet = workbook.Sheets[workbook.SheetNames[0]];
      let json = XLSX.utils.sheet_to_json(worksheet);
      //   console.log("jsonExcel:", jsonExcel);
      resolve(json);
    };

    reader.readAsArrayBuffer(file.raw);
  });
}

/**
 * 儲存json為Excel檔案
 * @param {*} data 
 * @param {*} filename  檔案名字尾為.xlsx
 */
export function saveJsonToExcel(data, filename) {
  let sheet = XLSX.utils.json_to_sheet(data);

  let workbook = {
    SheetNames: ["sheet1"],
    Sheets: {
      sheet1: sheet,
    },
  };

  let wbout = XLSX.write(workbook, {
    bookType: "xlsx",
    bookSST: true,
    type: "array",
  });

  FileSaver.saveAs(
    new Blob([wbout], { type: "application/octet-stream" }),
    filename
  );
}
      

參考文章

如何使用JavaScript實作純前端讀取和導出excel檔案 前端導出Excel和下載下傳後端Excel以及前端處理Excel