使用 AstroJS 让数据飙升

AstroJS[^1] 是一个站点生成器。AstroJS 项目支持 ViteJS[^2] 插件来制作自定义数据源。

¥AstroJS[^1] is a site generator. AstroJS projects support ViteJS[^2] plugins for making custom data sources.

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

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

该演示使用 AstroJS 和 SheetJS 从电子表格中提取数据并在 HTML 表格中显示内容。我们将探讨如何在 AstroJS 构建过程中加载 SheetJS 库并创建静态 HTML 页面。

¥This demo uses AstroJS and SheetJS to pull data from a spreadsheet and display the content in an HTML table. We'll explore how to load SheetJS libraries during the AstroJS build process and create static HTML pages.

"完整示例" 部分包括一个由 Apple Numbers 电子表格提供支持的完整网站。

¥The "Complete Example" section includes a complete website powered by Apple Numbers spreadsheets.

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

¥The following diagram depicts the workbook waltz:

遥测

AstroJS 默认启用遥测。该工具有一个禁用遥测的选项：

¥AstroJS enables telemetry by default. The tool has an option to disable telemetry:

npx astro telemetry disable

测试部署

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

¥This demo was tested in the following environments:

AstroJS	模板	日期
3.6.5	星光 0.14.0	2024-04-14
4.6.1	星光 0.21.5	2024-04-14

AstroJS 在次要版本中引入了许多重大更改。

¥AstroJS has introduced a number of breaking changes in minor releases.

演示在列出的日期与列出的版本按预期工作。

¥The demos worked as expected with the listed versions on the listed dates.

集成

¥Integration

该演示使用 ViteJS 演示中的 "Base64 加载器"。

¥This demo uses "Base64 Loader" from the ViteJS demo.

ViteJS 演示使用查询 ?b64 来识别文件。为了更好地使用 AstroJS，此演示直接匹配文件扩展名。

¥The ViteJS demo used the query ?b64 to identify files. To play nice with AstroJS, this demo matches the file extensions directly.

由于 AstroJS 在构建时执行每页处理，因此建议使用 Base64 字符串加载器来获取文件数据并在相关页面中使用 SheetJS 库进行解析。如果在 frontmatter 中执行 SheetJS 操作，则只有结果会添加到生成的页面中！

¥Since AstroJS performs per-page processing at build time, it is recommended to use the Base64 string loader to get file data and parse with the SheetJS library in the relevant pages. If the SheetJS operations are performed in frontmatter, only the results will be added to the generated pages!

加载器

¥Loader

加载程序应添加到 vite 键下的 astro.config.mjs 中。

¥The loader should be added to astro.config.mjs under the vite key.

astro.config.mjs

import { readFileSync } from 'fs';
import { defineConfig } from 'astro/config';
export default defineConfig({
  vite: {
    // this tells astro which extensions to handle
    assetsInclude: ['**/*.numbers', '**/*.xlsx', '**/*.xls', '**/*.xlsb'],

    plugins: [
      { // this plugin presents the data as a Base64 string
        name: "sheet-base64",
        transform(code, id) {
          if(!id.match(/\.(numbers|xlsx)$/)) return;
          var data = readFileSync(id, "base64");
          return `export default '${data}'`;
        }
      }
    ]
  }
});

类型

¥Types

对于 VSCodium 集成，可以在 src/env.d.ts 中指定类型。

¥For VSCodium integration, types can be specified in src/env.d.ts.

此数据加载器返回 Base64 字符串：

¥This data loader returns Base64 strings:

src/env.d.ts

/// <reference types="astro/client" />
declare module '*.numbers' { const data: string; export default data; }
declare module '*.xlsx'    { const data: string; export default data; }
declare module '*.xls'     { const data: string; export default data; }
declare module '*.xlsb'    { const data: string; export default data; }
// ... (more spreadsheet formats) ...

AstroJS 前沿问题

¥AstroJS Frontmatter

通常项目将文件存储在 src/pages 中。假设项目中 pres.numbers 存放在 src/data 目录下，则相对导入

¥Typically projects store files in src/pages. Assuming pres.numbers is stored in the src/data directory in the project, the relative import

import b64 from "../data/pres.numbers"

将返回一个 Base64 字符串。

¥will return a Base64 string.

可以使用 SheetJS read[^3] 方法和 base64 类型在 frontmatter 中解析 Base64 字符串。该方法返回一个 SheetJS 工作簿对象 [^4]。

¥The Base64 string can be parsed in the frontmatter using the SheetJS read[^3] method with the base64 type. The method returns a SheetJS workbook object[^4].

import { read } from "xlsx";

const wb = read(b64, { type: "base64" });

可以使用实用函数对工作簿对象进行后处理。以下示例使用 sheet_to_json[^5] 方法从工作簿中的第一个工作表生成行对象：

¥The workbook object can be post-processed using utility functions. The following example uses the sheet_to_json[^5] method to generate row objects from the first worksheet in the workbook:

src/pages/index.astro

---
/* -- the code in the frontmatter is only run at build time -- */
import { read, utils } from "xlsx";

/* parse workbook */
import b64 from "../data/pres.numbers";
const wb = read(b64, { type: "base64" });

/* generate row objects */
interface IPresident {
  Name: string;
  Index: number;
}
const data = utils.sheet_to_json<IPresident>(wb.Sheets[wb.SheetNames[0]]);
---

页面正文

¥Page Body

frontmatter 使用 sheet_to_json 创建了 "对象数组"[^6]。使用标准 JSX 技术，数据可以渲染为表行：

¥The frontmatter used sheet_to_json to create an "array of objects"[^6]. Using standard JSX techniques, the data can be rendered as table rows:

src/pages/index.astro

