Vue中級指南-01 如何在Vue專案中匯出Excel

番茄炒蛋少放糖發表於2019-04-18

Excel 匯出

Excel 的匯入匯出都是依賴於js-xlsx來實現的。

在 js-xlsx的基礎上又封裝了Export2Excel.js來方便匯出資料。

使用

由於 Export2Excel不僅依賴js-xlsx還依賴file-saver和script-loader。

所以你先需要安裝如下命令：

npm install xlsx file-saver -S
npm install script-loader -S -D
複製程式碼

由於js-xlsx體積還是很大的，匯出功能也不是一個非常常用的功能，所以使用的時候建議使用懶載入。使用方法如下：

import('@/vendor/Export2Excel').then(excel => {
  excel.export_json_to_excel({
    header: tHeader, //表頭 必填
    data, //具體資料 必填
    filename: 'excel-list', //非必填, 匯出檔案的名字
    autoWidth: true, //非必填, 匯出檔案的排列方式
    bookType: 'xlsx' //非必填, 匯出檔案的格式
  })
})
複製程式碼

注意

在v3.9.1+以後的版本中移除了對 Bolb 的相容性程式碼，如果你還需要相容很低版本的瀏覽器可以手動引入blob-polyfill進行相容。

引數

引數	    說明	          型別	    可選值	           預設值
header	    匯出資料的表頭	  Array	    /	                   []
data	    匯出的具體資料	  Array	    /	                   []]
filename    匯出檔名             String    /                      excel-list
autoWidth   單元格是否要自適應寬度  Boolean   true / false           true
bookType    匯出檔案型別           String    xlsx, csv, txt, more   xlsx
複製程式碼

專案實戰

使用腳手架搭建出基本專案雛形,這時候在src目錄下新建一個vendor(檔名自己定義)資料夾,新建一個Export2Excel.js檔案,這個檔案裡面在js-xlsx的基礎上又封裝了Export2Excel.js來方便匯出資料。

目錄如下
Export2Excel.js程式碼如下

require('script-loader!file-saver');
import XLSX from 'xlsx'

function generateArray(table) {
  var out = [];
  var rows = table.querySelectorAll('tr');
  var ranges = [];
  for (var R = 0; R < rows.length; ++R) {
    var outRow = [];
    var row = rows[R];
    var columns = row.querySelectorAll('td');
    for (var C = 0; C < columns.length; ++C) {
      var cell = columns[C];
      var colspan = cell.getAttribute('colspan');
      var rowspan = cell.getAttribute('rowspan');
      var cellValue = cell.innerText;
      if (cellValue !== "" && cellValue == +cellValue) cellValue = +cellValue;

      //Skip ranges
      ranges.forEach(function (range) {
        if (R >= range.s.r && R <= range.e.r && outRow.length >= range.s.c && outRow.length <= range.e.c) {
          for (var i = 0; i <= range.e.c - range.s.c; ++i) outRow.push(null);
        }
      });

      //Handle Row Span
      if (rowspan || colspan) {
        rowspan = rowspan || 1;
        colspan = colspan || 1;
        ranges.push({
          s: {
            r: R,
            c: outRow.length
          },
          e: {
            r: R + rowspan - 1,
            c: outRow.length + colspan - 1
          }
        });
      };

      //Handle Value
      outRow.push(cellValue !== "" ? cellValue : null);

      //Handle Colspan
      if (colspan)
        for (var k = 0; k < colspan - 1; ++k) outRow.push(null);
    }
    out.push(outRow);
  }
  return [out, ranges];
};

function datenum(v, date1904) {
  if (date1904) v += 1462;
  var epoch = Date.parse(v);
  return (epoch - new Date(Date.UTC(1899, 11, 30))) / (24 * 60 * 60 * 1000);
}

