Thursday, October 4, 2018

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

Monday, October 1, 2018

How to add a space before chamel case charecter using PHP

Suppose you have a string with chamel case and now you want to add a space or something else before every chamel case charecter then you are on the right place. Here i am going to explain how can you do this by using minimal code and effort. 

So be here and read the complete article. Lets understand this by example.

Suppose you have some chamel case strings like below -

-- phpSollutions
--- faceBook
---- linkedIn
----- extraMark
------ partTimeJob

<?php

$string = "phpSollutions";

$pattern = '/([a-z])([A-Z])/s';

$replace = '${1} ${2}';

$output = preg_replace($pattern, $replace, $string);

echo $output;

?>


Output: Now the output will be looking like this-

-- php Sollutions
--- face Book
---- linked In
----- extra Mark
------ part Time Job


Please don't forgot to leave a comment if you like this post. 


Chears :)
Happy Coding..

Thursday, September 27, 2018

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 :)
Related Posts Plugin for WordPress, Blogger...