Memory allocation problems

Topics: Developer Forum, Project Management Forum, User Forum
Feb 14, 2008 at 2:38 PM
ive tried to use the class on a server but how can i do to optimize the memory usage everytime i use this code
$objPHPExcel = $objReader->load("pordenes.xlsx");
i get this error
Out of memory (allocated 83623936) (tried to allocate 40 bytes) in

ive fixed this on my server increasing the setini(memlimit "200m"); if i dont use at least 200 m i cant use it how can i optimize the memory usage?
Feb 14, 2008 at 5:17 PM
I have an memory problem, too.
I have to create an Excel2007 File from a large Database content. 54.000 lines at the moment. We choose Excel 2007 to support more than 64.000 lines per worksheet, so it is very importent for us to have support for large files.

I get an:

Fatal error: Allowed memory size of 1048576000 bytes exhausted (tried to allocate 50331648 bytes) in /Users/velrok/Arbeit/projekte/CallcenterTool/callcentertool/software/trunk/library/external/PHPExcel/Worksheet.php on line 617

at Row 47653 of 53.xxx

may it be posibe to implement line by line writing to solve this problem?
Coordinator
Feb 14, 2008 at 10:43 PM
How many columns is each row? Do you have styles enabled?
Feb 15, 2008 at 1:03 PM

maartenba wrote:
How many columns is each row? Do you have styles enabled?


I havn't set any style information jet, even if I havn't desabled it explicitly.
22 cols per row:


Here is some code:

$this->xls = new PHPExcel();

$this->xls->setActiveSheetIndex(0);
$sheet = $this->xls->getActiveSheet();

$writer = new PHPExcelWriterExcel2007(new PHPExcel());

$rowNo = 1;
foreach($this->rows as $row){

$colNo = 1;
foreach($row as $value){
$coordinate = self::toAlphaNumber($colNo).(string)$rowNo;
$sheet->setCellValue($coordinate, utf8_encode($value)); // string typ
$sheet->getCell($coordinate)->setDataType(PHPExcelCellDataType::TYPE_STRING);
$colNo++;
}
$rowNo++;
}


$writer->setPHPExcel($this->xls);
$writer->setUseDiskCaching(true);
$writer->save("php://output");
Feb 18, 2008 at 12:10 PM
do you read your datasets line by line?

you should avoid reading a whole resultset into a phparray because you need a lot of memory and also you will get some speed impact.
Feb 18, 2008 at 5:45 PM
Edited Feb 18, 2008 at 5:46 PM

kills wrote:
do you read your datasets line by line?

you should avoid reading a whole resultset into a phparray because you need a lot of memory and also you will get some speed impact.


I've done some memory measurements:

number of rows 39073
memory usage before loop 90.25 MB
memory usage in Row 2 90.25 MB
...

memory usage in Row 9896 273.75 MB
memory usage in Row 9897 274 MB
...
memory usage in Row 9953 274.75 MB
memory usage in Row 9954 275 MB
...

so memory usage increases about 1 MB every 57 rows.

With holding the hole db Result in one array it consumes about 1/3 (90 MB) of the total memory(300 MB), but the PHPExcel instance consumes about 185 MB for about 9954 rows. There will be even more lines needed in de next years, so only reducing the db query result issn't enough.
So it would be nice to be able to save the current PHPExcel instance to disk while appending another instance later.

something like:

$writer = new PHPExcel2007Writer();
$exel = new PHPExcel();
// setting some data
$writer->add($exce);

$excel = new PHPExcel();
// setting some other data
$writer->add($exce);

$writer->save(filename);
//assuming one the written file containing the data of bouth excel files

would ne nice.
Feb 19, 2008 at 11:48 AM
if it is 1/3 of the memory than it is definitly worth to use a dbcursor with *fetchnext() etc to reduce memory usage..

but you are also right that the PHPExcel Class should also be improved in this direction..
(it could be a problem if you handle very big strings.. save numbers as an int/float and not as string will save memory...)
Feb 19, 2008 at 12:59 PM

kills wrote:
if it is 1/3 of the memory than it is definitly worth to use a dbcursor with *fetchnext() etc to reduce memory usage..

but you are also right that the PHPExcel Class should also be improved in this direction..
(it could be a problem if you handle very big strings.. save numbers as an int/float and not as string will save memory...)


I'm afraid I have to use strings for numbers, because we need the leading zeros. Allthow the numbers arn't so big, just regular phone numbers and zip codes.

Are you planning to improv PHPExcel in this direction? If so how long will it presumably take?

Tank's alot for youre fast responses so far.
Feb 19, 2008 at 4:19 PM
I'm no developer of the PHPExcel Class so I can't give you an answer..
Coordinator
Feb 25, 2008 at 9:54 AM
Better support of strings vs. numbers would be nice, will create a work item for this.
Coordinator
Feb 25, 2008 at 9:56 AM
This discussion has been copied to a work item. Click here to go to the work item and continue the discussion.