使用 HonoJS 在 Fire 中创建工作表

HonoJS 是一个轻量级的服务器端框架。

¥HonoJS is a lightweight server-side framework.

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

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

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

¥This demo uses HonoJS 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.

测试部署

该演示最后在以下部署中进行了测试：

¥This demo was last tested in the following deployments:

平台	HonoJS	日期
BunJS `1.1.21`	`4.5.1`	2024-07-27

集成详情

¥Integration Details

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

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

读取数据

¥Reading Data

HonoJS 主体解析器 [^1] 在 POST 请求中处理文件。主体解析器返回一个可以通过字段名称索引的对象：

¥The HonoJS body parser[^1] processes files in POST requests. The body parser returns an object that can be indexed by field name:

/* /import route */
app.post('/import', async(c) => {
  /* parse body */
  const body = await c.req.parseBody();
  /* get a file uploaded in the `upload` field */
  const file = body["upload"];

  /* `file` is a `File` object */
  // ...
});

默认情况下，当表单主体为给定字段指定多个值时，HonoJS 主体解析器将使用最后一个值。要强制主体解析器处理所有文件，字段名称必须以 [] 结尾：

¥By default, the HonoJS body parser will use the last value when the form body specifies multiple values for a given field. To force the body parser to process all files, the field name must end with []:

  /* parse body */
  const body = await c.req.parseBody();
  /* get all files uploaded in the `upload` field */
  const files = body["upload[]"];

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

¥HonoJS exposes each 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_csv 方法 [^3] 生成 CSV 行，并以文本响应：

¥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 and, generate CSV rows using the sheet_to_csv method[^3], and respond with text:

import { Hono } from 'hono';
import { read, utils } from 'xlsx';

const app = new Hono();
app.post('/import', async(c) => {
  /* get file data */
  const body = await c.req.parseBody();
  const file = body["upload"];
  const ab = await file.arrayBuffer();
  /* parse */
  const wb = read(ab);
  /* generate CSV */
  const csv = utils.sheet_to_csv(wb.Sheets[wb.SheetNames[0]]);
  return c.text(csv);
});
export default app;

写入数据

¥Writing Data

给定一个 SheetJS 工作簿对象，使用 type: "buffer"[^4] 的 write 方法生成可以传递给响应 body 方法的数据对象。

¥Given a SheetJS workbook object, the write method using type: "buffer"[^4] generates data objects which can be passed to the response body method.

此示例服务器响应 GET 请求。服务器将从数组 [^5] 的数组生成 SheetJS 工作表对象，使用 book_new[^6] 实用程序方法构建新的工作簿，使用 write 生成 XLSX 文件，并使用适当的标题发送文件以下载 SheetJSHonoJS.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] utility method, generate a XLSX file using write, and send the file with appropriate headers to download SheetJSHonoJS.xlsx:

import { Hono } from 'hono';
import { utils, write } from "xlsx";

const app = new Hono();
app.get("/export", (c) => {
  /* generate SheetJS workbook object */
  var ws = utils.aoa_to_sheet(["SheetJS".split(""), [5,4,3,3,7,9,5]]);
  var wb = utils.book_new(ws, "Data");
  /* generate buffer */
  var buf = write(wb, {type: "buffer", bookType: "xlsx"});
  /* set headers */
  c.header('Content-Disposition', 'attachment; filename="SheetJSHonoJS.xlsx"');
  c.header('Content-Type', 'application/vnd.ms-excel');
  /* export buffer */
  return c.body(buf);
});
export default app;

完整示例

¥Complete Example

此示例创建了一个存储数组数组的简单服务器。有三个服务器端点：

¥This example creates a simple server that stores an array of arrays. There are three server endpoints:

/import POST 请求需要 upload 字段中的文件。它将解析文件、更新内部数组数组并使用 CSV 数据进行响应。

¥/import POST request expects a file in the upload field. It will parse the file, update the internal array of arrays, and responds with CSV data.
/export GET 请求从内部数组数组生成工作簿。它将以 XLSX 数据进行响应并启动对 SheetJSHonoJS.xlsx 的下载。

¥/export GET request generates a workbook from the internal array of arrays. It will respond with XLSX data and initiate a download to SheetJSHonoJS.xlsx .
/json GET 请求以内部状态响应。

¥/json GET request responds with the internal state.

创建一个新的 BunJS + HonoJS 项目：

¥Create a new BunJS + HonoJS project:

bun create hono sheetjs-hono --template bun --install --pm bun
cd sheetjs-hono

安装 SheetJS BunJS 模块：

¥Install the SheetJS BunJS module:

bun i xlsx@https://sheet.lol/balls/xlsx-0.20.3.tgz

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

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

src/index.ts
import { Hono } from 'hono';
import { read, write, utils } from 'xlsx';

const app = new Hono();
let data = ["SheetJS".split(""), [5,4,3,3,7,9,5]];

app.get('/export', (c) => {
  const ws = utils.aoa_to_sheet(data);
  const wb = utils.book_new(ws, "SheetJSHono");
  const buf = write(wb, { type: "buffer", bookType: "xlsx" });
  c.header('Content-Disposition', 'attachment; filename="SheetJSHonoJS.xlsx"');
  c.header('Content-Type', 'application/vnd.ms-excel');
  return c.body(buf);
});

app.post('/import', async(c) => {
  const body = await c.req.parseBody();
  const file = body["upload"];
  const ab = await file.arrayBuffer();
  const wb = read(ab);
  data = utils.sheet_to_json(wb.Sheets[wb.SheetNames[0]], { header:1 });
  return c.text(utils.sheet_to_csv(wb.Sheets[wb.SheetNames[0]]));
});

app.get('/json', (c) => c.json(data));
export default app;

运行服务器：

¥Run the server:

bun run dev

该过程将显示一个 URL（通常为 http://localhost:3000）：

¥The process will display a URL (typically http://localhost:3000):

% bun run dev
$ bun run --hot src/index.ts
Started server http://localhost:3000

通过在浏览器中打开 http://localhost:3000/export 来测试导出。

¥Test exports by opening http://localhost:3000/export in your browser.

页面应尝试下载 SheetJSHonoJS.xlsx。保存下载并打开新文件。内容应与原始数据一致：

¥The page should attempt to download SheetJSHonoJS.xlsx . Save the download and open the new file. The contents should match the original data:

S	h	e	e	t	J	S
5	4	3	3	7	9	5

使用 https://xlsx.nodejs.cn/pres.numbers 测试导入。命令应在新终端窗口中运行：

¥Test imports 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/import

终端将显示从第一个工作表生成的 CSV 行：

¥The terminal will display CSV rows generated from the first worksheet:

Expected output
Name,Index
Bill Clinton,42
GeorgeW Bush,43
Barack Obama,44
Donald Trump,45
Joseph Biden,46

确认通过加载 http://localhost:3000/json 更新了状态：

¥Confirm the state was updated by loading http://localhost:3000/json :

curl -LO http://localhost:3000/json

终端将在数组数组中显示工作表数据：

¥The terminal will display the worksheet data in an array of arrays:

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

[^1]: 有关 HonoJS 文档中的 "parseBody()"。

¥See "parseBody()" in the HonoJS documentation.

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

¥See read in "Reading Files"

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

¥See sheet_to_csv 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"

集成详情​

读取数据​

写入数据​

完整示例​

集成详情

读取数据

写入数据

完整示例