How to duplicate a Sheet

Topics: Developer Forum
Oct 6, 2010 at 9:20 PM

Im working with a template file that has only one sheet (sheet_template).

How can I create a new Sheet base on the template on sheet_template?

Coordinator
Oct 6, 2010 at 9:42 PM
$newSheet = $objPHPExcel->getSheetByName("sheet_template")->copy();

Will create a "shallow" clone of your template sheet. Note that any changes to the "clone" will affect the original template as well.

 

To create a completely new sheet as a copy of the original template:

$newSheet = clone $objPHPExcel->getSheetByName("sheet_template");
$newSheet->setTitle('New Sheet');
$newSheetIndex = 1;
$objPHPExcel->addSheet($newSheet,$newSheetIndex);
Dec 2, 2010 at 10:51 PM
Edited Dec 2, 2010 at 10:52 PM

I used the method specified by MarkBaker, but what I get is as follows:

  • a copy of sheet A as a new sheet with title I specify for sheet B
  • if a create an additional copy (C) then values that I write into sheet C clobber those written to sheet , so that sheets B & C have the same values

:-(

 

Coordinator
Dec 7, 2010 at 10:09 PM
Edited Dec 7, 2010 at 10:29 PM

I've confirmed this problem with the 1.7.4 code. You'll be pleased to know that it is already fixed in the latest SVN code. I'm discussing when to release this as version 1.7.5 with Maarten, but hopefully it will be the end of this week or early next. In the meanwhile, it's available to download from the SVN repository: click on the "Source Code" tab, and select "download" from the "latest version" box on the right.

Using the following code:

$fileName = 'cloneTest.xls';

$objPHPExcel = PHPExcel_IOFactory::load($fileName);

$A = $objPHPExcel->getActiveSheet();

$B = clone $A;
$B->setTitle('Sheet B');
$sheetIndex = 1;
$objPHPExcel->addSheet($B,$sheetIndex);
$B->getCell('A1')->setValue('Mark Baker');

$C = clone $B;
$C->setTitle('Sheet C');
$sheetIndex = 2;
$objPHPExcel->addSheet($C,$sheetIndex);
$C->getCell('A1')->setValue('PHPExcel');

echo '<table border="1">';
echo '<tr><td><b>Sheet A</b></td><td><b>Sheet B</b></td><td><b>Sheet C</b></td></tr>';
echo '<tr><td>';
var_dump($A->toArray(null,true,true,true));
echo '</td><td>';
var_dump($B->toArray(null,true,true,true));
echo '</td><td>';
var_dump($C->toArray(null,true,true,true));
echo '</td></tr>';
echo '</table>';

With the 1.7.4 codebase, this gives:

Sheet A Sheet B Sheet C
array
  1 => 
    array
      0 => 'PHPExcel'
      1 => 'DEF' 
      2 => 'GHI' 
  2 => 
    array
      0 => 'JKL' 
      1 => 'MNO' 
      2 => 'PQR' 
  3 => 
    array
      0 => 'STU' 
      1 => 'VWX' 
      2 => 'YZ_' 
array
  1 => 
    array
      0 => 'PHPExcel'
      1 => 'DEF' 
      2 => 'GHI' 
  2 => 
    array
      0 => 'JKL' 
      1 => 'MNO' 
      2 => 'PQR' 
  3 => 
    array
      0 => 'STU' 
      1 => 'VWX' 
      2 => 'YZ_' 
array
  1 => 
    array
      0 => 'PHPExcel'
      1 => 'DEF' 
      2 => 'GHI' 
  2 => 
    array
      0 => 'JKL' 
      1 => 'MNO' 
      2 => 'PQR' 
  3 => 
    array
      0 => 'STU' 
      1 => 'VWX' 
      2 => 'YZ_' 

With the latest SVN code, it gives:

Sheet A Sheet B Sheet C
array
  1 => 
    array
      'A' => 'ABC'
      'B' => 'DEF'
      'C' => 'GHI'
  2 => 
    array
      'A' => 'JKL'
      'B' => 'MNO'
      'C' => 'PQR'
  3 => 
    array
      'A' => 'STU'
      'B' => 'VWX'
      'C' => 'YZ_'
array
  1 => 
    array
      'A' => 'Mark Baker'
      'B' => 'DEF'
      'C' => 'GHI'
  2 => 
    array
      'A' => 'JKL'
      'B' => 'MNO'
      'C' => 'PQR'
  3 => 
    array
      'A' => 'STU'
      'B' => 'VWX'
      'C' => 'YZ_'
array
  1 => 
    array
      'A' => 'PHPExcel'
      'B' => 'DEF'
      'C' => 'GHI'
  2 => 
    array
      'A' => 'JKL'
      'B' => 'MNO'
      'C' => 'PQR'
  3 => 
    array
      'A' => 'STU'
      'B' => 'VWX'
      'C' => 'YZ_'

 

Dec 7, 2010 at 10:54 PM
Glad to know.

Thanks.



MarkBaker <notifications@codeplex.com> wrote:

>From: MarkBaker
>
>I've confirmed this problem with the 1.7.4 code. You'll be pleased to know that it is already fixed in the latest SVN code.Using the following code:$fileName = 'cloneTest.xls';$objPHPExcel = PHPExcel_IOFactory::load($fileName);$A = $objPHPExcel->getActiveSheet();$B = clone $A;$B->setTitle('Sheet B');$sheetIndex = 1;$objPHPExcel->addSheet($B,$sheetIndex);$B->getCell('A1')->setValue('Mark Baker');$C = clone $B;$C->setTitle('Sheet C');$sheetIndex = 2;$objPHPExcel->addSheet($C,$sheetIndex);$C->getCell('A1')->setValue('PHPExcel');echo ' ';echo ' ';echo ' ';echo '
Sheet A Sheet B Sheet C
';var_dump($A->toArray(null,true,true,true));echo ' ';var_dump($B->toArray(null,true,true,true));echo ' ';var_dump($C->toArray(null,true,true,true));echo '
';With the 1.7.4 codebase, this gives:
>Sheet A Sheet B Sheet C
>array 1 =>array 0 => <small>string</small> 'PHPExcel' (length=8) 1 => <small>string</small> 'DEF' (length=3) 2 => <small>string</small> 'GHI' (length=3) 2 =>array 0 => <small>string</small> 'JKL' (length=3) 1 => <small>string</small> 'MNO' (length=3) 2 => <small>string</small> 'PQR' (length=3) 3 =>array 0 => <small>string</small> 'STU' (length=3) 1 => <small>string</small> 'VWX' (length=3) 2 => <small>string</small> 'YZ_' (length=3) array 1 =>array 0 => <small>string</small> 'PHPExcel' (length=8) 1 => <small>string</small> 'DEF' (length=3) 2 => <small>string</small> 'GHI' (length=3) 2 =>array 0 => <small>string</small> 'JKL' (length=3) 1 => <small>string</small> 'MNO' (length=3) 2 => <small>string</small> 'PQR' (length=3) 3 =>array 0 => <small>string</small> 'STU' (length=3) 1 => <small>string</small> 'VWX' (length=3) 2 => <small>string</small> 'YZ_' (length=3) array 1 =>array 0 => <small>string</small> 'PHPExcel' (length=8) 1 => <small>string</small> 'DEF' (length=3) 2 => <small>string</small> 'GHI' (length=3) 2 =>array 0 => <small>string</small> 'JKL' (length=3) 1 => <small>string</small> 'MNO' (length=3) 2 => <small>string</small> 'PQR' (length=3) 3 =>array 0 => <small>string</small> 'STU' (length=3) 1 => <small>string</small> 'VWX' (length=3) 2 => <small>string</small> 'YZ_' (length=3)
>With the latest SVN code, it gives:
>Sheet A Sheet B Sheet C
>array 1 =>array 'A' => <small>string</small> 'ABC' (length=3) 'B' => <small>string</small> 'DEF' (length=3) 'C' => <small>string</small> 'GHI' (length=3) 2 =>array 'A' => <small>string</small> 'JKL' (length=3) 'B' => <small>string</small> 'MNO' (length=3) 'C' => <small>string</small> 'PQR' (length=3) 3 =>array 'A' => <small>string</small> 'STU' (length=3) 'B' => <small>string</small> 'VWX' (length=3) 'C' => <small>string</small> 'YZ_' (length=3) array 1 =>array 'A' => <small>string</small> 'Mark Baker' (length=10) 'B' => <small>string</small> 'DEF' (length=3) 'C' => <small>string</small> 'GHI' (length=3) 2 =>array 'A' => <small>string</small> 'JKL' (length=3) 'B' => <small>string</small> 'MNO' (length=3) 'C' => <small>string</small> 'PQR' (length=3) 3 =>array 'A' => <small>string</small> 'STU' (length=3) 'B' => <small>string</small> 'VWX' (length=3) 'C' => <small>string</small> 'YZ_' (length=3) array 1 =>array 'A' => <small>string</small> 'PHPExcel' (length=8) 'B' => <small>string</small> 'DEF' (length=3) 'C' => <small>string</small> 'GHI' (length=3) 2 =>array 'A' => <small>string</small> 'JKL' (length=3) 'B' => <small>string</small> 'MNO' (length=3) 'C' => <small>string</small> 'PQR' (length=3) 3 =>array 'A' => <small>string</small> 'STU' (length=3) 'B' => <small>string</small> 'VWX' (length=3) 'C' => <small>string</small> 'YZ_' (length=3)
>
>
>
Dec 17, 2010 at 6:44 PM
Edited Dec 17, 2010 at 6:45 PM

I have one question...

I did this the example that MarkBaker provide....

$newSheet = clone $objPHPExcel->getSheetByName("sheet_template");
$newSheet->setTitle('New Sheet');
$newSheetIndex = 1;
$objPHPExcel->addSheet($newSheet,$newSheetIndex);

but it fails on copying one imagen that i have in the template, it doesnt copy the template, it shows an red X... like it doesnt find the imagen...

i also get this error with that

Warning: flose(): supplied argument is not a valid stream resource in $myroot/lib/Classes/PHPExcel/CachedObjectStorage/PHPTemp.php on line 152.

any suggestion...?????

Sep 10, 2013 at 1:54 PM
hi, i try you code MarkBaker but show me an error

Argument 1 passed to PHPExcel_Cell::attach() must be an instance of PHPExcel_CachedObjectStorage_CacheBase, instance of PHPExcel_Worksheet given, called in C:\xampp\htdocs\sistemanota\protected\vendors\PHPExcel\CachedObjectStorage\Memory.php on line 99 and defined
Sep 14, 2013 at 2:34 PM
I'm getting the same error as @yerkicaceres does. Any improvements/workarounds?
Coordinator
Sep 14, 2013 at 6:04 PM
This has been fixed in the develop branch on github
Oct 9, 2013 at 12:47 PM
Hi there, I am running into the same error, so I downloaded the 2.0.0 development branch from github.

The file PHPExcel.php seems to be missing in the dev package, am I missing something here ?

The installation instructions tell me to include PHPExcel.php inside the class dir.

What am I doing wrong ?
Coordinator
Oct 9, 2013 at 1:40 PM
The 2.0.0 branch should NOT be considered stable, and is first steps toward a complete rewrite which will NOT be backward compatible with the 1.7x branch.... the develop branch has all the latest patches to version 1.7x
Oct 9, 2013 at 1:50 PM
Thanks a lot for the swift reply Mark. That's appreciated a lot.

Downloading the 1.7.x dev pack right now. Cheers.
Oct 10, 2013 at 9:45 AM
Confirming the issue is fixed in the 1.7.x dev branch.
Jan 28, 2014 at 4:09 PM
Edited Jan 28, 2014 at 4:11 PM
Hi,
I have copied the develop pack but I still have the issue
Argument 1 passed to PHPExcel_Cell::attach() must be an instance of PHPExcel_CachedObjectStorage_CacheBase, instance of PHPExcel_Worksheet given
when I use the Clone function.
Am I missing something ?
Cheers
Jan 29, 2014 at 8:15 PM
I was having the same problem others have found with clone, and using the dev code fixed it. Thanks