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
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 ..
