Express-Server with Netlify functions

Support
1

Hi,

my question refers on this blog and also on this youtube-video.

My usecase is to serve a customized xlsx-file.

For this my user has to make a http-GET-request to:

> http://<<my-sitename>>.netlify.app/.netlify/functions/api?cell1=value1&cell2=value2...

The function/server…

  1. …takes the request’s URL-parameters
  2. …uses the npm-package xlsx-populate to modify a given xlsx-file with the received parameter-values
  3. …serves the modified xlsx-file for download on the user’s machine

My approach on this was:

  • setting up an Express-server (locally tested - it works fine! - result: download of customized xlsx-file as expected)
  • making code-changes like discribed in the blog/video above to wrap a netlify-function around the server which works exactly as the server on its own.

result/problem: the netlify-function running on development-server locally serves a corrupted xlsx-file. I am not able to open it. The express-server (not wrapped as a serverlee function) serves a well formatted file.

My code for the server/the function is:

const express = require("express");
const XlsxPopulate = require("xlsx-populate");

//! for switching Express-Server to a netlify-function uncomment the following line
// const serverless = require("serverless-http");

const app = express();
const router = express.Router();

router.get("/", (req, res) => {
  // Load an existing workbook
  XlsxPopulate.fromFileAsync("DB_PrivatKredit_Kalkulationstool_20220225.xlsx")
    .then((workbook) => {

      // store received IRL-parameters in variables
      let el = parseFloat(req.query.el.replace(",", "."));
      let raten = parseInt(req.query.raten);
      let laufzeit = parseInt(req.query.laufzeit);
      let kreditbetrag = parseFloat(req.query.kreditbetrag.replace(",", "."));
      let eigenabloese = parseFloat(req.query.eigenabloese.replace(",", "."));
      let fremdabloese = parseFloat(req.query.fremdabloese.replace(",", "."));
      let zufuehrer = req.query.zufuehrer;
      let ivs = parseInt(req.query.ivs);
    
      // Modify the workbook.
      const worksheet = workbook.sheet("Kalkulationstool");
      worksheet.cell("D7").value(el);
      worksheet.cell("D8").value(raten);
      worksheet.cell("D9").value(laufzeit);
      worksheet.cell("D10").value(kreditbetrag);
      worksheet.cell("D11").value(eigenabloese);
      worksheet.cell("D12").value(fremdabloese);
      worksheet.cell("D13").value(zufuehrer);
      worksheet.cell("D14").value(ivs);

      return workbook.outputAsync();
    })
    .then((data) => {
      // Set the output file name.
      res.attachment("output.xlsx");

      // Send the workbook.
      res.send(data);
    });

  // http://localhost:9000/.netlify/functions/api?el=60,5&raten=84&laufzeit=86&kreditbetrag=25000&eigenabloese=5500&fremdabloese=1500&zufuehrer=DVAG&ivs=125
});


app.use("/.netlify/functions/api", router);

//! for switching Express-Server to a netlify-function uncomment the following line
// module.exports.handler = serverless(app);

//! for switching Express-Server to a netlify-function comment out this app.listen()-method
app.listen(3000, () => {
  console.log(
    `Server running on port 3000`
  );
});

Just notice the few comments to make the server a netlify function. You can exchange the given xlsx.-file to any blank xlsx.file, just save it in the same directory.

What could cause the problem of getting that corrupted xlsx-file. Thank you in advance

Michael - Germany

2

Hi @Frodo1980,

Instead of the code, would it be possible for you to share a test repo that we can clone and get started without any additional setup? I don’t see the file would be corrupted, but I think you can also try using CSV instead of XLSX if having Excel features is not a hard requirement.

3

Hi @hrishikesh,

sure, here you are.

Unfortunately using xlsx.-format is a hard requirement.

Edit 25.04.2022:
Codesandbox for local Express-Server

Codesandbox for Netlify lambda-function

to get the xlsx-file add “/.netlify/functions/api?A1=test&A2=Hello” after the URL given in the sandbox

4

@hrishikesh or anyone - repo is online - any ideas?

5

Hey @Frodo1980

