Combine two or more xls files as worksheets PHPExcel

Question

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.

Mark Baker · Accepted Answer · 2014-03-31T09:20:04.430

19

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');

edited Mar 31 '14 at 09:20

answered May 28 '13 at 21:38

Mark Baker

209,507
32
346
385

I really am ready to give up documenting anything to do with PHPExcel.... I can't make it any more obvious that Documentation is in the folder called `/Documentation` or that documents about reading spreadsheet files might be called `Reading spreadsheet files` – Mark Baker May 28 '13 at 21:44
Hey! you're the developer for this! I recognize your face from github. I tried searching through the docs on it. Sorry. I used file_put_contents() to save the file to the disk. – user2430227 May 28 '13 at 21:51
7

@MarkBaker if it makes you feel better, I love PHPExcel. It's made my life super easy. Keep up the good work man. Don't give up. I'm sure there are THOUSANDS who really appreciate the effort you put into this! :) – Bird87 ZA Oct 08 '13 at 14:54
Fatal error: Call to undefined method `PHPExcel::getWorksheets()` Why am I getting the above error..?? :-( – mithilatw Mar 22 '14 at 08:31
1

Try `$objPHPExcel2->getAllSheets()` instead. – Mark Baker Mar 22 '14 at 09:32

score 4 · Answer 2 · answered Mar 15 '14 at 04:56

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

score 0 · Answer 3 · answered Sep 23 '17 at 11:39

// 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";

Combine two or more xls files as worksheets PHPExcel

3 Answers3

Linked