Improve memory management when loading xlsx files. Experiencing memory exhaustion.

adjenks commented 5 years ago

This is:

- [] a bug report
- [x] a feature request
- [ ] **not** a usage question (ask them on https://stackoverflow.com/questions/tagged/phpspreadsheet or https://gitter.im/PHPOffice/PhpSpreadsheet)

What is the expected behavior?

For it to use a comparable amount of memory to Microsoft Excel. Opening excel with no file uses 7MB of memory. Opening the same file (409KB) on windows increases memory usage to 26MB.

What is the current behavior?

Loading a file increases memory usage by 177MB for a very small xlsx file and causes a fatal error due to memory exhaustion. My script uses 1MB before loading the file. It uses 178MB of memory immediately after loading the 409KB file.

What are the steps to reproduce?

Please provide a Minimal, Complete, and Verifiable example of code that exhibits the issue without relying on an external Excel file or a web server:

<?php

require __DIR__ . '/vendor/autoload.php';
use PhpOffice\PhpSpreadsheet\IOFactory;
print('</br>Before load:');
print(memory_get_usage());
$spreadsheet = IOFactory::load('./testfile.xlsx');
print('</br>After load:');
print(memory_get_usage());

Output:

Before load:1039272 After load:178805392

Before increasing the available memory for the script to 320MB, it would throw this error:

PHP Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 4096 bytes)

Which versions of PhpSpreadsheet and PHP are affected?

phpoffice/phpspreadsheet 1.6.0

Comment:

Thank you for your hard work in making a great PHP library. I look forward to all future improvements.

franksl commented 5 years ago

I can confirm this issue, I have huge problems especially with xlsx files downloaded from google spreadsheet, I often have to delete emty rows and columns before exporting to avoid memory exaustion. Test is simple, just create a google spreadsheet with 10 sheets and about 50 rows of data for each sheet. Memory usage is lower when exporting to ods instead of xlsx

adjenks commented 5 years ago

@franksl My xlsx file needed to have custom properties. Instead of using an xlsx file with custom properties as a template, I worked around the problem by loading a csv and then adding the custom properties via the API. Loading the csv only used about 2MB of memory and loaded way faster. This is a fine workaround for me in this case, but if I had a very complex template that I needed to use, I would not want to rebuild it via the API.

franksl commented 5 years ago

@adjenks Thanks for the advice, unfortunately I have complex excel document with many sheets that are part of a complex automated system, using csv files would be overkill for me. The issue for me is that opening an xlsx document that contains about 1000 cells with significant values takes more than 256MB of memory, this is really strange.

adjenks commented 5 years ago

@franksl Have you tried an xls file instead of an xlsx file? Or tried converting it to an ods file?

franksl commented 5 years ago

@adjenks Yes, as I said in a previous comment I'm forced to use ods on some files actually and memory usage is lower, sometimes I still have problems even with ods.

ShahinSorkh commented 5 years ago

And also I'm experiencing memory leaks. This code:

require __DIR__ . '/vendor/autoload.php';
use PhpOffice\PhpSpreadsheet\IOFactory;
print('step 0 load:  ');
print(memory_get_usage() / 1024 / 1024);
echo PHP_EOL;
$spreadsheet = IOFactory::load('/tmp/201906171316-638092745/001-201906171316.xlsx');
print('step 1 load:  ');
print(memory_get_usage() / 1024 / 1024);
echo PHP_EOL;
$spreadsheet = IOFactory::load('/tmp/201906171316-638092745/002-201906171316.xlsx');
print('step 2 load:  ');
print(memory_get_usage() / 1024 / 1024);
echo PHP_EOL;
$spreadsheet = IOFactory::load('/tmp/201906171316-638092745/003-201906171316.xlsx');
print('step 3 load:  ');
print(memory_get_usage() / 1024 / 1024);
echo PHP_EOL;
$spreadsheet = IOFactory::load('/tmp/201906171316-638092745/005-201906171316.xlsx');
print('step 4 load:  ');
print(memory_get_usage() / 1024 / 1024);
echo PHP_EOL;
$spreadsheet = IOFactory::load('/tmp/201906171316-638092745/006-201906171316.xlsx');
print('step 5 load:  ');
print(memory_get_usage() / 1024 / 1024);
echo PHP_EOL;
$spreadsheet = IOFactory::load('/tmp/201906171316-638092745/007-201906171316.xlsx');
print('step 6 load:  ');
print(memory_get_usage() / 1024 / 1024);
echo PHP_EOL;
$spreadsheet = IOFactory::load('/tmp/201906171316-638092745/008-201906171316.xlsx');
print('step 7 load:  ');
print(memory_get_usage() / 1024 / 1024);
echo PHP_EOL;
$spreadsheet = IOFactory::load('/tmp/201906171316-638092745/009-201906171316.xlsx');
print('step 8 load:  ');
print(memory_get_usage() / 1024 / 1024);
echo PHP_EOL;
$spreadsheet = IOFactory::load('/tmp/201906171316-638092745/001-201906171316.xlsx');
print('step 9 load:  ');
print(memory_get_usage() / 1024 / 1024);
echo PHP_EOL;

