Combine two or more xls files as worksheets PHPExcel

Posted on

Combine two or more xls files as worksheets PHPExcel – Here in this article, we will share some of the most common and frequently asked about PHP problem in programming with detailed answers and code samples. There’s nothing quite so frustrating as being faced with PHP errors and being unable to figure out what is preventing your website from functioning as it should like php and phpexcel . If you have an existing PHP-based website or application that is experiencing performance issues, let’s get thinking about Combine two or more xls files as worksheets PHPExcel.

I’ve been looking everywhere on how to do this with two existing files, looks like all documentation is on creating new files. I’d like to take one of the files and add the second file to it as a new worksheet then save it to the server.
I’ve been trying with no avail like this:

$file="test.xls";
$file2="test2.xls";
$outputFile = "final.xls";
$phpExcel = new PHPExcel($file);
$phpExcel->getActiveSheet();
$phpExcel->setActiveSheetIndex(0);
$phpExcel->addSheet($file2);
header("Content-Type: application/vnd.ms-excel");
header("Content-Disposition: attachment; filename=$outputFile");
header("Cache-Control: max-age=0");
$objWriter = PHPExcel_IOFactory::createWriter($phpExcel, "Excel5");
file_put_contents($outputFile, $objWriter);

Any help would be greatly appreciated. Very new to PHP.

Solution :

Doesn’t anybody ever read documentation these days? There’s a whole document in the folder called /Documentation about reading files to PHPExcel objects (it’s called PHPExcel User Documentation - Reading Spreadsheet Files), together with dozens of examples (the /Documentation/Examples/Reader folder is a good place to look), and none of them use new PHPExcel($file). Nor do any of the examples or any of the documents say to use file_put_contents() when saving.

$file1="test.xls";
$file2="test2.xls";
$outputFile = "final.xls";

// Files are loaded to PHPExcel using the IOFactory load() method
$objPHPExcel1 = PHPExcel_IOFactory::load($file1);
$objPHPExcel2 = PHPExcel_IOFactory::load($file2);

// Copy worksheets from $objPHPExcel2 to $objPHPExcel1
foreach($objPHPExcel2->getAllSheets() as $sheet) {
    $objPHPExcel1->addExternalSheet($sheet)
}

// Save $objPHPExcel1 to browser as an .xls file
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel1, "Excel5");
header("Content-Type: application/vnd.ms-excel");
header("Content-Disposition: attachment; filename=$outputFile");
header("Cache-Control: max-age=0");
$objWriter->save('php://output');

    // update from office site

    $filenames = array('doc1.xlsx', 'doc2.xlsx');

    $bigExcel = new PHPExcel();
    $bigExcel->removeSheetByIndex(0);

    $reader = PHPExcel_IOFactory::createReader($input_file_type);

    foreach ($filenames as $filename) {
        $excel = $reader->load($filename);

        foreach ($excel->getAllSheets() as $sheet) {
            $bigExcel->addExternalSheet($sheet);
        }

        foreach ($excel->getNamedRanges() as $namedRange) {
            $bigExcel->addNamedRange($namedRange);
        }
    }

    $writer = PHPExcel_IOFactory::createWriter($bigExcel, 'Excel5');

    $file_creation_date = date("Y-m-d");

    // name of file, which needs to be attached during email sending
    $saving_name = "Report_Name" . $file_creation_date . '.xls';


    // save file at some random location    
    $writer->save($file_path_location . $saving_name);

    // More Detail : with different object: 
Merge multiple xls file into single one is explained here: 
I'm going to describe a bit different:

http://rosevinod.wordpress.com/2014/03/15/combine-two-or-more-xls-files-as-worksheets-phpexcel/

// Combine all .csv files into one .xls file,
$cache_dir = "/home/user_name/public_html/";
$book1 = $cache_dir . "book1.csv";
$book2 = $cache_dir . "book2.csv";       
$outputFile = $cache_dir . "combined.xls";
$inputFileType = 'CSV'; 
$inputFileNames = array($book1,$book2); 

$objReader = new PHPExcel_Reader_CSV();

/**  Extract the first named file from the array list  **/ 
$inputFileName = array_shift($inputFileNames); 
/**  Load the initial file to the first worksheet in a PHPExcel Object  **/ 
$objPHPExcel = $objReader->load($inputFileName); 
/**  Set the worksheet title (to the filename that we've loaded)  **/ 
$objPHPExcel->getActiveSheet()
            ->setTitle(pathinfo($inputFileName,PATHINFO_BASENAME)); 

/**  Loop through all the remaining files in the list  **/ 
foreach($inputFileNames as $sheet => $inputFileName) { 
     /**  Increment the worksheet index pointer for the Reader  **/ 
     $objReader->setSheetIndex($sheet+1); 
     /**  Load the current file into a new worksheet in PHPExcel  **/ 
     $objReader->loadIntoExisting($inputFileName,$objPHPExcel); 
     /**  Set the worksheet title (to the filename that we've loaded)  **/ 
     $objPHPExcel->getActiveSheet()->setTitle(pathinfo($inputFileName,PATHINFO_BASENAME)); 

} 

$objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);
$objWriter->save( $outputFile );

$objPHPExcel->disconnectWorksheets();
unset($objPHPExcel);

echo "DONE";

Leave a Reply

Your email address will not be published. Required fields are marked *