Sheetjs: Support large excel files

[oliverzy]

currently this module will load the excel file into memory and build the js model, but it doesn't work for large file because memory limitation.

Is it possible to provide a stream like api so that we can handle only part of data at one time?

[SheetJSDev]

It's certainly "possible". Here are the roadblocks:

1) AFAICT the zip library does not support node streams API. That will need to be replaced.

2) The static interface will need to be replaced with some event-driven one (next Sheet, next Cell, etc) if the in-memory object is too large

3) The converters will need to be reimplemented using the new event-driven interface

On a side note, have you tried saving the file as XLSB? IME it's about half the size of the equivalent XLSX/XLSM

[hmalphettes]

I am interested in this too.

Going through XLSB will help with regard to the zip library but what I am most interested in is number 2 to avoid building the parsed object in memory: nextSheet, nextRow is the interface I would use.
Would you guys need more granularity: nextCell, nextComment etc?

Also @SheetJSDev do you intend to add callbacks to the interface for async processing of the parsed data?

[SheetJSDev]

@hmalphettes I used the phrase 'event-driven' but yes that will involve callback functions.

We'd have to look through the test suite, but my initial concern is that certain data won't be available until you process the entire file (for example, if dimensions are not included in the sheet, the parser keeps track of the first and last seen column as well as the first and last seen row when calculating the range for the worksheet). Comments, merge cells and other metadata potentially could be stored after the actual sheet data (although we'd have to dig into some test cases to see what writers do)

[hmalphettes]

OK @SheetJSDev.

[antonama]

@SheetJSDev, it will be nice to figure out, what the max file size supported today

[SheetJSDev]

@antonama it's a tricky question because XLSX/XLSM/XLSB are zip files and some very large sheets can be stored in a small file (and vice versa -- a small string-heavy sheet will appear to be big).

For example, the command line tool xlsx (install the xlsx module in nodejs) can "handle" the 43MB calendar stress test (with cells from A1 to H1048576). This file has only a few styles and no charts or other jazz, which arguably is the best case scenario.

It "handles" the file insofar as it took a full minute on my computer. YMMV. IE6 seems to crash on files with more than 30 cells (it appears that IE6 tries to warn about a slow-running script, but that is thwarted by the fact that the script locks up the browser while processing)

Due to github's file size limitation, I don't have file larger than 50MB in the test suite.

[antonama]

@SheetJSDev, your file sizes are really impressive. I'm trying to convert file ~2MB size and I get blank output. And I don't have any other 'jazz' except raw text. Could you assume, why?

[SheetJSDev]

@antonama can you share that file (either email me directly -- sheetjs at the google email service -- or put the file up in a public location) and describe your conditions (OS, browser version, webpage/script) ?

[antonama]

@SheetJSDev, hmmm, I'm really sorry. I've just noticed macros in file. Is it may be the issue?

[SheetJSDev]

I doubt macros are the issue.

If you are seeing a blank screen (no output) can you check the javascript console? In Chrome, hit control-shift-j on windows / command-option-j on mac.

[nshopik]

Is large document suppose crash tab process? I have 4mb xlsx which converted from xls 17mb file, after 15 seconds of processing chrome tab crashes

[SheetJSDev]

Could you share a file? If not, can you at least share how many cells are
in the sheet?

Also, can you check if the node module
(https://www.npmjs.org/package/xlsx) successfully handles the file? If
you npm install -g xlsx then you can run xlsx your_file.xlsx.

Is large document suppose crash tab process? I have 4mb xlsx which
converted from xls 17mb file, after 15 seconds of processing chrome tab
crashes

---

Reply to this email directly or view it on GitHub:
https://github.com/SheetJS/js-xlsx/issues/61#issuecomment-55886087

[nshopik]

Sure here it is https://yadi.sk/d/gB5Qe8yMbXosL
Original XLS is parced correctly but when I converted it to XLSX always crash tab

[SheetJSDev]

The website uses the gh-pages branch. Can you check against the master
branch? To do this:

$ git clone https://github.com/SheetJS/js-xlsx
$ cd js-xlsx
$ python -mSimpleHTTPServer

Then go to http://localhost:8000 and try to repeat the test. I can
reproduce the problem on http://oss.sheetjs.com/js-xlsx (which uses the
gh-pages branch), but this oddly works in the master branch.

Sure here it is https://yadi.sk/d/gB5Qe8yMbXosL
Original XLS is parced correctly but when I converted it to XLSX always
crash tab

---

Reply to this email directly or view it on GitHub:
https://github.com/SheetJS/js-xlsx/issues/61#issuecomment-56007194

[nshopik]

I though gh-pages runing on master. Anyway master branch works for this file correctly.

But I did try another even more complex file (9,2mb) and this time master branch crash process too. its more complex file with 126 sheets inside of with different sizes of rows per sheet. I can share it privately if you let me know how.

[SheetJSDev]

You can email it to the gmail account listed at https://github.com/SheetJS or to the email address in the git log (the commit email is hosted on my own server directly)

Sent from my iPad

On Sep 19, 2014, at 6:16 AM, Nikolay Shopik [email protected] wrote:

I though gh-pages runing on master. Anyway master branch works for this file correctly.

But I did try another even more complex file (9,2mb) and this time master branch crash process too. its more complex file with 126 sheets inside of with different sizes of rows per sheet. I can share it privately if you let me know how.

—
Reply to this email directly or view it on GitHub.

[nshopik]

So it appears disabling "Web Workers" helps with larger files, and from what I can see its even little bit faster

[osher]

I could offer a different API if it makes it simpler and should not require a heavy rewrite:

The options may accept an event emitter (or any object with the method "emit(name,...args)).
When such emitter is provided - the parser should events in the following spirit:

• 'doc-open' - should pass the file path, when the doc is found valid and is being passed to parsing.
• 'sheet-start' - should pass the file path, name of sheet, index of sheet, when a sheet is being opened.
• 'row' - should pass the row on top of the args to sheet-start when a row in the sheet has been fully read - (*) - may not contain calculated values
• 'sheet-end' - should pass the sheet, the sheet index when the sheet is concluded.
• 'doc-end' - should pass the doc. All values in this stage should be fully populated

I'd recommend all events to be passed with a context reference on which the passed arguments should be found.
A context - depending in the event it fires for - may contain attributes (or getters for entries ) such as:

• ctx.currentSheet
• ctx.currentSheetIndex
• ctx.currentRow
• ctx.currentRowIndex
• ctx.book

The only thing I cannot consder in full by myself is the implication of calculated fields.
I know that calculated cells in a given sheet may pull values from preceding sheets...

I could offer for this

• 'sheet-calculated' - should emit when the sheet is fully calculated (all data on which it depends has been loaded
  However, this obviously an advanced feature and may not be included in the first release of this features.
  I have a feeling that a big workbook with inter-sheet formulas is an edge case.

What do you think?

[SheetJSDev]

We've started addressing the general performance problem in node:

• internally, instead of the address keys, worksheets are built up as arrays of arrays of cell objects. This behavior is controlled with the undocumented dense option. The read and write utilities know how to handle both types of worksheets, so if you are just translating formats you can enable it in your read interface, but raw manipulation requires a slightly different access pattern. The test suite uses a small helper function:

function get_cell(ws/*:Worksheet*/, addr/*:string*/) {
    if(!Array.isArray(ws)) return ws[addr];
    var a = X.utils.decode_cell(addr);
    return (ws[a.r]||[])[a.c];
}

• the XLSX object now has a streaming CSV write function XLSX.stream.to_csv which takes the same parameters as the normal XLSX.utils.sheet_to_csv. Instead of building the whole CSV in memory, it builds one line at a time and passes the data.

Besides the general issue of non-conformant files, there are more hurdles preventing a true streaming read solution:

1) you actually have to buffer the entire file before processing. For XLSX/XLSB the zip container metadata is at the end of the file (you have to read till the end anyway), so most "streaming" solutions buffer the file anyway

2) lots of metadata is stored at the end of the worksheet streams. Merge cell metadata is a good example. You have to jump to the end of the worksheet XML/binary to find the part encoding the merge ranges, so the entire file has to be held in memory

3) the dimension data reported in the worksheet may disagree with the true worksheet size, so you'd have to scan the whole thing to figure out the true worksheet size.

Now, it's possible to conserve memory at the expense of time by performing two passes of the worksheets: collect relevant metadata in the first pass then emit cell/row events in the second pass. Most formats store in row-major order so it's fairly straightforward to implement (some quattro pro formats use column major order but those aren't show-stoppers).

@osher the original stumbling block was the ZIP/CFB container format. You basically have to read the whole file into memory to start processing. If that behavior is acceptable, and if it's acceptable to limit the feature set, we could easily patch in support for streaming read

[the-spyke]

jszip now supports streaming. In my tests it consumes even more memory than creating document itself.