Setup exceljs library in nestjs framework

setup-exceljs-library-in-nestjs-framework.sh

#1  Install dependecies :
    - fs-extra
    - exceljs
    - multer
    - @types/multer
    - lodash
    - class-validator
    - class-transformer
    - rxjs
    
#2  Setup types
    a.  Create src/core/types/string-number-bigint-object.types.ts :
        //file: string-number-bigint-object.types.ts--------------------------------------
        export type StringNumberBigintObject = 'string' | 'number' | 'bigint' | 'Object' ;
        //file: string-number-bigint-object.types.ts--------------------------------------

#3  Setup DTO for your project
    a.  Create  src/modules/your-module/your-module-sheets.dto.ts :
        //file: your-module-sheets.dto.ts----------------------------------------------------------------
        import { IsNumber, IsObject, IsString, ValidateNested } from "class-validator";
        import { Type } from "class-transformer";
        import { StringNumberBigintObject } from "../../../core/types/string-number-bigint-object.types";

        export class YourModuleSheetsDto {
          @IsString()
          name: string;

          @IsObject()
          @ValidateNested()
          @Type(() => HeaderDto)
          header: {
            rows: number
          };

          @IsObject()
          columnToKey: { [key: string]: string };

          @IsObject()
          @ValidateNested()
          @Type(() => ColumnDto)
          columns: { [key: string]: ColumnDto };
        }

        export class HeaderDto {
          @IsNumber()
          rows: number;
        }

        export class ColumnDto {
          dataType: StringNumberBigintObject;
          maxLength?: number;
        }
        //file: your-module-sheets.dto.ts----------------------------------------------------------------

    b.  Create src/modules/your-module/dtos/your-module.dto.ts:
        //file : items-your-module.dto.ts-----------------------------------------------------------------
        import { Type } from "class-transformer";
        import { IsDate, IsNotEmpty, IsNumber, IsString, IsUUID } from "class-validator";
        import { randomUUID } from "crypto";
        import { StringNumberBigintObject } from "src/core/types/string-number-bigint-object.types";

        export class ItemsYourModuleDto {
          @IsNotEmpty()
          @IsUUID()
          id: string = randomUUID();

          @IsNotEmpty()
          @Type(()=> String)
          @IsString()
          customerName: string;

          @IsNotEmpty()
          @Type(()=> String)
          @IsString()
          aircraftRegistration: string;

          @Type(()=> String)
          @IsString()
          aircraftType: string = null;

          @Type(()=> String)
          @IsString()
          apuSn: string = null;

          @IsNumber()
          apuNegativeScore: number = null;

          @IsDate()
          createdAt: Date = null;

          static propertyConfig: 
            Partial<Record<keyof ItemsYourModuleDto, { dataType: StringNumberBigintObject; maxLength?: number }>> = {
              aircraftRegistration: { dataType: 'string', maxLength: 15 },
              aircraftType: { dataType: 'string', maxLength: 30 },
              apuSn: { dataType: 'string', maxLength: 30 },
          };

          static propertyNames: (keyof ItemsYourModuleDto)[] = [
            'aircraftRegistration',
            'aircraftType',
            'apuSn',
          ];
        }

    c.  Create src/modules/your-module/dtos/read-your-module.dto.ts:
        //file : read-your-module.dto.ts-----------------------------
        import { ItemsYourModuleDto } from "./items-your-module.dto";

        export class ReadYourModuleSheetDto {
          yourModule: ItemsYourModuleDto[];
        }
        //file : read-your-module.dto.ts-----------------------------

#4  Setup errors
    a.  Create src/core/errors/invalid-data.error.ts :
        //file : invalid-data.error.ts-----------------------
        export class InvalidDataError extends Error {
          constructor(columnName: string, reason: string) {
            super(`Column ${columnName}*, ${reason}`);
            this.name = 'InvalidDataError';
          }
        }

        export class MessagesInvalidDataError extends Error {
          errors: { sheetName: string; invalidColumn: {[key: string]: string}[] }[];

          constructor(errors: { sheetName: string; invalidColumn: {[key: string]: string}[] }[]) {
            const errorMessage = "Invalid data in one or more sheets";
            super(errorMessage);
            this.name = 'MessagesInvalidDataError';
            this.errors = errors;
          }
        }
        //file : invalid-data.error.ts-----------------------

