@pmilitowski/xlsxstream v1.0.2

xlsxstream

xlsxstream is a module that creates xlsx files based on data sent through the stream. This allows you to create large xlsx files with little memory usage. The condition of the module's operation is the correct connection of streams. The best way is to use pipe function.

Menu

• Installation
• Example
• Create worksheet from stream
• Add many worksheets
• Worksheet
  • Column
  • Row
  • Merge cells

Installation

npm install --save @pmilitowski/xlsxstream

Example

var PassThrough = require('stream').PassThrough;
var Xlsxstream = require('@pmilitowski/xlsxstream');
var Worksheet = Xlsxstream.worksheet;

var fs = require('fs');
var writeStream = fs.createWriteStream('./example.xlsx');
var passThrough = new PassThrough();

var workbookStream = new Xlsxstream.WorkbookStream();

workbookStream
  .setReadStream(passThrough)
  .setWriteStream(writeStream);

passThrough.push(Xlsxstream.actions.addWorksheet('WorksheetName'));

var row = new Worksheet.Row();
row.addCell(new Worksheet.Cell('field1'));
row.addCell(new Worksheet.Cell('field2'));
passThrough.push(Xlsxstream.actions.defineRow(row));

passThrough.push(Xlsxstream.actions.addRow(row, {
  field1: 'value1',
  field2: 'value2'
}));
passThrough.push(Xlsxstream.actions.addRow(row, {
  field1: 'value3',
  field2: 'value4'
}));

passThrough.push(Xlsxstream.actions.closeWorksheet());
passThrough.push(Xlsxstream.actions.closeWorkbook());

Create worksheet from stream

var fs = require('fs');
var readStream = fs.createReadStream('./example.csv');
var csv = require('csvtojson');

var Transform = require('stream').Transform;
var Xlsxstream = require('@pmilitowski/xlsxstream');
var Worksheet = Xlsxstream.worksheet;

var writeStream = fs.createWriteStream('./example.xlsx');

var row = new Worksheet.Row();
row.addCell(new Worksheet.Cell('field1'));
row.addCell(new Worksheet.Cell('field2'));

var transform = new Transform();
transform._transform = function (chunk, encoding, callback) {
  var value = JSON.parse(chunk.toString());
  this.push(Xlsxstream.actions.addRow(row, value));
  callback();
};
transform._flush = function (callback) {
  this.push(Xlsxstream.actions.closeWorksheet());
  this.push(Xlsxstream.actions.closeWorkbook());
  callback();
  console.log('finish');
};

var dataStream = csv({ noheader: true })
  .fromStream(readStream)
  .pipe(transform);

transform.push(Xlsxstream.actions.addWorksheet('WorksheetName'));
transform.push(Xlsxstream.actions.defineRow(row));

var workbookStream = new Xlsxstream.WorkbookStream();

workbookStream
  .setReadStream(dataStream)
  .setWriteStream(writeStream);

Add many worksheets

var PassThrough = require('stream').PassThrough;
var Xlsxstream = require('@pmilitowski/xlsxstream');
var Worksheet = Xlsxstream.worksheet;

var fs = require('fs');
var writeStream = fs.createWriteStream('./example.xlsx');
var passThrough = new PassThrough();

var workbookStream = new Xlsxstream.WorkbookStream();

workbookStream
  .setReadStream(passThrough)
  .setWriteStream(writeStream);

passThrough.write(Xlsxstream.actions.addWorksheet('WorksheetName1'));

var row = new Worksheet.Row();
row.addCell(new Worksheet.Cell('field1'));
row.addCell(new Worksheet.Cell('field2'));
passThrough.push(Xlsxstream.actions.defineRow(row));

passThrough.push(Xlsxstream.actions.addRow(row, {
  field1: 'value1',
  field2: 'value2'
}));
passThrough.push(Xlsxstream.actions.addRow(row, {
  field1: 'value3',
  field2: 'value4'
}));

