How to copy worksheet from one xls to another?

Topics: User Forum
Oct 27, 2009 at 4:36 PM

I have several manual-done XLS files. I want to use them as a templates of worksheets to join them together and create one XLS file with my data.

I try the following code:

  $xls = new PHPExcel();
  $xlsReader= new PHPExcel_Reader_Excel5();
  $xlsTemplate = $xlsReader->load( "template.xls" );
  $sheet1 = $xlsTemplate->getSheet(0);
  $xls->addSheet( $sheet1, 0 );
  $xlsWriter = new PHPExcel_Writer_Excel5($xls);
  $xlsWriter->save( "myfile.xls" );

After this code finishes result is not good. File "myfile.xls" contains all the numbers, texts and formulas of "template.xls", but does NOT contain cell style information. Font, font size, cell background - all of this is default, not as in "template.xls".

What can I do to copy worksheet with style information?

 

 

Developer
Oct 29, 2009 at 3:44 AM
GRIG wrote:

After this code finishes result is not good. File "myfile.xls" contains all the numbers, texts and formulas of "template.xls", but does NOT contain cell style information. Font, font size, cell background - all of this is default, not as in "template.xls".

What can I do to copy worksheet with style information?

Yes, you need to use addExternalSheet() instead of addSheet() when you need to move a sheet from one workbook to another.

Related thread:

http://phpexcel.codeplex.com/Thread/View.aspx?ThreadId=36984

Oct 29, 2009 at 10:21 AM

Thanks. That is really solution. But its implementation contain BUG:

File PHPExcel.php @version    1.7.0, 2009-08-10, line 389-418:

/**
     * Add external sheet
......
     * @return PHPExcel_Worksheet
     */
public function addExternalSheet(PHPExcel_Worksheet $pSheet) {
.....
		return $this->addSheet($pSheet);
	}

File PHPExcel.php @version    1.7.0, 2009-08-10, line 211-233:

/**
     * Add sheet
.....
     */
    public function addSheet(PHPExcel_Worksheet $pSheet = null, $iSheetIndex = null)
    {
.....
// no return any value. function documented as 'void'
    }

BTW, why addExternalSheet() has only 1 parameter, while its implementation calls to addSheet() which allows 2 parameters?

 

Developer
Oct 30, 2009 at 9:24 PM

Agree with your comments.

>> But its implementation contain BUG:
http://phpexcel.codeplex.com/WorkItem/View.aspx?WorkItemId=10859

>> BTW, why addExternalSheet() has only 1 parameter, while its implementation calls to addSheet() which allows 2 parameters?
http://phpexcel.codeplex.com/WorkItem/View.aspx?WorkItemId=10860

Download latest source code tomorrow:

http://phpexcel.codeplex.com/SourceControl/ListDownloadableCommits.aspx

Apr 5, 2012 at 2:36 PM

 

/* Load template sheet to be copied */
$template_sheet = $Libro->getSheetByName($Planta);
/* Create a copy into a new sheet object */
$sheet = $template_sheet->copy();
/* Change name to new (cloned) worksheet */
$sheet->setTitle("copied");
/* Add the sheet to workbook */
$Libro->addSheet($sheet, 1);
/* Then apply a formula to new worksheet */
$Libro->getSheetByName('copied')->setCellValueExplicit('H10','=Detalle!H' . $row);

The code above doesn't work for me, the value (formula) applied change for both sheets, template sheet and copied sheet. How can I fix this issue? please help! ;)

 

Coordinator
Apr 5, 2012 at 8:27 PM

The copy() method creates a "shallow" clone. You need to create a "deep" clone.

Instead of

$sheet = $template_sheet->copy();

use

$sheet = clone $template_sheet;

 

Apr 9, 2012 at 12:42 PM

Thanks for you reply Mark. I tried the code you've posted above, and It doesn't work, I still get the same issue, the value/formula apply for two sheets (template sheet and copied one). Please help!

/* Load template sheet to be copied */
$template_sheet = $Libro->getSheetByName($Planta);
/* Create a copy into a new sheet object */
$sheet = clone $template_sheet;
/* Change name to new (cloned) worksheet */
$sheet->setTitle("copied");
/* Add the sheet to workbook */
$Libro->addSheet($sheet, 1);
/* Then apply a formula to new worksheet */
$Libro->getSheetByName('copied')->setCellValueExplicit('H10','=Detalle!H' . $row);