FastifyJS 中的工作表

FastifyJS 是一个 NodeJS Web 框架。

¥FastifyJS is a NodeJS web framework.

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

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

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

¥This demo uses FastifyJS 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 日使用 fastify@4.26.2 进行了验证

¥This demo was verified on 2024 March 11 using fastify@4.26.2

集成详情

¥Integration Details

SheetJS NodeJS 模块可以从使用 FastifyJS 的脚本导入。

¥The SheetJS NodeJS module can be imported from scripts that use FastifyJS.

将数据导出到工作簿 (GET)

¥Exporting Data to Workbooks (GET)

带有选项 type: "buffer" 的 SheetJS write 方法 [^1] 生成包含原始文件数据的 NodeJS Buffer 对象。

¥The SheetJS write method[^1] with the option type: "buffer" generates NodeJS Buffer objects containing the raw file data.

FastifyJS 可以直接处理 Response#end 中的 Buffer 数据

¥FastifyJS can directly handle Buffer data in Response#end

导出的文件名可以使用 Content-Disposition 标头指定。

¥The exported filename can be specified using the Content-Disposition header.

以下演示 FastifyJS 服务器将使用 XLSX 电子表格响应对 /download 的 GET 请求。在此示例中，SheetJS aoa_to_sheet 方法 [^2] 生成工作表对象，book_new 和 book_append_sheet 辅助程序 [^3] 构建工作簿对象。

¥The following demo FastifyJS server will respond to GET requests to /download with a XLSX spreadsheet. In this example, the SheetJS aoa_to_sheet method[^2] generates a sheet object and the book_new and book_append_sheet helpers[^3] build the workbook object.

/* GET / returns a workbook */
fastify.get('/', (req, reply) => {
  /* make a workbook */
  var wb = XLSX.read("S,h,e,e,t,J,S\n5,4,3,3,7,9,5", {type: "binary"});

  /* write to Buffer */
  const buf = XLSX.write(wb, {type:"buffer", bookType: "xlsx"});

  /* set Content-Disposition header and send data */
  reply.header('Content-Disposition', 'attachment; filename="SheetJSFastify.xlsx"').send(buf);
});

解析上传的文件（POST）

¥Parsing Uploaded Files (POST)

@fastify/multipart 在底层使用 busbuy，必须注册：

¥@fastify/multipart, which uses busbuy under the hood, must be registered:

/* load SheetJS Library */
const XLSX = require("xlsx");
/* load fastify and enable body parsing */
const fastify = require('fastify')({logger: true});
fastify.register(require('@fastify/multipart'), { attachFieldsToBody: true });

一旦注册了选项 attachFieldsToBody，路由处理程序就可以直接使用 req.body。

¥Once registered with the option attachFieldsToBody, route handlers can use req.body directly.

主体中的每个文件对象都有一个解析为 Buffer 对象的 toBuffer 方法。SheetJS read 方法 [^4] 可以读取 Buffer 并生成工作簿对象 [^5]。

¥Each file object in the body has a toBuffer method that resolves to a Buffer object. The SheetJS read method[^4] can read the Buffer and generate a workbook object[^5].

以下演示 FastifyJS 服务器将响应对 /upload 的 POST 请求。假设表单数据的 upload 字段是文件，SheetJS read 方法将解析该文件。CSV 行是使用 SheetJS sheet_to_csv 方法 [^6] 从第一个工作表生成的。

¥The following demo FastifyJS server will respond to POST requests to /upload. Assuming the upload field of the form data is the file, the SheetJS read method will parse the file. CSV rows are generated from the first worksheet using the SheetJS sheet_to_csv method[^6].

/* POST / reads submitted file and exports to requested format */
fastify.post('/', async(req, reply) => {
  /* "file" is the name of the field in the HTML form*/
  const file = req.body.upload;
  /* toBuffer returns a promise that resolves to a Buffer */
  const buf = await file.toBuffer();
  /* `XLSX.read` can read the Buffer */
  const wb = XLSX.read(buf);
  /* reply with a CSV */
  reply.send(XLSX.utils.sheet_to_csv(wb.Sheets[wb.SheetNames[0]]));
});

开箱即用，Fastify 在处理大型电子表格 (statusCode 413) 时将返回错误 FST_ERR_CTP_BODY_TOO_LARGE。这是一个 Fastify 问题。

¥Out of the box, Fastify will return an error FST_ERR_CTP_BODY_TOO_LARGE when processing large spreadsheets (statusCode 413). This is a Fastify issue.

默认正文大小限制（包括所有上传的文件和字段）为 1 MB。可以通过在服务器创建期间设置 bodyLimit 选项来增加它：

¥The default body size limit (including all uploaded files and fields) is 1 MB. It can be increased by setting the bodyLimit option during server creation:

/* increase request body size limit to 5MB = 5 * 1024 * 1024 bytes */
const fastify = require('fastify')({bodyLimit: 5 * 1024 * 1024});

完整示例

¥Complete Example

将以下代码片段保存到 SheetJSFastify.js：

¥Save the following snippet to SheetJSFastify.js:

SheetJSFastify.js
/* load SheetJS Library */
const XLSX = require("xlsx");
/* load fastify and enable body parsing */
const fastify = require('fastify')({logger: true});
fastify.register(require('@fastify/multipart'), { attachFieldsToBody: true });

/* GET / returns a workbook */
fastify.get('/', (req, reply) => {
  /* make a workbook */
  var wb = XLSX.read("S,h,e,e,t,J,S\n5,4,3,3,7,9,5", {type: "binary"});

  /* write to Buffer */
  const buf = XLSX.write(wb, {type:"buffer", bookType: "xlsx"});

  /* set Content-Disposition header and send data */
  reply.header('Content-Disposition', 'attachment; filename="SheetJSFastify.xlsx"').send(buf);
});

/* POST / reads submitted file and exports to requested format */
fastify.post('/', async(req, reply) => {

  /* "file" is the name of the field in the HTML form*/
  const file = req.body.upload;

  /* toBuffer returns a promise that resolves to a Buffer */
  const wb = XLSX.read(await file.toBuffer());

  /* send back a CSV */
  reply.send(XLSX.utils.sheet_to_csv(wb.Sheets[wb.SheetNames[0]]));
});

/* start */
fastify.listen({port: process.env.PORT || 3000}, (err, addr) => { if(err) throw err; });

安装依赖：

¥Install dependencies:

npm i --save https://cdn.sheetjs.com/xlsx-0.20.3/xlsx-0.20.3.tgz fastify@4.26.2 @fastify/multipart@8.1.0

启动服务器

¥Start server

node SheetJSFastify.js

使用 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/

响应应以 CSV 行显示数据。

¥The response should show the data in CSV rows.

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

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

应该会提示下载 SheetJSFastify.xlsx

¥It should prompt to download SheetJSFastify.xlsx

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

¥See write in "Writing Files"

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

¥See aoa_to_sheet in "Utilities"

[^3]: 有关 book_new 和 book_append_sheet 的详细信息，请参阅 "工作簿助手" 于 "实用工具"。

¥See "Workbook Helpers" in "Utilities" for details on book_new and book_append_sheet.

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

¥See readFile in "Reading Files"

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

¥See "Workbook Object"

[^6]: 见 sheet_to_csv 于 "CSV 和文本"

¥See sheet_to_csv in "CSV and Text"

集成详情​

将数据导出到工作簿 (GET)​

解析上传的文件（POST）​

完整示例​

集成详情

将数据导出到工作簿 (GET)

解析上传的文件（POST）

完整示例