How to merge multiple excel files in a multiple excel sheet using PHP

Hey friends today i have again finished another complex task that i want to share with all of you. I need to combine multiple excel files in a multiple excel sheet. In my last tutorial you learn How to merge multiple excel files in single worksheet.
Here now i am going to explain how to merge multiple excel file in multiple sheet.

Requirenment :- My requirenment was i have a lots of files in a directory and i need to combine all these files data in a new excel file by adding all the excel data in seprate sheet. Lets understand this by an example. 

Suppose i have four excel file in a directory by the name of excel-1.xlsx, excel-2.xlsx, excel-3.xlsx, excel-4.xlsx. Now what i want to merge all these
file data into a single workbook by the name "merge-data.xlsx" in different sheet.

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];
$saving_name = "F:\commit-excel-merge-multiple\merge-data.xls";   //change the name and location of file.
require_once 'Classes/PHPExcel.php';
require_once 'Classes/PHPExcel/Writer/Excel2007.php';
require_once 'Classes/PHPExcel/IOFactory.php';

if ($dh = opendir($filePath)){
 $dataArray = array();
 $counter = 1;
 $file_name = array();
 $new_xl = new PHPExcel();
 while (($file = readdir($dh)) !== false) {
  $path_parts = pathinfo($file);  
  if ($path_parts['extension'] === 'xlsx') {   
   $singlefile = $path_parts["filename"]; //file name without extention
   echo "Start merging the content from file " . $singlefile .".xlsx" . PHP_EOL;
   $workbook_file = $filePath.'\\'. $file;
   $reader = PHPExcel_IOFactory::createReader('Excel2007');
   $xl = $reader->load($workbook_file);
   $firstSheet = $xl->getSheet(0);
   $workbook_name = $singlefile; 
   $firstSheet->setTitle($workbook_name);
   $new_xl->addExternalSheet($firstSheet);
   echo "End merging the content from file " . $singlefile . PHP_EOL;
  }
 }
 
 $new_xl->removeSheetByIndex(0);
 $writer = PHPExcel_IOFactory::createWriter($new_xl, 'Excel2007');
 // name of file, which needs to be attached during email sending
 $writer->save($saving_name);
        
 echo "**********************************************************************". PHP_EOL;
 echo "All the contents of excel file are successfully merged in sheet." . PHP_EOL;
 echo "**********************************************************************". PHP_EOL;
}

?>

Explain :

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

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

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

F:\commit-excel-merge-multiple\merge-excel> php merge_excel_data_in_multiple_sheet.php F:\commit-excel-merge-multiple\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-multiple\merge-excel\merge-excel.xlsx" file.

Final Directory Structure and Output :

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 :)