Open file with lots of data is pretty slow

[allenz8512]

Such as a sheet with 500000 cells, it cost beyond 1 minute to load, while it cost 4 seconds using NPOI on the same machine.

[rabanti-github]

Hello,
Thank you for your report.
But could you please provide some further information?:

• What version of NanoXLSX do you use (still stable 2.6.7 or already a release candidate of v3)?
• What kind of data are you loading (just strings, numeric data, very volatile data , dates, etc.)?
• Do the cells use a lot of styles?
• How do you load the data (by file, by stream, by file async, by stream async)?
• What is your setup roughly (used .net Version)?

Thank you in advance for clarification.

[allenz8512]

• Version: 2.x or 3.0rc2
• Unity 6.0.17, mono backend，.net standard 2.1
• Use =SEQUENCE(10000,50,0,1) to create 500000 cells in a sheet
• Create some test like below:

        [UnityTest]
        public IEnumerator NanoXLSXRead() => UniTask.ToCoroutine(async () =>
            {
                byte[] bytes = await ExternalAssetUtils.ReadBytes("Test/test_excel");
                DateTime start = DateTime.Now;
                using (MemoryStream stream = new MemoryStream(bytes))
                {
                    Workbook workbook = await WorkbookReader.LoadAsync(stream);
                    Worksheet sheet = workbook.GetWorksheet(0);
                    Cell cell = sheet.GetCell(0, 0);
                    Assert.AreEqual("0", cell.Value.ToString());
                    DateTime end = DateTime.Now;
                    Debug.Log($"NanoXLSX Read: {(end - start).TotalMilliseconds}ms Used");
                }
            }
        );

• Result:

ClosedXML Read: 2838.8302ms Used
NanoXLSX Read: 21846.1548ms Used
NPOI Read: 1466.7362ms Used

[rabanti-github]

Will be part of optimizations (attempts / options) in the upcoming v3 architecture

[rabanti-github]

Performance was addressed in the recent update v3.1.0.
According to measurements, the reading speed could be increased up to factor 3.
In the test setup of 500k random cells, the following results were measured:

library	mean read time (ms)	mean CPU core usage (%)	mean process memory (MB; peak)	performance index
ClosedXML	2138	53.9	119	0.21
NanoXLSX	1893	52,8	123	0.24
NPoi	455	67,3	69	1.0

In the test setup of 5 Million random cells, the following results were measured:

library	mean read time (ms)	mean CPU core usage (%)	mean process memory (MB; peak)	performance index
ClosedXML	21306	57	686	0.09
NanoXLSX	16758	54	913	0.12
NPoi	1945	50.8	448	1.0

Methodology:

• 11 Iterations, where 1st was not considered in the calculation of mean read time (1st as heat-up phase)
• GC.Collect (best effort) between each iteration
• 5 Seconds delay at startup, to let profiler do its setup
• Each Library was tested separately
• Mean times and Memory/CPU figures were executed in different runs
• The file to read was identical for all runs and libraries

There is still a lot room for improvement, but NanoXLSX should now be on the line (or slightly above) with ClosedXML.

Regarding NPoi: I'm not entirely sure, what the library does. There seems to be a completely different loading behavior in comparison to ClosedXML and NanoXLSX (memory and filestream bytes). Will be matter of further optimizations.