passThrough.push(Xlsxstream.actions.closeWorksheet());
passThrough.push(Xlsxstream.actions.addWorksheet('WorksheetName2'));

passThrough.push(Xlsxstream.actions.addRow(row, {
  field1: 'value5',
  field2: 'value6'
}));
passThrough.push(Xlsxstream.actions.addRow(row, {
  field1: 'value7',
  field2: 'value8'
}));

passThrough.push(Xlsxstream.actions.closeWorksheet());
passThrough.push(Xlsxstream.actions.closeWorkbook());

Worksheet

Column

var PassThrough = require('stream').PassThrough;
var Xlsxstream = require('@pmilitowski/xlsxstream');
var Worksheet = Xlsxstream.worksheet;

var fs = require('fs');
var writeStream = fs.createWriteStream('./example.xlsx');
var passThrough = new PassThrough();

var workbookStream = new Xlsxstream.WorkbookStream();

workbookStream
  .setReadStream(passThrough)
  .setWriteStream(writeStream);

passThrough.push(Xlsxstream.actions.addWorksheet('WorksheetName'));

// sets the width for columns 1 to 5
var minColumn = 1;
var maxColumn = 5;
var column = new Worksheet.Column(minColumn, maxColumn);
column.setWidth(30);
passThrough.push(Xlsxstream.actions.addColumn(column));

passThrough.push(Xlsxstream.actions.closeWorksheet());
passThrough.push(Xlsxstream.actions.closeWorkbook());

Row

var PassThrough = require('stream').PassThrough;
var Xlsxstream = require('@pmilitowski/xlsxstream');
var Worksheet = Xlsxstream.worksheet;

var fs = require('fs');
var writeStream = fs.createWriteStream('./example.xlsx');
var passThrough = new PassThrough();

var workbookStream = new Xlsxstream.WorkbookStream();

workbookStream
  .setReadStream(passThrough)
  .setWriteStream(writeStream);

passThrough.push(Xlsxstream.actions.addWorksheet('WorksheetName'));

var row = new Worksheet.Row();
row.setHeight(30);

var cell1 = new Worksheet.Cell('field1');
cell1.setIsFormula(true);
row.addCell(cell1);

var cell2 = new Worksheet.Cell('field2');
cell2.setType(Worksheet.Cell.types.NUMBER);
row.addCell(cell2);

var cell3 = new Worksheet.Cell('field3');
cell3.setType(Worksheet.Cell.types.NUMBER);
row.addCell(cell3);

var cell4 = new Worksheet.Cell('field4');
cell4
  .setType(Worksheet.Cell.types.NUMBER)
  .setNumberFormat(Worksheet.Cell.numberFormats['YYYY-MM-DD HH:MM']);
row.addCell(cell4);

passThrough.push(Xlsxstream.actions.defineRow(row));
passThrough.push(Xlsxstream.actions.addRow(row, {
  field1: 'SUM(B1:C1)',
  field2: 4,
  field3: 9,
  field4: '2017-06-05'
}));

passThrough.push(Xlsxstream.actions.closeWorksheet());
passThrough.push(Xlsxstream.actions.closeWorkbook());

Merge cells

var PassThrough = require('stream').PassThrough;
var Xlsxstream = require('@pmilitowski/xlsxstream');
var Worksheet = Xlsxstream.worksheet;

var fs = require('fs');
var writeStream = fs.createWriteStream('./example.xlsx');
var passThrough = new PassThrough();

var workbookStream = new Xlsxstream.WorkbookStream();

workbookStream
  .setReadStream(passThrough)
  .setWriteStream(writeStream);

passThrough.push(Xlsxstream.actions.addWorksheet('WorksheetName'));

// Merge A1:B1 cells
passThrough.push(Xlsxstream.actions.mergeCells(new Worksheet.MergedCells(1, 2, 1, 1)));

passThrough.push(Xlsxstream.actions.closeWorksheet());
passThrough.push(Xlsxstream.actions.closeWorkbook());