⏩ XLSX Write Stream ⏩

Stream huge amount of data into an XLSX generated file stream with minimum memory footprint.

XLSX Write Stream is a streaming writer for XLSX spreadsheets. Its purpose is to replace CSV for large exports, because using CSV in Excel is very buggy and error prone. It's very efficient and can quickly write hundreds of thousands of rows with low memory usage.

Table of content

⏩ XLSX Write Stream ⏩

📦 Install

npm install @atomictech/xlsx-write-stream

🦄 Usage

For all usages keep in mind that you should feed your XLSXWriteStream instance one row at a time, where a row could be an array or an object.

const row = [1, '02', new Date('2015-10-21T16:29:00.000Z'), true, false, '🦄'];

const row = {
  'A Number Column': 1,
  'A Text Column': '02',
  'A Date Column': new Date('2015-10-21T16:29:00.000Z'),
  'A Boolean Column': true,
  'Another Boolean Column': false,
  'Another Text Column': '🦄'
};

Basic pipe

import XLSXWriteStream from '@atomictech/xlsx-write-stream';

// Initialize the writer
const xlsxWriter = new XLSXWriteStream();

// Pipe a Stream.Readable input stream into the writer
const inputStream = new MyCustomReadableStream();
inputStream.pipe(xlsxWriter);

// Pipe the writer into a Stream.Writable output stream in order to retrieve XLSX file data,
// write it into file or send it as HTTP response.
const writeStream = fs.createWriteStream('file.xlsx');
xlsxWriter.pipe(writeStream);

Basic write

import XLSXWriteStream from '@atomictech/xlsx-write-stream';

// [optional] Define some header
const columns = ['A Number Column', 'A Text Column', 'A Date Column', 'A Boolean Column', 'Another Boolean Column', 'Another Text Column'];

// Initialize the writer
const xlsxWriter = new XLSXWriteStream({ header: true, columns });

// Pipe the writer into a Stream.Writable output stream in order to retrieve XLSX file data,
// write it into file or send it as HTTP response.
const writeStream = fs.createWriteStream('file.xlsx');
xlsxWriter.pipe(writeStream);

// Write rows one by one with
const row = [1, '02', new Date('2015-10-21T16:29:00.000Z'), true, false, '🦄'];
xlsxWriter.write(row);
xlsxWriter.end(); // Do not forget to end the stream!

⚠️ Caution ⚠️

If you are not familiar with streams, in order to take advantage of the smallest memory footprint possible with this form, you need to be aware of backpressure concept and use the boolean returned by the write function!

See explanations here: writable.write(chunk[, encoding][, callback])

Custom styles

An options.styleDefs parameter is available in order to redefine type style formats.

import { TypeStyleKey } from '@atomictech/xlsx-write-stream';

// Declare custom styles definitions
const styleDefs = {};
styleDefs[TypeStyleKey.DATE] = { formatCode: 'yy-mm-dd hh:mm' };
styleDefs[TypeStyleKey.INT] = { numFmtId: 49 }; // 49 is "enforced text format"

// Create the writer
const xlsxWriter = new XLSXWriterStream({ styleDefs });

// NB: if you set `format: false` your styleDefs will not be used

🔧 API