cassln February 2016

Creating Transform stream using ExcelJS for writing xlsx

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(ExcelTrans        

Answers


ShanShan February 2016

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
  

Post Status

Asked in February 2016
Viewed 2,186 times
Voted 4
Answered 1 times

Search




Leave an answer