Sheets in NestJS

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

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

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.

Tested Deployments

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

Integration Details

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

NestJS does not follow the conventional NodeJS server code structure.

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

Exporting Data to Workbooks (GET)

The SheetJS write method¹ with the option type: "buffer" generates NodeJS Buffer objects containing the raw file data.

NestJS strongly recommends wrapping the Buffer in a StreamableFile object².

The exported filename can be specified in a @Header decorator³

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⁴ generates a sheet object and the book_new and book_append_sheet helpers⁵ 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);
  }
}

Parsing Uploaded Files (POST)

The NestJS documentation has detailed instructions for file upload support.

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.

The file object has a buffer property which represents the raw bytes. The SheetJS read method⁶ can parse the Buffer into a workbook object⁷.

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⁸.

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

1. Create a new NestJS project:

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

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

npm i --save-dev @types/multer

3. Install the SheetJS library:

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

4. Make a folder for uploaded files:

mkdir -p upload

5. Create a new controller:

npx @nestjs/cli generate controller sheetjs

6. 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);
  }
}

7. Start the server with

npx @nestjs/cli start

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) {
                                                       ~~~~~~

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

This is a bug in the npm client

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

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

8. Test POST requests using https://docs.sheetjs.com/pres.numbers . The commands should be run in a new terminal window:

curl -LO https://docs.sheetjs.com/pres.numbers
curl -X POST -F upload=@pres.numbers http://localhost:3000/sheetjs/upload

The response should show the data in CSV rows.

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

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

Footnotes

1. See write in "Writing Files" ↩

2. See "Streaming files" in the NestJS documentation. ↩

3. See "Headers" in the NestJS documentation. ↩

4. See aoa_to_sheet in "Utilities" ↩

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

6. See read in "Reading Files" ↩

7. See "Workbook Object" ↩

8. See sheet_to_csv in "CSV and Text" ↩