Excel XSSFWorkbook takes more than 4 to 5sec to load from Stream (Bulk data)

[utthungagokul]

NPOI Version

2.6.2

File Type

• [] XLSX
• XLS
• DOCX
• XLSM
• OTHER

Upload the Excel File

         -

Reproduce Steps

       The excel should have bulk data with multiple sheet.

Issue Description

While reading the bulk data (2500KB) file taken almost 4 to 5 sec just create a XSSWorkBook instance.

Attempt 1:

               using (FileStream stream = new FileStream(filePath, FileMode.Open, FileAccess.Read))
               {
                         XSSFWorkbook xssfWorkbook = new XSSFWorkbook(stream);  == > 4 to 5 sec 
               }

Attempt 2:

             IWorkbook xssfWorkbook;
             using (FileStream stream = new FileStream(filePath, FileMode.Open, FileAccess.Read))
             {                            
                     xssfWorkbook = WorkbookFactory.Create(stream);  == > 4 to 5 sec 
              }

Both Attempt 1 & 2 has same behavior.

[lahma]

Without a sample Excel file I would just suggest buying a faster machine.

[tonyqus]

@utthungagokul Are you sure it's just 2500KB (2.5M) in size? Or 2500K rows? Can you upload this Excel file to help reproduce the issue?

[utthungagokul]

@tonyqus I have attached the sample (.xlsx) file for your reference.

Configuration1 - Copy.xlsx

@lahma FYI , I using a dev system with Intel 11th Generation Processors with 16GB RAM.

[lahma]

Looking at the file there just seems to be a lot data, like Sheet7 having 25k rows. With NPOI release build it takes 2 secs to initialize the workbook on my machine (AMD Ryzen 9 5950X). One option could be to lazy-initialize some structures, but if you need to access the data anyway it would then be loaded making the lazy-loading moot.

[tonyqus]

@utthungagokul Have you tried ExcelDataReader of ClosedXML? I'd like to know their response time on this case.

[Bykiev]

@utthungagokul Have you tried ExcelDataReader of ClosedXML? I'd like to know their response time on this case.

I've tested with ClosedXML and here is result:

Elapsed NPOI: 9,0083647
Elapsed ClosedXML: 13,5467912

tested on core i3 6100u, code:

string filePath = "Configuration1.-.Copy (1).xlsx";

Stopwatch sw = new Stopwatch();
sw.Start();

using (FileStream stream = new FileStream(filePath, FileMode.Open, FileAccess.Read))
{
    using (XSSFWorkbook xssfWorkbook = new XSSFWorkbook(stream)) { }
}

sw.Stop();
Console.WriteLine("Elapsed NPOI: " + sw.Elapsed.TotalSeconds);

sw.Reset();
sw.Start();

using (FileStream stream = new FileStream(filePath, FileMode.Open, FileAccess.Read))
{
    using (var workbook = new XLWorkbook(stream)) { }
}

sw.Stop();
Console.WriteLine("Elapsed ClosedXML: " + sw.Elapsed.TotalSeconds);