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
-
excelJS for creating a spreadsheet
-
serverless-offline for running lambda function locally
-
aws-sdk from interacting with AWS services
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 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;
}
}
Test locally
sls offline --stage local
Deploy to AWS
sls deploy --stage dev
You can clone this repo to see a working example -
