Serverless Excel export in nodejs

Question

I need a simple help about how to generate Excel and then export it on click of API url in serverless offline.It should show a download option for that file. When I am creating a file in normal node with express frework.It is generating correct Excel file and I am able to download that file easily. But when I am using the same code in serverless offline I am getting a corrupted .xlsx file on download. I am new to serverless offline and know only basics of creating and using lambda functions offline. Please help me for this task.

**app.js:**
var Excel = require('exceljs');
var app = express();
var nodeExcel = require('excel-export');
const serverless = require('serverless-http')
app.get("/click", async (req, res) => {

  var workbook = new Excel.Workbook();
  var worksheet = workbook.addWorksheet('My Sheet');
  worksheet.columns = [
    { header: 'Id', key: 'id', width: 10 },
    { header: 'Name', key: 'name', width: 32 },
    { header: 'D.O.B.', key: 'DOB', width: 10 }
  ];
  worksheet.addRow({ id: 1, name: 'John Doe', dob: new Date(1970, 1, 1) });
  worksheet.addRow({ id: 2, name: 'Jane Doe', dob: new Date(1965, 1, 7) });

  await workbook.xlsx.writeFile('./temp.xlsx')

  var fileName = 'temp.xlsx';

  res.setHeader('Content-Type', 'application/octet-stream');
  res.setHeader("Content-Disposition", "attachment; filename=" + fileName);
  await workbook.xlsx.write(res);


})
module.exports.handler = serverless(app);



**Serverless.yml:**
plugins:
  - serverless-offline

provider:
  name: aws
  runtime: nodejs10.x

custom:
  serverless-offline:
    host: '0.0.0.0'   
functions:
      app:
        handler: route/app.handler  
        events:
           - http:
                path: /click
                method: get

please provide some code you tried, the handler function and the processing function — BLACKMAMBA, Dec 25 '19 at 15:32
I am using node-excel-export module for this purpose.I am creating demo .xlsx file as described in this module and end result is corrupt .xlsx file.currently I am not able to send the code. — Shruti chandak, Dec 25 '19 at 16:19

score 0 · Answer 1 · answered Dec 29 '19 at 06:57

Finally there is solution which can be temporary,but solves issues easily. I was able to download a file on my local system. So on server side you can easily use static file method which on hitting a link easily download or open the file as per the file extension. On Client side you can easily call that method . If these are application/octet-stream file than file download in browser itself, and if it is image/pdf file then it will open in browser easily.

As I am working on serverless-offline it provides an easy package called 'serverless-aws-static-file-handler'.Have a look: https://www.npmjs.com/package/serverless-aws-static-file-handler.

This function will take the file from path and return file from the path provided on handler which work on async event call of lambda.

Here is my piece of code.Do correct if u find something wrong. For Binary files like images,you need to add plugin link and custom apigateway BinaryMediaType. For other files it can work without them as well

 #index.js# file
    const express = require('express')
    const app = express()
    const sls = require('serverless-http')
    const path=require('path')
    const StaticFileHandler = require('serverless-aws-static-file-handler')
    var excel = require('exceljs');
    const clientFilesPath = path.join(__dirname, "public")
    const fileHandler = new StaticFileHandler(clientFilesPath)
    module.exports.excel = async (event, context) => {
        const workbook = new excel.Workbook();
        var worksheet = workbook.addWorksheet('My Sheet');
        worksheet.columns = [
            { header: 'Id', key: 'id', width: 10 },
            { header: 'Name', key: 'name', width: 32 },
            { header: 'D.O.B.', key: 'DOB', width: 10 }
        ];

        worksheet.addRow({id: 1, name: 'John Doe', dob: new Date(1970,1,1)});
        worksheet.addRow({id: 2, name: 'Jane Doe', dob: new Date(1965,1,7)});
        await workbook.xlsx.writeFile('public/streamed-workbook.xlsx');
        event.path = "streamed-workbook.xlsx" 
        return fileHandler.get(event, context)
      }
       module.exports.server = sls(app)

#Serverless.yml:#

provider:
  name: aws
  runtime: nodejs12.x
plugins:
  - serverless-offline
  - serverless-aws-static-file-handler/plugins/BinaryMediaTypes
custom:
  apiGateway:
    binaryMediaTypes:
      - "image/png"
      - "image/jpeg"
functions:
  app:
    handler: index.server
    events:
      - http: ANY /
      - http: 'ANY {proxy+}'  
  excel:
    handler: index.excel
    events:
      - http:
          path: /exportexcel
          method: get

Serverless Excel export in nodejs

1 Answers1