2

I use PHPexcel to open a .xlsx file (on ovh mutualized server) and encountered problems that I solved.

I have a new problem when saving the the modified file :

"Fatal error: Allowed memory size of 536870912 bytes exhausted (tried to allocate 49 bytes) in /home/observatvu/www/libraries/phpexcel/library/PHPExcel/Cell.php on line 870"

I read many questions and answers on internet and tried some solutions like :

  • memory_limit in .htaccess => problem on the server, it does'nt work
  • ini_set('memory_limit','512M') => I have the message above... with other ini_set values I have other sizes of memory error but no saving of the file.
  • I can't modify php.ini

I tried to write setPreCalculateFormulas(false) during saving the file but always the same problem.

Please someone could help me to find a working solution ?

Thank you

Djo
  • 21
  • 1
  • 1
  • 3
  • possible duplicate of [phpexcel Allowed memory size of 134217728 bytes exhausted](http://stackoverflow.com/questions/5560053/phpexcel-allowed-memory-size-of-134217728-bytes-exhausted) – Halayem Anis Jul 01 '15 at 10:04
  • Have you tried any of the [cell caching options in PHPExcel](https://github.com/PHPOffice/PHPExcel/blob/develop/Documentation/markdown/Overview/04-Configuration-Settings.md) that are designed to reduce the amount of memory that it uses? – Mark Baker Jul 01 '15 at 10:04
  • i tried $cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_to_phpTemp; $cacheSettings = array( 'memoryCacheSize' => '1024MB'); PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings); – Djo Jul 01 '15 at 10:10

3 Answers3

3

If you tried 

$cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_to_phpTemp;
$cacheSettings = array( 'memoryCacheSize' => '1024MB');
PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings);

Then it wouldn't work.

You have a limit of 536,870,912 (512MB) for your PHP

The line 

$cacheSettings = array( 'memoryCacheSize' => '1024MB');

is telling PHPExcel to use 1024MB of PHP memory before switching to using php://temp for caching.... that's what the memory element of the argument name memoryCacheSize means.

Use a lower value for the memoryCacheSize value than the amount of your PHP memory limit

$cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_to_phpTemp;
$cacheSettings = array( 'memoryCacheSize' => '256MB');
PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings);
rekaszeru
  • 19,130
  • 7
  • 59
  • 73
Mark Baker
  • 209,507
  • 32
  • 346
  • 385
  • Thank you for your answer. I just tried your solution and I have the same error. I tried the cell caching options in PHPExcel and the same error... – Djo Jul 01 '15 at 10:37
  • You need to work out the right caching method, and the right arguments for the settings..... why don't you leave the settings for php://temp at their default value? – Mark Baker Jul 01 '15 at 10:42
  • Excuse me I don't understand... "leave the settings for php://temp at their default value?" => What do you mean? – Djo Jul 01 '15 at 12:16
  • Doing like this : $cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_to_phpTemp; PHPExcel_Settings::setCacheStorageMethod($cacheMethod); – Djo Jul 01 '15 at 12:19
  • Are you setting this ___before___ loading your file? – Mark Baker Jul 01 '15 at 12:48
  • Just after importing the library – Djo Jul 01 '15 at 13:03
  • In that case, I haven't a clue what's using all your memory.... are you building any big arrays? – Mark Baker Jul 01 '15 at 13:13
  • May be I could send you a mail with the .xlsx and the php code ? – Djo Jul 01 '15 at 13:19
  • I use VLOOKUP on tables with about 20 columns and 500 rows – Djo Jul 01 '15 at 13:57
  • Is it possible to change just two worksheets, whithout calculating formulas and save the workbook whith all worksheets ( and the both modified) ? May be it would solve my problem with no calcul ? – Djo Jul 01 '15 at 15:08
  • No it isn't.... the structure of Excel files simply isn't appropriate to make modifications without loading the whole thing.... to do so would require it to be written in a way that was only appropriate for the specific changes that a user was trying to make – Mark Baker Jul 01 '15 at 15:18
  • Yes, send me your code and file via email.... the PHPExcel 24x7x365 support service will be happy to help you free of charge, or your money back – Mark Baker Jul 01 '15 at 15:18
2

If you can't make it work with PHPExcel's caching system, you can give Spout a try: https://github.com/box/spout. It was designed to work with files of any size without causing memory or time limit issues.

All you need is 10MB of memory available and you can read all the XLSX files you want :)

Adrien
  • 1,929
  • 1
  • 13
  • 23
0

This type of error can occur when passing an incorrect cell letter to a phpExcel function such as:

$objPHPExcel->getActiveSheet()->setCellValue($cell, $value);

Be sure not to increment column letters like this:

chr(ord($col) + 1);

Best to use a custom increment function like:

//$start = 'A'
private function _incrementCol($start, $offset)
{
    $result = $start;
    for($i = 1; $i <= $offset; $i++) {
        $result++;
    }
    return $result;
}
duttyman
  • 151
  • 1
  • 2