#5  Setup utils for your project
    a.  Create src/core/utils/read-excel-sheet-your-module-builder.util.ts:
        //file: read-excel-sheet-your-module-builder.util.ts--------------------------------------------------------
        import { camelCase } from 'lodash';
        import { YourModuleSheetsDto, HeaderDto } from 'src/modules/your-module/dtos/your-module-sheets.dto';
        import { StringNumberBigintObject } from '../../types/string-number-bigint-object.types';
        import { Injectable } from '@nestjs/common';

        @Injectable() 
        export class ReadExcelSheetYourModuleBuilder {
          private sheet: YourModuleSheetsDto;

          getSheetName(name:string): this {
            this.sheet = new YourModuleSheetsDto();
            this.sheet.name = name;
            return this
          }

          ignoreHeaderRow(rows: number = 1): this {
            const header = new HeaderDto();
            header.rows = rows;
            this.sheet.header = header;
            return this;
          }

          setSheetNameToJsonFields(columns: string[]): this {
            const columnToKey: Record<string, string> = {};
            columns.forEach((col, index): void => {
              columnToKey[String.fromCharCode(65 + index)] = camelCase(col);
            });

            this.sheet.columnToKey = columnToKey;
            return this;
          }

          setColumnPropertyToJsonFields(columns: { [key: string]: { dataType: StringNumberBigintObject; maxLength?: number } }): this {
            this.sheet.columns = columns;
            return this;
          }

          build(): YourModuleSheetsDto {
            return this.sheet;
          }
        }
        
    b.  Create src/core/utils/your-module-process-excel-to-json-builder.util.ts:
        //file: your-module-process-excel-to-json-builder.util.ts--------------------------------------------------------
        import { Cell, CellValue, Row, Workbook, Worksheet } from 'exceljs';
        import { randomUUID } from 'crypto';
        import { ColumnDto, DataSheetsDto } from 'src/modules/your-module/dtos/your-module-sheets.dto';
        import { MessagesInvalidDataError } from '../../errors/invalid-data.error';
        import { BadRequestException, Injectable } from '@nestjs/common';
        import { Request } from 'express';
        import { ReadYourModuleSheetDto } from 'src/modules/your-module/dtos/read-your-module-sheet.dto';
        import { ItemsYourModuleDto } from 'src/modules/your-module/dtos/items-your-module.dto';

        @Injectable() 
        export class YourModuleProcessExcelToJsonBuilder {
          private filePath: string;
          private sheets: YourModuleSheetsDto[];
          private errorSheetName: string[] = [];
          private errorMessages: {[key: string]: string}[] = [];

          constructor() {
            super(YourModuleProcessExcelToJsonBuilder?.name);
          }

          getFile(filePath: string): this {
            this.filePath = filePath;
            this.sheets = [];
            this.errorSheetName = [];
            this.errorMessages = [];
            return this
          }

          addSheet(sheet: YourModuleSheetsDto): this {
            this.sheets.push(sheet);
            return this;
          }

          static validateCellData(cellValue: CellValue, dataType: string, maxLength?: number): boolean {
            if (dataType === 'string' && typeof cellValue !== 'string') return false;
            if (dataType === 'number' && (typeof cellValue !== 'number' || isNaN(cellValue))) return false;
            if (maxLength && cellValue.toLocaleString().length > maxLength) return false;
            return true;
          }

          processCell(cell: Cell, cellIndex: number, rowData: Record<string, CellValue>, sheetConfig: YourModuleSheetsDto): this {
            const columnName: string = sheetConfig.columnToKey[String.fromCharCode(64 + cellIndex)];
            if (!columnName) return;

            const columnConfig: ColumnDto = sheetConfig.columns[columnName];
            if (!columnConfig) return;

            const { dataType, maxLength }: ColumnDto = columnConfig;
            const cellValue: CellValue = cell?.value['result'] || cell?.value['error'] || cell?.value;

            // console.log(`column-name : ${columnName}`);
            // console.log(`value : ${cellValue}`);
            // console.log(`type: ${typeof cellValue}`);
            // console.log("______________________________ ");

            if (Object(cellValue).toString().startsWith('Invalid')) {
              this.errorSheetName.push(sheetConfig.name);
              this.errorMessages.push({[`column[${String.fromCharCode(64 + cellIndex)}]`]: `${columnName} cell format categories must be general* on row ${cell.row}`});
              return this;
            }

            if (!YourModuleProcessExcelToJsonBuilder.validateCellData(cellValue, dataType, maxLength)) {
              this.errorSheetName.push(sheetConfig.name);
              this.errorMessages.push({[`column[${String.fromCharCode(64 + cellIndex)}]`]: `${columnName} must be of type ${dataType === 'Object' ? 'string' : dataType}* or length limit is ${maxLength}* on row ${cell.row}`});
              return this;
            }

            rowData[columnName] = cellValue;
            return this;
          }

          readSheetData<T>(
            worksheet: Worksheet, 
            headerRows: number, 
            sheetConfig: YourModuleSheetsDto, 
            processCellFn: (cell: Cell, cellIndex: number, rowData: Record<string, CellValue>, sheetConfig: YourModuleSheetsDto) => this
            ): T[] {
            const rows: T[] = [];

            worksheet.eachRow((row: Row, rowIndex: number): void => {
              if (rowIndex > headerRows) {
                const rowData: Record<string, CellValue> = {};
                row.eachCell((cell: Cell, cellIndex: number): void => {
                  processCellFn(cell, cellIndex, rowData, sheetConfig);
                });
                rows.push(rowData as T);
              }
            });

            return rows;
          }

          textToCamelCase(text: string): string {
            const words: string[] = text.split(" ");
            const camelCaseWords: string[] = [words[0].toLowerCase()];

            for (let i = 1; i < words.length; i++) {
                camelCaseWords.push(words[i].charAt(0).toUpperCase() + words[i].slice(1).toLowerCase());
            }

            return camelCaseWords.join("");
          }

          async build<T>(req: Request): Promise<ReadYourModuleSheetDto> {
            const customerName: string = req?.body["customerName"];
            const workbook: Workbook = new Workbook();
            await workbook.xlsx.readFile(this.filePath);

            const data: ReadYourModuleSheetDto = {
              yourModule: null,
            };

            const $dataSheets: Observable<YourModuleSheetsDto> = of(this.sheets).pipe(
              mergeMap((items) => items),
              mergeMap((sheet) => {
                const { name, header } = sheet;
                const worksheet: Worksheet = workbook.getWorksheet(name);
                if (worksheet) {
                  const rows = this.readSheetData(worksheet, header.rows, sheet, this.processCell.bind(this));
                  data[this.textToCamelCase(name)] = rows;
                }
                return of(sheet);
              })
            );
            await lastValueFrom($dataSheets);

            if (this.errorSheetName.length > 0 && this.errorMessages.length > 0) {
              const uniqueSheetNames: string[] = [...new Set(this.errorSheetName)];
              const errors = uniqueSheetNames.map((sheetName) => {
                const sheetErrorMessages: {[key: string]: string}[] = this.errorMessages
                  .map((msg, index) => this.errorSheetName[index] === sheetName ? msg : null)
                  .filter(Boolean);
                return { sheetName, invalidColumn: sheetErrorMessages };
              });

              throw new MessagesInvalidDataError(errors);
            }

            return data;
          }
        }
        //file: read-excel-sheet-your-module-builder.util.ts--------------------------------------------------------

