Skip to main content

ViteJS 电子表格插件

ViteJS 是一个用于生成静态网站的构建工具。它有一个强大的 JavaScript 驱动的插件系统 [^1]。

¥ViteJS is a build tool for generating static websites. It has a robust JavaScript-powered plugin system[^1].

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

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

该演示使用 ViteJS 和 SheetJS 从电子表格中提取数据并将内容显示在 HTML 表格中。我们将探讨如何在 ViteJS 插件中加载 SheetJS 并评估数据加载策略。

¥This demo uses ViteJS and SheetJS to pull data from a spreadsheet and display the content in an HTML table. We'll explore how to load SheetJS in a ViteJS plugin and evaluate data loading strategies.

"完整演示" 部分创建了一个由 XLSX 电子表格支持的完整网站。

¥The "Complete Demo" section creates a complete website powered by a XLSX spreadsheet.

该演示涵盖了构建时数据可用的用例。此流程适用于以 HTML 表格发布的周末或月末 (EOM) 报告。

¥This demo covers use cases where data is available at build time. This flow is suitable for end of week or end of month (EOM) reports published in HTML tables.

为了在浏览器中处理用户提交的文件，ViteJS "打包器" 演示 显示了 SheetJS 库的客户端打包。"ReactJS" 演示 显示了使用 ViteJS 和 ReactJS 启动器的示例站点。

¥For processing user-submitted files in the browser, the ViteJS "Bundlers" demo shows client-side bundling of SheetJS libraries. The "ReactJS" demo shows example sites using ViteJS with the ReactJS starter.

插件​

¥Plugins

ViteJS 支持静态资源导入 [^2]，但默认的原始加载器将数据解释为 UTF-8 字符串。这会损坏二进制格式，包括 XLSX 和 XLS。自定义加载器可以绕过原始加载器并直接读取文件。

¥ViteJS supports static asset imports[^2], but the default raw loader interprets data as UTF-8 strings. This corrupts binary formats including XLSX and XLS. A custom loader can bypass the raw loader and directly read files.

由于必须使用自定义加载器，因此加载器可以执行一些数据处理工作。此演示中探讨了三种方法。

¥Since a custom loader must be used, some data processing work can be performed by the loader. Three approaches are explored in this demo.

下图展示了 ViteJS 数据流。粉红色 "主脚本导入" 框表示加载器和主脚本之间的划分。绿色 "SheetJS 操作" 框表示 SheetJS 库执行的步骤。

¥The following diagrams show the ViteJS data flow. The pink "Main Script import" boxes represent the division between the loader and the main script. The green "SheetJS Operations" boxes represent the steps performed by SheetJS libraries.

HTMLDataBase64

对于简单的数据表，强烈建议使用 "纯数据插件"。文件处理在构建时执行，生成的站点仅包含原始数据。

¥For simple tables of data, "Pure Data Plugin" is strongly recommended. The file processing is performed at build time and the generated site only includes the raw data.

对于更复杂的解析或显示逻辑，"Base64 插件" 更合适。由于原始解析逻辑是在页面中执行的，因此该库将包含在最终包中。

¥For more complex parsing or display logic, "Base64 Plugin" is preferable. Since the raw parsing logic is performed in the page, the library will be included in the final bundle.

"HTML 插件" 在加载器脚本中生成 HTML。SheetJS HTML 编写器呈现合并单元格和其他功能。

¥The "HTML Plugin" generates HTML in the loader script. The SheetJS HTML writer renders merged cells and other features.

纯数据插件​

¥Pure Data Plugin

对于纯静态站点，插件可以将数据加载到行对象数组中。SheetJS 的工作是在插件中执行的。页面中未加载库！

¥For a pure static site, a plugin can load data into an array of row objects. The SheetJS work is performed in the plugin. The library is not loaded in the page!

下图描绘了练习册华尔兹：

¥The following diagram depicts the workbook waltz:

此 ViteJS 插件将使用 SheetJS read 方法 [^3] 读取电子表格并使用 SheetJS sheet_to_json[^4] 方法生成行对象数组：

¥This ViteJS plugin will read spreadsheets using the SheetJS read method[^3] and generate arrays of row objects with the SheetJS sheet_to_json[^4] method:

vite.config.js
import { readFileSync } from 'fs';
import { read, utils } from 'xlsx';
import { defineConfig } from 'vite';