I can see a couple of issues with the repository.

  1. You have set the functions directory in the netlify.toml to functions, but the api.js function is in src.
  2. You have set included_files to ["**.xlsX"] (note the uppercase X) though the filename is xlsx (all lowercase)
  3. included_files need to site under the [functions] section of the netlify.toml e.g.
    [functions]
  directory = "functions"
  [functions.api]
    included_files = ["src/*.xlsx"]
  4. included_files paths are “relative to the base directory”
  5. npm start won’t work with Netlify. You cannot have a listener, but it is possible to use express as outlined in How to run Express.js apps with Netlify Functions (you need to export a handler.)
6

Hi @coelmay,

You have set the functions directory in the netlify.toml to functions , but the api.js function is in src .

Did you recognize that there a 2 branches in the repo? You refer to the “Express-branch” - this simple Express-server works fine. toml-file isn’t needed.
The “function” branch is the one which serves the corrupted file.

You have set included_files to ["**.xlsX"] (note the uppercase X ) though the filename is xlsx (all lowercase)

OK, you are right, but it has no consequence. As written above - the express-server works fine.

included_files need to site under the [functions] section of the netlify.toml e.g.

[functions]
  directory = "functions"
  [functions.api]
    included_files = ["src/*.xlsx"]

Look “functions”-branch

npm start won’t work with Netlify. You cannot have a listener, but it is possible to use express as outlined in How to run Express.js apps with Netlify Functions (you need to export a handler.)

npm start is just a script. In each branch it runs different commands. See each package.json. In “function”-branch there is a exported handler.

8

Hey @Frodo1980,

I spent quite some time testing this, but I can’t get it to work locally (with Netlicy CLI) - which you mention you can. I was wondering if you could help me test this locally at least.

So, I used your api.js and created a modified version like this:

import Express, {Router} from 'express'
import {promises as Fs} from 'fs'
import Serverless from 'serverless-http'
import {fromFileAsync as Xlsx} from 'xlsx-populate'
export async function handler(event, context) {
  const app = Express()
  const router = Router()
  router.get('/', (request, response) => {
    Xlsx('./file_example.xlsx').then(workbook => {
      const sheet = workbook.sheet('Sheet1')
      const params = request.query
      Object.keys(params).map((key, index) => {
        sheet.cell(key).value(Object.values(params)[index])
      })
      return workbook.outputAsync()
    }).then(data => {
      const file = './output.xlsx'
      Fs.writeFile(file, data).then(() => {
        response.download(file)
      })
    })
  })
  app.use('/.netlify/functions/api2', router)
  return Serverless(app)(event, context).then(result => {
    return result
  })
}

but I kept getting the error:

Request from ::ffff:127.0.0.1: GET /.netlify/functions/api2/?A2=1&B2=John&C2=Doe&D2=Male&E2=India&F2=23&G2=03%2F11%2F2000&H2=123
Response with status 200 in 176 ms.
/Users/hrishikesh/Desktop/netlifyforumquestion/node_modules/netlify-cli/node_modules/netlify-redirector/lib/redirects.js:116
      throw ex;
      ^

Error [ERR_STREAM_WRITE_AFTER_END]: write after end
    at writeAfterEnd (_http_outgoing.js:694:15)
    at write_ (_http_outgoing.js:706:5)
    at ServerResponse.write (_http_outgoing.js:687:15)
    at IncomingMessage.onEnd (/Users/hrishikesh/Desktop/netlifyforumquestion/node_modules/netlify-cli/src/utils/proxy.js:386:13)
    at IncomingMessage.emit (events.js:412:35)
    at IncomingMessage.emit (domain.js:470:12)
    at endReadableNT (internal/streams/readable.js:1317:12)
    at processTicksAndRejections (internal/process/task_queues.js:82:21)
