Memory exhausted in large data

Topics: Developer Forum, Project Management Forum, User Forum
Jul 12, 2010 at 7:51 AM

Hii,,

 

i have a same issue writing a large data to excel

Whenever I generate a Excel with just 1000 rows, it works perfectly.
But when I try to writing i.e. 20000 rows, the generated Excel-file is suddenly memory exhausted .

the error message like that

Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 64 bytes) in ...\class\PHPExcel\Style.php on line 676

sometime i got error in Color.php on line 401

 

can someone explain me how to fix that error?

 

 

best regards,

Zenixgrace

Coordinator
Jul 12, 2010 at 12:38 PM

Are you using any of the cell caching options?

Jul 13, 2010 at 7:34 AM
Edited Jul 15, 2010 at 10:07 AM
Hiii Mark... Thanks for quick reply.. i not using any cell caching.. if it's needed, What is the best options ? i'm try to use this option : $cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_to_memcache; $cacheSettings = array( 'memcacheServer' => 'localhost', 'memcachePort' => 11211, 'cacheTime' => 600 ); PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings); but i still got that error... best regards, zenixgrace
Jul 15, 2010 at 10:07 AM
hello.... any one can help me?
Jul 20, 2010 at 12:22 PM
Edited Jul 20, 2010 at 6:07 PM

Hi

I'm having a similar problem when trying to load a spreadsheet that is 2.7Mbytes in size. I've tried using cache_to_discISAM, cache_to_phpTemp and cache_to_memcache, but it doesn't appear to make any difference. In all cases I get a "Fatal error: Allowed memory size of 67108864 bytes exhausted (tried to allocate 80 bytes) in .../PHPExcel/CachedObjectStorage/DiscISAM.php on line 47" error message.

This aspect of PHPExcel appears to be a severe limitation. Is there any way around the problem?

Best regards

Si


Jul 23, 2010 at 8:54 PM

i had the same issue, but i increased the memory php can use in the php.ini file and it fixed the issue.

Jul 27, 2010 at 4:00 AM
glg216 wrote:

i had the same issue, but i increased the memory php can use in the php.ini file and it fixed the issue.

thank for ur reply glg216 how many size are u increased your php memory? i try to increased my php memory 256M and it's work but when i print 70000 rows or more, it's occur error again (same error).. same like my memory it's not enough to process my data.. anyone others solution to solved it? Best Regards, zenixgrace
Jul 27, 2010 at 9:12 AM
zenixgrace wrote:
glg216 wrote:

i had the same issue, but i increased the memory php can use in the php.ini file and it fixed the issue.

 

thank for ur reply glg216 how many size are u increased your php memory? i try to increased my php memory 256M and it's work but when i print 70000 rows or more, it's occur error again (same error).. same like my memory it's not enough to process my data.. anyone others solution to solved it? Best Regards, zenixgrace

However much you increase the memory availability, you will always find a spreadsheet that exceeds it. The best way to solve the problem is to use caching, which will limit the size of the spreadsheet to the maximum cache size, and this should always be much larger than the memory allocation. However, in my experience the caching does not work. Is this a bug? I tried cache_to_discISAM, cache_to_phpTemp, and cache_to_memcache. When initialsing them, the calls all returned TRUE, indicating they had been initialised correctly. In all cases I still got the dreaded "Fatal error: Allowed memory size of 67108864 bytes exhausted...'.

Has anyone got any experience of using the caching successfully? Any help would be very greatfully received!

Best regards

Si

Jul 29, 2010 at 12:41 PM

thanks before Si..

 

hahahahaha...

 

i have a same problem like u..

 

when i use a caching, it's like not work with my code..

i still got the same error....

i don't exactly sure is a bug or my code has wrong..

 

my code is like this :

    $cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_to_memcache;
    $cacheSettings = array( 'memcacheServer'  => 'localhost',
                            'memcachePort'    => 11211,
                            'cacheTime'       => 600
                          );
    PHPExcel_Settings::setCacheStorageMethod($cacheMethod,$cacheSettings);

 

can anyone help our?

 

 

Best regards,

zenixgrace

Jul 30, 2010 at 12:28 PM

