Problem with large file

Topics: Developer Forum
Jan 15, 2010 at 5:57 PM
Edited Jan 15, 2010 at 5:58 PM

Hello,

this script won't output error, it just do nothing. It return correct file only, when I delete some columns. Do you have any idea how to make it faster? This is the code:

<?php
/** Error reporting */
error_reporting(E_ALL);

/** PHPExcel */
require_once SERVER_ADDRESS.'excel/PHPExcel.php';

/** PHPExcel_IOFactory */
require_once SERVER_ADDRESS.'excel/PHPExcel/IOFactory.php';

// Create new PHPExcel object
$objPHPExcel = new PHPExcel();

// Set properties
$objPHPExcel->getProperties()->setCreator("www.evandar.net")
                             ->setLastModifiedBy("www.evandar.net")
                             ->setTitle("Office 2007 XLSX Document")
                             ->setSubject("Office 2007 XLSX Document")
                             ->setDescription("Document for Office 2007 XLSX, generated using PHP classes.")
                             ->setKeywords("office 2007 openxml php")
                             ->setCategory("Test result file");

// Create a first sheet
$objPHPExcel->setActiveSheetIndex(0);

$sharedStyle1 = new PHPExcel_Style();

$sharedStyle1->applyFromArray(array(
'fill' => array(
//'type' => PHPExcel_Style_Fill::FILL_SOLID,
//'color'        => array('argb' => 'ffffffff')
'type' => PHPExcel_Style_Fill::FILL_GRADIENT_LINEAR,
'rotation' => 90,
'startcolor' => array('argb' => 'ff76adff'),
'endcolor' => array('argb' => 'ffdeebff')
),
'font' => array(
'bold' => true,
'color' => array('argb' => 'ff000000')
)
));

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

$sql=mysql_query("SELECT * FROM `turnover_red` ORDER BY `id`");

if(mysql_num_rows($sql)!=0)
{
$objPHPExcel->getActiveSheet()->setCellValue('A1', 'Měsíc');
$objPHPExcel->getActiveSheet()->setCellValue('B1', 'Rok');
$objPHPExcel->getActiveSheet()->setCellValue('C1', 'Region');
$objPHPExcel->getActiveSheet()->setCellValue('D1', 'Odběratel');
$objPHPExcel->getActiveSheet()->setCellValue('E1', 'Produkt');
$objPHPExcel->getActiveSheet()->setCellValue('F1', 'Množství');
$objPHPExcel->getActiveSheet()->setCellValue('G1', 'Cena');
$objPHPExcel->getActiveSheet()->setCellValue('H1', 'Název premixu');
$objPHPExcel->getActiveSheet()->setCellValue('I1', 'Partner');
$objPHPExcel->getActiveSheet()->setCellValue('J1', 'Koncentrace premixu [%]');
$objPHPExcel->getActiveSheet()->setCellValue('K1', 'Množství premixu na tunu [g]');

$r=1;

$variables=null;
$variables=Array('id', 'month', 'year', 'region_id', 'vatin', 'product', 'quantity', 'amount', 'premix_name', 'partner', 'premix_concentration', 'premix_quantity');

while($entry=mysql_fetch_array($sql))
{
foreach($variables as $variable) { $$variable=$entry[$variable]; }

$r++;

$objPHPExcel->getActiveSheet()->setCellValue('A'.$r, $month);
$objPHPExcel->getActiveSheet()->setCellValue('B'.$r, $year);
$objPHPExcel->getActiveSheet()->setCellValue('C'.$r, $region_id);
$objPHPExcel->getActiveSheet()->setCellValue('D'.$r, $vatin_name);
$objPHPExcel->getActiveSheet()->setCellValue('E'.$r, $product_name);
$objPHPExcel->getActiveSheet()->setCellValue('F'.$r, $quantity);
$objPHPExcel->getActiveSheet()->setCellValue('G'.$r, $amount);
$objPHPExcel->getActiveSheet()->setCellValue('H'.$r, $premix_name);
$objPHPExcel->getActiveSheet()->setCellValue('I'.$r, $partner_name);
$objPHPExcel->getActiveSheet()->setCellValue('J'.$r, $premix_concentration);
$objPHPExcel->getActiveSheet()->setCellValue('K'.$r, $premix_quantity);
}
}

// Set autofilter
$objPHPExcel->getActiveSheet()->setAutoFilter('A1:K'.$r);

$objPHPExcel->getActiveSheet()->setSharedStyle($sharedStyle1, 'A1:K1');

$objPHPExcel->getActiveSheet()->setTitle('Obraty červené');
$objPHPExcel->getActiveSheet()->getTabColor()->setRGB('FF0000');

// Set active sheet index to the first sheet, so Excel opens this as the first sheet
$objPHPExcel->setActiveSheetIndex(0);

// Save Excel 2007 file
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save(str_replace('.php', '.xlsx', __FILE__));
?>
Thanks

Developer
Jan 17, 2010 at 5:37 AM

Have you identified the error? Is this only happening when there are many rows?

Jan 17, 2010 at 6:45 AM

No but i try it now, and it reply with this error:

Fatal error: Allowed memory size of 50331648 bytes exhausted (tried to allocate 262144 bytes) in /data/www/excel/PHPExcel/Worksheet.php on line 993

There is only 1560 rows.

Developer
Jan 17, 2010 at 9:57 AM

If possible, try to raise memory limit by putting this at the beginning of your script:

 

ini_set('memory_limit', '128M');
Jan 17, 2010 at 10:18 AM

It doesn't work, but I have default memory_limit 48M, and in error is:

Allowed memory size of 50331648 bytes exhausted (tried to allocate 262144 bytes)

-> it is quite unlogical ... limit 50331648 is much more than 262144 ... Do you have any idea how to repair it?

Developer
Jan 17, 2010 at 10:30 AM

It is definitely the memory that is exhausted. What version of PHPExcel are you using?

Jan 17, 2010 at 10:42 AM

version 1.7.2, 2010-01-11

Developer
Jan 19, 2010 at 8:19 AM

Sorry, but there may not be much you can do right now other than increasing memory limit to as high a value as possible and keep yourself updated on when situation with high memory usage changes in PHPExcel.

Feb 10, 2010 at 7:52 PM

I have the same problem.

A large excel file (500 rows, but many columns with much data per cell)

No problems arise with using a smaller file.


Fatal error: Allowed memory size of 41943040 bytes exhausted (tried to allocate 94 bytes) in /var/www/vhosts/coachteam.net/httpsdocs/scripts/PHPExcel/Classes/PHPExcel/Worksheet.php on line 977

Coordinator
Feb 10, 2010 at 8:16 PM
Edited Feb 10, 2010 at 8:19 PM

I refer you to my comments in http://phpexcel.codeplex.com/Thread/View.aspx?ThreadId=83554, particul;arly the first paragraph of my response.

Jun 2, 2010 at 7:49 PM

put this 

ini_set('memory_limit', '128M');
in the first line of the file, it will work fine.