#6. Setup services for your project
    a.  Create src/modules/your-module/services/your-module.service.ts:
        //file : your-module.service.ts-----------------------------------------------------------------------------------
        import { Injectable, BadRequestException } from '@nestjs/common';
        import { Request } from 'express';
        import * as fs from 'fs-extra';
        import { catchError, lastValueFrom, mergeMap, of } from 'rxjs';
        import { YourModuleSheetsDto } from '../dtos/your-module-sheets.dto';
        import { ItemsYourModuleDto } from '../dtos/items-your-module.dto';
        import { MessagesInvalidDataError } from 'src/core/errors/invalid-data.error';
        import { ReadExcelSheetYourModuleBuilder } from 'src/core/utils/read-excel-sheet-your-module-builder.util';
        import { EngineApuProcessExcelToJsonBuilder } from 'src/core/utils/your-module-process-excel-to-json-builder.util';
        import { ReadYourModuleSheetDto } from '../dtos/read-your-module-sheet.dto';

        @Injectable() 
        export class ExcelYourModuleService {

          constructor(
            private readonly readYourModuleBuilder: ReadExcelSheetYourModuleBuilder,
            private readonly processExcelToJsonBuilder: YourModuleProcessExcelToJsonBuilder,
          ) {
            super(ExcelYourModuleService?.name);
          }

          async readFormatExcel (req: Request): Promise<ReadYourModuleSheetDto> {
            try {

              const yourModuleSheet: YourModuleSheetsDto = this.readExcelSheetBuilder
                .getSheetName('your sheetname')
                .ignoreHeaderRow()
                .setSheetNameToJsonFields(ItemsYourModuleDto.propertyNames)
                .setColumnPropertyToJsonFields(ItemsYourModuleDto.propertyConfig)
                .build();

              const filePath: string = req?.file?.path;
              const data = of(filePath).pipe(
                catchError((error) => {
                  throw new BadRequestException(error);
                }),
                mergeMap(() => this.processExcelToJsonBuilder
                  .getFile(filePath)
                  .addSheet(yourModuleSheet)
                  .build(req)
                )
              );

              const result = await lastValueFrom(data);
              await fs.remove(filePath);

              return result;
            } catch (error) {
              const filePath: string = req?.file?.path;
              if (filePath) fs.remove(filePath);

              if (error instanceof MessagesInvalidDataError) {
                const errorResponse = { 
                  status: 400,
                  message: error?.errors,
                  error: "Bad Request"
                };
                throw new BadRequestException(errorResponse);
              } else {
                throw new BadRequestException(error?.response);
              }
            }
          }
        }
        //file : your-module.service.ts-----------------------------------------------------------------------------------
              
    b.  Create src/modules/your-module/services/your-module.service.ts:
        import { BadRequestException, Injectable } from '@nestjs/common';
        import { Request } from 'express';
        import { format } from 'date-fns';
        import { ItemsYourModuleDto } from '../dtos/items-your-module.dto';
        import { ExcelYourModuleService } from './excel-your-module.service';
        import { ReadYourModuleSheetDto } from '../dtos/read-your-module-sheet.dto';

        @Injectable()
        export class YourModuleService {
          constructor(
            private readonly excelService: ExcelEngineYourModuleService,
          ) {
            super(YourModuleService?.name);
          }

          async convertYourModuleFromExcelToJson<T>(req: Request): Promise<WriteResponseBase> {
            try {
              const read: ReadYourModuleSheetDto = await this.excelService.readFormatExcel(req);
              if (!read?.yourModule) throw new BadRequestException(`Failed to read Excel, sheetname invalid`);

              const items: ItemsYourModuleDto[] = read?.yourModule;
              return items;
            } catch (error) {
              throw new BadRequestException(error?.response);
            }
          }
        }  

