NestJS 中的工作表

NestJS 是一个用于构建服务器端应用的 NodeJS 框架。

¥NestJS is a NodeJS framework for building server-side applications.

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

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

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

¥This demo uses NestJS 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 日使用 NestJS 10.3.3 进行了测试。

¥This demo was tested on 2024 March 11 using NestJS 10.3.3.

集成详情

¥Integration Details

SheetJS NodeJS 模块可以从 NestJS 控制器脚本导入。

¥The SheetJS NodeJS module can be imported from NestJS controller scripts.

NestJS 不遵循传统的 NodeJS 服务器代码结构。

¥NestJS does not follow the conventional NodeJS server code structure.

强烈建议查看官方文档。官方文档涵盖了 Controller 结构和各种 JS 装饰器。

¥It is strongly recommended to review the official documentation. The official documentation covers Controller structure and various JS Decorators.

将数据导出到工作簿 (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.

NestJS 强烈建议将 Buffer 封装在 StreamableFile 对象 [^2] 中。

¥NestJS strongly recommends wrapping the Buffer in a StreamableFile object[^2].

导出的文件名可以在 @Header 装饰器 [^3] 中指定

¥The exported filename can be specified in a @Header decorator[^3]

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

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

src/sheetjs/sheetjs.controller.js
import { Controller, Get, Header, StreamableFile } from '@nestjs/common';
import { utils, write } from 'xlsx';

@Controller('sheetjs')
export class SheetjsController {
  @Get('download')
  @Header('Content-Disposition', 'attachment; filename="SheetJSNest.xlsx"')
  async downloadXlsxFile(): Promise<StreamableFile> {
    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 a streamable file */
    return new StreamableFile(buf);
  }
}

解析上传的文件（POST）

¥Parsing Uploaded Files (POST)

NestJS 文档有文件上传支持的详细说明。

¥The NestJS documentation has detailed instructions for file upload support.

正如 NestJS 文档中所解释的，在 POST 请求处理程序中，FileInterceptor 拦截器和 UploadedFile 装饰器串联使用来公开使用指定密钥上传的文件。

¥As explained in the NestJS documentation, in a POST request handler, the FileInterceptor interceptor and UploadedFile decorator are used in tandem to expose a file uploaded with a specified key.

文件对象有一个 buffer 属性，表示原始字节。SheetJS read 方法 [^6] 可以将 Buffer 解析为工作簿对象 [^7]。

¥The file object has a buffer property which represents the raw bytes. The SheetJS read method[^6] can parse the Buffer into a workbook object[^7].

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

¥The following demo NestJS Controller 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[^8].

src/sheetjs/sheetjs.controller.js
import { Controller, Post, UploadedFile, UseInterceptors } from '@nestjs/common';
import { FileInterceptor } from '@nestjs/platform-express';
import { read, utils } from 'xlsx';

@Controller('sheetjs')
export class SheetjsController {
  @Post('upload') //  <input type="file" id="upload" name="upload">
  @UseInterceptors(FileInterceptor('upload'))
  async uploadXlsxFile(@UploadedFile() file: Express.Multer.File) {
    /* file.buffer is a Buffer */
    const wb = read(file.buffer);
    /* generate CSV of first worksheet */
    return utils.sheet_to_csv(wb.Sheets[wb.SheetNames[0]]);
  }
}

完整示例

¥Complete Example

创建一个新的 NestJS 项目：

¥Create a new NestJS project:

npx @nestjs/cli@latest new -p npm sheetjs-nest
cd sheetjs-nest

安装 @types/multer 包作为开发依赖：

¥Install the @types/multer package as a development dependency:

npm i --save-dev @types/multer

安装 SheetJS 库：

¥Install the SheetJS library:

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

为上传的文件创建一个文件夹：

¥Make a folder for uploaded files:

mkdir -p upload

创建一个新的控制器：

¥Create a new controller:

npx @nestjs/cli generate controller sheetjs

将 src/sheetjs/sheetjs.controller.ts 替换为以下代码块：

¥Replace src/sheetjs/sheetjs.controller.ts with the following code block:

src/sheetjs/sheetjs.controller.ts
import { Controller, Get, Header, Post, StreamableFile, UploadedFile, UseInterceptors } from '@nestjs/common';
import { FileInterceptor } from '@nestjs/platform-express';
import { read, utils, write } from 'xlsx';

@Controller('sheetjs')
export class SheetjsController {
  @Post('upload') //  <input type="file" id="upload" name="upload">
  @UseInterceptors(FileInterceptor('upload'))
  async uploadXlsxFile(@UploadedFile() file: Express.Multer.File) {
    /* file.path is a path to the workbook */
    const wb = read(file.buffer);
    /* generate CSV of first worksheet */
    return utils.sheet_to_csv(wb.Sheets[wb.SheetNames[0]]);
  }

  @Get('download')
  @Header('Content-Disposition', 'attachment; filename="SheetJSNest.xlsx"')
  async downloadXlsxFile(): Promise<StreamableFile> {
    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 a streamable file */
    return new StreamableFile(buf);
  }
}

启动服务器

¥Start the server with

npx @nestjs/cli start

在最近的测试中，该过程失败并出现一条引用 Multer 的消息：

¥In the most recent test, the process failed with a message referencing Multer:

src/sheetjs/sheetjs.controller.ts:9:54 - error TS2694: Namespace 'global.Express' has no exported member 'Multer'.

9   async uploadXlsxFile(@UploadedFile() file: Express.Multer.File) {
                                                       ~~~~~~

此错误表明 @types/multer 不可用。

¥This error indicates that @types/multer is not available.

这是 npm 客户端的一个 bug

¥This is a bug in the npm client

建议的修复方法是再次安装 @types/multer：

¥The recommended fix is to install @types/multer again:

npm i --save-dev @types/multer
npx @nestjs/cli start

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

响应应以 CSV 行显示数据。

¥The response should show the data in CSV rows.

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

¥Test GET requests by opening http://localhost:3000/sheetjs/download in a web browser.

浏览器应尝试下载 SheetJSNest.xlsx。保存文件并在电子表格编辑器中打开。

¥The browser should attempt to download SheetJSNest.xlsx. Save the file and open in a spreadsheet editor.

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

¥See write in "Writing Files"

[^2]: 请参阅 NestJS 文档中的 "流式文件"。

¥See "Streaming files" in the NestJS documentation.

[^3]: 请参阅 NestJS 文档中的 "标头"。

¥See "Headers" in the NestJS documentation.

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

¥See aoa_to_sheet in "Utilities"

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

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