Send an Excel File as an attachment via Postmark Template API in NestJS.

Data is important to a business's success as it provides valuable insights into customer behaviour and preferences. This empowers organizations by helping them better understand their customers, influence product decisions, and identify areas for improvement. Data export is a common product feature that helps provide the required data today. Excel files are the most commonly exported file type.

This article will walk you through how to use NestJS to handle an export data request, excel file generation and email delivery. The request generates an Excel file that is sent as an email attachment through the Postmark template API. The article assumes you have an existing Nestjs application with bull queues already configured and a postmark account with API access.

The first step is to install the required packages which are exceljs and postmark.

npm install exceljs
npm install postmark

Create your report dto report.dto.ts.

import { IsNotEmpty } from 'class-validator';

export class ReportDto {
  @IsNotEmpty()
  startDate: Date;

  @IsNotEmpty()
  endDate: Date;
}

Update your controller to handle the request and response.

//report.controller.ts

  @Post('/')
  @HttpCode(HttpStatus.OK)
  async getReport(@Body() payload: ReportDto) {
    const response = await this.service.createReport(payload);
    return { message: response };
  }

The next step is to update the service and create the createReport function that is called on the report.controller.ts.

//report.service.ts

import { Injectable } from '@nestjs/common';
import { ReportDto } from './dto/report.dto';
import { InjectQueue } from '@nestjs/bull';
import { Queue } from 'bull';

@Injectable()
export class ReportService {
  constructor(@InjectQueue('report') private queue: Queue) {}

  async createReport(payload: ReportDto) {
    const startDate = new Date(payload.startDate);
    const endDate = new Date(payload.endDate);
    startDate.setHours(0, 0, 0, 0);
    endDate.setHours(23, 59, 59, 999);

    const queueData = {
      startDate: startDate,
      endDate: endDate,
    };

    await this.queue.add('report.generate-user-report', queueData);
    return 'Your report is on its way to your inbox';
  }
}

The code above converts the payload dates to the start of the day for the startDate and the end of the day for endDate. The converted dates are sent as data for the queue.

Here is the consumer class that handles the report queue.

//report.consumer.ts
import {
  OnQueueActive,
  OnQueueCompleted,
  Process,
  Processor,
} from '@nestjs/bull';
import { ConfigService } from '@nestjs/config';
import { Job } from 'bull';
import * as ExcelJS from 'exceljs';
import * as Postmark from 'postmark';
import { PrismaService } from '../prisma/prisma.service';

@Processor('report')
export class ReportConsumer {
  private readonly client = new Postmark.ServerClient(
    this.configService.getOrThrow<string>('POSTMARK_SECRET'),
  );

  constructor(
    private readonly prismaService: PrismaService,
    private readonly configService: ConfigService,
  ) {}

  @OnQueueActive()
  onActive(job: Job) {
    console.log(`Processing job ${job.id} of type ${job.name}`);
  }

  @OnQueueCompleted()
  onCompleted(job: Job) {
    console.log(`Processed job ${job.id} of type ${job.name}`);
  }
}

Add the generateUserReport to handle the report generation.

//report.consumer.ts

 @Process('report.generate-user-report')
  async generateUserReport(job: {
    data: {
      startDate: Date;
      endDate: Date;
    };
  }) {
    const { startDate, endDate } = job.data;
    // get registered user within the provided time frame
    const users = await this.prismaService.user.findMany({
      select: {
        firstname: true,
        lastname: true,
        email: true,
        createdAt: true,
      },
      where: {
        createdAt: {
          gt: startDate,
          lte: endDate,
        },
      },
    });

    //generate rows for the excel sheet
    const userRows = users.map((user) => {
      return {
        firstname: user.firstname,
        lastname: user.lastname,
        email: user.email,
        signup_date: user.createdAt,
      };
    });

    // create excel workbook and add headers
    const workbook = new ExcelJS.Workbook();
    const worksheet = workbook.addWorksheet('Users Report');
    worksheet.columns = [
      { header: 'firstname', key: 'firstname', width: 32 },
      { header: 'lastname', key: 'lastname', width: 32 },
      { header: 'Email', key: 'email', width: 30 },
      { header: 'Signup Date', key: 'signup_date', width: 10 },
    ];

    worksheet.addRows(userRows);

    // writes the workbook to a buffer
    const report = await workbook.xlsx.writeBuffer();

    // send the mail to user
    await this.sendMail(report, startDate, endDate);
  }

The next step is to add the function for sending the email.

async sendMail(report: ExcelJS.Buffer, startDate: Date, endDate: Date) {
    try {
      // data for the postmark template
      const emailData = {
        startDate: new Date(startDate).toISOString().split('T')[0],
        endDate: new Date(endDate).toISOString().split('T')[0],
      };

      // send an email with the generated sheet as an attachment
      const sendMail = await this.client.sendEmailWithTemplate({
        From: this.configService.getOrThrow<string>('ADMIN_EMAIL'),
        To: '[e-mail]',
        TemplateAlias: this.configService.getOrThrow<string>(
          'POSTMARK_TEMPLATE__REPORT',
        ),
        TemplateModel: emailData,
        Attachments: [
          {
            Name: 'user_report.xlsx',
            ContentID: new Date().toString(),
            Content: Buffer.from(report).toString('base64'),
            ContentType:
              'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
          },
        ],
      });

      if (sendMail.ErrorCode !== 0) {
        throw new Error('Message failed to deliver');
      }
    } catch (error) {
      console.log(error?.message || error);
    }
  }

The final step is to update your env file with the required variables.

POSTMARK_TEMPLATE__REPORT=test-user-report
POSTMARK_SECRET=********-****-****-****-*******
ADMIN_EMAIL=[e-mail]

The postmark template value is the alias of the templatecreated on Postmark, while the secret is the Postmark API key.

This article gives a top-level implementation for the feature. It can always be optimized to improve performance and meet specific needs.