In this article, I will show you how you can write a lambda function that can convert JSON data into a .xlsx file using nodeJS and typescript. This function is built using 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 for creating a spreadsheet excelJS for running lambda function locally serverless-offline from interacting with AWS services aws-sdk 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;
    });
  }
} 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 and in your environment AWS access keys AWS secret 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;
    }
} Test locally sls offline --stage local Deploy to AWS sls deploy --stage dev You can clone this to see a working example - repo git clone https://github.com/appletreeat56/json-to-xlsx.git

Nominated for 2022 - HackerNoon Contributor of the Year - Typescript

Nominated for 2022 - HackerNoon Contributor of the Year - Serverless

Nominated for 2022 - HackerNoon Contributor of the Year - Aws

Nominated for 2022 - No No No Nodejs

Too Long; Didn't Read

Make resilience your competitive advantage

The Lambda Function That Creates xlsx Files From JSON Data to Store On S3 Using NodeJS/Typescript

About Author

Comments

TOPICS

THIS ARTICLE WAS FEATURED IN

Related Stories

Untitled Story

10 Things in Engineering We Don't Spend Enough Time On

254 Stories To Learn About Amazon

193 Stories To Learn About AWS Lambda

17 of the Best Amazon Web Services (AWS) for Web Developers to Learn

3 Min Hack for Locally building a Native Extension

10 Things in Engineering We Don't Spend Enough Time On

254 Stories To Learn About Amazon

193 Stories To Learn About AWS Lambda

17 of the Best Amazon Web Services (AWS) for Web Developers to Learn

3 Min Hack for Locally building a Native Extension

Light-Mode

Classic

Newspaper

Dark-Mode

Neon Noir

Minty

HN StartUps