<html>
  <body>
    <h3>Presidents</h3>
    <table>
      <thead><tr><th>Name</th><th>Index</th></tr></thead>
      {/* Display each row object as a TR within the TBODY element */}
      <tbody>{data.map(row => (
        <tr><td>{row.Name}</td><td>{row.Index}</td></tr>
      ))}</tbody>
    </table>
  </body>
</html>

当使用 npx astro build 构建时，AstroJS 将执行转换并生成一个简单的 HTML 表格，而无需引用现有的电子表格文件！

¥When built using npx astro build, AstroJS will perform the conversion and emit a simple HTML table without any reference to the existing spreadsheet file!

完整示例

¥Complete Example

AstroJS 引入了许多重大更改。如果演示失败，请 留言

¥AstroJS has introduced a number of breaking changes. If the demo fails, please leave a note

0. 禁用 AstroJS 遥测：

   ¥Disable AstroJS telemetry:

npx astro telemetry disable

1. 使用 starlight 模板创建一个新站点：

   ¥Create a new site using the starlight template:

npm create astro@latest -- --template starlight --yes ./sheetjs-astro
cd sheetjs-astro

要测试旧版本的 AstroJS，请在创建项目后安装特定版本的 astro 和受支持的入门模板。

¥To test an older version of AstroJS, install the specific version of astro and a supported starter template after creating the project.

对于主要版本 3，Starlight 必须是版本 0.14.0：

¥For major version 3, Starlight must be version 0.14.0:

npm install --force astro@3.6.5 @astrojs/starlight@0.14.0

可以通过运行以下命令来验证版本：

¥The version can be verified by running:

npx astro --version

2. 获取示例文件 pres.numbers：

   ¥Fetch the example file pres.numbers:

mkdir -p src/data
curl -Lo src/data/pres.numbers https://xlsx.nodejs.cn/pres.numbers

3. 安装 SheetJS 库：

   ¥Install the SheetJS library:

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

4. 将以下行附加到 src/env.d.ts：

   ¥Append the following lines to src/env.d.ts:

src/env.d.ts (add to end)

/* add to the end of the file */
declare module '*.numbers' { const data: string; export default data; }
declare module '*.xlsx'    { const data: string; export default data; }

5. 将高亮的行添加到 astro.config.mjs：

   ¥Add the highlighted lines to astro.config.mjs:

• 在脚本顶部，导入 readFileSync：

  ¥At the top of the script, import readFileSync:

astro.config.mjs (add higlighted lines)

/* import `readFileSync` at the top of the script*/
import { readFileSync } from 'fs';
import { defineConfig } from 'astro/config';

• 在 defineConfig 的对象参数中，添加 vite 部分：

  ¥In the object argument to defineConfig, add a vite section:

astro.config.mjs (add highlighted lines)

export default defineConfig({
  /* this vite section should be added as a property of the object */
  vite: {
    // this tells astro which extensions to handle
    assetsInclude: ['**/*.numbers', '**/*.xlsx'],

    plugins: [
      { // this plugin presents the data as a Base64 string
        name: "sheet-base64",
        transform(code, id) {
          if(!id.match(/\.(numbers|xlsx)$/)) return;
          var data = readFileSync(id, "base64");
          return `export default '${data}'`;
        }
      }
    ]
  },

6. 下载 index.astro 并保存到 src/pages/index.astro（如果不存在则创建一个新文件夹）：

   ¥Download index.astro and save to src/pages/index.astro (creating a new folder if it does not exist):

mkdir -p src/pages
curl -o src/pages/index.astro https://xlsx.nodejs.cn/astrojs/index.astro

7. 删除 src/content 目录中的所有 index 文件：

   ¥Remove any index files in the src/content directory:

rm src/content/index.*

此命令可能会显示错误：

¥This command may show an error:

zsh: no matches found: src/content/index.*

该错误可以忽略。

¥This error can be ignored.

8. 构建静态站点：

   ¥Build the static site:

npx astro build

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

¥AstroJS will place the generated site in the dist subfolder.

9. 启动 Web 服务器来托管静态站点：

   ¥Start a web server to host the static site:

npx http-server dist

打开 Web 浏览器并访问显示的 URL ( http://localhost:8080 )。查看页面源码，确认页面中没有添加 JS。它仅包含 HTML 表中文件的内容。

¥Open a web browser and access the displayed URL ( http://localhost:8080 ). View the page source and confirm that no JS was added to the page. It only contains the content from the file in an HTML table.

如果站点显示欢迎页面，请检查该项目！

¥If the site shows a Welcome page, inspect the project!

第一次编写这个 demo 时，docs 模板使用了 src/pages/index.astro

¥When this demo was first written, the docs template used src/pages/index.astro

在后来的测试中，AstroJS 删除了 docs 模板，引入了 starlight 模板。该模板包含 src/content/index.mdx，其优先级高于 src/pages/index.astro。

¥In a later test, AstroJS removed the docs template and introduced the starlight template. This template included src/content/index.mdx, which takes priority over src/pages/index.astro.

要解决此问题，如步骤 7 中所述，请删除 src/content 文件夹中的所有 index.* 文件。

¥To resolve this issue, as noted in step 7, remove any index.* files in the src/content folder.

[^1]: 官方网站 使用名称 "Astro"，但大多数工具使用名称 @astrojs。

¥The official website uses the name "Astro" but most of the tooling uses the name @astrojs.

[^2]: 有关更多详细信息和高级用例，请参阅 "ViteJS" 演示。

¥See "ViteJS" demo for more details and advanced use cases.

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

¥See read in "Reading Files"

[^4]: 见 "SheetJS 数据模型"

¥See "SheetJS Data Model"

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

¥See sheet_to_json in "Utilities"

[^6]: 见 "对象数组" 于 "实用工具"

¥See "Arrays of Objects" in "Utilities"