Skip to main content

VueJS 站点中的工作表

VueJS 是一个用于构建用户界面的 JavaScript 库。

¥VueJS is a JavaScript library for building user interfaces.

SheetJS 是一个用于从电子表格读取和写入数据的 JavaScript 库。

¥SheetJS is a JavaScript library for reading and writing data from spreadsheets.

该演示使用 VueJS 和 SheetJS 来处理和生成电子表格。我们将探讨如何在 VueJS SFC（单文件组件）中加载 SheetJS，并比较常见的状态模型和数据流策略。

¥This demo uses VueJS and SheetJS to process and generate spreadsheets. We'll explore how to load SheetJS in a VueJS SFC (single-file component) and compare common state models and data flow strategies.

该演示重点介绍 VueJS 概念。其他演示涵盖一般部署：

¥This demo focuses on VueJS concepts. Other demos cover general deployments:

安装​

¥Installation

"构架" 章节 涵盖了 Yarn 和其他包管理器的安装。

¥The "Frameworks" section covers installation with Yarn and other package managers.

该库可以直接从 JS 或 JSX 代码导入：

¥The library can be imported directly from JS or JSX code with:

import { read, utils, writeFile } from 'xlsx';

内部状态​

¥Internal State

各种 SheetJS API 可处理各种数据形状。首选状态取决于应用。

¥The various SheetJS APIs work with various data shapes. The preferred state depends on the application.

对象数组​

¥Array of Objects

通常，一些用户会创建一个电子表格，其中包含应加载到网站中的源数据。该工作表将具有已知的列。

¥Typically, some users will create a spreadsheet with source data that should be loaded into the site. This sheet will have known columns.

状态​

¥State

示例 总统表 有一个标题行，其中包含 "名称" 和 "索引" 列。自然的 JS 表示是每行一个对象，使用第一行中的值作为键：

¥The example presidents sheet has one header row with "Name" and "Index" columns. The natural JS representation is an object for each row, using the values in the first rows as keys:

SpreadsheetState

pres.xlsx data

[
  { Name: "Bill Clinton", Index: 42 },
  { Name: "GeorgeW Bush", Index: 43 },
  { Name: "Barack Obama", Index: 44 },
  { Name: "Donald Trump", Index: 45 },
  { Name: "Joseph Biden", Index: 46 }
]

使用 VueJS Composition API，ref[^1] 函数创建状态对象：

¥Using the VueJS Composition API, the ref[^1] function creates state objects:

<script setup lang="ts">
import { ref } from "vue";

/* the component state is an array of objects */
const pres = ref<any[]>([]);
</script>

当提前知道电子表格标题行时，可以进行行输入：

¥When the spreadsheet header row is known ahead of time, row typing is possible:

<script setup lang="ts">
import { ref } from "vue";

interface President {
  Name: string;
  Index: number;
}

/* the component state is an array of presidents */
const pres = ref<President[]>([]);
</script>

这些类型信息丰富。它们不强制工作表包含命名列。应使用运行时数据验证库来验证数据集。

¥The types are informative. They do not enforce that worksheets include the named columns. A runtime data validation library should be used to verify the dataset.

当事先不知道文件头时，应使用 any。

¥When the file header is not known in advance, any should be used.

更新状态​

¥Updating State

SheetJS read 和 sheet_to_json 函数简化了状态更新。它们最好用在生命周期钩子的函数体中，包括 onMounted[^2]。

¥The SheetJS read and sheet_to_json functions simplify state updates. They are best used in the function bodies of lifecycle hooks including onMounted[^2].

当用户加载站点时，onMounted 钩子可以下载并更新状态：

¥The onMounted hook can download and update state when a person loads the site:

<script setup lang="ts">
import { ref, onMounted } from "vue";
import { read, utils } from 'xlsx';

interface President {
  Name: string;
  Index: number;
}

/* the component state is an array of presidents */
const pres = ref<President[]>([]);

/* Fetch and update the state once */
onMounted(async() => {
  /* Download from https://xlsx.nodejs.cn/pres.numbers */
  const f = await fetch("https://xlsx.nodejs.cn/pres.numbers");
  const ab = await f.arrayBuffer();

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

  /* generate array of presidents from the first worksheet */
  const ws = wb.Sheets[wb.SheetNames[0]]; // get the first worksheet
  const data: President[] = utils.sheet_to_json<President>(ws); // generate objects

  /* update state */
  pres.value = data;
});
</script>

渲染数据​

¥Rendering Data

组件通常会使用 v-for[^3] 映射数据。以下示例生成一个表，其中为每位总统生成一行：

¥A component will typically map over the data with v-for[^3]. The following example generates a TABLE with a row for each President:

