How do I create an Excel File in S3 though nodejs?

Question

I'm trying to create a script that will take a JSON object and put it into the S3 bucket as an xlsx file

I am planning on integrating SQL queries to the code but for now limiting it to JSON for easier coding. I've tried using alasql and xlsx to try and create the S3.putObject body but the output creates a corrupted excel file

var data = [{a:1,b:1,c:1},{a:1,b:2,c:1},{a:1,b:3,c:1}, {a:2,b:1,c:1}];
  var a = XLSX.utils.json_to_sheet(data);

  var params = {
  'Body' : Buffer.from(a),
  'Key': event.Key + '.xlsx',
  'Bucket': event.Bucket
};

s3.putObject(params).promise();

I expect that the data will be placed in an xlsx file in the S3 bucket and while the file is created, it is corrupted

Answer 1

What worked for me is this, using sheetjs import xlsx from 'xlsx';:

        // initiate the workbook
        const wb = xlsx.utils.book_new();

        // add properties to the sheet
        wb.Props = {
          Title: 'Books Borrowed',
          Subject: 'Borrowed Books',
          Author: 'Admin',
          CreatedDate: '2020-04-23',
        };

        // add a sheet
        wb.SheetNames.push('Borrowed');

        // I used aoa_to_sheet because I'm having an issue with json_to_sheet but was able to create a workaround, see: https://github.com/SheetJS/sheetjs/issues/1487#issuecomment-618767784
        // I find the aoa_to_sheet a much cleaner approach
        const ws = xlsx.utils.aoa_to_sheet(sheet);
        wb.Sheets.Borrowed = ws;

        // generate output as buffer
        const wbOut = xlsx.write(wb, {
          bookType: 'xlsx',
          type: 'buffer',
        });

        // upload to S3
        const data = await s3
          .putObject({
            Bucket: config.s3.s3BucketPublic,
            Key: 'filenameHere.xlsx',
            ACL: 'public-read',
            Body: wbOut,
            ContentType:
              'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
          })
          .promise();

Answer 2

Below is what works for me.

const Excel = require('exceljs');
const aws = require('aws-sdk');
let workbook = new Excel.Workbook();
workbook.creator = 'System';
workbook.lastModifiedBy = 'System';
workbook.created = new Date();
workbook.modified = new Date();
workbook.lastPrinted = new Date();

let worksheet = workbook.addWorksheet('SOME Data');
worksheet.columns = [{
    header: 'somekey',
    key: 'DeviceID'
}, {
    header: 'Type',
    key: 'Type'
}];

hcpData.forEach(element => {
   worksheet.addRow({
       somekey: element.DEVICEID,
       Type: element.TYPE
   });
});

worksheet.getRow(1).eachCell(cell => {
    cell.font = {
        bold: true
   };
});
//configuring the AWS environment
aws.config.update({
    accessKeyId: 'putyourkye',
    secretAccessKey: 'putyourkye',
});

const s3 = new aws.S3();
const stream = new Stream.PassThrough();

workbook.xlsx.write(stream).then(() => {
    return s3
       .upload({
        Key: Date.now() + '_XYZNAME.xlsx',
        Bucket: 'abc-reports',
        Body: stream,
        ACL: 'public-read',
    })
    .promise()
    .then(data => {
        // do some task after upload
    });
});