PHPExcel, Copy sheet from one to another xls document with style

Question

I need to create an xls file using lists of other files

I do something like this:

    $objReader = PHPExcel_IOFactory::createReader('Excel5');
    $file_tmpl = $objReader->load('doc10.xls');

$file_tmpl - the resulting file

    $file1 = $objReader->load('doc11.xls');

$file1 - file that is copied sheet

    $file1->setActiveSheetIndex(1);
    $sheet = $file1->getActiveSheet();          
    $file_tmpl->addSheet($sheet,1);

As a result, the sheet is copied, except for the style of the cell: the borders, fonts, text size, text color. How to move all together with style?

Thank you.

Seems like [this](http://stackoverflow.com/questions/32712542/phpexcel-clone-sheet-and-keep-its-original-style) is your issue. — Gendos-ua, Feb 06 '16 at 15:00

score 2 · Accepted Answer · answered Feb 06 '16 at 15:07

2

There is a method built into PHPExcel for this: addExternalSheet() which copies the styling as well as the content from one workbook to another.

There is a script (43mergeWorkbooks.php) to demonstrate its use in the /Examples folder of PHPExcel

answered Feb 06 '16 at 15:07

Mark Baker

209,507
32
346
385

i can't find script (43mergeWorkbooks.php) in /Examples folder (40duplicateStyle.php) is the last. Version PHPExcel is 1.8.0 – Иван Васильев Feb 06 '16 at 15:32
Thank you! It's exactly what I looked for! – Иван Васильев Feb 06 '16 at 15:47

score 2 · Answer 2 · answered Sep 07 '16 at 06:25

$objPHPExcel = PHPExcel_IOFactory::load("x.xlsx");
$objPHPExcel1 = PHPExcel_IOFactory::load("y.xlsx");
foreach($objPHPExcel1->getSheetNames() as $sheetName)
{
    $sheet = $objPHPExcel1->getSheetByName($sheetName);
    $sheet->setTitle('Sheet'.$k);
    $objPHPExcel->addExternalSheet($sheet);
    unset($sheet);

}
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objPHPExcel->setActiveSheetIndex(0);
$file='x';

$filename = $file."_".@date("Y-m-d_H-i",time()).'.xlsx';
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="'.$filename.'"');
header('Cache-Control: max-age=0');
$objWriter->save('php://output');  //send it to user, of course you can save it to disk also!
exit; //done.. exiting!

score 0 · Answer 3 · answered Aug 09 '18 at 20:32

0

The PHPExcel library is DEPRECATED. They suggest that all users should migrate to its direct successor PhpSpreadsheet.

In the following link you will find an example of how to copy a worksheet from one workbook to another.

https://github.com/PHPOffice/PhpSpreadsheet/blob/develop/samples/Basic/43_Merge_workbooks.php

answered Aug 09 '18 at 20:32

Ragnar

4,393
1
27
40

1

now is https://github.com/PHPOffice/PhpSpreadsheet/blob/master/samples/Basic/43_Merge_workbooks.php – Ehcnalb Mar 04 '22 at 14:56

PHPExcel, Copy sheet from one to another xls document with style

3 Answers3