Lambda function to create xlsx file from JSON data and store on s3 using nodeJS/typescript

In this article, I will show you how you can write a lambda function that can convert a nested JSON data into a .xlsx file using nodeJS and typescript.

This function is built using the serverless framework. You can find more about there here - https://www.serverless.com/

Install serverless globally

npm install -g serverless

Create a typescript template project

mkdir json-to-xlsx
cd json-to-xlsx
serverless create --template aws-nodejs-typescript

Install required npm packages

npm install --save exceljs @types/exceljs serverless-offline aws-sdk

• excelJS for creating a spreadsheet
• serverless-offline for running lambda function locally
• aws-sdkfrom interacting with AWS services

We are going to convert this sample JSON into XLSX

import { ingredients } from "../interface/ingredients";

export const sample:Array<ingredients> = [
  {
    id: "0001",
    type: "donut",
    name: "Cake",
    ppu: 0.55,
    batters: [
      { id: "1001", type: "Regular" },
      { id: "1002", type: "Chocolate" },
      { id: "1003", type: "Blueberry" },
      { id: "1004", type: "Devil's Food" },
    ],
    toppings: [
      { id: "5001", type: "None" },
      { id: "5002", type: "Glazed" },
      { id: "5005", type: "Sugar" },
      { id: "5007", type: "Powdered Sugar" },
      { id: "5006", type: "Chocolate with Sprinkles" },
      { id: "5003", type: "Chocolate" },
      { id: "5004", type: "Maple" },
    ],
  },
  {
    id: "0002",
    type: "donut",
    name: "Raised",
    ppu: 0.55,
    batters: [{ id: "1001", type: "Regular" }],
    toppings: [
      { id: "5001", type: "None" },
      { id: "5002", type: "Glazed" },
      { id: "5005", type: "Sugar" },
      { id: "5003", type: "Chocolate" },
      { id: "5004", type: "Maple" },
    ],
  },
  {
    id: "0003",
    type: "donut",
    name: "Old Fashioned",
    ppu: 0.55,
    batters: [
      { id: "1001", type: "Regular" },
      { id: "1002", type: "Chocolate" },
    ],
    toppings: [
      { id: "5001", type: "None" },
      { id: "5002", type: "Glazed" },
      { id: "5003", type: "Chocolate" },
      { id: "5004", type: "Maple" },
    ],
  },
];

Define serverless function and add offline package to serverless.ts

import type { AWS } from '@serverless/typescript';

const serverlessConfiguration: AWS = {
  service: 'jsontoxlsx',
  frameworkVersion: '2',
  custom: {
    webpack: {
      webpackConfig: './webpack.config.js',
      includeModules: true
    },

    //'${ssm:/jsontoxlsx/api/BUCKET_NAME}' ssm is not supported yet, I have 
    //raised an issue here - https://github.com/serverless/typescript/issues/59. 
    //Once this is fixed, we don't have to hardcode bucket names on this file 
    //and can be access from AWS system manager's parameter store

    AWS_BUCKET_NAME: 'Your bucket name'

  },
  // Add the serverless-webpack plugin
  plugins: ['serverless-webpack', 'serverless-offline'],
  provider: {
    name: 'aws',
    runtime: 'nodejs14.x',
    apiGateway: {
      minimumCompressionSize: 1024,
    },
    environment: {
      AWS_NODEJS_CONNECTION_REUSE_ENABLED: '1',
      AWS_BUCKET_NAME: '${self:custom.AWS_BUCKET_NAME}'
    },
  },
  functions: {
    jsontoxlsx: {
      handler: 'handler.jsontoxlsx',
      events: [
        {
          http: {
            method: 'get',
            path: 'jsontoxlsx',
          }
        }
      ]
    }
  }
}

module.exports = serverlessConfiguration;

Convert JSON data into excel format using excel node package

async function saveDataAsExcel(sample: ingredients[]) {
  const workbook:excel = new excel({ headerRowFillColor: '046917', defaultFillColor: 'FFFFFF' });
  let worksheet = await workbook.addWorkSheet({ title: 'Scrapped data' });
  workbook.addHeaderRow(worksheet, [
    "ID",
    "Type",
    "Name",
    "PPU",
    "Batter ID",
    "Batter Name",
    "Topping ID",
    "Topping Name"
  ]);

   for (let ingredient of sample) {
    workbook.addRow(
      worksheet,
      [
        ingredient.id.toString(),
        ingredient.type,
        ingredient.name,
        ingredient.ppu.toString()
      ],
      { bold: false, fillColor: "ffffff" }
    );

     let size:number = ingredient.batters.length > ingredient.toppings.length ? ingredient.batters.length : ingredient.toppings.length;  

     for (let i = 0; i < size; i++)  {
      workbook.addRow(
        worksheet,
        [
          '', '', '', '',
          ingredient.batters[i]?.id.toString(),
          ingredient.batters[i]?.type,
          ingredient.toppings[i]?.id.toString(),
          ingredient.toppings[i]?.type
        ],
        { bold: false, fillColor: "ffffff" }
      );
    }
  }

  return workbook; 
}

excel class implementation

import * as ExcelJS from "exceljs";

