Few days back I was looking for merging the multiple excel  file, located at some specific path, in single excel file. To achieve that I used the library “PHPExcel”.

If you can download the git by clicking the below link

Download PHPExcel

To start with , you can create a file  of any name of your choice , lets say “Merge.php”

error_reporting(E_ALL);

ini_set('display_errors', TRUE);

define('EOL',(PHP_SAPI == 'cli') ? PHP_EOL : '<br />');

require_once dirname(__FILE__) . '/../Classes/PHPExcel.php'; -- This file is used to create the xlsx file

require_once dirname(__FILE__) . '/../Classes/PHPExcel/IOFactory.php'; -- This file is used to reading the xlsx file.

 

Below directory will hold all the xlsx files and we will scan through all files within the directory

$directory = './files/';

$masterfile = $directory."/master.xlsx";

foreach (scandir($directory) as $file) {

if ('.' === $file) continue;

if ('..' === $file) continue;

 

$inputFileName = $directory.'/'.$file;

//  Read your Excel workbook

try {

$inputFileType = PHPExcel_IOFactory::identify($inputFileName);

$objReader = PHPExcel_IOFactory::createReader($inputFileType);

$objPHPExcel = $objReader->load($inputFileName);

 

 

} catch(Exception $e) {

die('Error loading file "'.pathinfo($inputFileName,PATHINFO_BASENAME).'": '.$e->getMessage());

}

 

//  Get worksheet dimensions

$sheet = $objPHPExcel->getSheet(0);

$highestRow = $sheet->getHighestRow();

$highestColumn = $sheet->getHighestColumn();

 

//  Loop through each row of the worksheet in turn

for ($row = 1 ; $row <= $highestRow; $row++){

//  Read a row of data into an array

//$sheet->getCellValue('B'.$row);

$rowData = $sheet->rangeToArray('A' . $row . ':' . $highestColumn . $row,NULL,TRUE, FALSE);

$newData = array();

$newData  = $rowData[0];

 

if($row == 1) {

// skip the first row of the xlsx

}else{

$newData[1] = $filenameData[0];

}

if(file_exists($masterfile)) {

$minputFileType = PHPExcel_IOFactory::identify($masterfile);

$mobjReader =PHPExcel_IOFactory::createReader($minputFileType);

$mobjPHPExcel = $mobjReader->load($masterfile);

$sheetm = $mobjPHPExcel->getSheet(0);

$highestRowM = $sheetm->getHighestRow();

// This check is to give one line gap after each row of another xlsx file

if($row == 1) $highestRowM = $highestRowM + 1;

}else{

$highestRowM = $row;

}

// this function will create and write the excel data in master file.

processFile($masterfile,$newData,$highestRowM+1);                                         }

 

}

 

// Creation and writing of the master file is happening in this function

function processFile($wpath,$data,$row){

if(file_exists($wpath)) {

$objTpl = PHPExcel_IOFactory::load($wpath);

}else{

$objTpl = new PHPExcel(); // If file is not created .. this will be executed once

}

 

$objTpl->setActiveSheetIndex(0);  //set first sheet as active

// I have considered 6 columns , you can change the logic as per your requirement.

$objTpl->getActiveSheet()->setCellValue('A'.$row, $data[0]);

$objTpl->getActiveSheet()->setCellValue('B'.$row, $data[1]);

$objTpl->getActiveSheet()->setCellValue('C'.$row, $data[2]);

$objTpl->getActiveSheet()->setCellValue('D'.$row, $data[3]);

$objTpl->getActiveSheet()->setCellValue('E'.$row, $data[4]);

$objTpl->getActiveSheet()->setCellValue('F'.$row, $data[5]);

$objWriter = PHPExcel_IOFactory::createWriter($objTpl, 'Excel2007'); // this will create xlsx file . If you want xls file you please do necessary changes in the code

$objWriter->save($wpath);

 

}

That’s all . Hope this help you . Happy Coding ..