Merge 2 worksheets into 1?

Topics: Developer Forum, User Forum
Aug 9, 2012 at 6:08 PM

i have some(2 for example) different excel2007 files.

And i want to get one worksheet like that:

first_excel_file_with_one_worksheet
--empty line--
second_excel_file_with_one_worksheet
--empty line--
etc.

Any suggestions?

Coordinator
Aug 9, 2012 at 9:46 PM

I'd start by using the addExternalSheet() to move the worksheets from each workbook into separate worksheet's in a single PHPExcel workbook. That will rationalise all the style information from the different workbook.

Once you've done that, you'll need to read the relevant sections from each worksheet into an array (using the toArray() method) and then insert each aray into the one master worksheet at the appropriate position... but you'll need to watch out for any formulae in the copy, and ensure that they're adjusted using the ReferenceHelper's updateFormulaReferences() method. Then, clone the styles from the original worksheet and update them in the relevant range of the master worksheet before deleting the now copied sheet.

Watch out for any images, filters, conditional styles that might need copying as well.

 

Aug 9, 2012 at 10:57 PM
Edited Aug 9, 2012 at 10:59 PM

Thank you for the answer.

As for now, im doing something like this:

 

$objPHPExcel1 = new PHPExcel();
//adding some content to file1//
$objReader2 = PHPExcel_IOFactory::createReader('Excel2007');
$objPHPExcel2 = $objReader2->load('SOME_OTHER_XLSX_FILE.xlsx'); //open other file
$list2 = $objPHPExcel2->setActiveSheetIndex(0); //getting its sheet
$objPHPExcel->addSheet($list2); //adding it to the first file.

So i have a one xlsx file with a lot of worksheets.
Am i need to use addSheet()/AddExternalSheet() here? As i undersand, i should get two WorkSheets's, then get toArray() results of them, and then merge arrays, right?

addSheet
Coordinator
Aug 10, 2012 at 10:31 AM

Instead of

$objPHPExcel->addSheet($list2); //adding it to the first file.

use

$objPHPExcel->addExternalSheet($list2); //adding it to the first file.

otherwise you'll lose any styling and worksheet-level information from the "secondary" worksheets (number format masks, merges, filters, etc).