NestJS – Data Export

NestJS

Data Export (Excel/CSV)

Generating and exporting data to Excel (.xlsx) formats using the exceljs package.

Export Service: Generating the Excel workbook and writing it to a buffer.

📄export.service.ts
typescript

import { Injectable } from '@nestjs/common';
import * as ExcelJS from 'exceljs';

@Injectable()
export class ExportService {
  async generateExcelReport(data: any[]): Promise<Buffer> {
    const workbook = new ExcelJS.Workbook();
    const worksheet = workbook.addWorksheet('Users Report');

    // Define columns
    worksheet.columns = [
      { header: 'ID', key: 'id', width: 10 },
      { header: 'Name', key: 'name', width: 30 },
      { header: 'Email', key: 'email', width: 40 },
      { header: 'Registration Date', key: 'createdAt', width: 25 },
    ];

    // Make header row bold
    worksheet.getRow(1).font = { bold: true };

    // Add data rows
    data.forEach((item) => {
      worksheet.addRow({
        id: item.id,
        name: item.name,
        email: item.email,
        createdAt: item.createdAt ? new Date(item.createdAt).toLocaleDateString() : 'N/A',
      });
    });

    // Write to a buffer and return
    const buffer = await workbook.xlsx.writeBuffer();
    return buffer as Buffer;
  }
}

Export Controller: Setting HTTP headers to stream the Excel file as a downloadable attachment.

📄export.controller.ts
typescript

import { Controller, Get, Res } from '@nestjs/common';
import { Response } from 'express';
import { ExportService } from './export.service';

@Controller('export')
export class ExportController {
  constructor(private readonly exportService: ExportService) {}

  @Get('users')
  async exportUsers(@Res() res: Response) {
    // Mock data, usually fetched from a database
    const users = [
      { id: 1, name: 'Alice Smith', email: 'alice@example.com', createdAt: '2023-01-15' },
      { id: 2, name: 'Bob Jones', email: 'bob@example.com', createdAt: '2023-03-22' },
      { id: 3, name: 'Charlie Brown', email: 'charlie@example.com', createdAt: '2023-05-10' },
    ];

    const excelBuffer = await this.exportService.generateExcelReport(users);

    // Set appropriate headers for file download
    res.setHeader(
      'Content-Type',
      'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
    );
    res.setHeader(
      'Content-Disposition',
      'attachment; filename=' + 'users-report.xlsx',
    );
    res.setHeader('Content-Length', excelBuffer.length.toString());

    // Send the buffer to the client
    res.send(excelBuffer);
  }
}