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.

Answer 1

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.

Answer 2

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);