export interface worksheetOptions {
    title: string;
}

export interface rowOptions {
    fillColor: string;
    bold: boolean;
}

export interface workBookOptions{
    headerRowFillColor: string;
    defaultFillColor: string;
}

export class excel {

  workbook: ExcelJS.Workbook;
  defaultFillColor: string;
  headerRowFillColor: string;

  constructor(options: workBookOptions) {
    this.workbook = new ExcelJS.Workbook();
    this.workbook.created = new Date();
    this.workbook.modified = new Date();
    this.headerRowFillColor = options.headerRowFillColor || "FFF242";
    this.defaultFillColor = options.defaultFillColor || "FFFFFF";
  }

  async addWorkSheet(options: worksheetOptions) {
    return this.workbook.addWorksheet(options.title, {
      pageSetup: {
        horizontalCentered: true,
        verticalCentered: true,
        margins: {
          left: 2,
          right: 2,
          top: 4,
          bottom: 4,
          header: 2,
          footer: 2,
        },
      }
    });
  }

  async addHeaderRow(
    worksheet: ExcelJS.Worksheet,
    headerRowData: Array<string>
  ) {
    worksheet.addRow(headerRowData.map(row => row));
    worksheet.getRow(1).fill = {
      type: "pattern",
      pattern: "solid",
      fgColor: { argb: this.headerRowFillColor },
    };
    worksheet.getRow(1).font = { size: 12, bold: true, name: 'Arial', family:2, color: {argb: 'FFFFFF', theme: 2} };
    worksheet.getRow(1).border = {
      top:    { style: "thin", color: { argb: "E8E8E8"} },
      bottom: { style: "thin" ,color: { argb: "E8E8E8"} },
      left:   { style: "thin" ,color: { argb: "E8E8E8"} },
      right:  { style: "thin" ,color: { argb: "E8E8E8"} },
    };
  }

  async addRow(
    worksheet: ExcelJS.Worksheet,
    data: Array<string>,
    options: rowOptions
  ) {
    worksheet.addRow(data);
    worksheet.getRow(worksheet.rowCount).font = {
      size: 13,
      bold: options.bold || false,
    };
    worksheet.getRow(worksheet.rowCount).fill = {
      type: "pattern",
      pattern: "solid",
      fgColor: { argb: options.fillColor || this.defaultFillColor },
    };
    worksheet.getRow(worksheet.rowCount).alignment = {
      vertical: "bottom",
      horizontal: "left",
    };
    worksheet.getRow(worksheet.rowCount).border = {
      top:    { style: "thin", color: { argb: "E8E8E8" } },
      bottom: { style: "thin", color: { argb: "E8E8E8" } },
      left:   { style: "thin", color: { argb: "E8E8E8" } },
      right:  { style: "thin", color: { argb: "E8E8E8" } },
    };

    this.adjustColumnWidth(worksheet);
  }

  async adjustColumnWidth(worksheet: ExcelJS.Worksheet) {
    worksheet.columns.forEach( column => {
      var dataMax = 0;
      column.eachCell({ includeEmpty: true }, cell => {
        dataMax = cell.value ? cell.value.toString().length : 0;
      });
      column.width = dataMax < 15 ? 15 : dataMax;
    });
  }
}

Test locally

sls offline --stage local

Upload excel sheet to s3

await uploadToS3({
      Bucket: process.env.AWS_BUCKET_NAME,
      Key: `${objectKey}.xlsx`,
      ContentType:
        'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
      Body: await excelSheet.workbook.xlsx.writeBuffer()
    });

    //Get signed url with an expiry date
    let downloadURL = await getS3SignedUrl({
      Bucket: process.env.AWS_BUCKET_NAME,
      Key: `${objectKey}.xlsx`,
      Expires: 3600 //this is 60 minutes, change as per your requirements
    });

    return okResponse({
      message: 'JSON to XLSX is complete, you can download your file now',
      downloadURL
    })

AWS Wrapper functions - Note: You will need to have your AWS access keys and AWS secret in your environment

import { S3 } from "aws-sdk";

const s3 = new S3({
  accessKeyId: process.env.AWS_ACCESS_KEY,
  secretAccessKey: process.env.AWS_SECRET_ACCESS_KEY,
});

export async function uploadToS3(s3Data: S3.PutObjectRequest) {
    console.info(
      "---- UPLODAING TO S3",
      JSON.stringify(`${s3Data.Bucket} ${s3Data.Key}`, null, 2)
    );

    try {
      return await s3.upload(s3Data).promise();
    } catch (error) {
      console.log(error);
      return error;
    }
}


export async function getS3SignedUrl(params: any): Promise<any> {
    console.info(
      "---- GETTING SIGNED URL FROM S3",
      JSON.stringify(params, null, 2)
    );
    try {
      return s3.getSignedUrl("getObject", {
        Bucket: params.Bucket,
        Key: params.Key,
        Expires: params.Expires,
      });
    } catch (error) {
      console.log(error);
      throw error;
    }
}

Deploy to AWS

sls deploy --stage dev

You can clone this repository to see a working example -

git clone https://github.com/appletreeat56/json-to-xlsx.git