CODEWITHSUNDEEP
phpphpexcel
enchance
enchance

Reputation: 30411

PHPExcel: Combine 2 Excel files into 1 into the same worksheet

I have 2 excel files birds.xlsx and bees.xlsx both of which have the same number of columns and same type of column header. I've seen how PHPExcel does wonders with excel files but is there some way to combine 2 separate files into the same worksheet and saving it as a new file? The analogy that comes to mind is something like the SQL UNION command.

Upvotes: 1

Views: 3752

Answers (1)

Mark Baker
Mark Baker

Reputation: 212402

Something like:

// Load both spreadsheet files
$objPHPExcel1 = PHPExcel_IOFactory::load("birds.xlsx");
$objPHPExcel2 = PHPExcel_IOFactory::load("bees.xlsx");

// Find the last cell in the second spreadsheet
$findEndDataRow = $objPHPExcel2->getActiveSheet->getHighestRow();
$findEndDataColumn = $objPHPExcel2->getActiveSheet->getHighestColumn();
$findEndData = $findEndDataColumn . $findEndDataRow;
// Read all the data from second spreadsheet to a normal PHP array
//    skipping the headers in row 1
$beeData = $objPHPExcel2->getActiveSheet->rangeToArray('A2:' . $findEndData);

// Identify the row in the first spreadsheet where we want to start 
//     adding merged bee data without overwriting any bird data
$appendStartRow = $objPHPExcel1->getActiveSheet->getHighestRow() + 1;
// Add bee data from the PHP array into the bird data
$objPHPExcel1->getActiveSheet->fromArray($beeData, null, 'A' . $appendStartRow);

// Save the spreadsheet with the merged data
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel1, 'Excel2007');
$objWriter->save(str_replace('animals.xlsx');

Upvotes: 3

Related Questions