使用 ESBuild 构建工作表

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

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

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

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

该演示使用 ESBuild 和 SheetJS 从电子表格中提取数据并在 HTML 表格中显示内容。我们将探讨如何在 ESBuild 加载器中加载 SheetJS 并生成在网页中使用的数据。

¥This demo uses ESBuild 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 ESBuild loader and generate data for use in webpages.

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

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

该演示涵盖静态资源导入。为了在浏览器中处理文件，"打包器" 演示 包含在浏览器脚本中导入 SheetJS 库的示例。

¥This demo covers static asset imports. For processing files in the browser, the "Bundlers" demo includes an example of importing the SheetJS library in a browser script.

ESBuild 加载器

¥ESBuild Loader

从 0.9.1 开始的 ESBuild 版本支持自定义加载器插件。加载程序接收文件系统上电子表格的绝对路径。

¥ESBuild releases starting from 0.9.1 support custom loader plugins. The loader receives an absolute path to the spreadsheet on the filesystem.

SheetJS NodeJS 模块 可以从 ESBuild 加载器插件导入。

¥The SheetJS NodeJS module can be imported from ESBuild loader plugins.

ESBuild 加载器插件使用 ECMAScript 模块。该插件最终接收文件的原始路径。fs 必须手动导入：

¥ESBuild loader plugins use ECMAScript Modules. The plugin ultimately receives raw paths to files. fs must be manually imported:

import * as XLSX from 'xlsx';

/* load 'fs' for readFile and writeFile support */
import * as fs from 'fs';
XLSX.set_fs(fs);

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

¥The following diagram depicts the workbook waltz:

ESBuild 配置

¥ESBuild Config

插件可以在构建配置对象的 plugins 数组中引用：

¥Plugins can be referenced in the plugins array of the build config object:

build.mjs (structure)

import * as esbuild from 'esbuild'

let sheetjsPlugin = {
  name: 'sheetjs',
  setup(build) {
    // ...
  }
};

await esbuild.build({
  entryPoints: ['app.js'],
  bundle: true,
  outfile: 'out.js',
  plugins: [sheetjsPlugin],
})

注册文件扩展名

¥Registering File Extensions

setup 方法接收构建选项。应使用 build.onLoad 添加自定义文件的处理程序。

¥The setup method receives the build options. Handlers for custom files should be added using build.onLoad.

onLoad 的第一个参数是配置对象。filter 属性应为正则表达式。以下正则表达式匹配 NUMBERS、XLSX、XLS 和 XLSB 文件：

¥The first argument to onLoad is a configuration object. The filter property is expected to be a regular expression. The following regular expression matches NUMBERS, XLSX, XLS, and XLSB files:

    const EXTS = /.(numbers|xlsx|xls|xlsb)$/;

onLoad 的第二个参数是接收参数对象的回调。对象的 path 属性是文件的绝对路径。

¥The second argument to onLoad is a callback that receives an arguments object. The path property of the object is the absolute path to the file.

  setup(build) {
    build.onLoad({ filter: EXTS }, (args) => {
      const path = args.path;
      // ...
    });
  },

SheetJS 操作

¥SheetJS Operations

SheetJS readFile 方法 [^2] 将直接读取文件系统上的文件。返回值是一个 SheetJS 工作簿对象 [^3]。

¥The SheetJS readFile method[^2] will directly read the file on the filesystem. The return value is a SheetJS workbook object[^3].

此演示中的加载器将解析工作簿，提取第一个工作表，并使用 sheet_to_json 方法 [^4] 生成行对象数组。

¥The loader in this demo will parse the workbook, pull the first worksheet, and generate an array of row objects using the sheet_to_json method[^4].

JSON 本身不支持日期！JSON.stringify 将生成字符串。

¥JSON does not natively support Dates! JSON.stringify will generate strings.

通过巧妙的解决方法，可以单独对日期进行编码并在生成的代码模块中恢复 Date 对象。

¥Through a clever workaround, it is possible to encode dates separately and recover the Date objects in the generated code module.

build.mjs (plugin implementation)

import * as XLSX from 'xlsx';
import * as fs from 'fs';
XLSX.set_fs(fs);

/* plugin */
let sheetjsPlugin = {
  name: 'sheetjs',
  setup(build) {
    /* match NUMBERS, XLSX, XLS, and XLSB files */
    const EXTS = /.(numbers|xlsx|xls|xlsb)$/;

    /* this method will be called once for each referenced file */
    build.onLoad({ filter: EXTS }, (args) => {
      /* parse file from filesystem */
      const wb = XLSX.readFile(args.path);
      /* get first worksheet */
      const ws = wb.Sheets[wb.SheetNames[0]];

      /* workaround for JSON limitation */
      Date.prototype.toJSON2 = Date.prototype.toJSON;
      Date.prototype.toJSON = function() { return {d:this.toISOString()}; };

      /* generate row objects */
      const data = XLSX.utils.sheet_to_json(ws);

      /* generate final module code */
      const res = JSON.stringify(data);
      Date.prototype.toJSON = Date.prototype.toJSON2;
      const contents = `const data = ${res};
data.forEach(row => {
  Object.keys(row).forEach(k => {
    if(row[k]?.d) row[k] = new Date(row[k].d);
  })
});
export default data;`
      return { contents, loader: 'js' };
    });
  },
};