function sheet_from_array_of_arrays(data, opts) {
  var ws = {};
  var range = {
    s: {
      c: 10000000,
      r: 10000000
    },
    e: {
      c: 0,
      r: 0
    }
  };
  for (var R = 0; R != data.length; ++R) {
    for (var C = 0; C != data[R].length; ++C) {
      if (range.s.r > R) range.s.r = R;
      if (range.s.c > C) range.s.c = C;
      if (range.e.r < R) range.e.r = R;
      if (range.e.c < C) range.e.c = C;
      var cell = {
        v: data[R][C]
      };
      if (cell.v == null) continue;
      var cell_ref = XLSX.utils.encode_cell({
        c: C,
        r: R
      });

      if (typeof cell.v === 'number') cell.t = 'n';
      else if (typeof cell.v === 'boolean') cell.t = 'b';
      else if (cell.v instanceof Date) {
        cell.t = 'n';
        cell.z = XLSX.SSF._table[14];
        cell.v = datenum(cell.v);
      } else cell.t = 's';

      ws[cell_ref] = cell;
    }
  }
  if (range.s.c < 10000000) ws['!ref'] = XLSX.utils.encode_range(range);
  return ws;
}

function Workbook() {
  if (!(this instanceof Workbook)) return new Workbook();
  this.SheetNames = [];
  this.Sheets = {};
}

function s2ab(s) {
  var buf = new ArrayBuffer(s.length);
  var view = new Uint8Array(buf);
  for (var i = 0; i != s.length; ++i) view[i] = s.charCodeAt(i) & 0xFF;
  return buf;
}

export function export_table_to_excel(id) {
  var theTable = document.getElementById(id);
  var oo = generateArray(theTable);
  var ranges = oo[1];

  /* original data */
  var data = oo[0];
  var ws_name = "SheetJS";

  var wb = new Workbook(),
    ws = sheet_from_array_of_arrays(data);

  /* add ranges to worksheet */
  // ws['!cols'] = ['apple', 'banan'];
  ws['!merges'] = ranges;

  /* add worksheet to workbook */
  wb.SheetNames.push(ws_name);
  wb.Sheets[ws_name] = ws;

  var wbout = XLSX.write(wb, {
    bookType: 'xlsx',
    bookSST: false,
    type: 'binary'
  });

  saveAs(new Blob([s2ab(wbout)], {
    type: "application/octet-stream"
  }), "test.xlsx")
}

export function export_json_to_excel({
  multiHeader = [],
  header,
  data,
  filename,
  merges = [],
  autoWidth = true,
  bookType = 'xlsx'
} = {}) {
  /* original data */
  filename = filename || 'excel-list'
  data = [...data]
  data.unshift(header);

  for (let i = multiHeader.length - 1; i > -1; i--) {
    data.unshift(multiHeader[i])
  }

  var ws_name = "SheetJS";
  var wb = new Workbook(),
    ws = sheet_from_array_of_arrays(data);

  if (merges.length > 0) {
    if (!ws['!merges']) ws['!merges'] = [];
    merges.forEach(item => {
      ws['!merges'].push(XLSX.utils.decode_range(item))
    })
  }

  if (autoWidth) {
    /*設定worksheet每列的最大寬度*/
    const colWidth = data.map(row => row.map(val => {
      /*先判斷是否為null/undefined*/
      if (val == null) {
        return {
          'wch': 10
        };
      }
      /*再判斷是否為中文*/
      else if (val.toString().charCodeAt(0) > 255) {
        return {
          'wch': val.toString().length * 2
        };
      } else {
        return {
          'wch': val.toString().length
        };
      }
    }))
    /*以第一行為初始值*/
    let result = colWidth[0];
    for (let i = 1; i < colWidth.length; i++) {
      for (let j = 0; j < colWidth[i].length; j++) {
        if (result[j]['wch'] < colWidth[i][j]['wch']) {
          result[j]['wch'] = colWidth[i][j]['wch'];
        }
      }
    }
    ws['!cols'] = result;
  }

  /* add worksheet to workbook */
  wb.SheetNames.push(ws_name);
  wb.Sheets[ws_name] = ws;

  var wbout = XLSX.write(wb, {
    bookType: bookType,
    bookSST: false,
    type: 'binary'
  });
  saveAs(new Blob([s2ab(wbout)], {
    type: "application/octet-stream"
  }), `${filename}.${bookType}`);
}
複製程式碼