#7. Setup config multer file for your project
    a.  Create src/config/multer-options.config.ts :
        //file : multer-options.config.ts-------------------------------------------------------
        import { extname } from 'path';
        import { existsSync, mkdirSync } from 'fs';
        import { diskStorage } from 'multer';
        import { v4 as uuid } from 'uuid';
        import { BadRequestException, HttpCode, HttpException, HttpStatus } from '@nestjs/common';
        import { Request } from 'express';

        // Multer configuration
        export const multerConfig = {
            dest: process.env.UPLOAD_LOCATION,
        };

        // Multer upload options
        export const multerOptions = {
          // Enable file size limits
          limits: {
              fileSize: +process.env.MAX_FILE_SIZE,
          },
          // Check the mimetypes to allow for upload
          fileFilter: (req: Request, file: Express.Multer.File, cb: Function) => {
            // Allow storage of file
            if (/\.(xlsx|xls|xlsm|xlsb|xlt|csv|ods)$/i.test(file?.originalname)) cb(null, true);

            // Reject file
            else cb(new BadRequestException(`Unsupported file type ${extname(file.originalname)}`), false);

          },
          // Storage properties
          storage: diskStorage({
            // Destination storage path details
            destination: (req: Request, file: Express.Multer.File, cb: Function) => {
              const uploadPath = multerConfig.dest;
              // Create folder if doesn't exist
              if (!existsSync(uploadPath)) mkdirSync(uploadPath);

              cb(null, uploadPath);
            },
            // File modification details
            filename: (req: Request, file: Express.Multer.File, cb: Function) => {
              const uploadedFileName = `${uuid()}${extname(file.originalname)}`;
              // Validation customerName & configName
              if (!req?.body?.customerName && !req?.body?.configName) 
                cb(new BadRequestException([
                  `customerName should not be empty`,
                  `configName should not be empty`
                ]), false);
              else if (!req?.body?.customerName) cb(new BadRequestException([`customerName should not be empty`]), false);
              else if (!req?.body?.configName) cb(new BadRequestException([`configName should not be empty`]), false);
              else 
                // Calling the callback passing the random name generated with the original extension name
                cb(null, uploadedFileName);
            },
          }),
        };
        //file : multer-options.config.ts-------------------------------------------------------

#8. Setup controllers for your project
    a.  Create src/modules/controllers/your-module.controller.ts:
        // file : your-module.controller.ts------------------------------------------------------------
        import {
          Body,
          Controller,
          Delete,
          Param,
          Post,
          Req,
          Res,
          UploadedFile,
          UseInterceptors,
        } from '@nestjs/common';
        import { FileInterceptor } from '@nestjs/platform-express';
        import { Response, Request } from 'express';
        import { multerOptions } from 'src/config/multer-options.config';
        import { YourModuleService } from './services/your-module.service';

        @Controller()
        export default class YourModuleController
        {
          constructor(
            private readonly yourModuleService: YourModuleService,
          ) {
            super(YourModuleController.name);
          }

          @Post('your-module')
          @UseInterceptors(FileInterceptor('file', multerOptions))
          async uploadDataBleed<T>(
            @UploadedFile() file: Express.Multer.File,
            @Req() req: Request,
            @Res() res: Response,
          ): Promise<Response<T, Record<string, T>>> {
            try {
              const payload = await this.yourModuleService.convertYourModuleFromExcelToJson(req);
              return res.status(201).json({
                data: payload,
                meta: {
                  fileName: req?.file?.originalname,
                  status: 'OK',
                },
                message: 'Data has been converted & saved',
                time: new Date()
              });
            } catch (error) {
              return res.status(400).json(error.response);
            }
          }
        }
        // file : your-module.controller.ts------------------------------------------------------------