PHPExcel + APC

Topics: Developer Forum, User Forum
Mar 11, 2013 at 6:21 PM
I'm trying to generate large file containing more than 5000 rows...

I had some memory problem. I've tried to use de caching but I think i'm half way there:

Before using APC I would memory exhaust when I was looping to set each cell value. Now, I can loop 5-10-15 thousand time without memory exhausting.My problem now is with the writer.

I still memory exhaust but when saving and generating the file.

Please help!!

Here is a pretty simple example:
// Set caching properties
echo date('H:i:s') . " Set cache properties\n";
$cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_to_apc;
$cacheSettings = array( 'cacheTime' => 600);
$response = PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings);
echo date('H:i:s') . " Cache Response: |$response|\r\n";

// Create new PHPExcel object
echo date('H:i:s') . " Create new PHPExcel object\n";
$objPHPExcel = new PHPExcel();

// Add some data
echo date('H:i:s') . " Add some data\n";
$objPHPExcel->setActiveSheetIndex(0);
$row=1;
while ($row <= 6000)
{
//print_r("$row\n");
$objPHPExcel->getActiveSheet()->SetCellValue('A'.$row, 'Hello');
$objPHPExcel->getActiveSheet()->SetCellValue('B'.$row, 'world!');
$objPHPExcel->getActiveSheet()->SetCellValue('C'.$row, 'HELLO');
$objPHPExcel->getActiveSheet()->SetCellValue('D'.$row, 'WORLD!');

++$row;
}

// Rename sheet
echo date('H:i:s') . " Rename sheet\n";
$objPHPExcel->getActiveSheet()->setTitle('Simple');

// Save Excel 2007 file
echo date('H:i:s') . " Write to Excel2007 format\n";

$objPHPExcel->getDefaultStyle()->getFont()->setName('Calibri');
$objPHPExcel->getDefaultStyle()->getFont()->setSize(8);

echo "Peak memory usage: " . (memory_get_peak_usage(true) / 1024 / 1024) . " MB"; // 217.25

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, "Excel2007");
$objWriter->setUseDiskCaching(true, '/tmp/');
$objWriter->save('spyke.xlsx');