Elysia 中的工作表

艾丽西亚是一个 BunJS 服务器端框架。

¥Elysia is a BunJS server-side framework.

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

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

该演示使用 ElysiaJS 和 SheetJS 来读取和写入数据。我们将探讨如何在 POST 请求处理程序中解析上传的文件并使用可下载的电子表格响应 GET 请求。

¥This demo uses ElysiaJS and SheetJS to read and write data. We'll explore how to parse uploaded files in a POST request handler and respond to GET requests with downloadable spreadsheets.

"完整示例" 部分包括一个完整的服务器。

¥The "Complete Example" section includes a complete server.

测试部署

该演示于 2024 年 3 月 11 日使用 ElysiaJS 0.8.17 和 BunJS 1.0.30 进行了最后一次测试。

¥This demo was last tested on 2024 March 11 with ElysiaJS 0.8.17 and BunJS 1.0.30.

集成详情

¥Integration Details

SheetJS BunJS 模块可以从 ElysiaJS 服务器脚本导入。

¥The SheetJS BunJS module can be imported from ElysiaJS server scripts.

读取数据

¥Reading Data

ElysiaJS 正文解析器接受 schema[^1]。t.File 方法标记字段应为文件。

¥The ElysiaJS body parser accepts a schema[^1]. The t.File method marks that a field is expected to be a file.

以下架构指示字段 upload 应该是已提交的文件：

¥The following schema indicates the field upload should be a submitted file:

{
  body: t.Object({
    upload: t.File()
  })
}

ElysiaJS 将文件公开为 Blob 对象。Blob#arrayBuffer 方法返回一个解析为 ArrayBuffer 的 Promise。可以使用 SheetJS read 方法 [^2] 来解析 ArrayBuffer。

¥ElysiaJS exposes the file as a Blob object. The Blob#arrayBuffer method returns a Promise that resolves to an ArrayBuffer. That ArrayBuffer can be parsed with the SheetJS read method[^2].

此示例服务器响应 POST 请求。服务器将在请求正文中查找 "upload" 键下的文件。如果存在文件，服务器将解析该文件，使用 sheet_to_html 方法 [^3] 生成 HTML 表并使用 HTML 代码进行响应：

¥This example server responds to POST requests. The server will look for a file in the request body under the "upload" key. If a file is present, the server will parse the file, generate an HTML table using the sheet_to_html method[^3] and respond with the HTML code:

import { Elysia, t } from "elysia";
import { read, utils } from "xlsx";

const app = new Elysia();
app.post("/", async({ body: { upload } }) => {
  const data = await upload.arrayBuffer();
  const wb = read(data);
  return utils.sheet_to_csv(wb.Sheets[wb.SheetNames[0]]);
}, {
  body: t.Object({
    upload: t.File()
  })
});
app.listen(3000);

写入数据

¥Writing Data

给定一个 SheetJS 工作簿对象，使用 type: "buffer"[^4] 的 write 方法会生成可以传递给 BunJS File 构造函数的数据对象。File 构造函数接受第二个参数作为生成的文件名。

¥Given a SheetJS workbook object, the write method using type: "buffer"[^4] generates data objects which can be passed to the BunJS File constructor. The File constructor accepts a second parameter for the generated file name.

此示例服务器响应 GET 请求。服务器将从数组 [^5] 生成一个 SheetJS 工作表对象，使用 book_new[^6] 和 book_append_sheet[^7] 实用程序方法构建一个新工作簿，使用 write 生成 XLSX 文件，并发送带有适当标头的文件以启动文件名 SheetJSElysia.xlsx 的下载：

¥This example server responds to GET requests. The server will generate a SheetJS worksheet object from an array of arrays[^5], build up a new workbook using the book_new[^6] and book_append_sheet[^7] utility methods, generate a XLSX file using write, and send the file with appropriate headers to initiate a download with file name SheetJSElysia.xlsx:

import { Elysia } from "elysia";
import { utils, write } from "xlsx";

const app = new Elysia();
app.get("/", () => {
  var ws = utils.aoa_to_sheet(["SheetJS".split(""), [5,4,3,3,7,9,5]]);
  var wb = utils.book_new(); utils.book_append_sheet(wb, ws, "Data");
  /* generate buffer */
  var buf = write(wb, {type: "buffer", bookType: "xlsx"});
  return new File([buf], "SheetJSElysia.xlsx");
});
app.listen(3000);

完整示例

¥Complete Example

创建一个新的 ElysiaJS 项目：

¥Create a new ElysiaJS project:

bun create elysia sheetjs-elysia
cd sheetjs-elysia

安装 SheetJS BunJS 模块：

¥Install the SheetJS BunJS module:

bun install https://cdn.sheetjs.com/xlsx-0.20.3/xlsx-0.20.3.tgz

将以下脚本保存到 src/SheetJSElysia.ts：

¥Save the following script to src/SheetJSElysia.ts:

src/SheetJSElysia.ts
import { Elysia, t } from "elysia";
import { read, utils, write } from "xlsx";

const app = new Elysia();
app.get("/", () => {
  var ws = utils.aoa_to_sheet(["SheetJS".split(""), [5,4,3,3,7,9,5]]);
  var wb = utils.book_new(); utils.book_append_sheet(wb, ws, "Data");
  /* generate buffer */
  var buf = write(wb, {type: "buffer", bookType: "xlsx"});
  return new File([buf], "SheetJSElysia.xlsx");
  /* set headers */
});
app.post("/", async({ body: { upload } }) => {
  const data = await upload.arrayBuffer();
  const wb = read(data);
  return utils.sheet_to_csv(wb.Sheets[wb.SheetNames[0]]);
}, {
  body: t.Object({
    upload: t.File()
  })
});
app.listen(3000);

运行服务器：

¥Run the server:

bun run src/SheetJSElysia.ts

使用 https://xlsx.nodejs.cn/pres.numbers 测试 POST 请求。这些命令应在新的终端窗口中运行：

¥Test POST requests using https://xlsx.nodejs.cn/pres.numbers . The commands should be run in a new terminal window:

curl -LO https://xlsx.nodejs.cn/pres.numbers
curl -X POST -F upload=@pres.numbers http://localhost:3000/

通过在浏览器中打开 http://localhost:3000/ 来测试 GET 请求。

¥Test GET requests by opening http://localhost:3000/ in your browser.

该页面应尝试下载 SheetJSElysia.xlsx 。打开新文件。

¥The page should attempt to download SheetJSElysia.xlsx . Open the new file.

[^1]: 请参阅 ElysiaJS 文档中的 "显式主体"。

¥See "Explicit Body" in the ElysiaJS documentation.

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

¥See read in "Reading Files"

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

¥See sheet_to_html in "Utilities"

[^4]: 见 write 于 "写入文件"

¥See write in "Writing Files"

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

¥See aoa_to_sheet in "Utilities"

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

¥See book_new in "Utilities"

[^7]: 见 book_append_sheet 于 "实用工具"

¥See book_append_sheet in "Utilities"

集成详情​

读取数据​

写入数据​

完整示例​

集成详情

读取数据

写入数据

完整示例