export default defineConfig({
  assetsInclude: ['**/*.xlsx'], // xlsx file should be treated as assets

  plugins: [
    { // this plugin handles ?sheetjs tags
      name: "vite-sheet",
      transform(code, id) {
        if(!id.match(/\?sheetjs$/)) return;
        var wb = read(readFileSync(id.replace(/\?sheetjs$/, "")));
        var data = utils.sheet_to_json(wb.Sheets[wb.SheetNames[0]]);
        return `export default JSON.parse('${JSON.stringify(data).replace(/\\/g, "\\\\")}')`;
      }
    }
  ]
});

ViteJS 插件应返回代表 ECMAScript 模块的字符串。

¥ViteJS plugins are expected to return strings representing ECMAScript modules.

插件使用 JSON.stringify 对对象数组进行编码。生成的字符串已注入到新模块代码中。当 ViteJS 处理模块时，JSON.parse 会恢复对象数组。

¥The plugin uses JSON.stringify to encode the array of objects. The generated string is injected into the new module code. When ViteJS processes the module, JSON.parse recovers the array of objects.

在前端代码中，加载器将查找具有 ?sheetjs 查询字符串的所有模块。默认导出是行对象数组。

¥In frontend code, the loader will look for all modules with a ?sheetjs query string. The default export is an array of row objects.

以下示例脚本显示表中的数据：

¥The following example script displays the data in a table:

main.js
import data from './data/pres.xlsx?sheetjs';

document.querySelector('#app').innerHTML = `<table>
  <thead><tr><th>Name</th><th>Index</th></tr></thead>
  <tbody>
    ${data.map(row => `<tr>
      <td>${row.Name}</td>
      <td>${row.Index}</td>
    </tr>`).join("\n")}
  </tbody>
</table>`;

HTML 插件​

¥HTML Plugin

插件可以生成可以添加到页面的原始 HTML 字符串。SheetJS 库在插件中使用，但不会添加到站点。

¥A plugin can generate raw HTML strings that can be added to a page. The SheetJS libraries are used in the plugin but will not be added to the site.

下图描绘了练习册华尔兹：

¥The following diagram depicts the workbook waltz:

此 ViteJS 插件将使用 SheetJS read 方法 [^5] 读取电子表格并使用 SheetJS sheet_to_html[^6] 方法生成 HTML：

¥This ViteJS plugin will read spreadsheets using the SheetJS read method[^5] and generate HTML using the SheetJS sheet_to_html[^6] method:

vite.config.js
import { readFileSync } from 'fs';
import { read, utils } from 'xlsx';
import { defineConfig } from 'vite';

export default defineConfig({
  assetsInclude: ['**/*.xlsx'], // xlsx file should be treated as assets

  plugins: [
    { // this plugin handles ?html tags
      name: "vite-sheet-html",
      transform(code, id) {
        if(!id.match(/\?html/)) return;
        var wb = read(readFileSync(id.replace(/\?html/, "")));
        var html = utils.sheet_to_html(wb.Sheets[wb.SheetNames[0]]);
        return (`export default JSON.parse('${JSON.stringify(html).replace(/\\/g, "\\\\")}')`);
      }
    }
  ]
});

ViteJS 插件应返回代表 ECMAScript 模块的字符串。

¥ViteJS plugins are expected to return strings representing ECMAScript modules.

插件使用 JSON.stringify 对 HTML 字符串进行编码。生成的字符串已注入到新模块代码中。当 ViteJS 处理模块时，JSON.parse 会恢复原始 HTML 字符串。

¥The plugin uses JSON.stringify to encode the HTML string. The generated string is injected into the new module code. When ViteJS processes the module, JSON.parse recovers the original HTML string.

在前端代码中，加载器将查找具有 ?html 查询字符串的所有模块。默认导出为字符串，可直接添加到页面。

¥In frontend code, the loader will look for all modules with a ?html query string. The default export is a string that can be directly added to the page.

以下示例脚本设置容器的 innerHTML 属性：

¥The following example script sets the innerHTML property of the container:

main.js
import html from './data/pres.xlsx?html';

document.querySelector('#app').innerHTML = html;

Base64 插件​

¥Base64 Plugin

该插件将数据作为 Base64 字符串提取，可以使用 read[^7] 读取。虽然这种方法有效，但不建议这样做，因为它会在前端站点加载库。

