2

I would like to read a big (40 000 rows) xlsx file. In the old version (PHPExcel) I used caching, and it worked fine.

Now I would like to migrate to the newest PhpSpreadsheet version, and I need to use caching. Without cache settings the program will end in a Memory Allocated Error. (in php.ini: memory_limit = 5000M)

*Fatal error: Out of memory (allocated 780140544) (tried to allocate 29360128 bytes) in D:***\phpoffice\phpspreadsheet\src\PhpSpreadsheet\Collection\Cells.php on line 400*

I tried 2 cache packages, APCu and Redis.

$client = new \Redis();
$client->connect('127.0.0.1', 6379);
$pool = new \Cache\Adapter\Redis\RedisCachePool($client);
//$pool = new \Cache\Adapter\Apcu\ApcuCachePool();

$simpleCache = new \Cache\Bridge\SimpleCache\SimpleCacheBridge($pool);
\PhpOffice\PhpSpreadsheet\Settings::setCache($simpleCache);

$objReader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader("Xlsx");
$objReader->setReadDataOnly(true);
$objPHPExcel = $objReader->load(dirname(__FILE__).'/Tmpfile'.$i.'.xlsx');

$objPHPExcel->setActiveSheetIndex(0);

foreach ( $objPHPExcel->getActiveSheet()->getRowIterator() as $row ) {
etc...

I got a fatal error in both of cases.

APCu: *Fatal error: Uncaught PhpOffice\PhpSpreadsheet\Exception: Cell entry A2 no longer exists in cache. This probably means that the cache was cleared by someone else. in D:***\phpoffice\phpspreadsheet\src\PhpSpreadsheet\Collection\Cells.php:433 Stack trace: #0 D:***\phpoffice\phpspreadsheet\src\PhpSpreadsheet\Worksheet\Worksheet.php(1239): PhpOffice\PhpSpreadsheet\Collection\Cells->get('A2') #1 D:***\phpoffice\phpspreadsheet\src\PhpSpreadsheet\Worksheet\RowCellIterator.php(128): PhpOffice\PhpSpreadsheet\Worksheet\Worksheet->getCellByColumnAndRow(1, 2) #2 D:***\Eclipse\WebShopUpdate\run.php(358): PhpOffice\PhpSpreadsheet\Worksheet\RowCellIterator->current() #3 {main} thrown in D:***\phpoffice\phpspreadsheet\src\PhpSpreadsheet\Collection\Cells.php on line 433*

Redis: *Fatal error: Uncaught RedisException: Redis server went away in D:***\cache\redis-adapter\RedisCachePool.php:82 Stack trace:

0 D:***\cache\redis-adapter\RedisCachePool.php(82): Redis->set('phpspreadsheet....', 'a:4:{i:0;b:1;i:...') #1

D:***\cache\adapter-common\AbstractCachePool.php(240): Cache\Adapter\Redis\RedisCachePool->storeItemInCache(Object(Cache\Adapter\Common\CacheItem), NULL) #2 D:***\cache\simple-cache-bridge\SimpleCacheBridge.php(72): Cache\Adapter\Common\AbstractCachePool->save(Object(Cache\Adapter\Common\CacheItem))

3 D:***\phpoffice\phpspreadsheet\src\PhpSpreadsheet\Collection\Cells.php(372):

Cache\Bridge\SimpleCache\SimpleCacheBridge->set('phpspreadsheet....', Object(PhpOffice\PhpSpreadsheet\Cell\Cell)) #4 D:***\phpoffice\phpspreadsheet\src\PhpSpreadsheet\Collection\Cells.php(398): PhpOffice\PhpSpr in D:***\cache\adapter-common\AbstractCachePool.php on line 337*

Environment:

  • PHP Version 7.1.3
  • Apache/2.4.25 (Win32)
  • APCu Version 5.1.11
  • Redis Version 4.0.2

Environment2:

  • PHP Version 5.6.30
  • Apache/2.4.25 (Win32)
  • APCu Version 4.0.10
  • Redis Version 2.2.7

I got the same error in all cases.

Nic3500
  • 8,144
  • 10
  • 29
  • 40
Ekhrikhor
  • 91
  • 2
  • 5
  • Have you considered using a generator? It's basically a function that uses 'yield', not 'return'. When you call the generator function, it returns an object that can be iterated over (e.g. with a foreach loop). Generators are highly memory efficient (no array needs to be built in memory) and fast. Might help you resolve your issue. further reading: http://php.net/manual/en/language.generators.overview.php – lovelace May 30 '18 at 07:50
  • 2
    I'm little late, but if anyone has a problem with memory allocation I think that this might help. `$spreadsheet->disconnectWorksheets(); unset($spreadsheet);` As stated in the docs: "_The PhpSpreadsheet object contains cyclic references (e.g. the workbook is linked to the worksheets, and the worksheets are linked to their parent workbook) which cause problems when PHP tries to clear the objects from memory when they are unset(), or at the end of a function when they are in local scope. The result of this is "memory leaks", which can easily use a large amount of PHP's limited memory._" – Stefan Tanevski Mar 26 '19 at 08:22
  • @stefanTanevski where do you use this ? – Reign.85 Sep 24 '20 at 13:46
  • @Reign.85 From here https://phpspreadsheet.readthedocs.io/en/latest/topics/creating-spreadsheet/#clearing-a-workbook-from-memory – Jekis Apr 04 '22 at 05:38

0 Answers0