Copying a Worksheet to a new Workbook

Topics: Developer Forum
May 17, 2011 at 2:32 PM
Edited May 17, 2011 at 2:34 PM

I'm using PHPExcel to generate a number of rather complex worksheets from external datasources (Oracle, CSV and Salesforce.com in fact) - which, by the way, is going to be fantastic once it's done, since it will save me around an hour each morning generating these files using Excel macros...

Once the data is retrieved, I create 4 separate reports, each in a separate worksheet, all as one large workbook.  This works fine.

However, I'd also like to save each worksheet as a separate Excel file, so that I can email them out automatically to the appropriate recipients (saving me another 10 minutes of work in the morning).  I don't want to just send the full file to everyone, as only 1 of the 4 reports is of any interest to any one person...

So, my idea was to copy the Worksheet object, and save that in a new PHPExcel object... but doing that has proved rather complicated!  What I've got so far works, but seems rather inelegant:

 

function saveWorksheetToFile(PHPExcel_Worksheet $worksheet, $filename) {
	/* Create a new workbook with just the supplied sheet */
	$objPHPExcel = new PHPExcel();
	$objPHPExcel->removeSheetByIndex(0);
	/* We use "clone" to ensure that the sheet is not removed from the old workbook */
	$cloned_sheet = clone $worksheet;
	/* Rename the cloned sheet, and add it to the original workbook */
	$sheet_title = $worksheet->getTitle();
	$cloned_sheet->setTitle('~'.$sheet_title);
	$worksheet->getParent()->addSheet($cloned_sheet);
	/* We use "addExternalSheet" to ensure the styles are copied to the new workbook */
	$objPHPExcel->addExternalSheet($cloned_sheet);
	/* Restore the original sheet title */
	$cloned_sheet->setTitle($sheet_title);
	/* Write the sheet to a file */
	$objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);
	$objWriter->save($filename);
}

 

As you can see, I first clone the sheet and add it to my original workbook (with a different name), then move it to the new workbook and rename it back, then write it to the file...

If I don't add the cloned sheet to the original workbook first, when I do "addExternalSheet" it appears to remove the original sheet from that workbook too!

Two questions, if the more experienced users here would be so kind:

  1. Is there a more efficient/elegant way to do this?
  2. What objects can I or should I "unset" in order to save as much memory as possible, without breaking my original sheets in the original workbook?

Thanks in advance for your help,

Nick

Addendum: PHPExcel 1.7.6, PHP 5.2.6 with Suhosin-Patch 0.9.6.2 (cli), AIX 5.3