新建一個exportExcel.vue模板用於匯出Excel表格,使用程式碼如下

<template>
  <div class="exportExcel">
    <div class="excel-header">
      <!--匯出檔名稱-->
      <div class="filename">
        <label class="radio-label" style="padding-left:0;">Filename:</label>
        <el-input
          v-model="filename"
          placeholder="請輸入匯出檔名"
          style="width:340px;"
        prefix-icon="el-icon-document" />
      </div>
      <!--設定表格匯出的寬度是否自動-->
      <div class="autoWidth">
        <label class="radio-label">Cell Auto-Width:</label>
        <el-radio-group v-model="autoWidth">
          <el-radio :label="true" border>True</el-radio>
          <el-radio :label="false" border>False</el-radio>
        </el-radio-group>
      </div>
      <!--匯出檔案字尾型別-->
      <div class="bookType">
        <label class="radio-label">Book Type:</label>
        <el-select v-model="bookType" style="width:120px;">
          <el-option v-for="item in options" :key="item" :label="item" :value="item"/>
        </el-select>
      </div>
      <!--匯出檔案-->
      <div class="download">
        <el-button
          :loading="downloadLoading"
          type="primary"
          icon="document"
        @click="handleDownload">export Excel</el-button>
      </div>
    </div>

    <el-table
      v-loading="listLoading"
      :data="list"
      element-loading-text="拼命載入中"
      border
      fit
      highlight-current-row
      height="390px"
    >
      <el-table-column align="center" label="序號" width="95">
        <template slot-scope="scope">{{ scope.$index }}</template>
      </el-table-column>
      <el-table-column label="訂單號" width="230">
        <template slot-scope="scope">{{ scope.row.title }}</template>
      </el-table-column>
      <el-table-column label="菜品" align="center">
        <template slot-scope="scope">{{ scope.row.foods }}</template>
      </el-table-column>
      <el-table-column label="收銀員" width="110" align="center">
        <template slot-scope="scope">
          <el-tag>{{ scope.row.author }}</el-tag>
        </template>
      </el-table-column>
      <el-table-column label="金額" width="115" align="center">
        <template slot-scope="scope">{{ scope.row.pageviews }}</template>
      </el-table-column>
      <el-table-column align="center" label="時間" width="220">
        <template slot-scope="scope">
          <i class="el-icon-time"/>
          <span>{{ scope.row.timestamp | parseTime('{y}-{m}-{d} {h}:{i}') }}</span>
        </template>
      </el-table-column>
    </el-table>
  </div>
</template>

