Can't create xlsx files with Firebase Cloud Functions

Question

I followed the basic usage tutorial for excel4node package.

For running the code, I have an https function which will create an Excel.xlsx file in the same directory as index.js on my local system.

The problem, however, is that every time I call the function, a zero byte Excel.xls file is created.

The function body is this:

const createXlxsFile = (req, res) => {
  const xl = require('excel4node');

  // Create a new instance of a Workbook class
  const workbook = new xl.Workbook();

  // Add Worksheets to the workbook
  const worksheet = workbook.addWorksheet('Sheet 1');
  const worksheet2 = workbook.addWorksheet('Sheet 2');

  // Create a reusable style
  const style = workbook.createStyle({
    font: {
      color: '#FF0800',
      size: 12
    },
    numberFormat: '$#,##0.00; ($#,##0.00); -'
  });

  // Set value of cell A1 to 100 as a number type styled with paramaters of style
  worksheet.cell(1, 1).number(100).style(style);

  // Set value of cell B1 to 300 as a number type styled with paramaters of style
  worksheet.cell(1, 2).number(200).style(style);

  // Set value of cell C1 to a formula styled with paramaters of style
  worksheet.cell(1, 3).formula('A1 + B1').style(style);

  // Set value of cell A2 to 'string' styled with paramaters of style
  worksheet.cell(2, 1).string('string').style(style);

  // Set value of cell A3 to true as a boolean type styled with paramaters of style but with an adjustment to the font size.
  worksheet.cell(3, 1).bool(true).style(style).style({ font: { size: 14 } });

  workbook.write('Excel.xlsx');

  res.end('DOC CREATED');
};

This code is working fine with standard Node.js, but not with Firebase cloud functions. Is there a restriction with writing files with the functions?

I'm having the same issue even when using the Xlsx-populate package.

Is `workbook.write()` asynchronous? Does it return a promise? — Doug Stevenson, Jul 05 '18 at 17:08
@DougStevenson Nope. Using the .then() on it results in an error. `workbook.write()` returns nothing. — Utkarsh Bhatt, Jul 05 '18 at 17:15
https://stackoverflow.com/questions/17450412/how-to-create-an-excel-file-with-nodejs — I_Al-thamary, Jul 05 '18 at 17:17

score 6 · Accepted Answer · answered Jul 10 '18 at 06:20

OK. Figured out the issue.

The thing is that the cloud function don't allow you to write to any directory in the OS.

The only place where you have the write access to is the /tmp in the cloud functions.

On your local PC, however, this too will crash (tested in Windows 10). Probably because I had not created the C:/tmp folder.

To fix this, you can use the tmpdir() method of the os module in Node.js

const os = require('os');
const path = require('path');
const pathToSave = path.join(os.tmpdir(), 'Excel.xlsx');

While deploying the code, you will need to replace the os.tmpdir() with `/tmp'.

const pathToSave = path.join('/tmp', 'Excel.xlsx');

I hope this helps.

score 1 · Answer 2 · answered Jul 05 '18 at 17:24

1

workbook.write('Excel.xlsx'); is asynchronous. The docs says it takes a callback which is invoked after completion. Use that to terminate the function. Right now, you're terminating early before the write can finish.

wb.write();

The write() method can accept a single filename, a filename with callback function or an HTTP response object.

wb.write('ExcelFile.xlsx', function (err, stats) {
    if (err) {
        res.send(500);
    } else {
        res.end('DOC CREATED');
    }
});

It looks like this may work as well:

wb.write('ExcelFile.xlsx', res);

answered Jul 05 '18 at 17:24

Doug Stevenson

297,357
32
422
441

OK. Thanks. Added this and now its crashing without any log in the console. `{"error":{"code":500,"status":"INTERNAL","message":"function crashed","errors":["read ECONNRESET"]}}`. I was having the same issue with the `xlsx-populate` package too where it would crash without any logging and created a zero byte xlsx file. My whole code is this function and nothing else. I have added the `console.log(err)` in the `if(err)` clause too. – Utkarsh Bhatt Jul 05 '18 at 17:32
1

Write your file to `/tmp/ExeclFile.xlsx` instead. `/tmp` is the only writable area in Cloud Functions. – Doug Stevenson Jul 05 '18 at 17:36
And for testing the code locally? Right now, it's creating this file in the same directory as the `index.js`. The issue is that the file is always zero bytes. – Utkarsh Bhatt Jul 05 '18 at 17:40

Can't create xlsx files with Firebase Cloud Functions

2 Answers2