How to write xls file and stream to response in node js

Question

What I have tried is below:

exports.downloadListOfUsers = (req, res) => {
    let users = [{id:'',name:'',lastName:'',gender:''},{...},{...}]
    const XLSX = require('xlsx');
    let ws = XLSX.utils.aoa_to_sheet(users); //XLSZ.utils.json_to_sheet(users)
    let wb = XLSX.utils.book_new();
    let wbout = XLSX.write(wb, {bookType: 'xlsx', type: 'binary'});
    wbout.pipe(res)
}

After sending it to the client, the response status is pending and it gets failed after some time. I have no idea what I am missing. Please guide me. Thanks.

Gotten

Add your whole code, "res" is not shown in your snippet! stackoverflow.com/help/minimal-reproducible-example – Hereafter

Answer 1

The idea is to create a valid Excel file and then stream it.

Observe the call sequence for the creation of Workbook and writing JSON data to it. You might want to create unique names for different Excel files if you want to handle multiple client requests and create a history/log of data sent.

Here is a complete working example (see comments inline):

const fs = require('fs');
const server = require('http').createServer();
const XLSX = require('xlsx');

server.on('request', (req, res) => {
    const data = [
        {id: '1', name: 'abc'},
        {id: '2', name: 'xyz'}
    ];

    const wb = XLSX.utils.book_new();                     // create workbook
    const ws = XLSX.utils.json_to_sheet(data);            // convert data to sheet
    XLSX.utils.book_append_sheet(wb, ws, 'users_sheet');  // add sheet to workbook

    const filename = "users.xlsx";
    const wb_opts = {bookType: 'xlsx', type: 'binary'};   // workbook options
    XLSX.writeFile(wb, filename, wb_opts);                // write workbook file

    const stream = fs.createReadStream(filename);         // create read stream
    stream.pipe(res);                                     // send to client
});

server.listen(8080);

Kingpin

I have a use case where I fetch json from api(with pagination) and then stream the data to google bucket as excel. Is there a way to do it with xlsx? or any other. library suggestion? – City

It would have been nice if xlsx provided a stream writer to avoid writing file to local file system on the server and pipe the response directly to client. – Exophthalmos

@ak_linus, did you manage to stream an Excel file to the client while appending more data to it, instead of creating the whole file locally at the server? – Unideaed

Answer 2

There is also an implementation with buffers instead of creating an file and reading from it (if its a small file you can use this method).

const fs = require('fs');
const server = require('http').createServer();
const XLSX = require('xlsx');
var Readable = require('stream').Readable; 

function bufferToStream(buffer) { 
  var stream = new Readable();
  stream.push(buffer);
  stream.push(null);

  return stream;
}

server.on('request', (req, res) => {
    const data = [
        {id: '1', name: 'abc'},
        {id: '2', name: 'xyz'}
    ];

    const wb = XLSX.utils.book_new();                     // create workbook
    const ws = XLSX.utils.json_to_sheet(data);            // convert data to sheet
    XLSX.utils.book_append_sheet(wb, ws, 'users_sheet');  // add sheet to workbook

    const wbOpts = { bookType: "xlsx", type: "buffer" };
    const resp = XLSX.write(wb, wbOpts);                  // write workbook buffer
    const stream = bufferToStream(resp)                   // convert buffer to stream
    stream.pipe(res);                                     // send to client
});

server.listen(8080);

Happy coding. Same code I have copied and modified thanks to @Zubad Ibrahim

Mindimindless