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)

2:6380924
3:7971140
4:7971140
5:11855776

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.

Thanks,

Rob

 

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.

Developer
Jul 1, 2009 at 1:56 AM

Can you try with latest source code:
http://phpexcel.codeplex.com/SourceControl/ListDownloadableCommits.aspx

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:

$reader->setLoadSheetsOnly(true);

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 :-)

Thanks,

Rob

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?

Thanks,

Rob

Developer
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 . '.');
require_once("./PHPExcel/IOFactory.php");

$objPHPExcel = new PHPExcel();
$objPHPExcel->removeSheetByIndex(0);

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

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

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output');
?>

Developer
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:

$objPHPExcel->addSheet($copiedSheet);

you need this:

$objPHPExcel->addExternalSheet($copiedSheet);

Jul 4, 2009 at 6:40 AM

Brilliant, thanks Koyama!

Jul 23, 2009 at 9:08 AM

Hi,

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

Hi,

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') {
continue;
}

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?

 

 

Developer
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

Worksheet.php

-2476                continue;

+2476                 break; 

but it completly WRONG

 

Apr 29, 2010 at 9:27 AM

<?php

/** Error reporting */
error_reporting(E_ALL);
/** PHPExcel */
require_once '.'.DIRECTORY_SEPARATOR.'Classes'.DIRECTORY_SEPARATOR.'PHPExcel.php';
require_once'.'.DIRECTORY_SEPARATOR.'Classes'.DIRECTORY_SEPARATOR.'PHPExcel'.DIRECTORY_SEPARATOR.'IOFactory.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);
$objPHPExcel->addSheet($sheet2);
$objPHPExcel->getSheetByName('List')
->InsertNewRowBefore(2,1)
->SetCellValue('A2',$sheet2title)
->getCell('A2')
->getHyperlink()
->setUrl("sheet://'".$sheet2title."'!A1");

//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; } ?>