Merge multiple excel files in single excel sheet using PHP

Today i have finished a task in which i have to combine multiple excel files i a single excel sheet. After spending a lot of time finally i found the solution which i want to share with all of you.

Requirenment :- Actually my requirenment is i have more than 500 files in a directory and i need to combine all these files data in a new excel file.

Below is my excel files folder structure. It will help you to better understand my requirenment. See this

To fulfill my requirement i am using PHPEXCEL library and i encluded these three file of PHPEXCEL. Below is my the sample code -

<?php

$filePath =  $argv[1];
require_once 'Classes/PHPExcel.php';
require_once 'Classes/PHPExcel/Writer/Excel2007.php';
require_once 'Classes/PHPExcel/IOFactory.php';
if ($dh = opendir($filePath)){
 $outputFile = "F:\commit-excel-merge\merge-excel"; //Change the output file location.and file name.
 $objReader = PHPExcel_IOFactory::createReaderForFile($outputFile);
 $objReader->setReadDataOnly(true);
 $objPHPExcel1 = $objReader->load($outputFile);
 
 while (($file = readdir($dh)) !== false) {
  $path_parts = pathinfo($file);  
  if ($path_parts['extension'] === 'xlsx') {   
   $singlefile = $path_parts["filename"]; 
   echo "Start merging the content from file " . $singlefile . PHP_EOL;
   $workbook_file = $filePath.'\\'. $file;
   $objReader2 = PHPExcel_IOFactory::createReaderForFile($workbook_file);
   $objReader2->setReadDataOnly(true);
   $objPHPExcel2 = $objReader2->load($workbook_file);
   $objExcel2 = $objPHPExcel2->setActiveSheetIndex(0);

   $findEndDataRow2        = $objExcel2->getHighestRow();
   $findEndDataColumn2     = $objExcel2->getHighestColumn();
   $findEndData2 = $findEndDataColumn2 . $findEndDataRow2;

   $data2 = $objExcel2->rangeToArray('A1:' . $findEndData2);

   unset($objExcel2);
   
   $objExcel1 = $objPHPExcel1->setActiveSheetIndex(0);
   $appendStartRow = $objExcel1->getHighestRow() + 1;
   $objExcel1->fromArray($data2, null, 'A' . $appendStartRow);

   unset($data2);
   echo "End merging the content from file " . $singlefile . PHP_EOL;
  }
 } 
         
 $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel1, 'Excel2007');
 $objWriter->save($outputFile);
 
 echo "**********************************************************************". PHP_EOL;
 echo "All the contents of excel file are successfully merged in single file." . PHP_EOL;
 echo "**********************************************************************". PHP_EOL;
 
}
?>

Explain :

1- Go to the directory where you put your downloaded folder in command prompt. In the above example i put it into my "F" drive. So first i go into this folder

F:\commit-excel-merge\merge-excel>

2- Then i run the "merge_excel_data_in_single_sheet.php" file by using below command.

F:\commit-excel-merge\merge-excel> php merge_excel_data_in_single_sheet.php F:\commit-excel-merge\merge-excel\files

3- Now after finish the process you will see the data is now stored of all the excel file from "files" folder into "F:\commit-excel-merge\merge-excel\mergeFileData.xlsx" file.


Final Directory Structure :

You can also download the complete source code from my GIT HUB Directory

If you like this post or having any issue in this code. Please give your valuable comment.

Chears
Happy Coding :)