资源导入

¥Asset Imports

可以使用该插件导入电子表格。假设 pres.xlsx 与脚本存储在同一文件夹中，./pres.xlsx 将是一个数据模块：

¥Spreadsheets can be imported using the plugin. Assuming pres.xlsx is stored in the same folder as the script, ./pres.xlsx will be a data module:

src/index.js

import data from './pres.xlsx';
/* `data` is an array of objects from ./pres.xlsx */

const elt = document.createElement('div');
elt.innerHTML = "<table><tr><th>Name</th><th>Index</th></tr>" +
  data.map((row) => `<tr>
    <td>${row.Name}</td>
    <td>${row.Index}</td>
  </tr>`).join("") +
"</table>";
document.body.appendChild(elt);

演示

¥Demo

测试部署

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

¥This demo was tested in the following environments:

esbuild	日期
0.20.2	2024-04-07
0.19.12	2024-04-07
0.18.20	2024-04-07
0.17.19	2024-04-07
0.16.17	2024-04-07
0.15.18	2024-04-07
0.14.54	2024-04-07
0.13.15	2024-04-07
0.12.29	2024-04-07
0.11.23	2024-04-07
0.10.2	2024-04-07
0.9.7	2024-04-07
0.9.1	2024-04-07

初始设置

¥Initial Setup

0. 创建一个新的骨架项目：

   ¥Create a new skeleton project:

mkdir sheetjs-esb
cd sheetjs-esb
npm init -y
npm i --save esbuild@0.20.2

1. 安装 SheetJS NodeJS 模块：

   ¥Install the SheetJS NodeJS module:

npm i --save https://cdn.sheetjs.com/xlsx-0.20.3/xlsx-0.20.3.tgz

2. 将以下内容保存到 index.html：

   ¥Save the following to index.html:

index.html

<!DOCTYPE html>
<html>
  

<head>
    <title>SheetJS + ESBuild</title>
  </head>


  <body>
   <script src="out.js"></script>
  </body>
</html>

3. 将以下内容保存到 app.js：

   ¥Save the following to app.js:

app.js

import data from './pres.numbers'
const elt = document.createElement('div');
elt.innerHTML = "<table><tr><th>Name</th><th>Index</th></tr>" +
  data.map((row) => `<tr>
    <td>${row.Name}</td>
    <td>${row.Index}</td>
  </tr>`).join("") +
"</table>";
document.body.appendChild(elt);

4. 下载 build.mjs 到项目文件夹：

   ¥Download build.mjs to the project folder:

curl -LO https://xlsx.nodejs.cn/esbuild/build.mjs

5. 下载 https://xlsx.nodejs.cn/pres.numbers 到项目文件夹：

   ¥Download https://xlsx.nodejs.cn/pres.numbers to the project folder:

curl -LO https://xlsx.nodejs.cn/pres.numbers

静态站点测试

¥Static Site Test

6. 建立网站：

   ¥Build the site:

node build.mjs

最终脚本将保存到 out.js

¥The final script will be saved to out.js

7. 启动本地 Web 服务器来托管项目文件夹：

   ¥Start a local web server to host the project folder:

npx http-server .

该命令将打印 URL 列表。

¥The command will print a list of URLs.

8. 打开上一步 (http://localhost:8080) 中打印的 URL 之一并确认显示相同的数据。

   ¥Open one of the URLs printed in the previous step (http://localhost:8080) and confirm that the same data is displayed.

要验证数据是否已添加到页面，请将 out.js 附加到 URL (http://localhost:8080/out.js) 并查看源。来源将包括总统名称。它不会包含 SheetJS 库引用！

¥To verify that the data was added to the page, append out.js to the URL (http://localhost:8080/out.js) and view the source. The source will include president names. It will not include SheetJS library references!

在上次测试中，生成的源代码如下所示

¥In the last test, the generated source looked like the following snippet

out.js (Expected output)

(() => {
  // pres.numbers
  var data = [{ "Name": "Bill Clinton", "Index": 42 }, /* ... more data */];
  data.forEach((row) => {
    Object.keys(row).forEach((k) => {
      if (row[k]?.d)
        row[k] = new Date(row[k].d);
    });
  });
  var pres_default = data;

  // app.js
  var elt = document.createElement("div");
  elt.innerHTML = "<table><tr><th>Name</th><th>Index</th></tr>" + pres_default.map((row) => `<tr>
    <td>${row.Name}</td>
    <td>${row.Index}</td>
  </tr>`).join("") + "</table>";
  document.body.appendChild(elt);
})();

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

¥See "Plugins" in the ESBuild documentation.

[^2]: 见 readFile 于 "读取文件"

¥See readFile in "Reading Files"

[^3]: 见 "工作簿对象"

¥See "Workbook Object"

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

¥See sheet_to_json in "Utilities"