I've tested on a small range and the various cache engines are accepted and change the memory use.

So I can at least confirm it is no jedi mind trick.

cache_to_discISAM

22 M
cache_in_memory_serialized 21 M
cache_in_memory 32 M
cache_to_phpTemp

22 M

This thread http://phpexcel.codeplex.com/Thread/View.aspx?ThreadId=203384 has a table with an extra cache strategy

Disk (Full) cell storage (using a temporary file for each cell)

How do I set this up ?

as I am failing on memory_limit 256M

 

Coordinator
Jul 30, 2010 at 1:07 PM
Edited Jul 30, 2010 at 1:08 PM
zenixgrace wrote:

when i use a caching, it's like not work with my code..

i still got the same error....

i don't exactly sure is a bug or my code has wrong..

$cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_to_memcache;
$cacheSettings = array( 'memcacheServer'  => 'localhost',
                        'memcachePort'    => 11211,
                        'cacheTime'       => 600
                      );
if (!PHPExcel_Settings::setCacheStorageMethod($cacheMethod,$cacheSettings))
   die('CACHEING ERROR');

 This will identify whether you are successfully configuring caching or not

I assume you are running a memcache server with these settings

Jul 31, 2010 at 2:29 AM
Edited Jul 31, 2010 at 4:55 AM
JohnGriffiths2 wrote:

I've tested on a small range and the various cache engines are accepted and change the memory use.

So I can at least confirm it is no jedi mind trick.

cache_to_discISAM

22 M
cache_in_memory_serialized 21 M
cache_in_memory 32 M
cache_to_phpTemp

22 M

This thread http://phpexcel.codeplex.com/Thread/View.aspx?ThreadId=203384 has a table with an extra cache strategy

Disk (Full) cell storage (using a temporary file for each cell)

How do I set this up ?

as I am failing on memory_limit 256M

 

 

thanks for your information john

it's very helpfull

 

 

MarkBaker wrote:
zenixgrace wrote:

when i use a caching, it's like not work with my code..

i still got the same error....

i don't exactly sure is a bug or my code has wrong..

$cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_to_memcache;
$cacheSettings = array( 'memcacheServer' => 'localhost',
'memcachePort' => 11211,
'cacheTime' => 600
);
if (!PHPExcel_Settings::setCacheStorageMethod($cacheMethod,$cacheSettings))
die('CACHEING ERROR');



 This will identify whether you are successfully configuring caching or not

I assume you are running a memcache server with these settings

 

thank for  reply mark...

when try that code, i don't get any error message cacheing..

 

but i still get exhausted memory..

hmmmss.......

 

 

Best Regards,

zenixgrace

 

Aug 2, 2010 at 6:04 PM

Hey there. I'm jumping into this discussion 'cause I'm also having this problem.

The whole thing goes like this:

  • I create a simple PHPExcel object: $xlsObj = new PHPExcel();
  • I add about 8000 lines, with 31 columns
  • At the end, I create the writer in Excel5 format: $xlsWriter = PHPExcel_IOFactory::createWriter($xlsObj, 'Excel5');
  • And save the file: $xlsWriter->save('file.xls');
  • The whole process is a simple read from CSV file, put on XLS file with masks.

At first, my "php.ini" was set to only 16M, so I got the memory exhausted error. Well, fair enough, I changed the config to use 128M. And I still got the error.

Next step was to change the caching method: I changed the caching to discISAM. The process went longer, but still failed. Adding some logs, I found that the whole file is read and added to the XLS, but the creation of the writer exhausts the available memory. The only solution was to add the memory limit to 256M, but it's kinda excessive in my opinion, specially since the cache file seems to get to only 69M and the resulting XLS only 4M.

The feeling I got is that, when I create the writer, it loads the whole PHPExcel object back in memory, even with the cache.

Is there something wrong with my process? Any other tips to reduce memory usage?

Aug 3, 2010 at 9:31 AM

hi all,

