Excel5 Sheet->copy() memory usage

Topics: User Forum
Jun 30, 2009 at 7:00 PM
Edited Jun 30, 2009 at 7:03 PM

Hi all,

I have recently started using PHPExcel to write a set of POST data into a Excel5 workbook.

My code looks like:

1 $reader = PHPExcel_IOFactory::createReader('Excel5');
2 $reader->setLoadSheetsOnly(true);
3 $excel = $reader->load($fileName);
4 $sheet = $excel->getActiveSheet();
5 $copiedSheet = $sheet->copy();

Using the copied sheet I then iterate through my post data and fill in the specific row/columns.

By outputting the memory usage I can see: (numbers relate to the steps above)


The above was generated using an excel file with the dimensions A1:H29.

My POST data can contain multiple sets of data relating to the same 'template' sheet, so steps 1-5 can be repeated several times.

My issue comes from the amount of memory required to copy the sheet in step #5.  After a few iterations I run out of memory and php fails with the standard error message. Although I can increase the memory limit, this will not solve the issue as when attempt to send more data/iterations I will eventually run out of space.

Is there a standard way of cleaning up the sheets/workbooks, etc.. so that the memory used goes down? I know that turning off the styles helps, but I do not want to lose the style information.




P.S There are no calculations in the example above and the styles are limited to a few grey backgrounds, bold text, etc... and there are about 4 comments within the workbook.

Jul 1, 2009 at 1:56 AM

Can you try with latest source code:

Latest source code uses shared styles, so copying a worksheet does not blow up memory that much as with PHPExcel 1.6.7.

Additional note:


This is wrong use of that method. I guess it is just a typo, setLoadSheetsOnly() takes a string, e.g. 'Sheet 1', or an array of sheet names, array('Sheet 1','Sheet 2').

Jul 1, 2009 at 8:24 AM

Hi Koyama,

Thanks for the hint - the loadSheetsOnly usage was just my attempt to choose any random methods to reduce the memory footprint! I'll remove it :-)



Jul 4, 2009 at 5:37 AM

Hi Koyama,

I've updated to the latest source code and without changing my code I'm not getting the memory error anymore, but I don't get any styles either - is there an option I need to set to enable styles now?



Jul 4, 2009 at 5:46 AM

This is strange. And you were getting styles before? Can you try to do a plain read / write of your template workbook and see if you lose styles?

Jul 4, 2009 at 5:56 AM

Trying the following code I lose all styles:

ini_set( 'memory_limit', '40M' );
set_include_path(get_include_path() . PATH_SEPARATOR . '.');

$objPHPExcel = new PHPExcel();

$reader = PHPExcel_IOFactory::createReader('Excel5');
$excel = $reader->load('Template.xls');
$sheet = $excel->getActiveSheet();
$copiedSheet = $sheet->copy();

header("Content-Type: application/x-excel5");
header("Content-Disposition: attachment;filename=Template.xls");
header("Content-Transfer-Encoding: binary ");

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');

Jul 4, 2009 at 6:11 AM

Ok, I see the problem. If you are copying sheet from one file to another, instead of this:


you need this:


Jul 4, 2009 at 6:40 AM

Brilliant, thanks Koyama!

Jul 23, 2009 at 9:08 AM


I have a the same issue of memory with the last version.

My code load an existing excel file containing 6 sheets, to put in some data and remove some sheets when i have to do it.

And i have one sheet i have to copy and put in some data everytime i need. This sheet contain 2 images and has a dimension A1:AB39.

Then i generate the file.

The process Apache use 400Mo when i copy 3 times the same sheet.

It seems like the memory up to 100Mo everytime i copy a sheet.

Is there a solution?


Jul 24, 2009 at 12:33 AM


I know it's late but i have to finish this work today. This afternoon, i'm in holyday ^^

So, i found where the issue come from:

In the file Classes/PHPExcel/Worksheet.php, there is th function __clone:


* Implement PHP __clone to create a deep clone, not just a shallow copy.
public function __clone() {
foreach ($this as $key => $val) {
if ($key == '_parent') {

if (is_object($val) || (is_array($val))) {
$this->{$key} = unserialize(serialize($val));

The red line "eating" me all my memory.

Can i use an alternative?



Jul 24, 2009 at 6:31 AM

@chado01: Can you send me the Excel file? (erik at phpexcel dot net). I will try to look at it this evening.

Apr 28, 2010 at 9:11 AM
Edited Apr 29, 2010 at 7:43 AM

i have the same problem with copy worksheet in template in excel2007

i try solve this when  stop recursion


-2476                continue;

+2476                 break; 

but it completly WRONG


Apr 29, 2010 at 9:27 AM


/** Error reporting */
/** PHPExcel */
require_once '.'.DIRECTORY_SEPARATOR.'Classes'.DIRECTORY_SEPARATOR.'PHPExcel.php';

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

$sheet1 = $objPHPExcel->getSheetByName('Simple')->copy();

//here template is copied
for ($i=1;$i<8;$i++){ $sheet2 = clone $sheet1; //notice that here sheet is cloned
//it work and solved prolblem with memory leaks

 $sheet2title = sheetSetTitles($sheet2,$i);

//strange that sometimes odd sheets do not add hyperlink

echo date('H:i:s') . " Peak memory usage: " . (memory_get_peak_usage(true) / 1024 / 1024) . " MB\r\n"; unset($sheet2); } $objPHPExcel->setActiveSheetIndex(1); // Save Excel 2007 file echo date('H:i:s') . " Write to Excel2007 format\n"; $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007'); $objWriter->save('resultbug.xlsx'); // Echo memory peak usage echo date('H:i:s') . " Peak memory usage: " . (memory_get_peak_usage(true) / 1024 ) . " KB\r\n"; // Echo done echo date('H:i:s') . " Done writing file.\r\n"; function sheetSetTitles($sheet,$number){ $sheettitle = 'Cloned'.$number; if ($number == 2 ){$sheettitle = 'OOOPS where link in List';}
$sheet->setTitle($sheettitle); $sheet->setCellValue('A1',$number.'New value at cloned'); $sheet->getCell('A1') ->getHyperlink() ->setUrl("sheet://'List'!A".$number); $sheet->setCellValue('C'.$number,(1/$number)); return $sheettitle; } ?>