Produced following output:

step 0 load:  0.60233306884766
step 1 load:  162.00587463379
step 2 load:  337.64506530762
step 3 load:  355.55595397949
step 4 load:  357.38922119141
step 5 load:  364.33184051514
step 6 load:  365.58126831055
step 7 load:  364.88866424561
step 8 load:  363.29885101318
step 9 load:  339.1379776001

All loaded files are produced using the package itself. Details on files:

Ξ php/test → ls -lh /tmp/201906171316-638092745/* -d
-rw-r--r-- 1 www-data www-data 4.7M Jun 17 13:17 /tmp/201906171316-638092745/001-201906171316.xlsx
-rw-r--r-- 1 www-data www-data 3.4M Jun 17 13:19 /tmp/201906171316-638092745/002-201906171316.xlsx
-rw-r--r-- 1 www-data www-data 3.5M Jun 17 13:21 /tmp/201906171316-638092745/003-201906171316.xlsx
-rw-r--r-- 1 www-data www-data 3.6M Jun 17 13:23 /tmp/201906171316-638092745/004-201906171316.xlsx
-rw-r--r-- 1 www-data www-data 3.9M Jun 17 13:25 /tmp/201906171316-638092745/005-201906171316.xlsx
-rw-r--r-- 1 www-data www-data 4.3M Jun 17 13:27 /tmp/201906171316-638092745/006-201906171316.xlsx
-rw-r--r-- 1 www-data www-data 3.9M Jun 17 13:30 /tmp/201906171316-638092745/007-201906171316.xlsx
-rw-r--r-- 1 www-data www-data 4.9M Jun 17 13:32 /tmp/201906171316-638092745/008-201906171316.xlsx
-rw-r--r-- 1 www-data www-data 3.7M Jun 17 13:35 /tmp/201906171316-638092745/009-201906171316.xlsx

Tested with phpoffice/phpspreadsheet ^1.7 AND phpoffice/phpspreadsheet ^1.6

Note: I'm behind of a private network so I don't care about my ENV vars.

MarkBaker commented 5 years ago

What is the expected behavior?
 - For it to use a comparable amount of memory to Microsoft Excel.

Seriously! I'm sorry, you'll haver to use COM for that to be feasible, or possibly a commercial interface to a C library like libxl; PHPSpreadsheet is a pure PHP script, that (like all PHP scripts) uses zvals for data storage of any variable or property; if you want purely native datatypes, then using a pure PHP script is simply not an option.

Yes, PHPSpreadsheet uses a lot of memory, and I'm happy to discuss serious suggestions on how that could be reduced. There are options, such as cell caching, that can be used to reduce the memory footprint of a spreadsheet in memory; but "in-memory" means just that.

BobDankert commented 5 years ago

Are there any official recommendations of how much memory should be allocated for Excel files dependent on their size? I'd be happy to increase the allowed memory based on the file size, but I'm not sure what size we should set this to.

adjenks commented 5 years ago

@ShahinSorkh That's quite the big comment. I had to scroll for a while. Ha. @MarkBaker That's reasonable. You're right, I shouldn't expect it to be the same as Excel. I just thought it seemed a little extreme in memory usage for such a small file with almost nothing in it. I understand though that there is a certain overhead associated with writing something in pure PHP. I just wasn't really sure if 177MB of memory for a simple 409KB file was normal or if it could be improved somehow and thought it should be addressed. Thank you for getting back to me.

MarkBaker commented 5 years ago

The official recommendation mentioned in the documentation is 1k per cell (32-bit PHP) and 1.6k per cell (64-bit PHP), but that was under PHP 5.4; I've not made significant measures on PHP7. What can't be determined is memory requirements based on filesize; because Excel formats typically optimise storage requirements for the file (e.g. shared strings table, etc)

UtechtDustin commented 4 years ago

@MarkBaker 1k = 1000bytes per cell ? Is there any offical documentation for that ?

adjenks commented 4 years ago

@UtechtDustin https://phpspreadsheet.readthedocs.io/en/latest/topics/memory_saving/#memory-saving

PhpSpreadsheet uses an average of about 1k per cell in your worksheets

PHPOffice / PhpSpreadsheet