¥This plugin pulls in data as a Base64 string that can be read with read[^7]. While this approach works, it is not recommended since it loads the library in the front-end site.

下图描绘了练习册华尔兹：

¥The following diagram depicts the workbook waltz:

该 ViteJS 插件将读取电子表格文件并将数据导出为 Base64 字符串。插件中未导入 SheetJS：

¥This ViteJS plugin will read spreadsheet files and export the data as a Base64 string. SheetJS is not imported in the plugin:

vite.config.js
import { readFileSync } from 'fs';
import { defineConfig } from 'vite';

export default defineConfig({
  assetsInclude: ['**/*.xlsx'], // mark that xlsx file should be treated as assets

  plugins: [
    { // this plugin handles ?b64 tags
      name: "vite-b64-plugin",
      transform(code, id) {
        if(!id.match(/\?b64$/)) return;
        var path = id.replace(/\?b64/, "");
        var data = readFileSync(path, "base64");
        return `export default '${data}'`;
      }
    }
  ]
});

使用 b64 查询导入时，原始 Base64 字符串将被公开。read 将使用 base64 输入类型 [^8] 处理 Base64 字符串：

¥When importing using the b64 query, the raw Base64 string will be exposed. read will process the Base64 string using the base64 input type[^8]:

main.js
import { read, utils } from "xlsx";

/* import workbook data */
import b64 from './data.xlsx?b64';

/* parse workbook and pull data from the first worksheet */
const wb = read(b64, { type: "base64" });
const wsname = wb.SheetNames[0];
const data = utils.sheet_to_json(wb.Sheets[wsname]);

document.querySelector('#app').innerHTML = `<table>
  <thead><tr><th>Name</th><th>Index</th></tr></thead>
  <tbody>
    ${data.map(row => `<tr>
      <td>${row.Name}</td>
      <td>${row.Index}</td>
    </tr>`).join("\n")}
  </tbody>
</table>`;

完整演示​

¥Complete Demo

该演示演示了从头开始创建一个新的 ViteJS 网站的过程。可以克隆具有已完成站点的 Git 存储库 [^9]。

¥The demo walks through the process of creating a new ViteJS website from scratch. A Git repository with the completed site can be cloned[^9].

测试部署

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

¥This demo was tested in the following environments:

ViteJS日期
5.2.122024-06-02
4.5.32024-06-02
3.2.102024-06-02
2.9.182024-06-02

初始设置​

¥Initial Setup

  1. 使用 vue-ts 模板创建一个新站点并安装 SheetJS 包：

    ¥Create a new site with the vue-ts template and install the SheetJS package:

要强制使用较旧的 ViteJS 主要版本，请将 vite@5 更改为所需的主要版本。例如，npm create vite@3 将使用 ViteJS 主要版本 3。

¥To force an older major version of ViteJS, change the vite@5 to the desired major version. For example, npm create vite@3 will use ViteJS major version 3.

npm create vite@5 sheetjs-vite -- --template vue-ts
cd sheetjs-vite
npm i
npm i --save https://cdn.sheetjs.com/xlsx-0.20.3/xlsx-0.20.3.tgz

  1. 下载并替换 vite.config.ts

    ¥Download and replace vite.config.ts

curl -O https://xlsx.nodejs.cn/vitejs/vite.config.ts

  1. 创建 data 文件夹并下载 https://xlsx.nodejs.cn/pres.xlsx ：

    ¥Make a data folder and download https://xlsx.nodejs.cn/pres.xlsx :

mkdir -p data
curl -L -o data/pres.xlsx https://xlsx.nodejs.cn/pres.xlsx

纯数据测试​

¥Pure Data Test

  1. 运行开发服务器：

    ¥Run the dev server:

npm run dev

打开浏览器窗口并访问显示的 URL（通常为 http://localhost:5173 ）