i'm also eagerly awaiting for a solution to this problem. the one i'm working on uses 31 columns and 32,000 rows. any size adjustment i make in the php.ini gets exhausted (tried upto 64M). and using the following:

    $cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_to_phpTemp;
    $cacheSettings = array( 'memoryCacheSize ' => '64MB');
    PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings);
   
   
    setExportFileHeaders($arrParams['filename'].'.csv'); // this allows us to load the output to the client's window as a "download file"
    $objPHPExcel = new PHPExcel();

still is a no go. either i get the fatal error exceeded memory error message, or my apache dies out. i'm using the latest svn code 57274. i hope there's still a light at the end of the tunnel..

 

Aug 3, 2010 at 11:45 AM
jubia wrote:

Hey there. I'm jumping into this discussion 'cause I'm also having this problem.

The whole thing goes like this:

  • I create a simple PHPExcel object: $xlsObj = new PHPExcel();
  • I add about 8000 lines, with 31 columns
  • At the end, I create the writer in Excel5 format: $xlsWriter = PHPExcel_IOFactory::createWriter($xlsObj, 'Excel5');
  • And save the file: $xlsWriter->save('file.xls');
  • The whole process is a simple read from CSV file, put on XLS file with masks.

At first, my "php.ini" was set to only 16M, so I got the memory exhausted error. Well, fair enough, I changed the config to use 128M. And I still got the error.

Next step was to change the caching method: I changed the caching to discISAM. The process went longer, but still failed. Adding some logs, I found that the whole file is read and added to the XLS, but the creation of the writer exhausts the available memory. The only solution was to add the memory limit to 256M, but it's kinda excessive in my opinion, specially since the cache file seems to get to only 69M and the resulting XLS only 4M.

The feeling I got is that, when I create the writer, it loads the whole PHPExcel object back in memory, even with the cache.

Is there something wrong with my process? Any other tips to reduce memory usage?

i think change the memory_limit it's not a best way.. hmmmsssss....
Coordinator
Aug 3, 2010 at 10:53 PM

Rule of thumb calculation:

The PHPExcel code has a footprint of between 10 and 25MB, depending on which components are being accessed. I can reduce the footprint by splitting the functions.php file into a series of individual files organised by category for worksheets that use formulae.

At present, each cell in a workbook takes on average 1k of memory (without any caching) or 1.6k on 64-bit PHP - I'll assume 32-bit PHP for the moment - so 8000 lines with 31 columns (248,000 cells) will be about 242MB. With cell cacheing (such as php://temp or DiskISAM), that can be reduced to about a third, so the 8000 lines by 31 columns will require about 80MB. I'm trying to squeeze a bit more out of that; but so far I've the saving is just a few bytes, or a horrendous (45x slower) deterioration in speed.

XML-based Readers (Excel2007, Excel2003XML and OOCalc) can be made more memory-efficient by switching from simpleXML (which loads the entire XML file into memory before parsing it into a PHPExcel object) to the stream-based XMLReader... that will be a part of the work I'll be doing over the next month. I still need to look more closely at memory usage of the Excel5 Reader, but none of the Readers are particularly memory-hungry; so that's a quick win but a relatively small saving in the memory footprint.

My main focus must be on the Writers, all of which are extremely memory-intensive. HTML and PDF are the worst (the the 8000 lines by 31 column worksheet will need a further 160MB dependent on formatting), while Excel5 and Excel2007 will require at least another 80MB.

I've also been looking at a mechanism to monitor memory usage, and to automatically adjust PHP's memory limit as necessary (where hosting permits).

Aug 4, 2010 at 2:23 AM

hi mark, i really appreciate your post as it explained a lot and help me understand the meat of the problem.

i guess we can only give you support, as you continue to provide efforts in improving/solving this issue for us :)

many thanks though, please let us know if we can help.

Aug 4, 2010 at 9:57 AM
MarkBaker wrote:

Rule of thumb calculation:

The PHPExcel code has a footprint of between 10 and 25MB, depending on which components are being accessed. I can reduce the footprint by splitting the functions.php file into a series of individual files organised by category for worksheets that use formulae.

