Skip to content

Claviz/xlstream

Repository files navigation

Build Status codecov npm

xlstream

Memory-efficiently turns XLSX file into a transform stream with all its benefits.

  • Stream is pausable.
  • Emits all default events (data, end, etc.)
  • Returns header, raw and formatted row data, as well as totalSheetSize and processedSheetSize (in bytes) in just one data event.
  • Maintains desirable behavior of merged cells.
  • Supports files created by OpenXML.
  • Supports standard, Excel and custom number formats.

Installation

npm install xlstream

Example

source.xlsx contents:

A B
hello 123

Where 123 is a 123.123 number formatted to be rounded to integer.

Script:

const { getXlsxStream } = require("xlstream");

(async () => {
  const stream = await getXlsxStream({
    filePath: "./source.xlsx",
    sheet: 0,
  });
  stream.on("data", (x) => console.log(x));
})();

Result:

{
    "raw": {
        "obj": { "A": "hello", "B": 123.123 },
        "arr": [ "hello", 123.123 ]
    },
    "formatted": {
        "obj": { "A": "hello", "B": 123 },
        "arr": [ "hello", 123 ]
    },
    "header": [],
    "totalSheetSize": 1110,
    "processedSheetSize": 1110
}

getXlsxStream

Returns transform stream of the sheet.

Options

option type description
filePath string Path to the XLSX file
sheet string or number If string is passed, finds sheet by it's name. If number, finds sheet by it's index.
withHeader boolean or number If true, column names will be taken from the first sheet row. If duplicated header name is found, column name will be prepended with column letter to maintain uniqueness. 0-based row location can be passed to this option if header is not located on the first row.
ignoreEmpty boolean If true, empty rows won't be emitted.
fillMergedCells boolean If true, merged cells will have the same value (by default, only the first cell of merged cells is filled with value). Warning! Enabling this feature may increase streaming time because file must be processed to detect merged cells before actual stream.
numberFormat standard or excel or object By default standard format is used. Excel implementation of number formatting differs from standard (can be read here) so excel option can be used to match this difference. If custom formatting is needed, a dictionary object can be passed to this option.
encoding string Sets file encoding.

getXlsxStreams

Async generator which yields transform streams of the sheets.

Options

Option Type Description
filePath string Path to the XLSX file
sheets array of sheet objects Options of sheet object can be found below.
encoding string Sets file encoding.

Sheet object

option type description
id string or number If string is passed, finds sheet by it's name. If number, finds sheet by it's index.
withHeader boolean If true, column names will be taken from the first sheet row. If duplicated header name is found, column name will be prepended with column letter to maintain uniqueness. 0-based row location can be passed to this option if header is not located on the first row.
ignoreEmpty boolean If true, empty rows won't be emitted.
fillMergedCells boolean If true, merged cells will have the same value (by default, only the first cell of merged cells is filled with value). Warning! Enabling this feature may increase streaming time because file must be processed to detect merged cells before actual stream.
numberFormat standard or excel or object By default standard format is used. Excel implementation of number formatting differs from standard (can be read here) so excel option can be used to match this difference. If custom formatting is needed, a dictionary object can be passed to this option.

getWorksheets

Returns array of sheets with name and hidden info.

Options

Option Type Description
filePath string Path to the XLSX file

Building

You can build js source by using npm run build command.

Testing

Tests can be run by using npm test command.