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
    })mit();
});

rs.on('end', function(doc) {
    worksheetmit();
    workbookmit();
});

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
        })mit();
    }
    callback();
};

ExcelTransform.prototype._flush = function(callback) {
    this.worksheetmit();
    this.workbookmit();
    callback();
};

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

But this is not working and giving me empty output.

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.

• javascript
• node.js
• xlsx

3 Answers 3

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

Below code snippet works for me

import { Workbook } from 'exceljs';
import { createReadStream } from 'fs';

function readExcelFile(){

const stream = createReadStream("./test.xlsx");
const workbook = new Workbook();
const streamWorkBook = await workbook.xlsx.read(stream);
const sheet = streamWorkBook.getWorksheet("Sheet1");

//Get all the rows data [1st and 2nd column]
 for (let i = 1; i <= sheet.rowCount; i++) {
  console.log(sheet.getRow(i).getCell(1).value);
  console.log(sheet.getRow(i).getCell(2).value);
 }

}

Since I was getting the same error as Raghu Chahar in his comment below https://stackoverflow.com/a/35285745/19357591 here some minor changes to allow Shanoor's answer to work.

The current release of exceljs creates a "StreamBuf" object which is utilising "readable-stream" version3.6 to allow streams to be compatible with e.g. nodejs 10. However, this StreamBuf has no "state", so reading "endEmitted" will lead to "Cannot read property 'endEmitted' of undefined". This means usage of this.workbook.stream.on('readable', function() {...}) is not possible.

What will work just fine is creating a writable stream, and do the push in its _write method.

The complete example based on Shanoor's solution (with creation of workbook and worksheet inside ExcelTransform):

var stream = require('stream');
var util = require('util');
var Excel = require('exceljs');

var ExcelTransform = function() {
  stream.Transform.call(this, {
    writableObjectMode: true,
    readableObjectMode: false
  });

  var writable = new stream.Writable({
    objectMode: false
  });
  var that = this;
  writable._write = function(chunk, encoding, next) {
    that.push(chunk);
    next();
  }

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

  this.worksheet = this.workbook.addWorksheet('sheet 1');
  this.worksheet.columns = [{
    header: 'Name',
    key: 'name'
  }];
}

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


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()).pipe(process.stdout);