匯出 Excel - Sheets in VueJS Sites

發表於 分類於 Disqus： 文章字數： 3.8k 所需閱讀時間 ≈ 3 分鐘

前言

SheetJS 是一個 JavaScript 函式庫，用於從表格中讀取和寫入資料。

下載

1
npm i xlsx

GitHub
官網 - SheetJS CE Docs - Vue

匯出資料 - Exporting Data

1
2
3
<template>
  <button @click="exportFile">Export XLSX</button>
</template>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
<script setup>
import { ref } from "vue";
import { utils, writeFileXLSX } from 'xlsx';

const pres = ref([]);

/* get state data and export to XLSX */
function exportFile() {
  /* generate worksheet from state */
  const ws = utils.json_to_sheet(pres.value);
  /* create workbook and append worksheet */
  const wb = utils.book_new();
  utils.book_append_sheet(wb, ws, "Data");
  /* export to XLSX */
  writeFileXLSX(wb, "SheetJSVueAoO.xlsx");
}
</script>

完整範例 (src/SheetJSVueAoO.vue)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
<template>
  <table>
     <thead>
        <th>Name</th>
        <th>Index</th>
     </thead>
     <tbody>
        <tr v-for="(row, idx) in rows" :key="idx">
          <td>{{ row.Name }}</td>
          <td>{{ row.Index }}</td>
        </tr>
     </tbody>
     <tfoot>
        <td colSpan={2}>
          <button @click="exportFile">Export XLSX</button>
        </td>
     </tfoot>
  </table>
</template>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
<script setup>
import { ref, onMounted } from "vue";
import { read, utils, writeFileXLSX } from 'xlsx';

const rows = ref([]);

onMounted(async() => {
  /* Download from https://sheetjs.com/pres.numbers */
  const f = await fetch("https://sheetjs.com/pres.numbers");
  const ab = await f.arrayBuffer();

  /* parse workbook */
  const wb = read(ab);

  /* update data */
  rows.value = utils.sheet_to_json(wb.Sheets[wb.SheetNames[0]]);
});

/* get state data and export to XLSX */
function exportFile() {
  const ws = utils.json_to_sheet(rows.value);
  const wb = utils.book_new();
  utils.book_append_sheet(wb, ws, "Data");
  writeFileXLSX(wb, "SheetJSVueAoO.xlsx");
}
</script>

如何測試運行範例

  1. Run npm init vue@latest -- sheetjs-vue --default
  2. Install the SheetJS dependency and start the dev server:
    1
    2
    3
    4
    cd sheetjs-vue
    npm i
    npm i --save https://cdn.sheetjs.com/xlsx-0.20.0/xlsx-0.20.0.tgz
    npm run dev
  3. Open a web browser and access the displayed URL (http://localhost:5173)
  4. Replace src/App.vue with the src/SheetJSVueAoO.vue example.

The page will refresh and show a table with an Export button. Click the button and the page will attempt to download SheetJSVueAoA.xlsx. There may be a delay > > since Vite will try to optimize the SheetJS library on the fly.
5) Build the site:

1
npm run build

The generated site will be placed in the dist folder.

  1. Start a local web server:
1
npx http-server dist

Access the displayed URL (typically http://localhost:8080) with a web browser and test the page.

可參考 TTGO (報表管理 / 車輛載運資料報表)

1
<button @click="exportExcel" btnType="copy" class="text-white bg-green-500 hover:brightness-[1.2] duration-150 fw-700 px-40px rounded-100px w-full md:w-auto min-h-40px">匯出報表</button>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
<!-- 匯出報表 -->
function exportExcel() {
  const exportList = JSON.parse(JSON.stringify(reportList.value));

  exportList.forEach((item) => {
    item.reserveDate = dayjs(item.reserveDate).format('YYYY/MM/DD');
    item.totalMileage = (item.totalMileage / 1000).toFixed(2);
    delete item.totalAmt;
  });

  // 排序Excel表格
  const header = ['行駛日期', '所屬機構', '司機', '站線類別', '路線名稱', '車號', '行駛趟數', '行駛里程', '營運成本', '載客人數'];
  const newCacheTable = exportList.map((item) => reverseObjKey(item));
  const worksheet = XLSX.utils.json_to_sheet(newCacheTable, { header });
  const workbook = XLSX.utils.book_new();

  XLSX.utils.book_append_sheet(workbook, worksheet, 'Dates');
  XLSX.utils.sheet_add_aoa(worksheet, [['行駛日期']], { origin: 'A1' });
  XLSX.writeFile(workbook, '車輛載運資料表.xlsx');
}