Example SFC for displaying arrays of objects
<script setup>
import { ref } from "vue";
const rows = ref([]);
</script>

<template>


<table>
  <!-- The `thead` section includes the table header row -->
  <thead><tr><th>Name</th><th>Index</th></tr></thead>
  <!-- The `tbody` section includes the data rows -->
  <tbody>
    <!-- generate row (TR) for each president -->
    <tr v-for="(row, idx) in rows" :key="idx">
      

<td>{{ row.Name }}</td>


      

<td>{{ row.Index }}</td>


    </tr>
  </tbody>
</table>


</template>

导出数据​

¥Exporting Data

writeFile 和 json_to_sheet 功能简化了数据导出。它们最适合用于 v-on 事件处理程序（如 @click[^4]）的函数体中。

¥The writeFile and json_to_sheet functions simplify exporting data. They are best used in the function bodies of v-on event handlers like @click[^4].

当用户单击按钮时，回调可以生成本地文件：

¥A callback can generate a local file when a user clicks a button:

<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>

<template>
  <button @click="exportFile">Export XLSX</button>
</template>

完整组件​

¥Complete Component

这个完整的组件示例获取一个测试文件并在 HTML 表中显示内容。单击导出按钮时，回调将导出文件：

¥This complete component example fetches a test file and displays the contents in a HTML table. When the export button is clicked, a callback will export a file:

src/SheetJSVueAoO.vue
<script setup>
import { ref, onMounted } from "vue";
import { read, utils, writeFileXLSX } from 'xlsx';

const rows = ref([]);

