Duplicating a worksheet to another document

Topics: User Forum
Sep 19, 2011 at 2:35 PM


I am not sure how simple this might be but I need to be able to copy a worksheet from an excel file and create a separate excel file from it. I currently have an excel file that has like 100+ worksheets in it. What I need to be able to do is search for a specific worksheet, which seems simple enough using the examples, and then duplicate that worksheet from the file and place it in a new file that then gets exported to a user. The original file needs to stay unchanged though

I dont need to read any specific data from the worksheet or change anything, I just need an exact copy of it. I can't give users the whole excel file with over 100 worksheets in it because each user will just need one sheet depending on what they are doing or looking for. Each user will know the name of specific worksheet they need so I will use that to search the file. So the 100+ worksheet excel file will be a master file that everyone can pull different worksheets from.

Thanks in advanced for any help anyone can provide in this.

Sep 19, 2011 at 2:40 PM

You should be able to just...

...load your spreadsheet, which will then load only this sheet, then save it as a new spreadsheet.

Sep 19, 2011 at 6:54 PM

Thanks. I will give this a try and see if it works.

Dec 14, 2011 at 12:26 AM

Hello Lifegiver36,

I need to do the same, did you figured it out? I would appreciate your help.


Dec 14, 2011 at 1:42 PM


Here is what I did, hopefully it works for you:


    require_once 'phpExcel/PHPExcel.php';
    include 'phpExcel/PHPExcel/IOFactory.php';
    // Create new PHPExcel object
    $objPHPExcel = new PHPExcel();
    // Set properties
                                 ->setTitle("Spec Sheet")
                                 ->setSubject("Spec Sheet")
                                 ->setDescription("some description");
    // Set active sheet index to the first sheet, so Excel opens this as the first sheet
    $inputFileType = 'Excel2007';
    $inputFileName = 'specSheetName.xlsx';
    /**  Create a new Reader of the type defined in $inputFileType  **/
    $objReader = PHPExcel_IOFactory::createReader($inputFileType);
    /**  $code is the name of the worksheet I want to grab from the main file  **/
    /**  Load $inputFileName to a PHPExcel Object  **/
    $objPHPExcel = $objReader->load($inputFileName);

//what the file will be called when exported out

$filename = $code.".xlsx";
    // Redirect output to a client’s web browser (Excel2007)
    header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    header('Content-Disposition: attachment;filename="'.$filename.'"');
    header('Cache-Control: max-age=0');
    $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');

Dec 18, 2011 at 3:23 AM
Edited Dec 18, 2011 at 3:23 AM

Hello Lifegiver,


Thanks for your help! One more question, how do you search thru the worksheets, in other word how do you iterate thru all the worksheets. In my case I will have to duplicate/copy all the worksheet into another one

Thnaks again for your help..

Best regards.

Dec 18, 2011 at 7:46 PM
foreach ($objPHPExcel->getWorksheetIterator() as $worksheet) {
    //do your work with each $worksheet here
Dec 19, 2011 at 4:04 PM

Hello Mark,

Thanks for the help, my issue is really when the sheets are being created, I am getting a 

PHP Fatal error:  Uncaught exception 'Exception' with message 'Active sheet index is out of bounds.'

Here is what I am doing:


$objReader = PHPExcel_IOFactory::createReader('Excel5');

$sheetIdx = 0;

$worksheetNames = $objReader->listWorksheetNames($value);

foreach ($worksheetNames as $sheetName) {



    $objPHPExcel = $objReader->load($content);





It works fine where there is only one worksheet, but when I am trying to read several file and duplicate the worksheet, it is throwing the above exception.

What I am trying to do is to read several xls file, and combine all their worksheet into one els document, so I want to create to read all the sheets and create one document. 

Thanks for your help.


Dec 19, 2011 at 4:33 PM

Did you create the additional worksheets in your new document? You are iterating over the worksheets in the objReader, but you are trying to set an active sheet on another object. But nowhere do you seem to create the new worksheets.


it works for one sheet, because that is created by default ;)

Dec 19, 2011 at 4:39 PM

Hell Borft,

Thanks for your reply, isn't the $objReader->load($content); doing the creation? Please advise.


Dec 19, 2011 at 10:19 PM

The load() method creates a new $objPHPExcel object in each iteration, overwriting the existing object instance in $objPHPExcel, it doesn't append to an existing object.

You'd need to create a new PHPExcel object $newPHPExcel, then do your loop and clone the loaded $objPHPExcel worksheet into $newPHPExcel using the addExternalSheet() method. But if you're going to loop through all worksheets in your file, why not simply load the whole workbook in the first place? Otherwise, if you just want one specific worksheet from the original in your new file, load that single sheet only, and save to a different filename (or php://output)