XLSX.write() taking too long

[irfanyounas]

Hi,
I have tried to write 3 columns and N number of rows, below we can see the time taken (on my i3 machine):

N --------------- TIME (s)
10000 --------------- 9
20000 --------------- 35
30000 --------------- 90
65000 --------------- 453

We can see that as we increase the number of rows, the time taken by the write() function increases dramatically (its not linear), its taking too long for 65000 records.
Can we have some other way which is efficient?

[SheetJSDev]

I am not happy with the performance at all (granted, our first goal was to be correct). There are quite a few slow/inefficient operations that can be improved.

When I last investigated, the runtime was dominated by functions from the ZIP library (we are currently using https://github.com/Stuk/jszip), so I'm working on a new component right now (https://github.com/SheetJS/js-crc32 and https://github.com/SheetJS/js-adler32 are the first few pieces of the puzzle).

[irfanyounas]

Thanks, good work. I hope so we will have efficient solution soon.

[SheetJSDev]

@irfanyounas just so we are on the same page, can you share the script you used?

[irfanyounas]

I am using the script give below:
src="//cdnjs.cloudflare.com/ajax/libs/xlsx/0.7.6-g/xlsx.core.min.js"

For creating xlsx file, I have followed the following example:
http://sheetjs.com/demos/writexlsx.html

[SheetJSDev]

@irfanyounas how did you create a JS object with 65000 rows? Do you have numbers / text / dates? Are there missing cells (in the example, C2 is missing) or did you fill every cell?

[SheetJSDev]

@irfanyounas Here's an example writing numbers in a 3x100001 file: http://sheetjs.com/demos/100001.html

Dates are printed at 3 times:

• just before starting to build the workbook
• after the write
• after the saveas call

On Chrome 35 it takes 4 seconds to process a much larger test case than what you showed.

[irfanyounas]

Thanks, I have 3 'text' (string) columns. The second column is empty sometimes.
I have used the following script:

function writeToXLSX(data) {
    var ws_name = "AllMembers",
          wb,
          wbout;
    wb = new Workbook(), ws = sheet_from_array_of_json(data, ["address", "name", "status"]);
    wb.SheetNames.push(ws_name);

    wb.Sheets[ws_name] = ws;
    wbout = XLSX.write(wb, {bookType:'xlsx', bookSST:true, type: 'binary'});
    postMessage({t:"data", d:wbout});
}

function Workbook() {
    if(!(this instanceof Workbook)) return new Workbook();
    this.SheetNames = [];
    this.Sheets = {};
}

function stringToArrayBuffer(s) {
    var buf = new ArrayBuffer(s.length);
    var view = new Uint8Array(buf);
    for (var i=0; i!=s.length; ++i) view[i] = s.charCodeAt(i) & 0xFF;
    return buf;
}


function sheet_from_array_of_json(data, columnNames) {
    var ws = {}, jsonObj, cell, cell_ref;
    var range = {s: {c:0, r:0}, e: {c:0, r:0 }};
    for(var R = 0; R != data.length; ++R) {
                jsonObj = data[R];

                if(range.e.r < R) range.e.r = R;
                for(var C = 0; C != columnNames.length; ++C) {

                        if(range.e.c < C) range.e.c = C;

                        if (jsonObj.hasOwnProperty(columnNames[C])) {
                cell = {v: jsonObj[columnNames[C]]};
                        } else {
                continue;
                        }
            if(cell.v == null)  {
                            continue; 
                        }

            cell_ref = XLSX.utils.encode_cell({c:C,r:R});

            if(typeof cell.v === 'number') cell.t = 'n';
            else if(typeof cell.v === 'boolean') cell.t = 'b';
            else if(cell.v instanceof Date) {
                cell.t = 'n'; cell.z = XLSX.SSF._table[14];
                cell.v = datenum(cell.v);
            }
                    else cell.t = 's';

            ws[cell_ref] = cell;
        }
    }
    ws['!ref'] = XLSX.utils.encode_range(range);
    return ws;
}

[SheetJSDev]

@irfanyounas http://sheetjs.com/demos/write_num.html?n=1000000 is a more general demo (you can specify the number of rows via the n parameter). The runtime appears to be dominated by generating the source array-of-arrays (not from the process that converts it to a workbook, and not from the process that generates the XLSX file).

Here are the times I see using the aforementioned page:

Number of Rows	Time
100K	4 sec
200K	8 sec
300K	15 sec
400K	18 sec
500K	Chrome crashes ...

[SheetJSDev]

@irfanyounas just saw your last message -- where are you measuring the times? Your code sample doesn't show it. Also, can you put up the main page (it looks like you shared only the worker logic)

[irfanyounas]

Thanks, I am looking into it, may the problem is on my side. Thanks for your help

[SheetJSDev]

If you are using web workers, I recommend also timing the process in the main thread (avoid we workers altogether). FWIW I found in some cases that transferring data to and from the worker is significantly slower than just doing the work in the main thread.

[irfanyounas]

I have tested it using different kinds of data:

Scenario 1: I create a list of 20,000 records, where each records is a dict
as given: {"status": "active", "name": "", "address": "sms:+45705846525"}.
In this scenario all the records are same (copy of the dict shown above).

Scenario 2: The list of 20,000 records, where each recordsis a dict but
each record is different.

Now runtime for Senario 1 is 4 sec, while for Scenario 2, it is around 14
sec.

As per my understanding, May be zip module is taking long due to different
records (not same text) in Scenario 2.
Can you please try this scenario?

On Tue, Jul 1, 2014 at 2:11 PM, SheetJSDev notifications@github.com wrote:

If you are using web workers, I recommend also timing the process in the
main thread (avoid we workers altogether). FWIW I found in some cases that
transferring data to and from the worker is significantly slower than just
doing the work in the main thread.

—
Reply to this email directly or view it on GitHub
#77 (comment).

[SheetJSDev]

Here's a good example: http://sheetjs.com/demos/write_text.html?n=8000. The length of the data in the B column increases as you go down. I do see a massive performance issue

[irfanyounas]

Yes, though we can keep the length of the data in B column fixed, the performance issue will also be there if the data is different, e.g, in my case the data looks like 'sms:+45704055659', 'sms:+457683738392', 'sms:+457049821521' and so on.....

[SheetJSDev]

I did some more testing by adding some timing statements (simple console.log(new Date().getTime()) all over the place) and about 90% of the runtime is in the zip.generate operation, which suggests that it should be replaced.