At present, each cell in a workbook takes on average 1k of memory (without any caching) or 1.6k on 64-bit PHP - I'll assume 32-bit PHP for the moment - so 8000 lines with 31 columns (248,000 cells) will be about 242MB. With cell cacheing (such as php://temp or DiskISAM), that can be reduced to about a third, so the 8000 lines by 31 columns will require about 80MB. I'm trying to squeeze a bit more out of that; but so far I've the saving is just a few bytes, or a horrendous (45x slower) deterioration in speed.

XML-based Readers (Excel2007, Excel2003XML and OOCalc) can be made more memory-efficient by switching from simpleXML (which loads the entire XML file into memory before parsing it into a PHPExcel object) to the stream-based XMLReader... that will be a part of the work I'll be doing over the next month. I still need to look more closely at memory usage of the Excel5 Reader, but none of the Readers are particularly memory-hungry; so that's a quick win but a relatively small saving in the memory footprint.

My main focus must be on the Writers, all of which are extremely memory-intensive. HTML and PDF are the worst (the the 8000 lines by 31 column worksheet will need a further 160MB dependent on formatting), while Excel5 and Excel2007 will require at least another 80MB.

I've also been looking at a mechanism to monitor memory usage, and to automatically adjust PHP's memory limit as necessary (where hosting permits).

 Hi Mark

Thank you for this information. It puts the whole memory issue into context. The spreadsheet I was trying to open easily exceeds the memory capabilities of the server, even when using caching. There is a huge data set contained within this spreadsheet, which could never be held in memory where each cell takes 1K of storage. I extracted the data and wrote it into a MySQL table which got around the problem. I am using PHPExcel in cases where the spreadsheets do not contain so much data and it works exceedingly well. I greatly appreciate your efforts in developing and maintaining such a valuable tool.

Best regards

Si

Aug 5, 2010 at 5:16 AM
MarkBaker wrote:

Rule of thumb calculation:

The PHPExcel code has a footprint of between 10 and 25MB, depending on which components are being accessed. I can reduce the footprint by splitting the functions.php file into a series of individual files organised by category for worksheets that use formulae.

At present, each cell in a workbook takes on average 1k of memory (without any caching) or 1.6k on 64-bit PHP - I'll assume 32-bit PHP for the moment - so 8000 lines with 31 columns (248,000 cells) will be about 242MB. With cell cacheing (such as php://temp or DiskISAM), that can be reduced to about a third, so the 8000 lines by 31 columns will require about 80MB. I'm trying to squeeze a bit more out of that; but so far I've the saving is just a few bytes, or a horrendous (45x slower) deterioration in speed.

XML-based Readers (Excel2007, Excel2003XML and OOCalc) can be made more memory-efficient by switching from simpleXML (which loads the entire XML file into memory before parsing it into a PHPExcel object) to the stream-based XMLReader... that will be a part of the work I'll be doing over the next month. I still need to look more closely at memory usage of the Excel5 Reader, but none of the Readers are particularly memory-hungry; so that's a quick win but a relatively small saving in the memory footprint.

My main focus must be on the Writers, all of which are extremely memory-intensive. HTML and PDF are the worst (the the 8000 lines by 31 column worksheet will need a further 160MB dependent on formatting), while Excel5 and Excel2007 will require at least another 80MB.

I've also been looking at a mechanism to monitor memory usage, and to automatically adjust PHP's memory limit as necessary (where hosting permits).

hi Mark

 

Thank you for this information.

same like une6.

i guess we can only give you support, as you continue to provide efforts in improving/solving this issue for us

hehehehe :))

 

many thank mark

Feb 8, 2011 at 3:38 PM

i have generated an excel2007 (xlsx) file of 2500rows and 100 columns. with memory_get_peak_usage in each step of the script, i have reached 283mb. the result file was about 670KB. in php.ini i have modified the max memory limit to 512 "just in case". that was an offline script. i can`t put that on a webserver :) anyway, that`s a very good tool for parse and create files. i will try to optimise the script from "Cell Caching" chapter :)

Coordinator
Feb 8, 2011 at 9:33 PM

@yo9gnc

Remember to set your styles by range (or even by row or column) where you can, rather than for every individual cell, and/or using the applyFromArray() method.

Sep 9, 2013 at 9:55 AM
Edited Sep 9, 2013 at 11:20 AM
Thanks for the advice