@atomictech/xlsx-write-stream
v2.0.2
Published
Stream huge amount of data into an XLSX generated file stream with minimum memory footprint.
Downloads
7,571
Readme
⏩ 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
📦 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, '🦄'];
or
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
XLSXWriteStream ⇐ Transform
new XLSXWriteStream([options])
Create new stream transform that handles Array or Object as input chunks. Be aware that first row chunk is determinant in the transform configuration process for further row chunks.
| Param | Type | Default | Description |
| --- | --- | --- | --- |
| [options] | Object
| | |
| [options.header] | Boolean
| false
| Display the column names on the first line if the columns option is provided or discovered. |
| [options.columns] | Array\|Object
| | List of properties when records are provided as objects. Work with records in the form of arrays based on index position; order matters. Auto discovered in the first record when the user write objects, can refer to nested properties of the input JSON, see the header
option on how to print columns names on the first line. |
| [options.format] | Boolean
| true
| If set to false writer will not format cells with number, date, boolean and text. |
| [options.styleDefs] | StyleDefs
| | If set you can overwrite default standard type styles by other standard ones or even define custom formatCode
. |
| [options.immediateInitialization] | Boolean
| false
| If set to true writer will initialize archive and start compressing xlsx common stuff immediately, adding subsequently a little memory and processor footprint. If not, initialization will be delayed to the first data processing. |
StyleDefs
A little of TypeScript to explain StyleDefs interface:
enum TypeStyleKey = {
NUMBER: 'default', //!\\ Unused in the actual type conversion
INT: 'int', // Integer <1000
FLOAT: 'float', // Float <1000
BIG_INT: 'bigInt', // Integer >=1000
BIG_FLOAT: 'bigFloat', // Float >=1000
EXP_NUMBER: 'expNumber', // Number with more than 10 digits/characters (ex: 10000000000 or 12.45678901)
TEXT: 'text', // String
DATE: 'date', // Date
DATETIME: 'datetime' //!\\ Unused in the actual type conversion
};
interface TypeFormatReference {
numFmtId: number;
}
interface TypeFormatDefinition {
formatCode: string;
}
interface StyleDefs {
[typeKey: TypeStyleKey]: TypeFormatReference | TypeFormatDefinition;
}
Example:
{
date: { formatCode: 'yy-mm-dd hh:mm' },
int: { numFmt: 49 }
}
See here for other default numFmtId: https://docs.microsoft.com/en-us/dotnet/api/documentformat.openxml.spreadsheet.numberingformat?view=openxml-2.8.1
🚧 Compatibility
XLSX Write Stream - supported:
Cell type:
- string [starting with
=
] ➡ formula - string [others] ➡ text
- date ➡ date
- number ➡ number
- boolean ➡ boolean
Cell type formatting:
- text (default:
numFmtId: 49
- enforce text even if could be interpreted as number) - date (default:
formatCode: 'yyyy-mm-dd'
- ) - number
- int (default:
numFmtId: 1
) - float (default:
numFmtId: 2
) - bigInt (default:
numFmtId: 3
) - bigFloat (default:
numFmtId: 4
) - expNumber (default:
numFmtId: 1
)
- int (default:
XLSX Write Stream - NOT supported:
- charts
- comments
- ... and a myriad of other OOXML features. It's strictly a CSV replacement.
👥 Authors
👤 Apify
- Website: https://apify.com/
- Github: @apifytech
👤 AtomicTech
- Github: @atomictech
🤝 Contributing
Contributions, issues and feature requests are welcome!Feel free to check issues page. You can also take a look at the contributing guide.
⭐️ Show your support
Give a ⭐️ if this project helped you!
📝 License
This project is Apache-2.0 licensed.