Emitted 'error' event on ServerResponse instance at:
    at writeAfterEndNT (_http_outgoing.js:753:7)
    at processTicksAndRejections (internal/process/task_queues.js:83:21) {
  code: 'ERR_STREAM_WRITE_AFTER_END'

Note that, the output.xlsx that I was able to generate and write to disk with fs seemed to work fine, however I could never download the file in the browser.

I tried various fixes, to no avail. I finally tried your code without any changes, and surprisingly, that gave me the same error.

9

Hi @hrishikesh,

so happy to see you spending so much time on my toppic - thank you!

Hmm - how could I help you to test this locally? Did you follow the steps in readme.md of the github-repo (function-branch) or did you try the code-sandbox? Doesn’t this work, too?

10

Hey @Frodo1980,

Sorry, I had not checked the Readme, but I noticed that I didn’t do anything much different. I cloned the repo, checked out the function branch, ran npx netlify dev (which calls npm run start) and sent a request like:

localhost:8888/.netlify/functions/api2/?A2=1&B2=John&C2=Doe&D2=Male&E2=India&F2=23&G2=03%2F11%2F2000&H2=123

(or functions/api). That should work, I suppose?

I wonder why your readme says, call localhost:3000 :thinking:

I haven’t yet tried the CodeSandbox thing

11

I think you did all good so far, but your request looks a little weird.

Would you try http://localhost:8888/.netlify/functions/api?A1=test&A2=Hello please? And you are right, on local machine it is port 8888.

I think with this little change, the cloned repo (function branch) should run locally.

12

Thanks for that, that URL indeed worked and I am not getting the error in the Function anymore (really wonder what was causing it though).

However, when I open the downloaded file, it still appears to be corrupted locally (with yours as well as my code). The interesting part is, the part where I’m doing Fs.writeSync(), the file that’s being written to disk works perfectly fine, it’s just the file returned by Express that comes out corrupted.

I’ll try to check this further once I get some more time.

1 Like
13

These are good news! So we both see the same issue, don’t we?

A normal xlsx-file file_example.xlsx should be modified by xlsx-populate-library and comes out corrupted.

And if you clone the “Express-branch” I think you will be able to reproduce the same - THERE it works all fine??? No corrupted file after modifiying.

I don’t know where to search for the problem. Is it netlify and the kind of how functions work? The xlsx-populate-library has proofed that it does what it should do.

Do you know if there is someone at netlify-staff to ask this?

14

Hey @Frodo1980,

I checked this:

and this:

and they keep saying about adding some kind of binary support which I’m not sure if it’s enabled or can be enabled.

The solution for you while I get the above confirmed, is that, try sending a base64 string to the browser and convert base64 to blob on the client-side and allow users to download that blob.

16

God bless you - I do not know if it will work, but now there is a new idea! Thank you! I will report here

1 Like
17

You’re in for a treat, I talked to one of our engineers, and we were able to reach to a working (local + production) solution:

https://github.com/Hrishikesh-K/supreme-octo-umbrella

You can try it live: https://papaya-tarsier-e354b2.netlify.app/.netlify/functions/api?A1=test&A2=Hello

1 Like
18

Hey @hrishikesh,

the last days I tried it the way…

…sending a base64 string to the browser and convert base64 to blob on the client-side and allow users to download that blob

IT WORKS!

Your way with that binary is shorter and can be done without any client logic, just with a link :smiley: - NICE!

I copy that code here, so you can delete your repo.

import Express, {Router} from 'express'
import Serverless from 'serverless-http'
import {fromFileAsync as Xlsx} from 'xlsx-populate'
export async function handler(event, context) {
  const app = Express()
  const router = Router()
  router.get('/', (request, response) => {
    Xlsx('./file_example.xlsx').then(workbook => {
      const sheet = workbook.sheet('Sheet1')
      const params = request.query
      Object.keys(params).map((key, index) => {
        sheet.cell(key).value(Object.values(params)[index])
      })
      return workbook.outputAsync()
    }).then(data => {
      response.attachment('output.xlsx')
      response.send(data)
    })
  })
  app.use('/.netlify/functions/api', router)
  return Serverless(app, {
    binary: ['application/vnd.openxmlformats-officedocument.spreadsheetml.sheet']
  })(event, context).then(result => {
    return result
  })
}

Thank you very much for your support!

Greetings from germany - Michael

1 Like