export default {
  name: "exportExcelDialog",
  data() {
    return {
      // 列表內容
      list: null,
      // loding視窗狀態
      listLoading: true,
      // 下載loding視窗狀態
      downloadLoading: false,
      // 匯出檔名稱
      filename: "",
      // 匯出表格寬度是否auto
      autoWidth: true,
      // 匯出檔案格式
      bookType: "xlsx",
      // 預設匯出檔案字尾型別
      options: ["xlsx", "csv", "txt"]
    };
  },
  methods: {
    // 匯出Excel表格
    handleDownload() {
      this.downloadLoading = true;
      // 懶載入該用法
      import("@/vendor/Export2Excel").then(excel => {
        // 設定匯出表格的頭部
        const tHeader = ["序號", "訂單號", "菜品", "收銀員", "金額", "時間"];
        // 設定要匯出的屬性
        const filterVal = [
          "id",
          "title",
          "foods",
          "author",
          "pageviews",
          "display_time"
        ];
        // 獲取當前展示的表格資料
        const list = this.list;
        // 將要匯出的資料進行一個過濾
        const data = this.formatJson(filterVal, list);
        // 呼叫我們封裝好的方法進行匯出Excel
        excel.export_json_to_excel({
          // 匯出的頭部
          header: tHeader,
          // 匯出的內容
          data,
          // 匯出的檔名稱
          filename: this.filename,
          // 匯出的表格寬度是否自動
          autoWidth: this.autoWidth,
          // 匯出檔案的字尾型別
          bookType: this.bookType
        });
        this.downloadLoading = false;
      });
    },
    // 對要匯出的內容進行過濾
    formatJson(filterVal, jsonData) {
      return jsonData.map(v =>
        filterVal.map(j => {
          if (j === "timestamp") {
            return this.parseTime(v[j]);
          } else {
            return v[j];
          }
        })
      );
    },
    // 過濾時間
    parseTime(time, cFormat) {
      if (arguments.length === 0) {
        return null;
      }
      const format = cFormat || "{y}-{m}-{d} {h}:{i}:{s}";
      let date;
      if (typeof time === "object") {
        date = time;
      } else {
        if (typeof time === "string" && /^[0-9]+$/.test(time)) {
          time = parseInt(time);
        }
        if (typeof time === "number" && time.toString().length === 10) {
          time = time * 1000;
        }
        date = new Date(time);
      }
      const formatObj = {
        y: date.getFullYear(),
        m: date.getMonth() + 1,
        d: date.getDate(),
        h: date.getHours(),
        i: date.getMinutes(),
        s: date.getSeconds(),
        a: date.getDay()
      };
      const timeStr = format.replace(/{(y|m|d|h|i|s|a)+}/g, (result, key) => {
        let value = formatObj[key];
        // Note: getDay() returns 0 on Sunday
        if (key === "a") {
          return ["日", "一", "二", "三", "四", "五", "六"][value];
        }
        if (result.length > 0 && value < 10) {
          value = "0" + value;
        }
        return value || 0;
      });
      return timeStr;
    }
  },
  mounted() {
    // 模擬獲取資料
    setTimeout(() => {
      this.list = [
        {
          timestamp: 1432179778664,
          author: "Charles",
          comment_disabled: true,
          content_short: "mock data",
          display_time: "1994-05-25 23:37:25",
          foods: "雞翅、蘿蔔、牛肉、紅燒大閘蟹、紅燒雞翅",
          id: 1,
          image_uri:
            "https://wpimg.wallstcn.com/e4558086-631c-425c-9430-56ffb46e70b3",
          importance: 3,
          pageviews: 2864,
          platforms: ["a-platform"],
          reviewer: "Sandra",
          status: "published",
          title: "O20190407135010000000001",
          type: "CN"
        },
        {
          timestamp: 1432179778664,
          author: "Charles",
          comment_disabled: true,
          content_short: "mock data",
          display_time: "1994-05-25 23:37:25",
          foods: "雞翅、蘿蔔、牛肉、紅燒大閘蟹、紅燒雞翅",
          id: 1,
          image_uri:
            "https://wpimg.wallstcn.com/e4558086-631c-425c-9430-56ffb46e70b3",
          importance: 3,
          pageviews: 2864,
          platforms: ["a-platform"],
          reviewer: "Sandra",
          status: "published",
          title: "O20190407135010000000001",
          type: "CN"
        },
        {
          timestamp: 1432179778664,
          author: "Charles",
          comment_disabled: true,
          content_short: "mock data",
          display_time: "1994-05-25 23:37:25",
          foods: "雞翅、蘿蔔、牛肉、紅燒大閘蟹、紅燒雞翅",
          id: 1,
          image_uri:
            "https://wpimg.wallstcn.com/e4558086-631c-425c-9430-56ffb46e70b3",
          importance: 3,
          pageviews: 2864,
          platforms: ["a-platform"],
          reviewer: "Sandra",
          status: "published",
          title: "O20190407135010000000001",
          type: "CN"
        },
        {
          timestamp: 1432179778664,
          author: "Charles",
          comment_disabled: true,
          content_short: "mock data",
          display_time: "1994-05-25 23:37:25",
          foods: "雞翅、蘿蔔、牛肉、紅燒大閘蟹、紅燒雞翅",
          id: 1,
          image_uri:
            "https://wpimg.wallstcn.com/e4558086-631c-425c-9430-56ffb46e70b3",
          importance: 3,
          pageviews: 2864,
          platforms: ["a-platform"],
          reviewer: "Sandra",
          status: "published",
          title: "O20190407135010000000001",
          type: "CN"
        },
        {
          timestamp: 1432179778664,
          author: "Charles",
          comment_disabled: true,
          content_short: "mock data",
          display_time: "1994-05-25 23:37:25",
          foods: "雞翅、蘿蔔、牛肉、紅燒大閘蟹、紅燒雞翅",
          id: 1,
          image_uri:
            "https://wpimg.wallstcn.com/e4558086-631c-425c-9430-56ffb46e70b3",
          importance: 3,
          pageviews: 2864,
          platforms: ["a-platform"],
          reviewer: "Sandra",
          status: "published",
          title: "O20190407135010000000001",
          type: "CN"
        },
        {
          timestamp: 1432179778664,
          author: "Charles",
          comment_disabled: true,
          content_short: "mock data",
          display_time: "1994-05-25 23:37:25",
          foods: "雞翅、蘿蔔、牛肉、紅燒大閘蟹、紅燒雞翅",
          id: 1,
          image_uri:
            "https://wpimg.wallstcn.com/e4558086-631c-425c-9430-56ffb46e70b3",
          importance: 3,
          pageviews: 2864,
          platforms: ["a-platform"],
          reviewer: "Sandra",
          status: "published",
          title: "O20190407135010000000001",
          type: "CN"
        },
        {
          timestamp: 1432179778664,
          author: "Charles",
          comment_disabled: true,
          content_short: "mock data",
          display_time: "1994-05-25 23:37:25",
          foods: "雞翅、蘿蔔、牛肉、紅燒大閘蟹、紅燒雞翅",
          id: 1,
          image_uri:
            "https://wpimg.wallstcn.com/e4558086-631c-425c-9430-56ffb46e70b3",
          importance: 3,
          pageviews: 2864,
          platforms: ["a-platform"],
          reviewer: "Sandra",
          status: "published",
          title: "O20190407135010000000001",
          type: "CN"
        },
        {
          timestamp: 1432179778664,
          author: "Charles",
          comment_disabled: true,
          content_short: "mock data",
          display_time: "1994-05-25 23:37:25",
          foods: "雞翅、蘿蔔、牛肉、紅燒大閘蟹、紅燒雞翅",
          id: 1,
          image_uri:
            "https://wpimg.wallstcn.com/e4558086-631c-425c-9430-56ffb46e70b3",
          importance: 3,
          pageviews: 2864,
          platforms: ["a-platform"],
          reviewer: "Sandra",
          status: "published",
          title: "O20190407135010000000001",
          type: "CN"
        },
        {
          timestamp: 1432179778664,
          author: "Charles",
          comment_disabled: true,
          content_short: "mock data",
          display_time: "1994-05-25 23:37:25",
          foods: "雞翅、蘿蔔、牛肉、紅燒大閘蟹、紅燒雞翅",
          id: 1,
          image_uri:
            "https://wpimg.wallstcn.com/e4558086-631c-425c-9430-56ffb46e70b3",
          importance: 3,
          pageviews: 2864,
          platforms: ["a-platform"],
          reviewer: "Sandra",
          status: "published",
          title: "O20190407135010000000001",
          type: "CN"
        }
      ];
      this.listLoading = false;
    }, 2000);
  },
  filters: {
    // 過濾時間
    parseTime(time, cFormat) {
      if (arguments.length === 0) {
        return null;
      }
      const format = cFormat || "{y}-{m}-{d} {h}:{i}:{s}";
      let date;
      if (typeof time === "object") {
        date = time;
      } else {
        if (typeof time === "string" && /^[0-9]+$/.test(time)) {
          time = parseInt(time);
        }
        if (typeof time === "number" && time.toString().length === 10) {
          time = time * 1000;
        }
        date = new Date(time);
      }
      const formatObj = {
        y: date.getFullYear(),
        m: date.getMonth() + 1,
        d: date.getDate(),
        h: date.getHours(),
        i: date.getMinutes(),
        s: date.getSeconds(),
        a: date.getDay()
      };
      const timeStr = format.replace(/{(y|m|d|h|i|s|a)+}/g, (result, key) => {
        let value = formatObj[key];
        // Note: getDay() returns 0 on Sunday
        if (key === "a") {
          return ["日", "一", "二", "三", "四", "五", "六"][value];
        }
        if (result.length > 0 && value < 10) {
          value = "0" + value;
        }
        return value || 0;
      });
      return timeStr;
    }
  }
}
複製程式碼

