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.

[antonabramovich]

@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.

[antonabramovich]

@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) ?

[antonabramovich]

@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:
#61 (comment)

[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:
#61 (comment)

[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.

[edbrannin]

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 GB in 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:

• 12 chunks (about 9k rows per file) all completed in ~30 seconds
• 9 chunks (about 11k rows per file) all completed in ~34 seconds
• 6 chunks (about 16k rows per file) all completed in ~37 seconds
• 3 chunks (about 33k rows per file) all completed in ~50 seconds
• One chunk of 100k records exceeded the 90 seconds I gave it in a benchmarking setting.
  • When I tried that amount in my application, it churned for hours and never completed.
  • (The application is now working fine with output files split by max 20k rows)

(This was on node 12.20, via node-xlsx)

[UzairAhmad-1]

@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?

[kisaragi99]

Had an experience when a browser extension caused infinite parsing(sheet_to_json method was not returning data).
When i disabled it - parsing of the file was successfully done.

Browser: Chrome
Extension: Metamask
File: 500k rows, 52mb

Smaller files were parsed successfully even with enabled Metamask ext.
Have no time to make more tests.

[Qwqw127]

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?

hello,How do you deal with this problem？I came across this, too

[davidfoxio]

I'm getting the following errors on a javascript script file that I'm trying to run:

Error: Cannot create a string longer than 0x1fffffe8 characters
at ReadStream.emit (events.js:388:22) {
  code: 'ERR_STRING_TOO_LONG'
}

Here is the code I'm trying to run:

var fs = require('fs');
var XLSX = require('xlsx');

var parseData = async () => {
  var file = fs.createReadStream('./data/data.ods');
  function process_RS(stream, cb) {
    var buffers = [];
    stream.on('data', function (data) {
      buffers.push(data);
    });
    stream.on('end', function () {
      var buffer = Buffer.concat(buffers);
      var workbook = XLSX.read(buffer, { type: 'buffer' });

      /* DO SOMETHING WITH workbook IN THE CALLBACK */
      cb(workbook);
    });
  }

  process_RS(file);
};

parseData();

Would really appreciate some help with this, thanks

[kumarr10]

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

[devgrigor]

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.

s[tag.r] = p;

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 useFiles flag combining with recordsPerFile number or something similar

[SheetJSDev]

@davidfoxio ODS files are ZIP files under the hood and the library tries to decompress the content.xml entry before processing. The underlying file size is probably larger than 0x1fffffe8 characters, 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

[devgrigor]

@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

    let buf;
    await new Promise((resolve, reject) => {
        fs.readFile(inputFilePath, (err, data) => {
            buf= data
            resolve('')
        })
    })
    // code reaches here in seconds
    const xslJson = XLSX.read(buf, {type: 'buffer'});
    // code doesn't reach here for minutes

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 read operation 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 useFiles is 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)

[nguyenvantruong17]

@SheetJSDev I'm trying to parse a ".xlsx" file to "JSON" uploads from the Client/Browser with a Sheet that has 30k rows and 685 columns but getting an "empty string" value in "Sheets". Then I reduced it from 30k to 22.5k rows and it worked correctly.
Do you know why? I worry about that because my data is growing up day by day.

The following code below uses Reactjs as a Client Framework.

const onUploadFile = useCallback(
    (event: React.ChangeEvent<HTMLInputElement>) => {
      const reader = new FileReader();
      const file = event.target.files?.[0];

      if (!file) return;

      reader.readAsBinaryString(file);

      reader.onload = (e: ProgressEvent<FileReader>) => {
        const data = e.target?.result;
        const workbook = read(data, { type: "binary", dense: true });
        const result: Record<string, unknown> = {};
        workbook.SheetNames.forEach((sheetName: string) => {
          const roa = utils.sheet_to_json(workbook.Sheets[sheetName]);
          result[sheetName] = roa.length > 0 ? roa : [];
        });
      };
    },
    []
  );

The "Employee Data" has a value of "empty string":

[iplitt]

I need some help understanding the row, column, and memory limits of this library.
I'd like to predict its behavior in advance so that I can avoid failing scenarios going forward.

I had a 126 MB XLSX file with more than 1,000,000 rows and 27 columns which failed to ingest - the library found zero rows in the worksheet.
I used the 'dense' option when reading the file.
I tried several work-arounds which all succeeded but I don't know exactly why.

1. I tried reducing the number of rows and succeeded when the row count was reduced to 529,000 and the file size was 63MB.
2. I tried reducing the number of columns to 10 and succeeded again. This time the file size was 48MB.
3. I saved the original file as an XLSB (Binary) file and succeeded again. This time the file size was 26MB.

[bloodykheeng]

You Guys facing the issue of browser freezing try to use workers for me they really helped me
hei i think the best soution is to use workers m using react but when i tried workers browser no longer freezez
@simyara
@hmalphettes
@edbrannin
@osher
@oliverzy
@antonabramovich

 const createWorker = () => {
        const blob = new Blob(
            [
                `
                importScripts('https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.17.0/xlsx.full.min.js');
                self.onmessage = async function(e) {
                    try {
                        const file = e.data;
                        const data = await file.arrayBuffer();
                        const workbook = XLSX.read(data, { type: 'array' });
                        const worksheet = workbook.Sheets[workbook.SheetNames[0]];
                        const jsonData = XLSX.utils.sheet_to_json(worksheet, {
                            header: 1,
                            defval: "",
                        });
                        self.postMessage({ status: 'success', data: jsonData });
                    } catch (error) {
                        self.postMessage({ status: 'error', error: error.message });
                    }
                };
            `,
            ],
            { type: "application/javascript" }
        );

        return new Worker(URL.createObjectURL(blob));
    };

read excel file

const readExcel = (file) => {
        return new Promise((resolve, reject) => {
            const worker = createWorker();

            worker.onmessage = function (e) {
                if (e.data.status === "success") {
                    resolve(e.data.data);
                } else {
                    reject(new Error(e.data.error));
                }
                worker.terminate();
            };

            worker.onerror = function (e) {
                reject(new Error(e.message));
                worker.terminate();
            };

            worker.postMessage(file);
        });
    };