¥Open a browser window to the displayed URL (typically http://localhost:5173 )

  1. 将组件 src/components/HelloWorld.vue 替换为：

    ¥Replace the component src/components/HelloWorld.vue with:

src/components/HelloWorld.vue
<script setup lang="ts">
// @ts-ignore
import data from '../../data/pres.xlsx?sheetjs';
</script>

<template>
  

<table>
    <tr><th>Name</th><th>Index</th></tr>
    <tr v-for="(row,R) in data" v-bind:key="R">
      

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


      

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


    </tr>
  </table>


</template>

保存并刷新页面。应显示数据表

¥Save and refresh the page. A data table should be displayed

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

    ¥Stop the dev server and build the site

npm run build
npx http-server dist/

终端将显示一个 URL，通常为 http://127.0.0.1:8080 。 使用网络浏览器访问该页面。

¥The terminal will display a URL, typically http://127.0.0.1:8080 . Access that page with a web browser.

针对 ViteJS 2.9.18 测试此演示时，构建失败：

¥When this demo was tested against ViteJS 2.9.18, the build failed:

src/App.vue:8:3 - error TS7026: JSX element implicitly has type 'any' because no interface 'JSX.IntrinsicElements' exists.

8   <img alt="Vue logo" src="./assets/logo.png" />
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

由于它会影响项目模板，因此这是 ViteJS 中的一个错误。

¥As it affects the project template, this is a bug in ViteJS.

最简单的解决方法是强制升级 vue-tsc 依赖：

¥The simplest workaround is to force upgrade the vue-tsc dependency:

npm i vue-tsc@latest

  1. 要确认页面中仅存在原始数据，请查看页面源。代码将引用脚本 /assets/index-HASH.js，其中 HASH 是一串字符。打开该脚本。

    ¥To confirm that only the raw data is present in the page, view the page source. The code will reference a script /assets/index-HASH.js where HASH is a string of characters. Open that script.

搜索 Bill Clinton 会显示以下内容：

¥Searching for Bill Clinton reveals the following:

{"Name":"Bill Clinton","Index":42}

搜索 BESSELJ 应该不会显示任何结果。SheetJS 脚本未包含在最终站点中！

¥Searching for BESSELJ should reveal no results. The SheetJS scripts are not included in the final site!

ViteJS 还支持 "服务器端渲染"。在 SSR 中，只有 HTML 表格会添加到最终页面。ViteJS 文档 [^10] 中介绍了详细信息。

¥ViteJS also supports "Server-Side Rendering". In SSR, only the HTML table would be added to the final page. Details are covered in the ViteJS docs[^10].

HTML 测试​

¥HTML Test

  1. 运行开发服务器：

    ¥Run the dev server:

npm run dev

打开浏览器窗口并访问显示的 URL（通常为 http://localhost:5173 ）

¥Open a browser window to the displayed URL (typically http://localhost:5173 )

  1. 将组件 src/components/HelloWorld.vue 替换为：

    ¥Replace the component src/components/HelloWorld.vue with:

src/components/HelloWorld.vue
<script setup lang="ts">
// @ts-ignore
import html from '../../data/pres.xlsx?html';
</script>

<template>
  <div v-html="html"></div>
</template>

保存并刷新页面。应显示数据表

¥Save and refresh the page. A data table should be displayed

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

    ¥Stop the dev server and build the site

npm run build
npx http-server dist/

终端将显示一个 URL，通常为 http://127.0.0.1:8080 。 使用网络浏览器访问该页面。

¥The terminal will display a URL, typically http://127.0.0.1:8080 . Access that page with a web browser.

针对 ViteJS 2.9.18 测试此演示时，构建失败：

¥When this demo was tested against ViteJS 2.9.18, the build failed:

src/App.vue:8:3 - error TS7026: JSX element implicitly has type 'any' because no interface 'JSX.IntrinsicElements' exists.

8   <img alt="Vue logo" src="./assets/logo.png" />
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

由于它会影响项目模板，因此这是 ViteJS 中的一个错误。

¥As it affects the project template, this is a bug in ViteJS.

最简单的解决方法是强制升级 vue-tsc 依赖：

¥The simplest workaround is to force upgrade the vue-tsc dependency:

npm i vue-tsc@latest

  1. 要确认页面中仅存在原始 HTML，请查看页面源代码。代码将引用脚本 /assets/index-HASH.js，其中 HASH 是一串字符。打开该脚本。

    ¥To confirm that only the raw HTML is present in the page, view the page source. The code will reference a script /assets/index-HASH.js where HASH is a string of characters. Open that script.

搜索 Bill Clinton 会显示以下编码的 HTML 元素：

¥Searching for Bill Clinton reveals the following encoded HTML element:

<td data-t=\\"s\\" data-v=\\"Bill Clinton\\" id=\\"sjs-A2\\">Bill Clinton</td>

搜索 BESSELJ 应该不会显示任何结果。SheetJS 脚本未包含在最终站点中！

¥Searching for BESSELJ should reveal no results. The SheetJS scripts are not included in the final site!

HTML 代码仍存储在脚本中并动态注入。

¥The HTML code is still stored in a script and is injected dynamically.

ViteJS "服务器端渲染" 提供了在构建时渲染站点的选项，确保 HTML 表直接添加到页面。

¥ViteJS "Server-Side Rendering" offers the option to render the site at build time, ensuring that the HTML table is directly added to the page.

Base64 测试​

¥Base64 Test

  1. 运行开发服务器：

    ¥Run the dev server:

npm run dev

打开浏览器窗口并访问显示的 URL（通常为 http://localhost:5173 ）

¥Open a browser window to the displayed URL (typically http://localhost:5173 )

  1. 将组件 src/components/HelloWorld.vue 替换为：

    ¥Replace the component src/components/HelloWorld.vue with:

src/components/HelloWorld.vue
<script setup lang="ts">
// @ts-ignore
import b64 from '../../data/pres.xlsx?b64';
import { read, utils } from "xlsx";
/* parse workbook and convert first sheet to row array */
const wb = read(b64);
const ws = wb.Sheets[wb.SheetNames[0]];
interface IPresident { Name: string; Index: number; };
const data = utils.sheet_to_json<IPresident>(ws);
</script>

<template>
  

<table>
    <tr><th>Name</th><th>Index</th></tr>
    <tr v-for="(row,R) in data" v-bind:key="R">
      

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


      

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


    </tr>
  </table>


</template>

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

    ¥Stop the dev server and build the site

npm run build
npx http-server dist/

终端将显示一个 URL ( http://127.0.0.1:8080 )。使用网络浏览器访问该页面。

¥The terminal will display a URL ( http://127.0.0.1:8080 ). Access that page with a web browser.

针对 ViteJS 2.9.18 测试此演示时，构建失败：

¥When this demo was tested against ViteJS 2.9.18, the build failed:

src/App.vue:8:3 - error TS7026: JSX element implicitly has type 'any' because no interface 'JSX.IntrinsicElements' exists.

8   <img alt="Vue logo" src="./assets/logo.png" />
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

由于它会影响项目模板，因此这是 ViteJS 中的一个错误。

¥As it affects the project template, this is a bug in ViteJS.

最简单的解决方法是强制升级 vue-tsc 依赖：

¥The simplest workaround is to force upgrade the vue-tsc dependency:

npm i vue-tsc@latest

  1. 要确认页面中不存在对象数据，请查看页面源。代码将引用脚本 /assets/index-HASH.js，其中 HASH 是一串字符。打开该脚本。

    ¥To confirm that the object data is not present in the page, view the page source. The code will reference a script /assets/index-HASH.js where HASH is a string of characters. Open that script.

搜索 BESSELJ 应与代码匹配：

¥Searching for BESSELJ should match the code:

425:"BESSELJ"

搜索 Bill Clinton 应该不会产生任何结果。SheetJS 库嵌入到最终站点中，并在加载页面时解析数据。

¥Searching for Bill Clinton should yield no results. The SheetJS library is embedded in the final site and the data is parsed when the page is loaded.

[^1]: 请参阅 ViteJS 文档中的 "使用插件"。

¥See "Using Plugins" in the ViteJS documentation.

[^2]: 请参阅 ViteJS 文档中的 "静态资源处理"。

¥See "Static Asset Handling" in the ViteJS documentation.

[^3]: 见 read 于 "读取文件"

¥See read in "Reading Files"

[^4]: 见 sheet_to_html 于 "实用工具"

¥See sheet_to_html in "Utilities"

[^5]: 见 read 于 "读取文件"

¥See read in "Reading Files"

[^6]: 见 sheet_to_json 于 "实用工具"

¥See sheet_to_json in "Utilities"

[^7]: 见 read 于 "读取文件"

¥See read in "Reading Files"

[^8]: 见 "读取文件" 中的 "base64" 类型

¥See the "base64" type in "Reading Files"

[^9]: 请参阅 SheetJS git 服务器上的 examples/sheetjs-vite。

¥See examples/sheetjs-vite on the SheetJS git server.

[^10]: 请参阅 ViteJS 文档中的 "服务器端渲染"。

¥See "Server-Side Rendering" in the ViteJS documentation.