\
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

\
```bash
npm install -g serverless
```

\
## Create a typescript template project

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

\
## Install required npm packages

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

\
* [excelJS](https://www.npmjs.com/package/exceljs) for creating a spreadsheet
* [serverless-offline](https://www.npmjs.com/package/serverless-offline) for running lambda function locally
* [aws-sdk](https://www.npmjs.com/package/aws-sdk) from interacting with AWS services

  \

## Define serverless function and add offline package to serverless.ts

\
```typescript
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

\
```typescript
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

\
```typescript
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

\
```typescript
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

\
```typescript
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

\
```bash
sls offline --stage local
```

\
 ![](https://cdn.hackernoon.com/images/OBFgr2enmUfHmrRQ0JRaj3w97rJ3-2ea3epm.png)

## Deploy to AWS

\
```bash
sls deploy --stage dev
```

\
You can clone this [repo](https://github.com/appletreeat56/json-to-xlsx) to see a working example -

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

\


2022 - HackerNoon Contributor of the Year - Aws

2022 - HackerNoon Contributor of the Year - Serverless

2022 - HackerNoon Contributor of the Year - Typescript

2022 - No No No Nodejs

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

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

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

Too Long; Didn't Read

@appletreeat56

RELATED STORIES