Taking too much memory space while writing into excel

Topics: Developer Forum, Project Management Forum, User Forum
Oct 1, 2012 at 11:17 AM


I am trying to write 60K database records into an excel file using PHPEXCEL code. earlier it was showing error regarding allocating more memory space so increased the memory space for script now it is working fine for 30K records but still not working for 60K. i increased the space allocation to 2 GB but the system hanged. Can any one sugges any idea how to optimize?



Oct 1, 2012 at 12:38 PM

is 30K rows work with a mem limit of 2GB, it stands to reason that 4GB should be enough for 60K ;)

Oct 1, 2012 at 9:16 PM

What cell caching method are you using?

Oct 2, 2012 at 4:39 AM

Dear Borft, i am new to this & i just mentioned the space statistics in which condition it was executing. earlier by default the memory was 128M in my php.ini file then i increased it to 800M & found working for 30K. Please suggest any soultion?

Oct 2, 2012 at 4:43 AM

Mark. currently i am not using any cell caching method. once i tried PHPExcel_CachedObjectStorageFactory::cache_in_memory_serialized; method but it was showing error. i enables APC in PHP.ini file but it was showing unable to load driver though dll file was there in ext folder. Help me with any solution?

Oct 2, 2012 at 10:47 AM

If something shows an error, let me know what the error is

Oct 3, 2012 at 4:51 AM

it is showing Fatal error: Allowed memory size of 838860800 bytes exhausted (tried to allocate 69 bytes) . i allocated 800MB in php.ini file and the sample code for cell cahing is:




require_once 'PHPExcel.php';

$objPHPExcel = new PHPExcel();
$cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_in_memory_serialized;;PHPExcel_Settings::setCacheStorageMethod($cacheMethod);

i executed it for 60K times using for loop in order to fill the cells.

Oct 3, 2012 at 6:18 AM

Mr Mark one more thing i want to ask. how much memory should it takes if i am writing 30K records from database into excel file having 18 columns?  

Oct 3, 2012 at 8:12 AM

It can only ever be an estimate, because text strings take as much space as they need, but I always use a 1k/cell estimate on 32-bit PHP, 1.6k/cell on 64-bit using the default memory caching method. Assuming you're using 32-bit PHP, then 30,000 rows and 18 columns equates to 540,000 cells or 530MB. The Writer doubles that requirement, so about 1.6GB. That's without factoring in any memory for the library itself (about 20MB) and your own script and variables.

Caching methods reduce the cell memory requirement, but don't eliminate it.