onMounted(async() => {
  /* Download from https://xlsx.nodejs.cn/pres.numbers */
  const f = await fetch("https://xlsx.nodejs.cn/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>

<template>
  

<table><thead><tr><th>Name</th><th>Index</th></tr></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>
How to run the example (click to hide)
测试部署

本 demo 在以下环境下进行了测试：

¥This demo was tested in the following environments:

VueJSViteJS日期
3.4.275.2.112024-05-26

  1. 创建一个新站点：

    ¥Create a new site:

npm init vue@latest -- sheetjs-vue --default

  1. 安装 SheetJS 依赖并启动开发服务器：

    ¥Install the SheetJS dependency and start the dev server:

cd sheetjs-vue
npm i
npm i --save https://cdn.sheetjs.com/xlsx-0.20.3/xlsx-0.20.3.tgz
npm run dev

  1. 打开网络浏览器并访问显示的 URL (http://localhost:5173)

    ¥Open a web browser and access the displayed URL (http://localhost:5173)

  2. 将 src/App.vue 替换为 src/SheetJSVueAoO.vue 示例。

    ¥Replace src/App.vue with the src/SheetJSVueAoO.vue example.

该页面将刷新并显示带有“导出”按钮的表格。单击该按钮，页面将尝试下载 SheetJSVueAoO.xlsx。由于 Vite 会尝试即时优化 SheetJS 库，因此可能会有延迟。

¥The page will refresh and show a table with an Export button. Click the button and the page will attempt to download SheetJSVueAoO.xlsx. There may be a delay since Vite will try to optimize the SheetJS library on the fly.

  1. 停止开发服务器并构建站点：

    ¥Stop the dev server and build the site:

npm run build

生成的站点将放置在 dist 文件夹中。

¥The generated site will be placed in the dist folder.

  1. 启动本地网络服务器：

    ¥Start a local web server:

npx http-server dist

使用 Web 浏览器访问显示的 URL（通常为 http://localhost:8080）并测试该页面。

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

HTML​

对象数组方法的主要缺点是列的特定性质。对于更一般的用途，传递数组的数组是可行的。但是，这不能很好地处理合并单元格！

¥The main disadvantage of the Array of Objects approach is the specific nature of the columns. For more general use, passing around an Array of Arrays works. However, this does not handle merge cells[^5] well!

sheet_to_html 函数生成可识别合并和其他工作表功能的 HTML。VueJS v-html[^6] 属性允许代码设置 innerHTML 属性，从而有效地将代码插入到页面中。

¥The sheet_to_html function generates HTML that is aware of merges and other worksheet features. VueJS v-html[^6] attribute allows code to set the innerHTML attribute, effectively inserting the code into the page.

在此示例中，组件将 ref 附加到 DIV 容器。导出时，可以使用 table_to_book 解析第一个 TABLE 子元素以生成工作簿对象。

¥In this example, the component attaches a ref to the DIV container. During export, the first TABLE child element can be parsed with table_to_book to generate a workbook object.

src/SheetJSVueHTML.vue
<script setup>
import { ref, onMounted } from "vue";
import { read, utils, writeFileXLSX } from 'xlsx';

const html = ref("");
const tableau = ref();

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

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

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

/* get live table and export to XLSX */
function exportFile() {
  const wb = utils.table_to_book(tableau.value.getElementsByTagName("TABLE")[0])
  writeFileXLSX(wb, "SheetJSVueHTML.xlsx");
}
</script>

<template>
  <div ref="tableau" v-html="html"></div>
  <button @click="exportFile">Export XLSX</button>
</template>
How to run the example (click to hide)
测试部署

本 demo 在以下环境下进行了测试：

¥This demo was tested in the following environments:

VueJSViteJS日期
3.4.215.2.22024-03-21

  1. 创建一个新站点：

    ¥Create a new site:

npm init vue@latest -- sheetjs-vue --default

  1. 安装 SheetJS 依赖并启动开发服务器：

    ¥Install the SheetJS dependency and start the dev server:

cd sheetjs-vue
npm i
npm i --save https://cdn.sheetjs.com/xlsx-0.20.3/xlsx-0.20.3.tgz
npm run dev

  1. 打开网络浏览器并访问显示的 URL (http://localhost:5173)

    ¥Open a web browser and access the displayed URL (http://localhost:5173)

  2. 将 src/App.vue 替换为 src/SheetJSVueHTML.vue 示例。

    ¥Replace src/App.vue with the src/SheetJSVueHTML.vue example.

该页面将刷新并显示带有“导出”按钮的表格。单击该按钮，页面将尝试下载 SheetJSVueHTML.xlsx。由于 Vite 会尝试即时优化 SheetJS 库，因此可能会有延迟。

¥The page will refresh and show a table with an Export button. Click the button and the page will attempt to download SheetJSVueHTML.xlsx. There may be a delay since Vite will try to optimize the SheetJS library on the fly.

  1. 停止开发服务器并构建站点：

    ¥Stop the dev server and build the site:

npm run build

生成的站点将放置在 dist 文件夹中。

¥The generated site will be placed in the dist folder.

  1. 启动本地网络服务器：

    ¥Start a local web server:

npx http-server dist

使用 Web 浏览器访问显示的 URL（通常为 http://localhost:8080）并测试该页面。

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

行和列​

¥Rows and Columns

一些数据网格和 UI 组件将工作表状态分为两部分：列属性对象数组和行对象数组。前者用于生成列标题和对行对象进行索引。

¥Some data grids and UI components split worksheet state in two parts: an array of column attribute objects and an array of row objects. The former is used to generate column headings and for indexing into the row objects.

最安全的方法是使用数组的数组来表示状态并生成映射到 A1 样式列标题的列对象。

¥The safest approach is to use an array of arrays for state and to generate column objects that map to A1-Style column headers.

vue3-table-lite 演示 生成具有以下结构的行和列对象：

¥The vue3-table-lite demo generates rows and columns objects with the following structure:

/* rows are generated with a simple array of arrays */
rows.value = utils.sheet_to_json(worksheet, { header: 1 });

/* column objects are generated based on the worksheet range */
const range = utils.decode_range(ws["!ref"]||"A1");
columns.value = Array.from({ length: range.e.c + 1 }, (_, i) => ({
  /* for an array of arrays, the keys are "0", "1", "2", ... */
  field: String(i),
  /* column labels: encode_col translates 0 -> "A", 1 -> "B", 2 -> "C", ... */
  label: XLSX.utils.encode_col(i)
}));

旧版部署​

¥Legacy Deployments

独立脚本 可以很好地适应不使用打包器的旧部署。

¥The Standalone Scripts play nice with legacy deployments that do not use a bundler.

旧版演示展示了一个简单的 VueJS 组件。它是用 ES5 语法编写的。页面未缩小，应使用 "查看源代码" 进行检查。

¥The legacy demos show a simple VueJS component. It is written in ES5 syntax. The pages are not minified and "View Source" should be used to inspect.

有一个共享组件 SheetJS-vue.js

¥There is a shared component SheetJS-vue.js

整个演示设计为在 Internet Explorer 中运行，并不反映现代设计模式。

¥The entire demo is designed to run in Internet Explorer and does not reflect modern design patterns.

[^1]: 请参阅 VueJS 文档中的 ref()。

¥See ref() in the VueJS documentation.

[^2]: 请参阅 VueJS 文档中的 onMounted()。

¥See onMounted() in the VueJS documentation.

[^3]: 请参阅 VueJS 文档中的 v-for。

¥See v-for in the VueJS documentation.

[^4]: 请参阅 VueJS 文档中的 v-on。

¥See v-on in the VueJS documentation.

[^5]: 详细信息请参见 "合并单元格" 于 "SheetJS 数据模型"。

¥See "Merged Cells" in "SheetJS Data Model" for more details.

[^6]: 请参阅 VueJS 文档中的 v-html。

¥See v-html in the VueJS documentation.