Load file xlsx is very slow

[nguyenthao1988]

`<?php

require 'vendor/autoload.php';

use PhpOffice\PhpSpreadsheet\IOFactory;
$filename = 'data/danhsach.xlsx';
//$filename = 'data/nhanvien.xlsx';
$spreadsheet = IOFactory::load($filename);`

I use this code load file "danhsach.xlsx" with 16k record, file size excel: 7MB.
+> this code load finish taken time: 32s

how to optimize this, i load full column and row, not limit.

I used:
PHP: 8.2.4
PhpSpreadsheet: 2.0

Thanks bro,

[tvillafane]

I have an XLSX that is 1.4MB and my code just hangs when I use the same method, doesn't even finish.

[nguyenthao1988]

@tvillafane please use https://github.com/aVadim483/fast-excel-reader is better faster

[henno]

Same problem.

[nineday90]

相同问题，2.2mb的文件，执行IOFactory::load()竟然需要四五十秒，有优化的方案吗？

[hetao29]

specialize $reader->setReadDataOnly(true) if you only read the file.

$reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader(\PhpOffice\PhpSpreadsheet\IOFactory::identify($filename));
$reader->setReadDataOnly(true);
$spreadsheet = $reader->load($filename);

[konovalovk]

My issue appeared for >2.1.0 versions.
$t = microtime(true); $this->workbook = PhpSpreadsheet\IOFactory::load($path); echo microtime(true) - $t;

2.1.0 – 0.29 sec
2.2.2 – 8.28 sec

I started to delete sheets from the workbook and left for you to judge the only one. The opening in 2.2.2 takes 2.71 sec, in 2.1.0 takes 0.08 sec.
ru.xlsx

[DanielRuf]

According to git bisect, the following commit might have introduced this issue:

e404389

@oleibman could this be the cause?

[oleibman]

@DanielRuf Thank you for the information. Yes, it appears that might indeed be the cause. I'm flabbergasted. Our unit test suite reads tons of Xlsx files, and there didn't seem to be any significant change in run-time after that PR. I will see if I can improve it.

[sharat-rhinofish]

Thanks for looking into this. My composer which upgraded to this version also indicated that version 2.2.2 seems to have caused a problem.

[DanielRuf]

@henno @tvillafane @nguyenthao1988 can you provide test files regarding your exact issue? Then I can check that.

The commit identified by git bisect is only relevant for the issue by @konovalovk.

My test setup:

test.php

<?php

namespace PhpOffice;

require 'vendor/autoload.php';

$filename = 'ru.xlsx';
$spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load($filename);

test command: time php test.php

test steps:

git checkout master
git bisect start
git bisect bad
git bisect good 2.1.0
time php test.php
#  when result of "time php test.php" is high, run "git bisect bad"
git bisect bad
#  when result of "time php test.php" is low, run "git bisect good"
git bisect good
# test again after each "git bisect good" / "git bisect bad"
time php test.php
# repeat "git bisect bad" / "git bisect good" + "time php test.php" until you see "... is the first bad commit"

[oleibman]

Please test against PR #4153 if possible. That should help a lot for the issue reported by bisect.

[DanielRuf]

Performance-wise it looks good after applying the changes from the linked PR:

wget https://github.com/PHPOffice/PhpSpreadsheet/pull/4153.patch
git apply -v --index 4153.patch
# Checking patch src/PhpSpreadsheet/Worksheet/Worksheet.php...
# Hunk #1 succeeded at 3730 (offset 6 lines).
# Checking patch src/PhpSpreadsheet/Cell/Cell.php...
# Checking patch tests/PhpSpreadsheetTests/Worksheet/ApplyStylesTest.php...
# Checking patch tests/PhpSpreadsheetTests/Worksheet/ApplyStylesTest.php...
# Applied patch src/PhpSpreadsheet/Worksheet/Worksheet.php cleanly.
# Applied patch src/PhpSpreadsheet/Cell/Cell.php cleanly.
# Applied patch tests/PhpSpreadsheetTests/Worksheet/ApplyStylesTest.php cleanly.
# Applied patch tests/PhpSpreadsheetTests/Worksheet/ApplyStylesTest.php cleanly.
time php8.2 test.php
# real    0m0.303s
# user    0m0.126s
# sys     0m0.055s

[oleibman]

@DanielRuf Thank you for confirming. I will wait a few days before merging the PR in case somebody else supplies a spreadsheet that needs to be checked.

[DanielRuf]

@oleibman I probably have another spreadsheet which results in very long execution times, if no one else provides one. But it would be better if the other participants of this issue can provide a corresponding file.