Question

60

Creating Transform stream using ExcelJS for writing xlsx

rated 0 times [ 61] [ 1] / answers: 1 / hits: 21845 / 8 Years ago, mon, february 8, 2016, 12:00:00

I'm using ExcelJS module and writing wrapper for my purposes, which implements Transform Stream API (yes, node version is 0.10.40).

ExcelJS has a stream API and according to example in ExcelJS module we can use one like this (execute node index.js > test.xlsx):

var stream = require('stream'),

    Excel = require('exceljs');



var rs = new stream.Readable({objectMode: true});

rs.push({name: 'one'});

rs.push({name: 'two'});

rs.push({name: 'three'});

rs.push(null);



var workbook = new Excel.stream.xlsx.WorkbookWriter({stream: process.stdout}),

    worksheet = workbook.addWorksheet('sheet 1');



worksheet.columns = [{header: 'Name', key: 'name'}];



rs.on('data', function(doc) {

    worksheet.addRow({

        name: doc.name

    }).commit();

});



rs.on('end', function(doc) {

    worksheet.commit();

    workbook.commit();

});

And it is working fine, but looks not very clean. Because we cannot use .pipe.

What I need:

'use strict';



var buffer = require('buffer'),

    stream = require('stream'),

    util = require('util'),

    Excel = require('exceljs');



var rs = new stream.Readable({objectMode: true});

rs.push({name: 'one'});

rs.push({name: 'two'});

rs.push({name: 'three'});

rs.push(null);



var ExcelTransform = function(options) {

    stream.Transform.call(this, options);

    this._writableState.objectMode = true;

    this._readableState.objectMode = false;



    this.workbook = new Excel.stream.xlsx.WorkbookWriter({stream: this});

    this.worksheet = this.workbook.addWorksheet('sheet 1');

    this.worksheet.columns = [{header: 'Name', key: 'name'}];

};



util.inherits(ExcelTransform, stream.Transform);



ExcelTransform.prototype._transform = function(chunk, encoding, callback) {

    if (buffer.Buffer.isBuffer(chunk)) {

        this.push(chunk);

    } else {

        this.worksheet.addRow({

            name: chunk.name

        }).commit();

    }

    callback();

};



ExcelTransform.prototype._flush = function(callback) {

    this.worksheet.commit();

    this.workbook.commit();

    callback();

};



rs.pipe(new ExcelTransform()).pipe(process.stdout);

But this is not working and giving me empty output.

Answers

Only authorized users can answer the question. Please sign in first, or register a free account.

tylerdamiena

Add To Favorites

Follow

Total Points: 139

Total Questions: 90

Total Answers: 118

Location: Liechtenstein

Member since Wed, Dec 8, 2021

3 Years ago

tylerdamiena questions

1 How to access sveltekit session in endpoint?

Tue, May 3, 22, 00:00, 2 Years ago

1 React.js, Each child in a list should have a unique key prop. how to fix if key is already given

Mon, Mar 7, 22, 00:00, 2 Years ago

1 Can`t resolve image.png in styles.css

Fri, Sep 17, 21, 00:00, 3 Years ago

1 Warning: validateDOMNesting(...): < body> cannot appear as a child of < div> in React.js

Wed, May 26, 21, 00:00, 3 Years ago

1 Using readonly on a reactive object

Tue, May 26, 20, 00:00, 4 Years ago

View All

answered 8 Years ago catrinas · Accepted Answer

The output is empty because you're pushing nothing out of your transform stream. You're in object mode so it never goes in this if:

if (buffer.Buffer.isBuffer(chunk)) {

  this.push(chunk);

}

Here is a working pipeable version (the data is streamed at once at the end):

var stream = require('stream');

var util = require('util');

var Excel = require('exceljs');

var bl = require('bl');



var ExcelTransform = function(options) {

  stream.Transform.call(this, { objectMode: true });



  this.workbook = options.workbook;

  // you can make this optional by checking for it and

  // creating an empty worksheet if none provided

  this.worksheet = options.worksheet;

}



util.inherits(ExcelTransform, stream.Transform);



ExcelTransform.prototype._transform = function(doc, encoding, callback) {

  this.worksheet.addRow({ name: doc.name });    

  callback();

};



ExcelTransform.prototype._flush = function(callback) {

  this.workbook.commit(); // commit only when you're done



  var that = this;

  // bl drains the stream and create a Buffer object you can then push

  this.workbook.stream.pipe(bl(function(err, data) {

    that.push(data);

    callback();

  }));

};



// it's better to provide the workbook as a parameter to ExcelTransform

var workbook = new Excel.stream.xlsx.WorkbookWriter();

var worksheet = workbook.addWorksheet('sheet 1');

worksheet.columns = [{

  header: 'Name',

  key: 'name'

}];



var rs = new stream.Readable({ objectMode: true });

rs.push({ name: 'one' });

rs.push({ name: 'two' });

rs.push({ name: 'three' });

rs.push(null);



rs.pipe(new ExcelTransform({

  workbook: workbook,

  worksheet: worksheet

})).pipe(process.stdout);

Another solution, streaming all the time:

var stream = require('stream');

var util = require('util');

var Excel = require('exceljs');



var ExcelTransform = function(options) {

  stream.Transform.call(this, {

    writableObjectMode: true,

    readableObjectMode: false

  });



  this.workbook = options.workbook;

  var that = this;

  this.workbook.stream.on('readable', function() {

    var chunk = workbook.stream.read();

    that.push(chunk);

  });

  this.worksheet = options.worksheet;

}



util.inherits(ExcelTransform, stream.Transform);



ExcelTransform.prototype._transform = function(doc, encoding, callback) {

  this.worksheet.addRow({

    name: doc.name

  }).commit();



  callback();

};



ExcelTransform.prototype._flush = function(callback) {

  this.workbook.commit(); // final commit

};



// it's better to provide the workbook as a parameter to the ExcelTransform

var workbook = new Excel.stream.xlsx.WorkbookWriter();

var worksheet = workbook.addWorksheet('sheet 1');

worksheet.columns = [{

  header: 'Name',

  key: 'name'

}];



var rs = new stream.Readable({ objectMode: true });

rs.push({ name: 'one' });

rs.push({ name: 'two' });

rs.push({ name: 'three' });

rs.push(null);



rs.pipe(new ExcelTransform({

  workbook: workbook,

  worksheet: worksheet

})).pipe(process.stdout);