Error in file generation

Topics: Developer Forum, User Forum
Sep 6, 2012 at 7:37 PM
Edited Sep 6, 2012 at 7:48 PM

Hi!

First, congratulations for great code for PHP!!

I downloaded the source code of PHPExcel, but am having trouble exporting files ....

I did the installation, and when I export a sample file (MySQL SELECT from table) works if mysql table have any less than 10,000 rows of data... When you have more than 10,000 rows of data, generates the error:
"Fatal error: Allowed memory size of 536870912 bytes exhausted (tried to allocate 24 bytes) in / var / www / xls / Classes / PHPExcel / Cell.php on line 506"

In my PHP file for generation, i'm setting:
ini_set ("memory_limit", "512M");
ini_set ("max_execution_time", "300");

But if i increasing the memory_limit of PHP, the server will drop....

What should I do?

Please I need urgent help!!

 

Thanks Diego !!


 

Complementing:

The files that I export to Excel, has averaged 250,000 rows of data....

Coordinator
Sep 6, 2012 at 8:57 PM

So have you tried using any of the cell caching techniques to reduce memory consumption?

Sep 6, 2012 at 9:41 PM

Thanks for response!

No.... How can I do this??

Coordinator
Sep 7, 2012 at 9:33 AM

Start by reading section 4.2.1 of the PHPExcel developer documentation in /Documentation. This describes the options available for cell caching and how to use it

Sep 17, 2012 at 8:46 PM

Hi MarkBaker!

I studied and applied cell caching but I'm still having a strange problem ...

I'm using 'cache_in_memory_serialized' to build a worksheet, and after exactly 70 seconds, the process in the server stop and the page stay open to expecting a response from the server ...

I applied also the 'CacheTime' to '999999 'to inhibit but the problem was not solved. I also made the following settings in PHP/PHPExcel:

ini_set ("memory_limit", "512M");
set_time_limit (999999);
ini_set ('mysql.connect_timeout', 999999);
ini_set ('default_socket_timeout', 999999);
ini_set ('max_execution_time', 999999);
ini_set ('max_input_time', 999999);
ini_set ('expect.timeout', 999999);

$cacheMethod = PHPExcel_CachedObjectStorageFactory :: cache_in_memory_serialized;
$cacheSettings = array ('CacheTime' => '999999');

PHPExcel_Settings :: setCacheStorageMethod ($cacheMethod, $cacheSettings);

$objPHPExcel = new PHPExcel();
....

But not solved to generated worksheet with more of 250000 rows of data ...

An important observation is that a small worksheet with 10000 rows of data is normally generated with setup above.... (with 'LIMIT' on query)

What should I do?

 

Thanks a lot!!!

Coordinator
Sep 17, 2012 at 11:38 PM

Have you tried the other caching methods? Each of the different methods is a different trade-off between memory usage and performance, or suited to specific circumstances (e.g. access to memcache). Serialized is not the most efficient memory conservation: the table in this thread gives an indication of the relative memory/performance of each different method.

If you're working with such large volumes of data, you should really be using a background process to handle the export ratehr than forcing the user to wait.

Sep 18, 2012 at 7:48 AM
Edited Sep 18, 2012 at 7:48 AM

I used to use PHPExcel for that kind of volumes, and it took my server somewhere in the ballpark of 24GB (depending on the no of worksheets and columns), to generate xlsx documents with 200K rows. So, I'm guessing your memory limit of 512MB is way too low, and your php process just dies because it is out of memory. Chec your servers error logs, and php error logs!

 

Furthermore, if you are running out of memory, don't use in memory cell caching!

Sep 18, 2012 at 7:18 PM
Edited Sep 18, 2012 at 7:49 PM

Hi!

Guys, I guess that half the puzzle was discovered ... The problem before the 70 seconds was actually server memory (parameterized before with '512M ')...

Analyzing these last days, I believe the real problem is the method I'm filling cells ... I studied the section '4 .5.5 Looping cells', but I could not transfer the method for 'foreach' (as recommended according to the documentation ...)

If you can help me I appreciate it! Part of code for generation the following:

$sql2 = "SELECT * FROM $table";

$result2 = mysql_query($sql2);

$count = mysql_num_rows($result2);

$colunas = mysql_num_fields($result2);

$i = 2;

while($temp2 = mysql_fetch_array($result2)) {
    for($k=1;$k<$colunas+1;$k++){
        $campo_ativo = "r".$k;
        $objPHPExcel->getActiveSheet()->setCellValue($celulas[$k-1].$i, $temp2[$campo_ativo]);
    }
    $i++;
}Complementing:

 

Thanks a lot for help!!! 

 


Complementing:

I change cache method for PHPExcel_CachedObjectStorageFactory::cache_to_discISAM