Support large excel files
Openopened 9 years ago by oliverzy · 29 comments
No Branch/Tag Specified
good first issue
No Assignees1 Participants
The due date is invalid or out of range. Please use the format 'yyyy-mm-dd'.
No due date set.
No dependencies set.
Reference in New Issue
There is no content yet.
Delete Branch '%!s(<nil>)'
Deleting a branch is permanent. It CANNOT be undone. Continue?
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?
It's certainly "possible". Here are the roadblocks:
AFAICT the zip library does not support node streams API. That will need to be replaced.
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
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
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?
@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)
@SheetJSDev, it will be nice to figure out, what the max file size supported today
@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
xlsxmodule 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.
@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?
@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) ?
@SheetJSDev, hmmm, I'm really sorry. I've just noticed macros in file. Is it may be the issue?
I doubt macros are the issue.
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
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
npm install -g xlsxthen you can run
Sure here it is https://yadi.sk/d/gB5Qe8yMbXosL
Original XLS is parced correctly but when I converted it to XLSX always crash tab
The website uses the gh-pages branch. Can you check against the master
branch? To do this:
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.
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.
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
So it appears disabling "Web Workers" helps with larger files, and from what I can see its even little bit faster
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:
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:
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
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?
We've started addressing the general performance problem in node:
denseoption. 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:
XLSX.stream.to_csvwhich 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:
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
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
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
jszipnow supports streaming. In my tests it consumes even more memory than creating document itself.
FWIW, I've experienced much-worse-than-linear performance on very large spreadsheets even stored in plaintext SpreadsheetML (bookType:
xlml), so I don't think this can be pinned just on the Zip library.
The spreadsheet I was filling out was pretty sparse, but it had columns going out to
GBin Excel. I had a total of 100k rows to write, and I timed how long it would take to write all files when I split them into evenly-balanced chunks:
(This was on node 12.20, via node-xlsx)
@SheetJSDev Is it possible to read an excel file with 1M rows and the file size is around 220MB. How can I read such a file?
Had an experience when a browser extension caused infinite parsing(
sheet_to_jsonmethod was not returning data).
When i disabled it - parsing of the file was successfully done.
File: 500k rows, 52mb
Smaller files were parsed successfully even with enabled Metamask ext.
Have no time to make more tests.
hello,How do you deal with this problem？I came across this, too
Here is the code I'm trying to run:
Would really appreciate some help with this, thanks
I am trying to use the api json_to_sheet to create excel file(I return 300K rows from backend API) on client side/browser. Sheet_to_json seems to error out with this much data. Any help or alternate ? please help
let jsonData = data.Table; let ws = XLSX.utils.json_to_sheet(jsonData); var wb = XLSX.utils.book_new(); XLSX.utils.book_append_sheet(wb, ws); XLSX.writeFile(wb, filename + '.xlsx');
Though, I have created an issue for this , I thought active community could help if they have already resolved it for themselves
Small suggestion about this. I was looking into the source code and augmented it to write the result of the parsed xml data to file instead of variable. This way there is no memory issue, although the final result must be rendered as well.
This line can be replace by a for loop that writes to file if the result is larger than given number. I didn't want to open a PR with feature because I ended up using files without parsing into a final result, but maybe there is a more generic solution that can be implemented into the library at some point ? Something like
useFilesflag combining with
recordsPerFilenumber or something similar
@davidfoxio ODS files are ZIP files under the hood and the library tries to decompress the
content.xmlentry before processing. The underlying file size is probably larger than
0x1fffffe8characters, hence the error (this is a V8 limit)
@devgrigor The general approach would use a callback or iterator or stream, generating X rows at a time and allowing the end code to process. This would avoid building up the entire worksheet in memory. It works well for simple row-level operations like generating SQL statements or CSV rows but does not have the flexibility of the in-memory object approach
@SheetJSDev I am afraid the file does not exceed the limit. The file I've used was 55 MB. And it didn't throw an error it just stopped working once it reached 400231th row :) when I opened task manager it showed that the node process uses 2.7GB memory of RAM. I believe it was waiting for a memory to be available to continue. This was simple node process (an empty js file with nothing than read operation of XLSX) Basically the code was the following
It is about having a single variable with that size, once the underlying code was modified to use files to write down the json the
readoperation was being done in 1,2 minutes. My solution was using those files directly after the read operation was done. I was suggesting adding that solution to the library. So when the flag
useFilesis true it returns the file path list (array of strings) that contains the information parsed in JSON format, so the end developer can access each file separately and not think about overusing the RAM (obviously one will be obligated to handle the file removal as well, but that will be on them rather on the lib itself)