效果圖如下

用法都是看GitHub開源專案的和部落格的,自己本身還沒有二次封裝這樣內容的實力,歡迎大佬提出寶貴的意見。

vue 專案中實現列表的匯出excel表格的功能
2020-10-22
VueExcel
Vue匯出Excel
2018-05-03
VueExcel
vue excel匯入匯出
2019-03-07
VueExcel
vue匯出Excel表格
2020-12-28
VueExcel
如何在現有的Vue專案中嵌入 Blazor專案？
2023-01-20
VueBlazor
VUE中使用vue-json-excel超級方便匯出excel表格資料
2020-11-13
VueJSONExcel
vue + element + 匯入、匯出excel表格
2018-09-10
VueExcel
vue + element UI 中 el-table 資料匯出Excel表格
2020-12-30
VueUIExcel
vue+elementUI表格匯出excel
2018-11-16
VueUIExcel
vue將表格匯出為excel
2018-03-18
VueExcel
Vue實現匯出excel表格
2018-03-28
VueExcel
如何在vue專案中優雅的使用SVG
2018-12-03
VueSVG
說說如何在Vue專案中應用TypeScript？
2024-03-16
VueTypeScript
Vue + Element 實現匯入匯出Excel
2020-04-13
VueExcel
vue2.0 + element UI 中 el-table 資料匯出Excel 。
2018-01-30
VueUIExcel
尋找專案中頂級Vue物件 (一)
2018-07-28
Vue物件
升級vue專案中的外掛版本
2021-01-01
Vue
Vue框架下實現匯入匯出Excel、匯出PDF
2021-09-09
Vue框架Excel
abp框架Excel匯出——基於vue
2020-07-29
框架ExcelVue
vue匯出excel資料表格功能
2018-11-17
VueExcel
vue實現前端匯出excel表格
2018-08-01
Vue前端Excel
在vue專案中自定義事件匯流排eventHub
2021-01-03
Vue事件
基於Vue + axios + WebApi + NPOI匯出Excel檔案
2019-06-04
VueiOSWebAPIExcel
【Vue】vue專案目錄介紹 es6的匯入匯出語法 vue專案開發規範 Vue專案編寫步驟
2023-02-20
Vue
如何在Vue專案中引入騰訊驗證碼服務
2018-04-02
Vue
Vue+Element 實現excel的匯入匯出
2020-08-13
VueExcel
Vue匯出資料到Excel電子表格
2020-03-13
VueExcel
使用vue匯出excel遇到的那些坑
2019-03-14
VueExcel
vue2.0 匯出Excel表格資料
2018-03-22
VueExcel
【Mysql】匯出資料到excel檔案中
2016-10-08
MySqlExcel
vue專案前端匯出word檔案(bug解決)
2020-09-11
Vue前端
從Gitlab中匯出issue到Excel檔案中
2017-06-11
GitlabExcel
Vue 專案中接入sentry
2019-03-04
Vue
Vue專案中應用TypeScript
2021-09-17
VueTypeScript
vue 專案中ivew按需引入
2018-05-23
Vue
vue+element + table將選中的資料匯出為excel（匯出的是當前頁選中的資料）
2020-06-08
VueExcel
Mvc 5中匯出Excel
2018-05-10
MVCExcel
vue+elementUI el-table匯出excel表格
2019-08-20
VueUIExcel

Vue中級指南-01 如何在Vue專案中匯出Excel

Excel 匯出

使用

注意

引數

專案實戰

相關文章