cell().style() is blocking; excessive with large number of cells

[enesien]

Describe the bug I'm creating a spreadsheet with over 20k rows, each with ~50 columns.

I apply a relatively simple style:

let borderStyle = wb.createStyle({
        border: {
            left: {
                style: 'thin',
                color: '000000'
            },
            right: {
                style: 'thin',
                color: '000000'
            },
            bottom: {
                style: 'thin',
                color: '000000'
            },
            top: {
                style: 'thin',
                color: '000000'
            }
        }
    });

console.time('style');
ws.cell(12, 1, 20000, 50).style(borderStyle);
console.timeEnd('style');

Console.time indicates this takes about 40 seconds to complete, all the while Node is completely blocked.

To Reproduce Create a spreadsheet with many rows and apply a style to each cell.

Expected Behavior This process should ideally not take so long, otherwise it should not block.

Environment

• Node Version: 10.15.3
• excel4node Version: [e.g. 1.7.2]

[natergj]

I agree that the library can be further optimized and asynchronous versions of methods could be added to help with this, I just don't have the time to do that right now. When I initially wrote this library, exporting huge datasets was not one of the things I was trying to solve for. Right now you're iterating over nearly 1 million cells and in a synchronous world that is going to take a while and be blocking. I see two options that you may have to work around this limitation.

1. manually iterate over the cells in an asynchronous manner and apply styles to smaller chunks (possible one row at a time?)
2. extract the workbook generation and use a child_process to write the actual Excel file to a temp directory and then use the main process to read and return the file upon the child process completion.

You may also want to look into the experimental worker threads in node 10 and later.

[enesien]

Thanks for the response! I understand not having the time. I'm thinking I can do something like your first suggestion in cases like this, possibly using setImmediate() every 100 iterations or as needed.

Thanks again.

[enesien]

@natergj I'm having additional problems and I'd rather not open a new issue since it's likely related.

I'm trying to create a spreadsheet with ~8990605 cells but I'm hitting some kind of limitation and the workbook.cell() function begins to take ~3000ms per cell, effectively stopping the processing in its tracks. Do you know of a limitation within excel4node or its dependencies that could be causing this? Any help would be appreciated - thank you.

[natergj]

@enesien, I'd need to see a code example to look more into that one. The issue you're seeing seems a bit odd. Could you create a github gist with sample code that demonstrates the issue and post the link here?

[LucGranato]

Hi @enesien , Have you solved your problem? I'm facing very slow cell inputs too. Thanks!