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

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 ) { 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 , 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 { 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